标题: 了解 MySQL binlog

分类: 工具
创建: 2024-02-20 21:44
修改:
链接: http://0x2531.tech/tools/202402202144.txt

--------------------------------------------------------------------------------

MySQL 中的 binlog（二进制日志）文件是 MySQL 中重要的日志文件，它记录了所有对 MySQL 数据库进行变
更的情况，如：增删改数据、建改删表等操作。

binlog 的主要功能如下：

1. 数据复制：在读写分离场景中，往往会向主库写数据，然后从库可以依赖 binlog 进行数据复制，确保数据一
致性；
2. 数据恢复：由于 binlog 中记录了所有的变更操作，因此可以用于数据恢复；
3. 合规审计：binlog 为二进制文件不易修改，且记录了所有的变更操作，因此可用于合规审计。

binlog 日志有两种格式：基于语句的复制（Statement-Based Replication，SBR）和基于行的复制（Row-
Based Replication，RBR）。SBR 记录的是 SQL 语句的原始内容，RBR 记录的是行的变更情况。查看变量
 binlog_format 值可知具体的格式，STATEMENT 对应 SBR、ROW 对应 RBR。

查看是否开启 binlog：

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

如果未开启，找到 MySQL 配置文件（Linux 下一般是 /etc/my.cnf 文件），将 log_bin 指令前 # 移除。

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
log_bin

接着，重启 MySQL 服务。

service mysqld restart  

重启成功后，在 MySQL 数据目录可以看到 binlog 相关的文件，其中，mysqld-bin.000001 是 binlog 
二进制文件，mysqld-bin.index 是管理 binlog 文件的文本文件。

查看 binlog 相关配置：

mysql> show variables like 'binlog%';
+-----------------------------------------+--------------+
| Variable_name                           | Value        |
+-----------------------------------------+--------------+
| binlog_cache_size                       | 32768        |
| binlog_checksum                         | CRC32        |
| binlog_direct_non_transactional_updates | OFF          |
| binlog_error_action                     | IGNORE_ERROR |
| binlog_format                           | STATEMENT    |
| binlog_gtid_simple_recovery             | OFF          |
| binlog_max_flush_queue_time             | 0            |
| binlog_order_commits                    | ON           |
| binlog_row_image                        | FULL         |
| binlog_rows_query_log_events            | OFF          |
| binlog_stmt_cache_size                  | 32768        |
| binlogging_impossible_mode              | IGNORE_ERROR |
+-----------------------------------------+--------------+
12 rows in set (0.00 sec)

接下来，我们可以做些数据变更操作。先插入一条记录，再删除之。

mysql> select employeeNumber,lastName,firstName,email,jobTitle from employees;
+----------------+-----------+-----------+---------------------------------+----------------------+
| employeeNumber | lastName  | firstName | email                           | jobTitle             |
+----------------+-----------+-----------+---------------------------------+----------------------+
|           1002 | Murphy    | Diane     | dmurphy@classicmodelcars.com    | President            |
|           1056 | Patterson | Mary      | mpatterso@classicmodelcars.com  | VP Sales             |
|           1076 | Firrelli  | Jeff      | jfirrelli@classicmodelcars.com  | VP Marketing         |
|           1088 | Patterson | William   | wpatterson@classicmodelcars.com | Sales Manager (APAC) |
|           1102 | Bondur    | Gerard    | gbondur@classicmodelcars.com    | Sale Manager (EMEA)  |
|           1143 | Bow       | Anthony   | abow@classicmodelcars.com       | Sales Manager (NA)   |
|           1166 | Jennings  | Leslie    | ljennings@classicmodelcars.com  | Sales Rep            |
+----------------+-----------+-----------+---------------------------------+----------------------+
7 rows in set (0.00 sec)

mysql> 
mysql> insert into employees (employeeNumber,lastName,firstName,extension,email,officeCode,jobTitle,title) values
 (1100,'aa','bb','cc','dd',1,'test',"just a title");
Query OK, 1 row affected (0.00 sec)

mysql> select employeeNumber,lastName,firstName,email,jobTitle from employees;
+----------------+-----------+-----------+---------------------------------+----------------------+
| employeeNumber | lastName  | firstName | email                           | jobTitle             |
+----------------+-----------+-----------+---------------------------------+----------------------+
|           1002 | Murphy    | Diane     | dmurphy@classicmodelcars.com    | President            |
|           1056 | Patterson | Mary      | mpatterso@classicmodelcars.com  | VP Sales             |
|           1076 | Firrelli  | Jeff      | jfirrelli@classicmodelcars.com  | VP Marketing         |
|           1088 | Patterson | William   | wpatterson@classicmodelcars.com | Sales Manager (APAC) |
|           1100 | aa        | bb        | dd                              | test                 |
|           1102 | Bondur    | Gerard    | gbondur@classicmodelcars.com    | Sale Manager (EMEA)  |
|           1143 | Bow       | Anthony   | abow@classicmodelcars.com       | Sales Manager (NA)   |
|           1166 | Jennings  | Leslie    | ljennings@classicmodelcars.com  | Sales Rep            |
+----------------+-----------+-----------+---------------------------------+----------------------+
8 rows in set (0.00 sec)

mysql> delete from employees where employeeNumber=1100;
Query OK, 1 row affected (0.01 sec)

使用 mysqlbinlog 工具将 binlog 二进制文件转换为 SQL 文本文件

mysqlbinlog --start-datetime="2024-02-20 00:00:00" --stop-datetime="2024-02-20 
19:00:00" /var/lib/mysql/mysqld-bin.000001 > output.sql

查看 output.sql，可以清楚的看到之前的插入和删除数据操作

......
BEGIN
/*!*/;
# at 851
#240220 18:10:00 server id 1  end_log_pos 1098 CRC32 0x36f4e45c         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1708423800/*!*/;
insert into employees (employeeNumber,lastName,firstName,extension,email,officeCode,jobTitle,title) values (1100,'aa','bb','cc','dd',1,'test',"just a title")
/*!*/;
# at 1098
#240220 18:10:00 server id 1  end_log_pos 1129 CRC32 0x9a0d0998         Xid = 7
COMMIT/*!*/;
# at 1129
#240220 18:10:58 server id 1  end_log_pos 1224 CRC32 0x99b3098b         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1708423858/*!*/;
BEGIN
/*!*/;
# at 1224
#240220 18:10:58 server id 1  end_log_pos 1361 CRC32 0xdc2d579a         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1708423858/*!*/;
delete from employees where employeeNumber=1100
/*!*/;
# at 1361
#240220 18:10:58 server id 1  end_log_pos 1392 CRC32 0xb9fa1a75         Xid = 9
COMMIT/*!*/;
......

如果想恢复该条记录，再次执行 insert 语句就好了，非常方便。当然，这个过程也可能相当复杂和耗时，尤其
是当数据库很大，或者删除操作发生在很久之前的时候。如果可能的话，定期备份数据库是一个更好的选择，这样
可以更容易地恢复被删除的数据。

综上所述，binlog 在数据复制、数据恢复和合规审计方面都有很好的应用。