MySQL主从复制深度解析：原理、架构与实战部署指南

一、主从复制核心原理

复制流程解析

MySQL主从复制本质是通过二进制日志(binlog)实现数据同步的异步复制机制：

1. 写操作记录：主库执行写操作时，将变更记录到binlog

2. 日志传输：主库的binlog dump线程将日志发送给从库

3. 中继存储：从库I/O线程接收日志并写入relay log

4. 数据重放：从库SQL线程解析relay log并执行SQL

核心组件解析

二、主流复制架构方案

1. 一主一从架构

适用场景：读写分离、备份容灾

特点：

• 简单易部署

• Slave承担读负载

• Master故障需手动切换

2. 一主多从架构

适用场景：读密集型应用

优势：

• 线性扩展读能力

• 多副本数据安全

• 负载均衡读请求

3. 多级复制架构

适用场景：大规模集群、跨机房部署

特点：

• 降低主库网络压力

• 支持异地容灾

• 数据延迟增加

4. 双主复制架构

适用场景：高可用要求高的系统

优势：

• 故障自动切换

• 维护不影响服务

• 无单点故障

三、主从搭建实战部署

环境准备（192.168.66.143/144）

# 关闭防火墙
systemctl stop firewalld
systemctl disable firewalld

# 禁用SELinux
setenforce 0

# 时间同步
ntpdate ntp.aliyun.com

主库配置（192.168.66.143）

1. 修改my.cnf

[mysqld]
server-id = 1
log-bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7

2. 创建复制账号

CREATE USER 'repl'@'192.168.66.%' IDENTIFIED BY 'SecurePass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.66.%';
FLUSH PRIVILEGES;

3. 查看binlog状态

SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      785 |              |                  |
+------------------+----------+--------------+------------------+

从库配置（192.168.66.144）

1. 修改my.cnf

[mysqld]
server-id = 2
relay-log = /var/lib/mysql/relay-bin
read_only = 1

2. 配置主从连接

CHANGE MASTER TO
  MASTER_HOST='192.168.66.143',
  MASTER_USER='repl',
  MASTER_PASSWORD='SecurePass123!',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=785;

3. 启动复制

START SLAVE;

4. 检查复制状态

SHOW SLAVE STATUS\G

关键指标检查：

• Slave_IO_Running: Yes

• Slave_SQL_Running: Yes

• Seconds_Behind_Master: 0

四、生产环境优化建议

1. 参数调优

# 主库
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

# 从库
slave_parallel_workers = 4
slave_preserve_commit_order = 1

2. 监控指标

# 实时监控延迟
mysql -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master"

# 监控线程状态
watch -n 1 'mysql -e "SHOW PROCESSLIST" | grep -i "system user"'

3. 高可用方案

组件：

• Keepalived：VIP漂移

• MHA：自动故障转移

• ProxySQL：读写分离

五、常见故障排查指南

问题1：复制中断（1062错误）

现象：

Last_Error: Could not execute Write_rows event on table db.users; 
Duplicate entry '1001' for key 'PRIMARY', Error_code: 1062;

解决方案：

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

问题2：主从不一致

修复步骤：

# 1. 主库导出数据
mysqldump --single-transaction --master-data=2 -A > full.sql

# 2. 从库重置复制
STOP SLAVE;
RESET SLAVE ALL;

# 3. 导入数据
mysql < full.sql

# 4. 重新配置复制
CHANGE MASTER TO ...;
START SLAVE;

问题3：认证插件错误

错误信息：

Authentication plugin 'caching_sha2_password' reported error

解决方案：

-- 主库执行
ALTER USER 'repl'@'192.168.66.%' 
IDENTIFIED WITH mysql_native_password BY 'SecurePass123!';

六、主从复制限制与应对