MySQL 主从复制实践：配置、故障排查与监控

一篇 MySQL 主从复制资料整理，覆盖复制线程、binlog 模式、主从配置、故障排查、主主架构和复制监控思路。

这篇是 MySQL 主从复制实践资料整理，重点保留复制线程、binlog 模式、主从配置、常见故障处理和监控脚本思路。

复制线程

MySQL 主从复制通常涉及三个线程：主库上的 binlog dump 线程，从库上的 I/O 线程和 SQL 线程。从库 I/O 线程请求主库 binlog，写入 relay log；SQL 线程再重放 relay log。

复制基本过程

主库开启 binary log。
从库连接主库，请求指定 binlog 文件和位置之后的事件。
从库 I/O 线程写入 relay log。
从库 SQL 线程重放 relay log。

binlog 模式

[mysqld]
log-bin=mysql-bin
# binlog_format=STATEMENT
# binlog_format=ROW
binlog_format=MIXED

运行时也可以调整当前会话或全局 binlog 格式。

SET SESSION binlog_format = 'STATEMENT';
SET SESSION binlog_format = 'ROW';
SET SESSION binlog_format = 'MIXED';

SET GLOBAL binlog_format = 'STATEMENT';
SET GLOBAL binlog_format = 'ROW';
SET GLOBAL binlog_format = 'MIXED';

主库配置示例

[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=MIXED

从库配置示例

[mysqld]
server-id=2
relay-log=relay-bin
read_only=on
master-connect-retry=30
slave-skip-errors=1007,1008,1053,1062,1213,1158,1159

初始化主从同步

首次同步前，先备份主库数据并导入从库，然后在主库查看 binlog 位置。

SHOW MASTER STATUS;

在从库执行同步配置：

STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.2',
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'strong_password',
    MASTER_PORT = 3306,
    MASTER_LOG_FILE = 'bin-log.003',
    MASTER_LOG_POS = 4;
START SLAVE;
SHOW SLAVE STATUS;

常见故障处理

如果出现 Slave_SQL_Running: No，常见原因是从库被写入、事务回滚或遇到主键冲突等复制错误。

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;
SHOW SLAVE STATUS;

如果出现 Slave_IO_Running: No，优先检查主库地址、端口、账号、密码、防火墙和主库 binlog 状态。

监控思路

主从监控通常关注 I/O 线程、SQL 线程和延迟。

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Master_Log_File: bin-log.003
Relay_Master_Log_File: bin-log.003
Read_Master_Log_Pos: 4
Exec_Master_Log_Pos: 4

早期版本也可以通过比较主库 File/Position 与从库 Relay_Master_Log_File/Exec_Master_Log_Pos 判断延迟。

#!/bin/sh

user="repl"
passwd="strong_password"
master_ip="192.168.1.2"
log="/data3/check_repl.log"

master=$(mysql -u"$user" -p"$passwd" -h"$master_ip" -e "SHOW MASTER STATUS;" | egrep "File|Position")
slave=$(mysql -u"$user" -p"$passwd" -h127.0.0.1 -e "SHOW SLAVE STATUS;" | egrep "Relay_Master_Log_File|Exec_Master_Log_Pos")

echo "$master" >> "$log"
echo "$slave" >> "$log"

结论

MySQL 主从复制成熟可靠，但它不是强一致方案。生产环境需要同时关注备份、延迟、误写、防火墙、复制账号权限和故障切换策略。

MySQL 主从复制实践资料整理