参考文献

  • MySQL技术内幕 InnoDB存储引擎
  • MySQL 8 Cookbook

binlog

什么是binlog?

  • 二进制日志包含数据库的所有更改记录,包括数据和结构两方面.二进制不记录SELECT或SHOW等不修改数据的操作.
  • 运行带有二进制日志的服务器会带来轻微的性能影响.二进制日志能保证数据库出现故障时数据是安全的.
  • 只有完整的事件或事务才会被记录或者回读.

binlog格式

  • 该参数可设的值有STATEMENT,ROW,MIXED.

  • STATEMENT模式下,每一条会修改数据的sql都会记录在binlog中.不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能.由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息,同时还有一些使用了函数之类的语句无法被记录复制.

  • ROW格式,二进制日志记录的不再是简单的SQL语句了,而是记录表的行更改情况.从MySQL5.1版本开始,如果设置了binlog_format为ROW,可以将InnoDB的事务隔离级别设置为READ COMMITTED,以获得更好的并发性.

  • MIXED格式,默认采用STATEMENT格式进行二进制日志文件的记录,但是在一些情况下会使用ROW格式.可能的情况有:

    • 表的存储引擎未NDB,此时对表的DML操作都会以ROW格式记录.
    • 使用UUID(),USER(),CURRENT_USER()的不确定函数.
    • 使用了INSERT DELAY语句.
    • 使用了用户自定义函数(UDF).
    • 使用了临时表.

binlog文件内容

  • binlog会存储以下信息:
    1. 更新语句的类型:INSERT、UPDATE或DELETE.
    2. 被更新的表的名称和库名.
    3. 更新前和更新后的数据值.
    4. 更新的时间戳和事务ID.
    5. 执行更新操作的线程ID.
    6. MySQL所使用的字符集.
    7. 是否存在外键依赖关系.
1
2
3
4
5
# at 20707752
#240925 15:56:38 server id 1  end_log_pos 20707837 CRC32 0xd6fb8d61 	Query	thread_id=270	exec_time=0	error_code=0
SET TIMESTAMP=1727250998/*!*/;
BEGIN
/*!*/;
  • 在第一行中,#at后面的数字表示二进制日志文件中事件的起始位置(文件偏移量).
  • 第二行包含了语句在服务器上被启用的时间戳.时间戳后面跟着server id,end_log_pos,thread_id,exec_time和error_code
    • server id : 产生该事件的服务器的server_id值(240925)
    • end_log_pos: 下一个事件的开始位置
    • thread_id: 指示哪个线程执行了该事件.
    • exec_time: 在主服务器上,它代表执行事件的事件;在从服务器上,它代表从服务器的最终执行时间与主服务器的开始时间之间的差值,这个差值可以作为备份相对于主服务器滞后多少的指标.
    • error_code: 代表执行事件的结果.零意味着没有错误发生.

binlog有什么用?

  • 其主要作用包括:

    1. 数据恢复:binlog记录了数据库的所有更新操作,包括插入、更新和删除等操作,因此可以用于数据恢复.例如,如果一条误删除数据的SQL语句被执行了,可以根据从binlog中找到该操作的时间戳等关键信息来还原数据.

    2. 主从复制:binlog也是MySQL中用于实现主从复制的关键机制之一.在主数据库上,binlog记录了所有写操作的信息,可以将这些信息重新应用到从服务器中,从而保持所有数据的一致性.

    3. 增量备份:binlog可用于实现MySQL逐增备份(incremental backup)的机制.通过备份存储在binlog文件中的更新操作,可以在每次备份中仅备份新增的数据,从而提高备份效率.

    4. 审计与安全:binlog中包含了大量的数据库操作信息,例如执行每个更新操作的时间和用户等信息,在数据库审计和安全方面具有重要作用.

  • 总之,由于binlog记录了MySQL数据库中所有的写操作信息,因此可以用于数据库的数据恢复、主从复制和备份等方面.同时,binlog也可以用于数据库的安全审计和追踪

如何使用binlog?

  • 要启动二进制日志,必须设置log_bin和server_id并重新启动服务器.可以在log_bin内提及path和base名称.
    • 例如log_bin设置为/data/mysql/binlogs/server1,二进制日志存储在/data/mysql/binlogs文件夹中名为server1.000001,server1.000002等日志文件中.
  • 每当服务器启动或刷新日志时,或者当前日志的大小达到max_binlog_size时,服务器都会在系列中创建一个新文件.每个二进制日志的位置都在server1.index文件中维护.

启用二进制日志

  • 在MySQL的配置文件my.cnf中添加

    1
    2
    3
    [mysqld]
    log_bin = /data/mysql/binlogs/server1
    server_id = 100

  • 重新启动MySQL服务器: sudo systemctl restart mysql

binlog查看

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
mysql> show variables like 'log_bin%';
+---------------------------------+------------------------------------+
| Variable_name                   | Value                              |
+---------------------------------+------------------------------------+
| log_bin                         | ON                                 |
| log_bin_basename                | /usr/local/mysql/data/binlog       |
| log_bin_index                   | /usr/local/mysql/data/binlog.index |
| log_bin_trust_function_creators | OFF                                |
| log_bin_use_v1_row_events       | OFF                                |
+---------------------------------+------------------------------------+
5 rows in set (0.00 sec)

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000033 |     33869 | No        |
| binlog.000034 |       179 | No        |
| binlog.000035 |     92672 | No        |
| binlog.000036 |       179 | No        |
+---------------+-----------+-----------+

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000189 |  20708059 | No        |
+---------------+-----------+-----------+
1 row in set (0.01 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000189 | 20708059 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
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
73
74
75
76
77
mysql> set session binlog_format = statement;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `temp`.`wechat_official_account` ( `app_id`, `name`, `sort`) VALUES ('wxxxxx', 'AI', 2);
Query OK, 1 row affected (0.00 sec)

mysql> show master status\G
*************************** 1. row ***************************
             File: binlog.000091
         Position: 568
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

mysql> show binlog events in 'binlog.000091'\G
*************************** 1. row ***************************
   Log_name: binlog.000091
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 125
       Info: Server ver: 8.0.26, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: binlog.000091
        Pos: 125
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 156
       Info:
*************************** 3. row ***************************
   Log_name: binlog.000091
        Pos: 156
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 235
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: binlog.000091
        Pos: 235
 Event_type: Query
  Server_id: 1
End_log_pos: 317
       Info: BEGIN
*************************** 5. row ***************************
   Log_name: binlog.000091
        Pos: 317
 Event_type: Intvar
  Server_id: 1
End_log_pos: 349
       Info: INSERT_ID=5
*************************** 6. row ***************************
   Log_name: binlog.000091
        Pos: 349
 Event_type: Query
  Server_id: 1
End_log_pos: 537
       Info: use `temp`; INSERT INTO `temp`.`wechat_official_account` ( `app_id`, `name`, `sort`) VALUES ('wxxxxx', 'AI', 2)
*************************** 7. row ***************************
   Log_name: binlog.000091
        Pos: 537
 Event_type: Xid
  Server_id: 1
End_log_pos: 568
       Info: COMMIT /* xid=4800 */
7 rows in set (0.00 sec)
  • 可以通过命令SHOW BINLOG EVENTS可以查看二进制日志中的记录.
1
mysqlbinlog -vv  --start-position=155  binlog.000089

禁用会话的二进制日志

  • 有些情况下不希望将执行语句复制到其他服务器上.为此,可以使用以下命令来禁用该会话的二进制日志:

    1
    SET SQL_LOG_BIN = 0;

  • 在这条语句后的所有SQL都不会被记录到二进制日志中,不过这仅仅是针对该会话的.

  • 要重新启用二进制日志,可以执行以下操作:

    1
    SET SQL_LOG_BIN = 1;

清理二进制日志

  • 使用binlog_expire_logs_seconds和expire_logs_days设置日志的到期时间.
    • 如果想以天为单位设置到期时间,请设置expire_logs_days.例如,如果要删除两天之前的所有二进制日志,请SET @@global.expire_logs_days = 2,若将该值设置为0,则禁用设置会自动到期.
    • 若要设置更细粒度的到期时间,可以使用binlog_expire_logs_seconds,它能够以秒为单位来设置二进制日志过期时间.
    • binlog_expire_logs_seconds和expire_logs_days的效果是叠加的.
      • 例如expire_logs_days = 1并且binlog_expire_logs_seconds = 43200则二进制日志就会每1.5天清除一次.
    • 在MySQL中binlog_expire_logs_seconds和expire_logs_days必须设置为0,以禁止自动清除二进制日志
  • 要手动清除日志,可以执行PURGE BINARY LOGS TO '<file_name>',例如,有server1.000001,server1.000002,server1.000003,server1.000004如果执行PURGE BINARY LOGS TO 'server1.000004'则从server1.000001到server1.000003的所有文件都会被删除,但文件server1.000004不会被删除.
  • 要删除所有二进制日志并再次从头开始,请执行RESET MASTER

binlog的缺点

  • 事务不能太大,否则会导致二进制日志非常大,一个大事务的提交会非常慢
  • 处理办法:
    • 设计时,把DELETE删除操作转化为DROP TABLE/PARTITION操作
    • 业务设计时,把大事务拆成小事务