mysql的binlog是二进制日志文件，记录了数据库所有的写操作，包括数据的增删改、表结构的变更等，是数据库备份恢复、主从复制的核心依赖文件。掌握binlog的分析方法，能帮助开发者快速定位数据异常原因，也能在误删数据后完成数据恢复。
binlog的基础配置与开启
首先要确保mysql已经开启了binlog功能，默认情况下部分mysql版本可能没有开启该配置。可以通过下面的命令查看binlog是否开启：
-- 查看binlog开启状态 SHOW VARIABLES LIKE 'log_bin';
如果返回的结果是log_bin对应的值为ON，说明已经开启了binlog。如果未开启，需要修改mysql的配置文件my.cnf或者my.ini，添加以下配置后重启mysql服务：
[mysqld] # 开启binlog log_bin=mysql-bin # binlog格式，可选STATEMENT、ROW、MIXED，推荐ROW binlog_format=ROW # 单个binlog文件大小 max_binlog_size=100M
使用mysql自带工具分析binlog
1. 查看binlog文件列表
可以通过下面的命令查看当前所有的binlog文件：
SHOW BINARY LOGS;
2. 使用mysqlbinlog命令解析binlog
mysql自带了mysqlbinlog命令行工具，可以直接解析binlog文件内容，该工具通常在mysql的安装目录的bin文件夹下。基本的使用方式如下：
# 解析指定的binlog文件，输出到控制台 mysqlbinlog /var/lib/mysql/mysql-bin.000001
如果要解析指定时间段的binlog，可以添加时间参数：
# 解析2024-01-01 00:00:00到2024-01-01 12:00:00的binlog mysqlbinlog --start-datetime="2024-01-01 00:00:00" --stop-datetime="2024-01-01 12:00:00" /var/lib/mysql/mysql-bin.000001
如果binlog格式是ROW模式，默认解析出来的内容是不可读的，需要添加-v或者-vv参数来显示具体的数据变更：
# 解析ROW格式的binlog，显示具体变更内容 mysqlbinlog -vv /var/lib/mysql/mysql-bin.000001
3. 在mysql客户端内查看binlog事件
也可以通过sql命令查看binlog中的具体事件：
-- 查看指定binlog文件的内容，指定起始位置 SHOW BINLOG EVENTS IN 'mysql-bin.000001' FROM 123 LIMIT 10;
使用第三方工具分析binlog
1. binlog2sql工具
binlog2sql是常用的binlog解析工具，可以将binlog解析成对应的sql语句，还支持生成回滚sql，非常适合数据恢复场景。使用前需要先安装python环境，然后安装该工具：
# 安装binlog2sql git clone https://github.com/ipipp/binlog2sql.git cd binlog2sql pip install -r requirements.txt
解析binlog生成正向sql的命令示例：
# 解析binlog生成sql语句 python binlog2sql.py -h 127.0.0.1 -P 3306 -u root -p -d test_db --start-file='mysql-bin.000001'
生成回滚sql的命令示例：
# 生成回滚sql python binlog2sql.py -h 127.0.0.1 -P 3306 -u root -p -d test_db --start-file='mysql-bin.000001' -B
2. Canal工具
Canal是阿里开源的用于解析mysql binlog的中间件，主要用于将binlog的变更同步到其他存储系统，也可以用于分析binlog变更。它的工作原理是模拟mysql从库，拉取主库的binlog进行解析，适合需要实时监听binlog变更的场景。
binlog分析的常见场景示例
误删数据恢复
假设在test_db库的user表误执行了delete语句，删除了一部分数据，我们可以通过以下步骤恢复：
首先找到删除操作对应的binlog文件和位置，使用mysqlbinlog或者binlog2sql解析出删除操作的sql，然后生成对应的insert回滚语句，执行回滚语句即可恢复数据。
使用binlog2sql生成回滚sql的示例：
# 假设删除操作在mysql-bin.000001，位置在123到456之间 python binlog2sql.py -h 127.0.0.1 -P 3306 -u root -p -d test_db --start-file='mysql-bin.000001' --start-position=123 --stop-position=456 -B > rollback.sql # 执行回滚sql mysql -u root -p test_db < rollback.sql
数据变更排查
如果发现某张表的数据异常变更，可以通过binlog定位到具体的变更时间、执行的sql以及操作的用户，快速排查问题原因。使用mysqlbinlog解析指定时间段的binlog，搜索对应的表名即可找到相关的变更记录。
分析binlog的注意事项
- 分析binlog时需要确保有足够的权限，通常需要mysql的
REPLICATION SLAVE和REPLICATION CLIENT权限。 - ROW格式的binlog会记录每一行数据的变更，文件体积会比STATEMENT格式大很多，解析时也需要更多的时间。
- 线上环境分析binlog时，尽量避免在业务高峰期执行大文件的解析操作，避免影响数据库性能。
- binlog文件默认会定期自动清理，如果需要保留更长时间的binlog，可以调整
expire_logs_days参数，或者手动备份binlog文件。