mysql不可逆的加密算法有哪些 mysql不可能出现的日志

MySQL：（十一）MySQL日志

（一）关系型数据库基础

（二）MySQL安装

（三）管理数据库和表

（四）用户和权限管理

（五）函数，存储过程和触发器

（六）MySQL架构

（七）存储引擎

（八）MySQL服务器选项，系统和状态变量

（九）优化查询和索引管理

（十）锁和事务管理

（十一）日志管理

（十二）备份还原

（十三）MySQL集群

mysql日志类型

• 事务日志 transaction log
• 错误日志 error log
• 通用日志 general log
• 慢查询日志 slow query log
• 二进制日志 binary log
• 中继日志 reley log

事务日志

事务日志

• myisam不支持事务，因此事务日志与myisam无关。
• 事务日志文件： ib_logfile0， ib_logfile1
• 事务日志的写入类型为“追加”，因此其操作为“顺序IO”；通常也被称为：预写式日志 write ahead logging
• 注：事务即使没有提交，事务日志仍会记录
• 默认 ib_logfile0， ib_logfile1 两个文件固定大小为5M，文件被写满，前面的日志将会被覆盖
• 查询不会记录事务日志，事务日志只记录增删改操作即DDL（insert,update,delete）
• 注意：

事务日志相关变量

• 事务日志：transaction log
• 事务型存储引擎自行管理和使用，建议和数据文件分开存放
  redo log
  undo log
• Innodb事务日志相关配置：
  |show variables like ‘%innodb_log%’;|MariaDB version:5.5.60|
  |–|--|
  | innodb_log_file_size 5242880 | 每个日志文件大小 |
  | innodb_log_files_in_group 2| 日志组成员个数 |
  | innodb_log_group_home_dir ./| 事务文件路径
  | innodb_flush_log_at_trx_commit |默认为1 |

> show variables like ‘innodb_log%’;	MariaDB version:10.2.27
innodb_log_arch_dir
innodb_log_arch_expire_sec	0
innodb_log_archive	OFF
innodb_log_block_size	0
innodb_log_buffer_size	16777216
innodb_log_checksum_algorithm	DEPRECATED
innodb_log_checksums	ON
innodb_log_compressed_pages	ON
innodb_log_file_size	50331648
innodb_log_files_in_group	2
innodb_log_group_home_dir	./
innodb_log_optimize_ddl	ON
innodb_log_write_ahead_size	8192

|优化建议：
|
|①事务日志单独存放在一个独立的分区，因为事务日志是顺序往里面写的单独分区可以加快效率
|②修改默认配置： innodb_log_file_size、innodb_log_group_home_dir

示例：事务日志存放路径

    ①建立事务日志存放目录，注意目录属性
# mkdir /data/logs/
# chown mysql.mysql /data/logs/

    ②修改配置文件
[mysqld]
# Transaction log related options
innodb_log_group_home_dir=/data/logs/
innodb_log_files_in_group=3
innodb_log_file_size=50M

    对students表进行增删改操作
> delete from students where stuid=39;
## ll  -h /data/logs/
total 150M
-rw-rw---- 1 mysql mysql 50M Sep 20 14:30 ib_logfile0
-rw-rw---- 1 mysql mysql 50M Sep 20 14:27 ib_logfile1
-rw-rw---- 1 mysql mysql 50M Sep 20 14:27 ib_logfile2
    因为事务文件为50M之后，一个文件的时间发生变化。

事务日志何时写？※※※※※

• innodb_flush_log_at_trx_commit 对服务器性能影响巨大
• innodb_flush_log_at_trx_commit
  |> show variables like ‘innodb_flush_log_at_trx_commit’;| 查询默认值|
  |–|--|
  | innodb_flush_log_at_trx_commit | 1 |
• 说明：设置为1，同时sync_binlog = 1表示最高级别的容错
  innodb_use_global_flush_log_at_trx_commit的值确定是否可以使用SET语句重置此变量
• 1默认情况下，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。这是完全遵守事务ACID特性
• 0提交时没有任何操作; 而是每秒执行一次日志缓冲区写入和刷新。 这样可以提供更好的性能，但服务器崩溃可能丢失最后一秒的事务
• 2每次提交后都会写入日志缓冲区，但每秒都会进行一次刷新。 性能比0略好一些，但操作系统或停电可能导致最后一秒的交易丢失
• 3模拟MariaDB 5.5组提交（每组提交3个同步），此项MariaDB 10.0支持

innodb_flush_log_at_trx_commit=[0|1|2]的具体差别

• 图中有左边有三个不同的内存空间依次为：
  修改数据的内存空间、
  事务日志的缓冲区（mysql事务日志使用的）、
  OS缓冲区（与应用程序无关）、
• 因此innodb_flush_log_at_trx_commit=[0|2]使得mysql的增删改性能更强
• 如果数据库崩溃可能丢失数据，最多丢失1s的数据，即事务已经提交，数据并未修改。

性能测试：
[mysqld]
innodb_flush_log_at_trx_commit=2

> call proc_test;
Query OK, 1 row affected (5.71 sec)

[mysqld]
innodb_flush_log_at_trx_commit=0

> call proc_test;
Query OK, 1 row affected (4.07 sec)

[mysqld]
innodb_flush_log_at_trx_commit=1

> call proc_test;
Query OK, 1 row affected (2 min 26.47 sec)

执行的存储过程的定义：
create table test (id int auto_increment primary key,name char(10));
delimiter $$

create procedure  proc_test() 
begin  
declare i int;
set i = 1; 
while i < 100000 
do  insert into test(name) values (concat('bob',i)); 
set i = i +1; 
end while; 
end$$

delimiter ;

错误日志

• 错误日志
  mysqld启动和关闭过程中输出的事件信息
  mysqld运行中产生的错误信息
  event scheduler运行一个event时产生的日志信息
  在主从复制架构中的从服务器上启动从服务器线程时产生的信息
• 错误日志相关配置
  SHOW GLOBAL VARIABLES LIKE ‘log_error’
  错误文件路径
  log_error=/PATH/TO/LOG_ERROR_FILE
  是否记录警告信息至错误日志文件
  log_warnings=1|0 默认值1

错误日志记录路径
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log

查看错误日志：
# cat /var/log/mariadb/mariadb.log

若服务起不来，服务器选项配置错误等可查看错误日志定位错误。

通用日志

• 通用日志：记录对数据库的通用操作，包括错误的SQL语句
  文件：file，默认值
  表：table
• 通用日志相关设置
  general_log=ON|OFF
  general_log_file=HOSTNAME.log
  log_output=TABLE|FILE|NONE
• 通用日志文件方式存放的默认路径在datadir目录文件名为HOSTNAME.log
• 通用日志表：存放在mysql.general表中
• 只要对数据库做任何操作都会被记录，通用日志一般用来监控一段时间内对数据库做了哪些操作。

慢查询日志

• 慢查询日志：记录执行查询时长超出指定时长的操作
• slow_query_log=ON|OFF 开启或关闭慢查询
• long_query_time=N 慢查询的阀值，单位秒
• slow_query_log_file=HOSTNAME-slow.log 慢查询日志文件

log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
上述查询类型且查询时长超过long_query_time，则记录日志

• log_queries_not_using_indexes=ON 不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录
• log_slow_rate_limit = 1 多少次查询才记录，mariadb特有
• log_slow_verbosity= Query_plan,explain 记录内容
• log_slow_queries = OFF 同slow_query_log 新版已废弃

上述慢查询相关变量系统的默认值：
> select @@var;查询变量的值

变量	系统默认值
slow_query_log	0
long_query_time	10
slow_query_log_file
注：慢查询不是只记录select。

慢查询测试：
慢查询日志文件：/datadir/centos7-17-slow.log
[mysqld]
# Slow query related options
slow_query_log
long_query_time=3
[log_queries_not_using_indexes]
[log_slow_rate_limit = # ]


> select *,sleep(1) from teachers;
+-----+---------------+-----+--------+----------+
| TID | Name          | Age | Gender | sleep(1) |
+-----+---------------+-----+--------+----------+
|   1 | Song Jiang    |  45 | M      |        0 |
|   2 | Zhang Sanfeng |  94 | M      |        0 |
|   3 | Miejue Shitai |  77 | F      |        0 |
|   4 | Lin Chaoying  |  93 | F      |        0 |
+-----+---------------+-----+--------+----------+
4 rows in set (4.01 sec)

# tail -f /data/mysql/centos7-17-slow.log 
# Time: 190920 15:34:34
# User@Host: root[root] @ localhost []
# Thread_id: 9  Schema: hellodb  QC_hit: No
# Query_time: 4.007591  Lock_time: 0.000556  Rows_sent: 4  Rows_examined: 4
# Rows_affected: 0
SET timestamp=1568964874;
select *,sleep(1) from teachers;

慢查询可以结合explain命令分析SQL语句为什么，查新是否利用索引。。。

二进制日志

二进制日志的特点

• 记录导致数据改变或潜在导致数据改变的SQL语句
• 记录对数据库增删改和对表的增删改操作
• 与存储引擎类型无关
• 记录已提交的日志
• 记录的是执行后的SQL语句转化为二进制的信息

此处的提交是针对innodb的，以事务方式提交
而myisam则是相当于执行一个操作，就被二进制日志记录

由于二进制日志记录的是执行后的SQL语句转化的二进制信息，可以通过一定方式
将二进制日志阅读，即可了解对数据库的操作。

二进制日志对数据库的还原至关重要！

• 功能：通过“重放”日志文件中的事件来生成数据副本
  注意：

二进制日志记录格式

• 二进制日志记录三种格式
• ①基于“语句”记录：statement，记录语句，默认模式
• ②基于“行”记录：row，记录数据，日志量较大
• ③混合模式：mixed, 让系统自行判定该基于哪种方式进行
• 格式配置
  show variables like ‘binlog_format’;

二进制日志文件的构成

• 二进制文件由两类文件构成
• 日志文件：mysql|mariadb-bin.文件名后缀，二进制格式
  如： mariadb-bin.000001
• 索引文件：mysql|mariadb-bin.index，文本格式

二进制日志相关的服务器变量

• sql_log_bin=ON|OFF：
  是否记录二进制日志，默认ON
  sql_log_bin可以动态更改，临时关闭二进制，说明：
• log_bin=/PATH/BIN_LOG_FILE：
  指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可
  其中log_bin=/path/filename 写入配置文件时默认是二进制文件的文件名
• binlog_format=STATEMENT|ROW|MIXED
  二进制日志记录的格式，默认STATEMENT
• max_binlog_size=1073741824
  单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
  说明：文件达到上限时的大小未必为指定的精确值
• sync_binlog=1|0
  设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
• expire_logs_days=N：
  二进制日志可以自动删除的天数。 默认为0，即不自动删除
  ~~做过备份的二进制文件可以删，根据需要设定一个二进制日志删除时间
  1.启用示例：

二进制日志的启用：
    sql_log_bin=ON  and log_bin=/PATH/BIN_LOG_FILE 均开启才启用
==> sql_log_bin=ON 可以支持动态更改
==> log_bin是只读变量，因为表示二进制文件的路径

注：sql_log_bin系统变量，并非选项


log_bin  # 文件名路径 默认为/var/lib/mysql/mariadb-bin.*
[mysqld]
# Binary log related options
log_bin=/data/binlogs/mysql-bin  #后缀会自动补

# ll /data/binlogs/
-rw-rw----. 1 mysql mysql 245 Sep 20 16:45 mysql-bin.000001
-rw-rw----. 1 mysql mysql  31 Sep 20 16:45 mysql-bin.index
==>二进制文件包含二进制文件的索引文件和记录日志文件
==>mysql-bin.00000# 的初始文件大小均为245
==>mysql-bin.index 文本文件，记录的是二进制文件的文件名

只有对数据进行增删改操作：提交的事务才会记录哈~
> update students set classid=1 where classid is null;
# ll /data/binlogs/
-rw-rw----. 1 mysql mysql 460 Sep 20 16:50 mysql-bin.000001
-rw-rw----. 1 mysql mysql  31 Sep 20 16:45 mysql-bin.index
    二进制日志均记录

> drop table coc ;
# ll /data/binlogs/
-rw-rw----. 1 mysql mysql 568 Sep 20 16:52 mysql-bin.000001
    可见不仅仅记录DML语言、只要是对数据库增删改都将记录

2.二进制日志记录格式示例：

binlog_format=STATEMENT|ROW|MIXED
    STATEMENT：语句型，记录的就是SQl语句
    ROW：每个记录的变化均记录
    MIXED：由数据库决定什么时候记录STATEMENT什么时候记录ROW

当然首先得看到二进制文件记录的内容，才能知道记录文件格式的差异：
# mysqlbinlog /data/binlogs/mysql-bin.000001 查看二进制日志文件内容
# at 316
#190920 16:50:30 server id 1  end_log_pos 433 	Query	thread_id=2	exec_time=0	error_code=0
use `hellodb`/*!*/;
SET TIMESTAMP=1568969430/*!*/;
update students set classid=1 where classid is null
/*!*/;
...
# at 639
#190920 17:07:47 server id 1  end_log_pos 749 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1568970467/*!*/;
update students set classid=1 where stuid<=5
/*!*/;
==>update students set classid=1 where classid is null
    二进制文件记录SQL语句，其实classid为null的记录不只一行
==>update students set classid=1 where stuid<=5
    其中stuid<=5的记录也不只一条
以语句形式存放就是完全的记录执行的SQL语句

STATEMENT记录格式的缺点：
语句形式存放可能导致问题：
    eg：以此种形式修改的某个字段的值时
> update students set age=(select avg(age) from teachers) where stuid=20;
二进制日志记录
# at 847
#190920 17:16:31 server id 1  end_log_pos 983 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1568970991/*!*/;
update students set age=(select avg(age) from teachers) where stuid=20
/*!*/;
    当某个时间对teachers表中的age进行修改时，则avg（age）变化
    则数据还原时，导致students的stuid=20的记录中age将变化
update students set age=(select avg(age) from teachers) where stuid=20
    不同时间执行此命令的结果可能不一样。

binlog_format=ROW
[mysqld]
# Binary log related options
log_bin=/data/binlogs/mysql-bin
binlog_format=ROW

重启服务：会生成另外的二进制文件
    即每次启动服务就会生成新的二进制文件

执行以下语句：
> update students set age=(select avg(age) from teachers) where stuid<5;

查看二进制日志文件：
# at 316
# at 375
#190920 17:25:53 server id 1  end_log_pos 375 	Table_map: `hellodb`.`students` mapped to number 36
#190920 17:25:53 server id 1  end_log_pos 499 	Update_rows: table id 36 flags: STMT_END_F

BINLOG '
IZuEXRMBAAAAOwAAAHcBAAAAACQAAAAAAAEAB2hlbGxvZGIACHN0dWRlbnRzAAYDDwH+AQMElgD3
ATA=
IZuEXRgBAAAAfAAAAPMBAAAAACQAAAAAAAEABv//wAEAAAALU2hpIFpob25neXUWAgEDAAAAwAEA
AAALU2hpIFpob25neXVIAgEDAAAAwAIAAAAKU2hpIFBvdGlhbhYCAQcAAADAAgAAAApTaGkgUG90
aWFuSAIBBwAAAA==
'/*!*/;
### UPDATE `hellodb`.`students`
### WHERE
###   @1=1
###   @2='Shi Zhongyu'
###   @3=22
###   @4=2
###   @5=1
###   @6=3
### SET
###   @1=1
###   @2='Shi Zhongyu'
###   @3=72
###   @4=2
###   @5=1
###   @6=3
### UPDATE `hellodb`.`students`
### WHERE
###   @1=2
###   @2='Shi Potian'
###   @3=22
###   @4=2
###   @5=1
###   @6=7
### SET
###   @1=2
###   @2='Shi Potian'
###   @3=72
###   @4=2
###   @5=1
###   @6=7
# at 499
#190920 17:25:53 server id 1  end_log_pos 526 	Xid = 16
COMMIT/*!*/;
# at 526
#190920 17:26:16 server id 1  end_log_pos 597 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1568971576/*!*/;
BEGIN
/*!*/;

==>二进制日志用行格式记录的优势：将每行和其中select调用的值均具体化
==>还原时将不受影响，


ROW记录格式的缺点：
    二进制日志记录的格式增长速度可能比数据库的增长还快
    代价是占用更大的磁盘空间

ROW记录格式的优势：
    若执行的SQL与别的表存在关系
    数据还原时不受影响

binlog_format=MIXED
    兼顾ROW和STATEMENT。其生成的对应的二进制日志文件略~~

[mysqld]
# Binary log related options
log_bin=/data/binlogs/mysql-bin
binlog_format=MIXED

重启服务，SQL语句测试

汇总：

• 生产中至少选择MIXED格式，或者ROW格式
• 因此数据库二进制文件需要监控，并且与数据库分离出来，放于独立分区。

二进制日志相关配置

• SHOW {BINARY | MASTER} LOGS
  查看mariadb自行管理使用中的二进制日志文件列表，及大小
• SHOW MASTER STATUS
  查看使用中的二进制日志文件
• SHOW BINLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]
  查看二进制文件中的指定内容
• show binlog events in ‘mysql-bin.000001’ from 6516 limit 2,3
  查看二进制文件中的指定内容

在数据库中使用SQL命令查询二进制日志相关信息：
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1029 |
| mysql-bin.000002 |       245 |
+------------------+-----------+
2 rows in set (0.00 sec)

MariaDB [hellodb]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1029 |
| mysql-bin.000002 |       245 |
+------------------+-----------+
2 rows in set (0.00 sec)

MariaDB [hellodb]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      245 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

二进制日志的客户端命令工具: mysqlbinlog

• 命令格式：
  mysqlbinlog [OPTIONS] log_file…
  –start-position=# 指定开始位置
  –stop-position=#
  –start-datetime=
  –stop-datetime=
  时间格式：YYYY-MM-DD hh:mm:ss
  –base64-output[=name]
  -v -vvv

二进制日志事件的格式：
# at 328
#151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1 exec_time=0 
error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1446712300/*!*/;
CREATE TABLE tb1 (id int, name char(30))
/*!*/; 
事件发生的日期和时间：151105 16:31:40
事件发生的服务器标识：server id 1
事件的结束位置：end_log_pos 431
事件的类型：Query 
事件发生时所在服务器执行此事件的线程的ID：thread_id=1
语句的时间戳与将其写入二进制文件中的时间差：exec_time=0
错误代码：error_code=0
事件内容：
GTID：Global Transaction ID，mysql5.6以mariadb10以上版本专属属性：GTID

使用二进制日志对其修改的改进行还原

> flush logs; 切换日志文件
状态1：> update students set age=55 where stuid=24;
# mysqlbinlog /data/binlogs/mysql-bin.000004>/root/binlog.sql
    将对应的上面修改的SQL命令导出

状态2：> update students set age=100 where stuid=24;再次更改

先利用导出的文件将更改的记录还原至状态1：
# mysql hellodb < binlog.sql
> select * from students; 对应记录的age=55，还原至状态1

二进制文件的清理

• 清除指定二进制日志：
  PURGE { BINARY | MASTER } LOGS
  { TO ‘log_name’ | BEFORE datetime_expr }
  示例：
  PURGE BINARY LOGS TO ‘mariadb-bin.000003’;删除3之前的日志
  PURGE BINARY LOGS BEFORE ‘2017-01-23’;
  PURGE BINARY LOGS BEFORE ‘2017-03-22 09:25:30’;
• 删除所有二进制日志，index文件重新记数
  RESET MASTER [TO #]; 删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从1开始，一般是master主机第一次启动时执行，MariaDB10.1.6开始支持TO #
• 切换日志文件：
  FLUSH LOGS;
• 注意：建议使用命令清理日志文件，不要使用rm删文件

删日志文件示例：
> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1029 |
| mysql-bin.000002 |       844 |
| mysql-bin.000003 |       494 |
| mysql-bin.000004 |       902 |
+------------------+-----------+
4 rows in set (0.00 sec)

> purge binary logs to 'mysql-bin.000003';
Query OK, 0 rows affected (0.01 sec)

]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000003 |       494 |
| mysql-bin.000004 |       902 |
+------------------+-----------+
2 rows in set (0.00 sec)

删除后对应的磁盘文件
# ll /data/binlogs/mysql-bin.*
-rw-rw----. 1 mysql mysql 494 Sep 20 18:29 /data/binlogs/mysql-bin.000003
-rw-rw----. 1 mysql mysql 902 Sep 20 18:34 /data/binlogs/mysql-bin.000004
-rw-rw----. 1 mysql mysql  62 Sep 20 18:46 /data/binlogs/mysql-bin.index
# cat /data/binlogs/mysql-bin.index
/data/binlogs/mysql-bin.000003
/data/binlogs/mysql-bin.000004

中继日志

• 中继日志：relay log
• 主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件

基于二进制日志如何实现主从复制：

• master服务器：启动一个Dump线程，Dump线程负责将二进制日日志中生成新的二进制日志发送给slave服务器
• slave服务器：开启io Thread的线程负责从master传来的二进制日志，然后专门生成一个从master接收的二进制日志的日志relay log（中继日志）。最后开启SQL thread线程将中继日志记录的SQL语句进行执行，来更新slave数据库。