mysql的二进制日志（binlog）是mysql服务层生成的日志文件，默认记录所有对数据库的增删改操作以及表结构变更操作，是数据库运维和问题排查中非常重要的工具。不管是数据误删恢复、主从同步故障定位，还是业务操作溯源，都可以通过分析二进制日志快速找到问题根源。
二进制日志的基础配置
要使用二进制日志排查问题，首先需要确认mysql已经开启了二进制日志功能，相关配置在mysql的配置文件my.cnf中：
[mysqld] # 开启二进制日志，指定日志文件前缀 log_bin=mysql-bin # 设置server-id，主从架构下每个实例的id必须唯一 server-id=1 # 二进制日志格式，推荐用ROW格式，记录更详细的数据变更 binlog_format=ROW # 日志过期时间，单位天，避免日志文件占用过多磁盘空间 expire_logs_days=7
配置完成后重启mysql服务即可生效，可以通过如下命令查看二进制日志是否开启：
-- 查看二进制日志开启状态 SHOW VARIABLES LIKE 'log_bin'; -- 查看当前所有二进制日志文件列表 SHOW BINARY LOGS; -- 查看当前正在写入的二进制日志文件 SHOW MASTER STATUS;
二进制日志的查看与解析
二进制日志是二进制格式的文件，无法直接用文本编辑器打开，需要使用mysql自带的mysqlbinlog工具进行解析，常用的解析命令如下：
基础解析命令
# 解析指定二进制日志文件，输出为可读的文本格式 mysqlbinlog /var/lib/mysql/mysql-bin.000001 # 解析时指定字符集，避免中文乱码 mysqlbinlog --character-set=utf8 /var/lib/mysql/mysql-bin.000001 # 将解析结果输出到文件，方便后续分析 mysqlbinlog /var/lib/mysql/mysql-bin.000001 > binlog_analyze.txt
按时间范围过滤解析
如果只需要分析某个时间段内的日志，可以加上时间参数：
# 解析2024-05-01 10:00:00到2024-05-01 12:00:00之间的日志 mysqlbinlog --start-datetime="2024-05-01 10:00:00" --stop-datetime="2024-05-01 12:00:00" /var/lib/mysql/mysql-bin.000001
按位置范围过滤解析
二进制日志中的每个操作都有对应的位置偏移量，也可以通过位置范围过滤：
# 解析位置从154到500之间的日志内容 mysqlbinlog --start-position=154 --stop-position=500 /var/lib/mysql/mysql-bin.000001
常见场景的排查实战
场景一：误删数据恢复
如果执行了误删数据的操作，比如不小心执行了DELETE FROM user WHERE id < 100，可以通过二进制日志找到对应的删除操作，反向生成恢复语句。
首先找到误删操作发生的时间段，解析对应的二进制日志：
# 假设误删发生在2024-05-01 11:30左右，解析前后10分钟的日志 mysqlbinlog --start-datetime="2024-05-01 11:20:00" --stop-datetime="2024-05-01 11:40:00" /var/lib/mysql/mysql-bin.000001 > delete_log.txt
在解析后的文件中找到对应的DELETE操作，ROW格式的二进制日志会记录删除前的数据内容，我们可以提取这些数据生成INSERT恢复语句。如果是STATEMENT格式的日志，需要找到对应的DELETE语句，手动反向生成插入语句。
也可以直接使用mysqlbinlog导出恢复用的sql文件，然后执行恢复：
# 导出指定时间段的日志为可执行的sql文件 mysqlbinlog --start-datetime="2024-05-01 11:20:00" --stop-datetime="2024-05-01 11:30:00" /var/lib/mysql/mysql-bin.000001 | mysql -u root -p
场景二：主从同步异常排查
当mysql主从同步出现故障时，首先可以在从库执行SHOW SLAVE STATUSG查看错误信息，常见的错误比如从库执行relay log中的语句失败，这时候可以通过主库的二进制日志定位问题。
比如从库报错提示某个语句执行失败，错误日志中给出了对应的binlog文件和位置，就可以到主库解析对应的二进制日志：
# 解析主库对应文件和位置的日志，查看具体操作内容 mysqlbinlog --start-position=1234 /var/lib/mysql/mysql-bin.000002
查看解析结果后，确认是主库的操作在从库不兼容，比如从库的表结构和主库不一致，就可以针对性修复从库的表结构，然后重启同步。
场景三：业务操作溯源
如果有业务反馈数据异常，需要定位是哪个操作修改了数据，可以通过二进制日志按表、按操作类型过滤分析。比如要查找user表的所有更新操作：
# 解析日志后过滤包含user表的内容 mysqlbinlog /var/lib/mysql/mysql-bin.000001 | grep -i "user"
结合操作时间和操作类型，就可以快速定位到对应的业务操作，找到问题来源。
注意事项
- 二进制日志默认只记录增删改和表结构变更，查询操作不会记录，所以无法通过binlog排查查询相关问题。
- 生产环境建议将二进制日志格式设置为ROW，虽然日志文件会大一些，但是记录的信息更完整，恢复数据和排查问题更可靠。
- 解析二进制日志时最好先备份原日志文件，避免误操作损坏日志导致无法恢复。
- 如果二进制日志文件很多，排查问题时先通过
SHOW BINARY LOGS确认需要解析的文件范围，避免不必要的全量解析浪费时间。