MySQL主从复制与读写分离实战指南

本文详解MySQL主从复制原理与配置，以及读写分离的实现方案，从单机到高可用架构。

前言

单机MySQL的问题：

• 单点故障
• 读写压力集中
• 无法水平扩展

主从复制是MySQL高可用的基础：

• 数据冗余，提高可用性
• 读写分离，提升性能
• 实时备份，降低风险

今天来详解MySQL主从复制的实战配置。

一、主从复制原理

1.1 复制流程

┌─────────────────────────────────────────────────────────┐ │ Master │ │ ┌─────────┐ ┌─────────────┐ │ │ │ 数据变更 │ → │ Binlog │ │ │ └─────────┘ └──────┬──────┘ │ └─────────────────────────┼────────────────────────────────┘ │ ① 传输binlog ↓ ┌─────────────────────────────────────────────────────────┐ │ Slave │ │ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ │ │ IO Thread │ → │ Relay Log │ → │ SQL Thread │ │ │ │ 接收binlog │ │ 中继日志 │ │ 回放执行 │ │ │ └─────────────┘ └─────────────┘ └─────────────┘ │ └─────────────────────────────────────────────────────────┘

1.2 复制模式

二、环境准备

2.1 Docker Compose部署

# docker-compose.ymlversion:'3.8'services:mysql-master:image:mysql:8.0container_name:mysql-masterenvironment:MYSQL_ROOT_PASSWORD:root123ports:-"3306:3306"volumes:-./master/conf:/etc/mysql/conf.d-./master/data:/var/lib/mysql-./master/logs:/var/log/mysqlcommand:--server-id=1--log-bin=mysql-bin--binlog-format=ROWmysql-slave:image:mysql:8.0container_name:mysql-slaveenvironment:MYSQL_ROOT_PASSWORD:root123ports:-"3307:3306"volumes:-./slave/conf:/etc/mysql/conf.d-./slave/data:/var/lib/mysql-./slave/logs:/var/log/mysqlcommand:--server-id=2--log-bin=mysql-bin--binlog-format=ROW--read-only=1depends_on:-mysql-master

2.2 配置文件

Master配置：

# master/conf/my.cnf [mysqld] server-id = 1 log-bin = mysql-bin binlog-format = ROW sync-binlog = 1 # 需要同步的数据库（不配则同步所有） # binlog-do-db = mydb # 忽略的数据库 binlog-ignore-db = mysql binlog-ignore-db = information_schema binlog-ignore-db = performance_schema binlog-ignore-db = sys # GTID模式（推荐） gtid_mode = ON enforce_gtid_consistency = ON

Slave配置：

# slave/conf/my.cnf [mysqld] server-id = 2 log-bin = mysql-bin binlog-format = ROW relay-log = relay-bin read-only = 1 # GTID模式 gtid_mode = ON enforce_gtid_consistency = ON # 跳过某些错误（谨慎使用） # slave-skip-errors = 1062

2.3 启动服务

# 创建目录mkdir-p master/{conf,data,logs}slave/{conf,data,logs}# 启动docker compose up -d# 查看状态dockerps

三、配置主从复制

3.1 在Master创建复制用户

-- 连接Mastermysql-h127.0.0.1-P3306-uroot-proot123-- 创建复制用户CREATEUSER'repl'@'%'IDENTIFIEDBY'repl123';GRANTREPLICATIONSLAVE,REPLICATIONCLIENTON*.*TO'repl'@'%';FLUSHPRIVILEGES;-- 查看Master状态SHOWMASTERSTATUS;+------------------+----------+--------------+------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|+------------------+----------+--------------+------------------+|mysql-bin.000003|857||mysql,...|+------------------+----------+--------------+------------------+

3.2 配置Slave

-- 连接Slavemysql-h127.0.0.1-P3307-uroot-proot123-- 方式1：传统位点复制CHANGE MASTERTOMASTER_HOST='mysql-master',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='repl123',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=857;-- 方式2：GTID复制（推荐）CHANGE MASTERTOMASTER_HOST='mysql-master',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='repl123',MASTER_AUTO_POSITION=1;-- 启动复制STARTSLAVE;-- 查看复制状态SHOWSLAVESTATUS\G

3.3 验证复制

-- 关键字段Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master:0-- Master写入数据CREATEDATABASEtestdb;USEtestdb;CREATETABLEusers(idINTPRIMARYKEY,nameVARCHAR(50));INSERTINTOusersVALUES(1,'test');-- Slave查询验证USEtestdb;SELECT*FROMusers;+----+------+|id|name|+----+------+|1|test|+----+------+

四、读写分离

4.1 方案对比

4.2 代码层实现（Spring Boot）

// 数据源配置@ConfigurationpublicclassDataSourceConfig{@Bean@ConfigurationProperties("spring.datasource.master")publicDataSourcemasterDataSource(){returnDataSourceBuilder.create().build();}@Bean@ConfigurationProperties("spring.datasource.slave")publicDataSourceslaveDataSource(){returnDataSourceBuilder.create().build();}@BeanpublicDataSourceroutingDataSource(@Qualifier("masterDataSource")DataSourcemaster,@Qualifier("slaveDataSource")DataSourceslave){Map<Object,Object>targetDataSources=newHashMap<>();targetDataSources.put("master",master);targetDataSources.put("slave",slave);RoutingDataSourcerouting=newRoutingDataSource();routing.setTargetDataSources(targetDataSources);routing.setDefaultTargetDataSource(master);returnrouting;}}// 动态数据源publicclassRoutingDataSourceextendsAbstractRoutingDataSource{@OverrideprotectedObjectdetermineCurrentLookupKey(){returnDataSourceContextHolder.getDataSource();}}// 数据源上下文publicclassDataSourceContextHolder{privatestaticfinalThreadLocal<String>CONTEXT=newThreadLocal<>();publicstaticvoidsetMaster(){CONTEXT.set("master");}publicstaticvoidsetSlave(){CONTEXT.set("slave");}publicstaticStringgetDataSource(){returnCONTEXT.get();}publicstaticvoidclear(){CONTEXT.remove();}}// AOP切面@Aspect@ComponentpublicclassDataSourceAspect{@Before("@annotation(readOnly)")publicvoidsetReadDataSource(ReadOnlyreadOnly){DataSourceContextHolder.setSlave();}@Before("@annotation(org.springframework.transaction.annotation.Transactional)")publicvoidsetWriteDataSource(){DataSourceContextHolder.setMaster();}@After("execution(* com.example.service.*.*(..))")publicvoidclear(){DataSourceContextHolder.clear();}}

4.3 中间件方案（ShardingSphere）

# application.ymlspring:shardingsphere:datasource:names:master,slavemaster:type:com.zaxxer.hikari.HikariDataSourcedriver-class-name:com.mysql.cj.jdbc.Driverjdbc-url:jdbc:mysql://192.168.1.1:3306/mydbusername:rootpassword:root123slave:type:com.zaxxer.hikari.HikariDataSourcedriver-class-name:com.mysql.cj.jdbc.Driverjdbc-url:jdbc:mysql://192.168.1.2:3306/mydbusername:rootpassword:root123rules:readwrite-splitting:data-sources:readwrite_ds:static-strategy:write-data-source-name:masterread-data-source-names:slaveload-balancer-name:round_robinload-balancers:round_robin:type:ROUND_ROBIN

五、高可用架构

5.1 MHA架构

┌─────────────────────────────────────────────────────┐ │ MHA Manager │ │ (监控+故障转移) │ └─────────────────────────────────────────────────────┘ ↓ ↓ ↓ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │ Master │ │ Slave1 │ │ Slave2 │ │ (可写) │ │ (候选Master) │ │ (只读) │ └──────────────┘ └──────────────┘ └──────────────┘

5.2 MGR组复制

-- 所有节点配置[mysqld]server_id=1gtid_mode=ONenforce_gtid_consistency=ONbinlog_checksum=NONE# 组复制配置plugin_load_add='group_replication.so'group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"group_replication_start_on_boot=OFFgroup_replication_local_address="192.168.1.1:33061"group_replication_group_seeds="192.168.1.1:33061,192.168.1.2:33061,192.168.1.3:33061"group_replication_bootstrap_group=OFF

六、跨机房部署

6.1 场景挑战

需求： - 主库在总部机房 - 从库在分部机房（异地灾备） - 两个机房网络不通 传统方案： - 专线：成本高 - 公网暴露MySQL端口：风险大

6.2 组网方案

使用组网软件（如星空组网）打通网络：

┌─────────────────────────────────────────────────────────┐ │ 组网虚拟局域网 │ │ │ │ ┌──────────────────┐ ┌──────────────────┐ │ │ │ 总部机房 │ │ 分部机房 │ │ │ │ │ │ │ │ │ │ Master │ │ Slave │ │ │ │ 10.10.0.1:3306 │ ←同步─│ 10.10.0.2:3306 │ │ │ │ │ │ │ │ │ └──────────────────┘ └──────────────────┘ │ │ │ └─────────────────────────────────────────────────────────┘

Slave配置：

-- 使用组网IP连接MasterCHANGE MASTERTOMASTER_HOST='10.10.0.1',-- 组网IPMASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='repl123',MASTER_AUTO_POSITION=1;STARTSLAVE;

优势：

• 不需要公网暴露3306端口
• 加密传输，安全可靠
• 配置简单
• 运维人员可通过组网远程管理

6.3 远程运维

# 通过组网IP远程连接mysql -h10.10.0.1 -P3306-uroot -p# 远程备份mysqldump -h10.10.0.1 -uroot -p mydb>backup.sql# 远程监控mysqlsh --uri root@10.10.0.1:3306 --js

七、监控与运维

7.1 监控指标

-- 复制延迟SHOWSLAVESTATUS\G-- Seconds_Behind_Master-- 线程状态SHOWPROCESSLIST;-- 复制错误SHOWSLAVESTATUS\G-- Last_Error, Last_IO_Error, Last_SQL_Error

7.2 监控脚本

#!/bin/bash# check_replication.shMYSQL_CMD="mysql -h 127.0.0.1 -P 3307 -urepl -prepl123"IO_RUNNING=$($MYSQL_CMD -e"SHOW SLAVE STATUS\G"|grep"Slave_IO_Running"|awk'{print$2}')SQL_RUNNING=$($MYSQL_CMD -e"SHOW SLAVE STATUS\G"|grep"Slave_SQL_Running"|awk'{print$2}')DELAY=$($MYSQL_CMD -e"SHOW SLAVE STATUS\G"|grep"Seconds_Behind_Master"|awk'{print$2}')echo"IO Thread:$IO_RUNNING"echo"SQL Thread:$SQL_RUNNING"echo"Delay:${DELAY}s"if["$IO_RUNNING"!="Yes"]||["$SQL_RUNNING"!="Yes"];thenecho"ALERT: Replication is broken!"# 发送告警fiif["$DELAY"-gt60];thenecho"ALERT: Replication delay > 60s"fi

7.3 常见问题处理

复制中断：

-- 查看错误SHOWSLAVESTATUS\G-- 跳过错误（谨慎）STOP SLAVE;SETGLOBALSQL_SLAVE_SKIP_COUNTER=1;STARTSLAVE;-- 或使用GTID跳过SETGTID_NEXT='xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx:N';BEGIN;COMMIT;SETGTID_NEXT='AUTOMATIC';STARTSLAVE;

主从切换：

-- 原Slave提升为MasterSTOP SLAVE;RESET SLAVEALL;SETGLOBALread_only=0;-- 原Master降为SlaveCHANGE MASTERTO...;SETGLOBALread_only=1;STARTSLAVE;

八、性能优化

8.1 并行复制

# MySQL 5.7+ slave_parallel_type = LOGICAL_CLOCK slave_parallel_workers = 4 slave_preserve_commit_order = 1

8.2 半同步复制

-- MasterINSTALL PLUGIN rpl_semi_sync_masterSONAME'semisync_master.so';SETGLOBALrpl_semi_sync_master_enabled=1;SETGLOBALrpl_semi_sync_master_timeout=10000;-- SlaveINSTALL PLUGIN rpl_semi_sync_slaveSONAME'semisync_slave.so';SETGLOBALrpl_semi_sync_slave_enabled=1;STOP SLAVE;STARTSLAVE;

8.3 参数优化

# binlog优化 binlog_cache_size = 4M max_binlog_size = 500M expire_logs_days = 7 # 复制优化 slave_net_timeout = 60 sync_relay_log = 10000 relay_log_recovery = 1

九、总结

MySQL主从复制要点：

1. 基础配置：server-id唯一，开启binlog
2. GTID模式：推荐使用，简化管理
3. 读写分离：中间件方案更优雅
4. 高可用：MHA/MGR实现自动故障转移
5. 跨机房：组网打通后正常同步
6. 监控告警：复制状态和延迟

生产环境清单：

☑ 主从复制配置完成 ☑ 复制用户权限最小化 ☑ 监控脚本部署 ☑ 备份策略制定 ☑ 故障切换演练

参考资料

1. MySQL官方复制文档：https://dev.mysql.com/doc/refman/8.0/en/replication.html
2. MySQL高可用：https://dev.mysql.com/doc/mysql-ha-scalability/en/

💡建议：生产环境务必使用GTID模式，配置半同步复制，定期进行主从切换演练。