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

一、主从复制核心原理

复制流程解析

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

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

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

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

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

核心组件解析

组件	作用	关键配置参数
Binlog	主库记录所有数据变更	log-bin=mysql-bin
Relay Log	从库暂存主库传输的日志	relay-log=relay-bin
I/O Thread	从库获取主库binlog的线程	自动管理
SQL Thread	从库执行relay log的线程	自动管理
Binlog Dump	主库向从库发送binlog的线程	每个从库对应一个线程

二、主流复制架构方案

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 TOMASTER_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!';

六、主从复制限制与应对

限制类型	影响	解决方案
异步复制	数据丢失风险	半同步复制
单线程应用	大事务延迟	并行复制(MTS)
DDL锁表	主库锁定影响写入	pt-online-schema-change
MyISAM引擎	崩溃后数据不一致	全量InnoDB迁移
过滤规则复杂	配置错误导致数据缺失	全库复制+应用层过滤