MySQL主从复制与读写分离（1）-优快云博客

阶段名称	执行主体	关键操作	日志类型	线程类型
‌日志生成‌	主库Master	事务提交时记录所有DML/DDL操作	Binlog	-
‌日志传输‌	主从协作	从库拉取主库binlog并存储为中继日志	Relay log	I/O线程、Dump线程
‌日志执行‌	从库Slave	解析中继日志并应用数据变更	Relay log	SQL线程

‌核心日志对比‌：

- &zwnj;**Binlog**&zwnj;：主库写入格式由`binlog_format`控制
 - `STATEMENT`：记录SQL语句（存在函数结果不一致风险）:ml-citation{ref="2,5" data="citationList"}
 - `ROW`：记录行数据变化（推荐模式，数据一致性高）:ml-citation{ref="6,8" data="citationList"} 
- &zwnj;**Relay log**&zwnj;：临时存储从主库接收的日志，格式与Binlog相同:ml-citation{ref="4,7" data="citationList"}

1.2 线程协作模型

线程类型	所属服务器	核心职责	性能影响点
‌Binlog Dump‌	主库	响应从库IO线程请求，推送binlog事件	主库网络带宽
‌I/O Thread‌	从库	接收主库日志并写入relay log	从库磁盘IO性能
‌SQL Thread‌	从库	解析执行relay log中的事件	从库CPU计算能力

‌线程状态监控命令‌：

-- 主库查看Dump线程状态 
SHOW PROCESSLIST; 
-- 从库查看复制线程状态 
SHOW SLAVE STATUS\G

第二部分主从复制的核心价值

2.1 技术价值矩阵

价值维度	技术实现原理	业务场景案例	关键配置参数
‌高可用架构‌	主库故障时通过VIP切换或MHA工具快速切换至从库	金融核心交易系统容灾	`read_only=ON`
‌读写分离‌	中间件（如MyCat/ShardingSphere）按SQL类型路由请求	电商大促期间流量削峰	`max_connections=2000`
‌数据热备份‌	从库持续同步主库数据，可设置延时复制应对误操作	医疗系统病历数据保护	`MASTER_DELAY=3600`
‌横向扩展能力‌	通过增加从库节点支撑百万级QPS读请求	社交平台实时消息推送系统	`slave_parallel_workers=8`

2.2 复制演进趋势

‌日志格式迭代‌：
- ‌MySQL 5.6‌ 引入GTID（全局事务标识）实现精确故障恢复
- ‌MySQL 8.0‌ 增强并行复制支持WRITESET模式

‌架构升级路线‌：

graph LR 
A[传统主从] --> B[半同步复制] 
B --> C[多源复制] 
C --> D[MGR集群]

第三部分经典部署实战

3.1 主库配置全流程

‌步骤1：修改配置文件‌

# /etc/my.cnf 
[mysqld] 
server-id = 1001 
log_bin = /var/lib/mysql/mysql-bin 
binlog_format = ROW 
innodb_flush_log_at_trx_commit = 1 
sync_binlog = 1 
expire_logs_days = 7 
max_binlog_size = 1G

‌步骤2：创建专用复制账号‌

CREATE USER 'repl'@'192.168.50.%' IDENTIFIED WITH mysql_native_password BY 'S3cur3P@ss!'; 
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.50.%'; 
FLUSH PRIVILEGES;

‌步骤3：获取初始复制坐标‌

FLUSH TABLES WITH READ LOCK; 
SHOW MASTER STATUS; -- 记录File(mysql-bin.000002)和Position(785) 
UNLOCK TABLES;

3.2 从库初始化配置

‌步骤1：基础参数配置‌

# /etc/my.cnf 
[mysqld] 
server-id = 1002 
relay_log = /var/lib/mysql/mysql-relay-bin 
read_only = 1 
log_slave_updates = 1 
slave_parallel_workers = 4

‌步骤2：建立复制链路‌

CHANGE MASTER TO
 MASTER_HOST = '192.168.50.101',
 MASTER_USER = 'repl',
 MASTER_PASSWORD = 'S3cur3P@ss!',
 MASTER_PORT = 3306,
 MASTER_LOG_FILE = 'mysql-bin.000002',
 MASTER_LOG_POS = 785; 

START SLAVE;

‌步骤3：验证复制状态‌

SHOW SLAVE STATUS\G

 /* 关键验证点：
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Seconds_Behind_Master: 0 */

第四部分高阶优化方案

4.1 并行复制加速

# 从库配置文件优化 
slave_parallel_type = LOGICAL_CLOCK 
slave_parallel_workers = 8 
master_info_repository = TABLE 
relay_log_info_repository = TABLE

‌效果对比测试‌：

并发线程数	同步延迟（秒）	资源消耗（CPU%）
1	12.3	45%
4	3.8	68%
8	1.2	92%

4.2 半同步复制配置

‌主库调整‌：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; 
SET GLOBAL rpl_semi_sync_master_enabled = 1; 
SET GLOBAL rpl_semi_sync_master_timeout = 1000; -- 单位：毫秒

‌从库调整‌：

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; 
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

第五部分监控与排错体系

5.1 关键监控指标

监控项	健康标准	异常处理建议
`Slave_IO_Running`	持续为Yes	检查主从网络连接和权限配置
`Slave_SQL_Running`	持续为Yes	查看`Last_SQL_Error`字段
`Seconds_Behind_Master`	<5秒	优化从库硬件或启用并行复制

5.2 典型故障场景

‌案例1：主键冲突（Error 1062）‌

STOP SLAVE; 
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 
START SLAVE; 
-- 永久解决方案需修复数据一致性:ml-citation{ref="7" data="citationList"}

‌案例2：主库binlog被清除‌

-- 重新全量同步 
RESET SLAVE ALL; 
CHANGE MASTER TO ... -- 重新指定binlog坐标 
START SLAVE;

第六部分总结与展望

6.1 核心价值再梳理

‌可靠性保障‌：通过异步/半同步混合模式平衡性能与数据安全
‌弹性扩展能力‌：支持动态增减从库应对业务流量波动
‌成本效益优化‌：利用廉价硬件承载读流量，降低TCO（总拥有成本）

6.2 技术演进方向

‌智能化运维‌：AI驱动的异常检测与自愈系统构建
‌多云架构支持‌：跨云厂商的主从同步方案标准化
‌安全增强‌：TLS加密传输与动态密钥管理机制

MySQL主从复制与读写分离（1）

第一部分 核心原理与工作机制

1.1 数据流转三阶段模型

1.2 线程协作模型

第二部分 主从复制的核心价值