mysql如何备份复制环境中的主从数据库_mysql主从备份方案

主库备份必须加–single-transaction和–master-data=2，前者保证innodb一致性快照，后者记录备份结束时binlog位点；从库备份须先stop slave再记录relay_master_log_file和exec_master_log_pos；dump中的change master语句不可直接执行，需基于目标库状态重算。

主库备份时必须加 --single-transaction 和 --master-data=2

直接用 mysqldump 备份主库却不加关键参数，会导致从库恢复后复制中断或数据不一致。原因在于：--single-transaction 保证 InnoDB 表一致性快照（避免锁表），而 --master-data=2 会把备份时刻的 binlog 文件名和位置写进 dump 文件里（注释形式），这是从库执行 CHANGE MASTER TO 的唯一可靠依据。

常见错误是只加 --lock-all-tables 或干脆不加任何事务/位点参数，结果恢复后 SHOW SLAVE STATUSG 显示 Seconds_Behind_Master: NULL，Slave_IO_Running: No —— 因为从库根本不知道该从哪条 binlog 开始追。

• 必须确认主库已开启 binlog（log_bin = ON）且 server_id 唯一
• 若主库混用 MyISAM 表，--single-transaction 无效，得改用 --lock-all-tables，但会阻塞写入
• --master-data=2 输出的位点是备份结束时刻的位点，不是开始时刻；如果备份耗时长，中间有大事务，位点可能跳过部分事件

从库备份优先用 STOP SLAVE + SHOW SLAVE STATUS 记录位点

对从库做物理备份（如 rsync datadir）或逻辑备份前，不能直接停 mysqld，必须先 STOP SLAVE，再查 Relay_Master_Log_File 和 Exec_Master_Log_Pos —— 这两个值才是从库已成功执行到主库 binlog 的精确位置，比 SHOW MASTER STATUS 在从库上运行的结果可靠得多。

有人误以为从库的 SHOW MASTER STATUS 能反映复制进度，其实它只显示从库自己产生的 binlog（如果开启了 log_bin），跟主从同步完全无关。

• STOP SLAVE 后立即执行 SHOW SLAVE STATUSG，记下 Relay_Master_Log_File 和 Exec_Master_Log_Pos
• 备份完成后，用这两个值配置新从库：CHANGE MASTER TO MASTER_LOG_FILE='xxx', MASTER_LOG_POS=yyy
• 若从库启用了 relay_log_recovery=ON，重启后会自动重建 relay log，此时 Relay_Master_Log_File 仍有效，但 Relay_Log_File 和 Relay_Log_Pos 不可依赖

备份文件里含 CHANGE MASTER TO 语句，但不能直接执行

主库用 mysqldump --master-data=2 生成的 SQL 文件开头会有类似这样的注释：

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=154;

很多人复制这行去掉注释后直接在从库执行，结果报错 Error 1236 (HY000) —— 因为该位点对应的是主库当时的 binlog 状态，而你恢复的目标库可能是旧数据、或是另一台从库，其本地 binlog 文件名和位置与主库不匹配。

真正要执行的 CHANGE MASTER TO 必须基于目标库当前状态重算：要么用上面提到的从库 SHOW SLAVE STATUS 位点，要么在新从库恢复完后，用 mysqlbinlog 解析主库最新 binlog 找到对应 GTID 或位置。

• 不要信任 dump 文件里的 CHANGE MASTER TO 注释行，它只对“主库 dump → 新从库恢复”这一特定流程有效
• 如果主库开启了 GTID（gtid_mode=ON），备份时加 --set-gtid-purged=ON，恢复后用 SET GLOBAL gtid_slave_skip_counter=1 或 START SLAVE UNTIL SQL_AFTER_GTIDS 更安全
• 用 grep -n "CHANGE MASTER" backup.sql 快速定位该行，但务必人工核对再执行

定期校验主从数据一致性，别只靠 Seconds_Behind_Master

Seconds_Behind_Master 显示 0 并不代表数据一致——它只表示 IO 线程已拉取完 binlog，SQL 线程也执行到了那个位置，但无法发现隐式冲突（如主库重复插入唯一键、从库被误写、触发器行为不一致）。真实环境中，主从 CRC 校验失败常发生在上线新功能或修改字符集之后。

推荐用 pt-table-checksum（Percona Toolkit）跑全量校验，它通过分块 checksum + 主从查询结果比对实现，不影响线上业务。注意：该工具必须在主库执行，且从库需关闭 read_only=OFF（临时），否则校验语句无法写入从库的校验表。

• 校验前确保主从 time_zone、sql_mode、collation 完全一致，否则字符串比较会误报
• 避免在业务高峰跑全库校验；可用 --chunk-size 和 --chunk-index 控制粒度
• 一旦发现不一致，用 pt-table-sync 修复，但它会直接改从库数据，务必先备份从库

备份主从环境的核心不是“多备份几份”，而是让每一份备份都自带可复现的复制起点。最容易被忽略的，是备份动作本身对复制链路的扰动 —— 比如没停从库就打包 datadir，或恢复后忘了重置 relay_log_purge 导致磁盘爆满。