【整理】MySQL 之 日志

2022-04-26 00:43:22


MySQL 中的各种日志文件 

1. 错误日志 
--log-error[=file_name] 
      错误日志记录了 mysql server 运行过程中所有较为严重的警告和错误信息，以及 mysql 每次启动和关闭的详细信息。 
      错误日志默认放在数据目录下，以 hostname.err 命名。但是可以使用命令 --log-error[=file_name] 修改其存放目录和文件名。 
      有时候，希望将错误日志做备份并重新开始记录，使用 flush logs 命令备份文件以 .old 结尾。 

2. 二进制日志 
--log-bin[=file_name] 
      即 binlog ，是 mysql 最为重要的日志之一。 
      在通过 --log-bin[=file_name] 启动功能之后，mysql 会将所有修改数据库数据的 query 以二进制的形式记录到日志文件中，其中包括每一条 query 的执行时间，消耗的资源，以及相关事务信息。如果没有指定 file_name ，会在数据目录下记录为 mysql-bin.**** 。 

附加选项参数： 
--max_binlog_size   设置 binlog 的最大存储上限，当日志到达这个上限的时候，会重新创建一个文件记录。 
--binlog-do-db=db_name   参数告诉 mysql 只对某个数据库记录 binlog 。 
--binlog-ignore-db=db_name   参数告诉 mysql 忽略对某个数据库记录 binlog 。 

3. 更新日志 
mysql5.0 以后不支持，和 binlog 类似，但是不是以二进制形式记录，是简单的文本格式记录。 

4. 查询日志 
--log[=file_name] 
      查询日志记录 mysql 中所有的 query ，可通过 --log[=file_name] 来打开该日志。由于记录了所有的 query ，开启后对性能也有较大的影响，默认的文件名 hostname.log 。 

5. 慢查询日志 
--log-slow-queries[=file_name] 
      通过 --log-slow-queries[=file_name] 来打开该功能并设置记录位置和文件名，默认文件名为 hostname-slow.log，默认目录也是数据目录。 

6. InnoDB 的在线的 REDO 日志 

      REDO 日志中记录了 InnoDB 所做的所有物理变更和事务信息。 
      通过 REDO 日志和 UNDO 信息，InnoDB 保证了在任何情况下的事务安全性。InnoDB 的 REDO 日志同样默认存放在数据目录下。 

通过 innodb_log_group_home_dir 来更改设置日志的存放位置。 
通过 innodb_log_files_in_group 设置日志的数量。 


============ 我是分割线 ============ 

设置自动清理 mysql binlog 日志和手动删除的方法 

（下面的描述是基于 MySQL 配置了主从复制工作模式下的 binlog） 

设置自动清理mysql binlog日志，配置 my.cnf： 
?
1
expire_logs_days = 10
在运行时修改： 
?
1
set global expire_logs_days = 10;
清除之前可以采用相应的备份策略。 

手动删除10天前的mysql binlog日志： 
?
1
2
PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
show master logs;

【MYSQL复制的几种模式】 

从 MySQL 5.1.12 开始，可以用以下三种模式来实现： 
  • 基于 SQL 语句的复制（statement-based replication, SBR）
  • 基于行的复制（row-based replication, RBR）
  • 混合模式复制（mixed-based replication, MBR）
相应地，binlog 的格式也有三种：STATEMENT，ROW，MIXED。  

在 MBR 模式中，SBR 模式是默认的。 

在运行时可以动态改动 binlog 的格式，除了以下几种情况： 
  1. 存储过程或者触发器中间；
  2. 启用了 NDB；
  3. 当前会话使用 RBR 模式，并且已打开了临时表。
如果 binlog 采用了 MIXED 模式，那么在以下几种情况下会自动将 binlog 的模式由 SBR 模式改成 RBR 模式： 
  1. 当 DML 语句更新一个 NDB 表时；
  2. 当函数中包含 UUID() 时；
  3. 2 个及以上包含 AUTO_INCREMENT 字段的表被更新时；
  4. 行任何 INSERT DELAYED 语句时；
  5. 用 UDF 时；
  6. 视图中必须要求运用 RBR 时，例如建立视图是运用了 UUID() 函数。
设定 binlog 格式： 
?
1
2
3
4
log-bin=mysql-bin
#binlog_format="STATEMENT"
#binlog_format="ROW"
binlog_format="MIXED"
也可以在运行时动态修改 binlog 的格式。例如： 
?
1
2
3
4
5
6
mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

两种模式各自的优缺点： 

SBR 的优点： 
  • 历史悠久，技能成熟；
  • binlog 文件较小；
  • binlog 中包含了所有数据库修改信息，可以据此来审核数据库的安全等情况；
  • binlog 可以用于实时的还原，而不仅仅用于复制；
  • 主从版本可以不一样，从服务器版本可以比主服务器版本高。
SBR 的缺点： 
  1. 不是所有的 UPDATE 语句都能被复制，尤其是包含不确定操作的时候；
  2. 调用具有不确定因素的 UDF 时复制也可能出疑问；
  3. 运用以下函数的语句也不能被复制：
    LOAD_FILE()
    UUID()
    USER()
    FOUND_ROWS()
    SYSDATE() (除非启动时启用了 –sysdate-is-now 选项)
  4. INSERT … SELECT 会产生比 RBR 更多的行级锁；
  5. 复制须要执行全表扫描（WHERE 语句中没有运用到索引）的 UPDATE 时，须要比 RBR 请求更多的行级锁；
  6. 对于有 AUTO_INCREMENT 字段的 InnoDB 表而言，INSERT 语句会阻塞其他 INSERT 语句；
  7. 对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记录产生影响；
  8. 存储函数（不是存储过程）在被调用的同时也会执行一次 NOW() 函数，这个可以说是坏事也可能是好事；
  9. 确定了的 UDF 也须要在从服务器上执行；
  10. 数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错；
  11. 执行复杂语句如果出错的话，会消耗更多资源。

RBR 的优点： 
  1. 任何情况都可以被复制，这对复制来说是最安全可靠的；
  2. 和其他大多数数据库系统的复制技能一样；
  3. 多数情况下，从服务器上的表如果有主键的话，复制就会快了很多；
  4. 复制以下几种语句时的行锁更少：
    INSERT … SELECT
    包含 AUTO_INCREMENT 字段的 INSERT
    没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句
  5. 执行 INSERT，UPDATE，DELETE 语句时锁更少；
  6. 从服务器上采用多线程来执行复制成为可能；

RBR 的缺点： 
  1. binlog 大了很多；
  2. 复杂的回滚时 binlog 中会包含大量的数据；
  3. 主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一次，这会导致频繁发生 binlog 的并发写疑问；
  4. UDF 产生的大 BLOB 值会导致复制变慢；
  5. 不能从 binlog 中看到都复制了写什么语句（加密过的)；
  6. 当在非事务表上执行一段堆积的SQL语句时，最好采用 SBR 模式，否则很容易导致主从服务器的数据不一致情况发生；
另外，针对系统库 mysql 里面的表发生变化时的处理准则如下： 
如果是采用 INSERT，UPDATE，DELETE 直接操作表的情况，则日志格式根据 binlog_format 的设定而记录； 
如果是采用 GRANT，REVOKE，SET PASSWORD 等管理语句来做的话，那么无论如何都采用 SBR 模式记录。 


========== 我是分割线 ========== 


一般来说，开启 binlog 大概会有 %1 的性能损耗（参见 MySQL 官方中文手册 5.1.24 版）。 

binlog 有两个最重要的使用场景： 
  1. MySQL Replication 在 Master 端开启 binlog 。Master 把它的 binlog 传递给 slaves 来达到 master-slave 数据一致的目的。
  2. 数据恢复。通过使用 mysqlbinlog 工具来即使恢复数据。
binlog 包括两类文件： 
  1. binlog 索引文件（文件名后缀为 .index），用于记录所有的二进制文件；
  2. binlog 文件（文件名后缀为 .00000*），记录数据库所有的 DDL 和 DML （除了数据查询语句）语句事件。

MySQL 默认关闭了 binlog ，因此在 MySQL 安装完成过后需要手动设置。在配置文件中设置： 
?
1
2
[mysqld]
Log-bin=”二进制日志文件存储路径/文件名” （默认路径为数据目录，二进制文件）
show master status;   -- 查看 master 当前正在使用哪个 binlog 文件。 
?
1
2
3
4
5
6
7
8
9
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 
mysql>
show binary logs;   -- 查看所有二进制日志文件及其大小。 
?
1
2
3
4
5
6
7
8
9
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
+------------------+-----------+
1 row in set (0.00 sec)
 
mysql>
注：重启 MySQL binlog 文件达到默认最大值（1G），系统会开启新的二进制文件。 

show binlog events [in 'log_name'][from pos][limit [offset] row_count];  -- 用于显示 binlog 中的事件。如果不指定'log_name'，则默认显示第一个 binlog 日志。   
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.10-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
1 row in set (0.00 sec)
 
mysql>
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.10-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
1 row in set (0.00 sec)
 
mysql>
flush logs;  -- 告诉服务器关闭当前的 binlog 文件并创建一个新文件。 
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
+------------------+-----------+
1 row in set (0.00 sec)
 
mysql>
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
 
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       167 |
| mysql-bin.000002 |       120 |
+------------------+-----------+
2 rows in set (0.00 sec)
 
mysql>
purge master logs;  -- 可以根据实际情况删除过期的 binlog 文件。 
PURGE {MASTER | BINARY} LOGS TO 'log_name'; 
PURGE {MASTER | BINARY} LOGS BEFORE 'date'; 
      用于删除存在于在指定日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除，这样的话，被指定的日志将成为第一个。 
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
 
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       167 |
| mysql-bin.000002 |       167 |
| mysql-bin.000003 |       120 |
+------------------+-----------+
3 rows in set (0.00 sec)
 
mysql> purge master logs to 'mysql-bin.000003';
Query OK, 0 rows affected (0.00 sec)
 
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000003 |       120 |
+------------------+-----------+
1 row in set (0.00 sec)
 
mysql>
purge 之前 
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[root@Betty data]# ll
total 176420
-rw-r----- 1 mysql root     68343 Mar 18 13:19 Betty.err
-rw-rw---- 1 mysql mysql        6 Mar 18 13:19 Betty.pid
-rw-rw---- 1 mysql mysql       56 Feb 21 14:20 auto.cnf
-rw-rw---- 1 mysql mysql 50331648 Mar 18 13:19 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Feb 21 14:17 ib_logfile1
-rw-rw---- 1 mysql mysql 79691776 Mar 18 13:19 ibdata1
drwxr-xr-x 2 mysql mysql     4096 Feb 21 14:17 mysql
-rw-rw---- 1 mysql mysql      167 Mar 18 13:31 mysql-bin.000001
-rw-rw---- 1 mysql mysql      167 Mar 18 13:35 mysql-bin.000002
-rw-rw---- 1 mysql mysql      120 Mar 18 13:35 mysql-bin.000003
-rw-rw---- 1 mysql mysql       57 Mar 18 13:35 mysql-bin.index
drwx------ 2 mysql mysql     4096 Feb 21 14:17 performance_schema
drwxr-xr-x 2 mysql mysql     4096 Feb 21 14:02 test
[root@Betty data]#
[root@Betty data]# cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
[root@Betty data]#
purge 之后 
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[root@Betty data]#
[root@Betty data]# cat mysql-bin.index
./mysql-bin.000003
[root@Betty data]# ll
total 176412
-rw-r----- 1 mysql root     68343 Mar 18 13:19 Betty.err
-rw-rw---- 1 mysql mysql        6 Mar 18 13:19 Betty.pid
-rw-rw---- 1 mysql mysql       56 Feb 21 14:20 auto.cnf
-rw-rw---- 1 mysql mysql 50331648 Mar 18 13:19 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Feb 21 14:17 ib_logfile1
-rw-rw---- 1 mysql mysql 79691776 Mar 18 13:19 ibdata1
drwxr-xr-x 2 mysql mysql     4096 Feb 21 14:17 mysql
-rw-rw---- 1 mysql mysql      120 Mar 18 13:35 mysql-bin.000003
-rw-rw---- 1 mysql mysql       19 Mar 18 13:36 mysql-bin.index
drwx------ 2 mysql mysql     4096 Feb 21 14:17 performance_schema
drwxr-xr-x 2 mysql mysql     4096 Feb 21 14:02 test
[root@Betty data]#
reset master;  -- 可以删除列于索引文件（.index ）中的所有 binlog ，把 binlog 的索引文件重置为空，再创建一个新的 binlog 文件。 
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000003 |       120 |
+------------------+-----------+
1 row in set (0.00 sec)
 
mysql>
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
 
mysql>
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
+------------------+-----------+
1 row in set (0.00 sec)
 
mysql>

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
[root@Betty data]# cat mysql-bin.index
./mysql-bin.000003
[root@Betty data]# ll
total 176412
-rw-r----- 1 mysql root     68343 Mar 18 13:19 Betty.err
-rw-rw---- 1 mysql mysql        6 Mar 18 13:19 Betty.pid
-rw-rw---- 1 mysql mysql       56 Feb 21 14:20 auto.cnf
-rw-rw---- 1 mysql mysql 50331648 Mar 18 13:19 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Feb 21 14:17 ib_logfile1
-rw-rw---- 1 mysql mysql 79691776 Mar 18 13:19 ibdata1
drwxr-xr-x 2 mysql mysql     4096 Feb 21 14:17 mysql
-rw-rw---- 1 mysql mysql      120 Mar 18 13:35 mysql-bin.000003
-rw-rw---- 1 mysql mysql       19 Mar 18 13:36 mysql-bin.index
drwx------ 2 mysql mysql     4096 Feb 21 14:17 performance_schema
drwxr-xr-x 2 mysql mysql     4096 Feb 21 14:02 test
[root@Betty data]#
 
[root@Betty data]#    
[root@Betty data]# cat mysql-bin.index
./mysql-bin.000001
[root@Betty data]#
[root@Betty data]# ll
total 176412
-rw-r----- 1 mysql root     68343 Mar 18 13:19 Betty.err
-rw-rw---- 1 mysql mysql        6 Mar 18 13:19 Betty.pid
-rw-rw---- 1 mysql mysql       56 Feb 21 14:20 auto.cnf
-rw-rw---- 1 mysql mysql 50331648 Mar 18 13:19 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Feb 21 14:17 ib_logfile1
-rw-rw---- 1 mysql mysql 79691776 Mar 18 13:19 ibdata1
drwxr-xr-x 2 mysql mysql     4096 Feb 21 14:17 mysql
-rw-rw---- 1 mysql mysql      120 Mar 18 13:41 mysql-bin.000001
-rw-rw---- 1 mysql mysql       19 Mar 18 13:41 mysql-bin.index
drwx------ 2 mysql mysql     4096 Feb 21 14:17 performance_schema
drwxr-xr-x 2 mysql mysql     4096 Feb 21 14:02 test
show binlog events;  -- 在 mysql 命令行上查看 binlog 中的 event 内容。 
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
+------------------+-----------+
1 row in set (0.00 sec)
 
mysql>
mysql> show binlog events in "mysql-bin.000001"\G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 120
       Info: Server ver: 5.6.10-log, Binlog ver: 4
1 row in set (0.00 sec)
 
ERROR:
No query specified
 
mysql>
mysql>
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
 
mysql>
mysql> show binary logs;   
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       167 |
| mysql-bin.000002 |       120 |
+------------------+-----------+
2 rows in set (0.00 sec)
 
mysql> show binlog events in "mysql-bin.000001"\G;     
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 120
       Info: Server ver: 5.6.10-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 120
 Event_type: Rotate
  Server_id: 1
End_log_pos: 167
       Info: mysql-bin.000002;pos=4
2 rows in set (0.00 sec)
 
ERROR:
No query specified
 
mysql>
mysql> show binlog events in "mysql-bin.000002"\G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000002
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 120
       Info: Server ver: 5.6.10-log, Binlog ver: 4
1 row in set (0.00 sec)
 
ERROR:
No query specified
 
mysql>

Format_desc：每一个 binlog 文件的第一个 event ，在这个 event 中记录了一些诸如 binary log 格式版本，产生这个 event 的 mysql server 版本等等。 
Rotate：一般来说，这是每个 binlog 文件的结束 event 。 

  • 当使用 flush logs 命令时，mysql 会自动在当前正在使用的 binlog 文件末尾加上这个 rotate 事件，然后就不再使用这个 binlog 了，同时会创建一个新的 binlog 文件，然后自动在新创建的这个 binlog 中加上一个 Format_desc event，并且更新 binlog index 文件(将这个新创建的binlog文件名添加进入)。
  • 每次重启 mysqld（通过 mysqladmin 关闭，再手动重启）的时候，mysql 也会关闭当前 binlog ，并且写入一个 Stop event（注意不是 rotate event），然后再次启动的时候会和上面一样创建新的 binlog 文件并作相关工作。
  • 通过 kill 命令直接杀死掉 mysql ，这个时候发生没有任何 event 写入。
  • 当 binlog 文件的大小达到 max_binlog_size 时，会开启新的 binlog 文件。
      其他还有很多 event 类型，最常见的可能就是 Query 了，update、delete 等操作都是 Query 类型呢。通常一个 update 操作在 binlog 文件中会产生好几个 event，因为为了保证主从一致，需要确保很多外部因素的一致。 

      在这里我们引入一个分组的概念：除了 format_desc 和 rotate ，其他的 event 我们往往可以对其进行分组，对于事务型数据库来说，往往认为一个事务就是一个分组，对于非事务型数据库和类似 create、alter 这类语句来说，一条语句本身就是一个分组。 
      通常一个分组内的语句要么全都执行，要么全都不执行，在 replication 中，如果 slave 在执行一个分组内的某条语句时，mysql 突然中断了，那么 mysql 会重新从头开始这个分组，而非从中断点继续。这就能很好的保证了事务的原则。 


上一篇：关于OA中权限越级的问题


下一篇：双剑合壁 畅快云端 新华三云学苑和教育版云平台开启“未来校园”