MySQL 查看 Binlog
1. 什么是 Binlog
在MySQL中，Binlog（二进制日志）是一种记录MySQL数据库所有修改操作的日志文件。它以二进制格式存储，包含了对数据库的增删改操作，可以用于数据恢复、数据复制、数据同步等应用场景。Binlog文件可以通过MySQL提供的工具进行查看和解析。
2. 查看 Binlog
2.1 查看 Binlog 文件列表
使用以下命令可以查看当前数据库实例的 Binlog 文件列表：
SHOW BINARY LOGS;
示例输出：
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| binlog.000001 | 1073741824 |
| binlog.000002 | 1073741824 |
| binlog.000003 | 1073741824 |
+------------------+-----------+
2.2 查看 Binlog 文件内容
使用以下命令可以查看指定 Binlog 文件的内容：
SHOW BINLOG EVENTS IN 'binlog.000001';
示例输出：
+------------------+-----+----------------+-----------+-------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos |
+------------------+-----+----------------+-----------+-------------+
| binlog.000001 | 4 | Format_desc | 1 | 123 |
| binlog.000001 | 123 | Query | 1 | 456 |
| binlog.000001 | 456 | Xid | 1 | 789 |
+------------------+-----+----------------+-----------+-------------+
2.3 解析 Binlog 内容
可以使用MySQL官方提供的mysqlbinlog工具来解析和查看Binlog文件。以下是查看Binlog文件内容的示例命令：
mysqlbinlog binlog.000001
示例输出：
#190610 11:30:00 server id 1 end_log_pos 123 Start: binlog v 4, server v 5.7.26-log created 190610 11:30:00 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
XQAAAAEAAAABAAQAAAALAAABAAgICAAAAAIAAAABAAgAAAAAAAAACAA+AAgAAKAAUAAgAAKAAVAAgAAKAA
WAAgAAKAAagAgAAKAAawAgAAKAAagBFAAAAAAAAAAEAAAAMAAABAAQAAAAGAAgAAAAIAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAA==
'/*!*/;
DELIMITER ;
# End of log file
2.4 解析 Binlog 文件中的 SQL 语句
可以使用以下命令将 Binlog 文件中的二进制日志转换为可读的 SQL 语句：
mysqlbinlog binlog.000001 --base64-output=DECODE-ROWS > binlog.sql
输出的 binlog.sql 文件中包含了所有的 SQL 语句，可以使用文本编辑器或命令行工具查看。
3. 总结
通过查看和解析 Binlog 文件，我们可以了解数据库的修改历史，进行数据恢复、数据复制等操作。MySQL 提供了多种工具和命令来实现这些功能，如 SHOW BINARY LOGS、SHOW BINLOG EVENTS 和 mysqlbinlog 等。在实际应用中，我们可以根据具体的需求选择合适的工具和命令来处理 Binlog 文件。
