mysql主从复制在生产环境中的最佳实践_实践分享

mysql主从复制必须稳、可查、能切、不丢数据：主库需binlog_format=row、server_id唯一、binlog_row_image=full；从库设read_only=on并回收super权限；统一启用gtid；监控需结合io/sql线程状态、gtid集差值及pt-heartbeat延迟。

MySQL 主从复制在生产环境里不是配通了就行，关键得稳、可查、能切、不丢数据。

主库必须开启 binlog 且使用 ROW 格式

很多线上事故源于主库 binlog_format 设成了 MIXED 或 STATEMENT。后者在非确定性函数（如 NOW()、UUID()）、自增主键冲突、触发器等场景下会导致从库数据不一致，且无法回溯。

• binlog_format = ROW 是唯一能保证语句级精确重放的模式
• 主库 server_id 必须唯一且非 0，建议用 IP 段+序号方式命名（如 1921680101）
• 开启 binlog_row_image = FULL（默认值），避免部分更新字段丢失上下文
• 不要依赖 expire_logs_days 自动清理，应配合外部脚本按 GTID 或时间窗口归档 + 安全删除

从库务必设为 read_only = ON 并禁用超级权限

仅靠应用层约束写从库不可靠，网络误连、配置错误、运维误操作都可能直接写入从库，造成主从错位甚至复制中断。

• read_only = ON 可阻止普通用户写入，但对 SUPER 权限用户无效
• 必须回收从库上所有账号的 SUPER 权限（REVOKE SUPER ON *.* FROM 'user'@'%'）
• 若需临时写入（如修复数据），应先 SET GLOBAL read_only = OFF，操作完立刻恢复，并记录操作人与时间
• 检查 show variables like 'read_only'; 和 select user, super_priv from mysql.user; 双确认

用 GTID 替代传统 file/pos 复制，但必须统一主从 gtid_mode

基于文件名和偏移量的复制在故障切换、跳过错误、多级复制时极易出错；GTID 能自动定位同步点，是现代 MySQL 高可用的基础。

• 主从都必须设 gtid_mode = ON 且 enforce_gtid_consistency = ON，二者缺一不可
• 初始化从库时，必须用 mysqldump --set-gtid-purged=ON 或 xtrabackup --dump-gtids，否则 GTID 集不匹配会拒绝启动复制
• 跳过事务不能用 sql_slave_skip_counter（GTID 下已废弃），改用 SET GTID_NEXT='xxx'; BEGIN; COMMIT; 注入空事务
• 注意 auto_position = 1 必须显式开启，否则仍走传统协议

监控不能只看 Seconds_Behind_Master

这个值在从库 IO 线程卡住、SQL 线程空闲时可能显示为 0，但实际已落后大量事务；它也无法反映主从数据逻辑一致性。

• 必须同时监控：Slave_IO_Running、Slave_SQL_Running、Retrieved_Gtid_Set、Executed_Gtid_Set 的差值
• 用 pt-heartbeat 在主库定时写入心跳，从库比对时间戳，才能真实反映复制延迟
• 定期校验主从表一致性（如 pt-table-checksum），尤其在大表 ALTER 或批量更新后
• 错误日志里重点关注 Got fatal Error 1236（binlog 截断）、Could not execute Write_rows_v1 Event（行镜像缺失）等关键报错

真正难的不是搭起复制，而是让复制在半夜自动扩容、主库宕机后秒级切换、DDL 变更不锁主库、以及当某张表突然多了 200 万条脏数据时，你能三分钟内定位到是哪个事务、在哪台从库、被什么操作写进去的——这些都取决于最开始那几行配置和监控埋点扎得够不够深。