mysql备份恢复中的日志文件与恢复优化

binlog 是 mysql 恢复的核心日志，用于基于时间点或事务的逻辑恢复；relay log 仅在复制中暂存主库 binlog，不直接用于人工恢复。

binlog 与 relay log 在恢复中各自起什么作用

MySQL 恢复依赖两类日志：binlog 记录主库所有写操作，用于基于时间点（PITR）或事务的逻辑恢复；relay log 是从库接收并暂存主库 binlog 的副本，只在复制场景下参与恢复（比如从库崩溃后重放 relay log 继续同步）。生产环境做全量 + 增量恢复时，binlog 是核心依据，relay_log 一般不直接用于人工恢复操作。

容易混淆的点：mysqldump --single-transaction 不会自动包含 binlog 位置，必须显式加 --master-data=2 才能在 dump 文件里注释出 CHANGE MASTER TO 所需的 MASTER_LOG_FILE 和 MASTER_LOG_POS。漏掉这个参数，后续增量恢复就断档。

用 mysqlbinlog 解析 binlog 时怎么跳过无效事件

mysqlbinlog 默认会把格式描述、心跳事件、匿名 GTID 等非用户 SQL 都输出出来，直接导入可能报错或重复执行。关键是要过滤掉这些干扰项：

• 用 --base64-output=DECODE-ROWS + -v 查看行格式事件，但不要直接导入，它输出的是伪 SQL
• 真正要导入的语句，优先用 --read-from-remote-server 配合 --stop-datetime 或 --to-last-log 控制范围
• 跳过特定事件类型：加 --exclude-gtids="xxx" 或用 grep -v "Anonymous_Gtid|Rotate|Format_desc" 管道过滤（注意别误删 Query 事件）
• 如果 binlog 开启了 binlog_row_image=MINIMAL，mysqlbinlog -v 可能无法还原完整 SQL，此时应依赖 binlog_row_image=FULL 的备份策略

恢复大库时如何避免锁表和 I/O 打满

直接 mysql -u root db_name 会单线程执行、全程持有 MDL 锁，且大量随机 I/O 容易拖垮磁盘。优化路径有三条：

• 导入前关掉自动提交：SET autocommit=0;，并在文件开头加 BEGIN;、结尾加 COMMIT;，减少日志刷盘次数
• 用 mysqlimport 替代 INSERT 导入 CSV：对大表更高效，支持 --use-threads=4 并行解析
• 对于超大库（>100GB），先停写、用 xtrabackup --prepare 做物理恢复，再启动 mysqld；比逻辑恢复快一个数量级，且不依赖 binlog 解析
• 务必避开高峰期；恢复前检查 innodb_buffer_pool_size 是否足够，否则大量页淘汰会进一步拉慢速度

mysql --defaults-file=/etc/my.cnf -u root -D mydb -e "SET autocommit=0; SOURCE /backup/full.sql; COMMIT;"

GTID 模式下恢复失败的典型原因

开启 gtid_mode=ON 后，恢复不能靠 position，必须用 GTID 集合。常见卡点：

• dump 文件里没带 SET @@GLOBAL.GTID_PURGED —— 用 mysqldump --set-gtid-purged=ON（默认值）才生效；设为 OFF 会导致后续 START SLAVE 报错 Cannot replicate because the master purged required binary logs
• 目标实例已执行过其他事务，GTID_PURGED 不为空，但 dump 里又没清理——需手动 RESET MASTER（慎用！会清空本地 binlog）或用 SET GLOBAL GTID_PURGED='...' 合并集合
• 跨版本恢复（如 5.7 → 8.0）时，mysql_upgrade 必须在恢复后、启动复制前运行，否则 slave_sql_thread 会因系统表结构不匹配而 stop

GTID 恢复不是“设了就自动好”，GTID_EXECUTED、GTID_PURGED、备份时刻的 select @@global.gtid_executed 输出三者必须对得上，差一位都会导致复制中断。