深入学习MySQL（六）：主从复制与读写分离
在高并发场景下，单机MySQL往往难以满足业务需求。主从复制是MySQL高可用和读写分离的基础，本文将深入讲解主从复制原理、配置实战以及读写分离方案。
主从复制概述
什么是主从复制
主从复制（Master-Slave Replication）是指将主数据库的DDL和DML操作通过二进制日志传输到从数据库，从数据库对这些日志进行重放，从而实现数据同步。
主从复制的作用
|
作用 |
说明 |
|
数据备份 |
从库作为主库的实时备份 |
|
读写分离 |
主库写、从库读，提升并发能力 |
|
高可用 |
主库故障时可切换到从库 |
|
负载均衡 |
多个从库分担读请求 |
复制架构模式
一主一从：
┌────────┐ ┌────────┐
│ Master │────▶│ Slave │
└────────┘ └────────┘
一主多从：
┌────────┐
┌▶│ Slave1 │
┌────────┐ │ └────────┘
│ Master │───┤ ┌────────┐
└────────┘ ├▶│ Slave2 │
│ └────────┘
│ ┌────────┐
└▶│ Slave3 │
└────────┘
级联复制：
┌────────┐ ┌────────┐ ┌────────┐
│ Master │────▶│ Slave1 │────▶│ Slave2 │
└────────┘ └────────┘ └────────┘
双主复制：
┌────────┐◀───▶┌────────┐
│ Master1│ │ Master2│
└────────┘ └────────┘主从复制原理
复制流程
MySQL主从复制涉及三个线程：
┌─────────────────────────────────────────────────────────────┐
│ Master │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ Binary Log │ │
│ │ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ │ │
│ │ │Event│ │Event│ │Event│ │Event│ │Event│ │ │
│ │ └─────┘ └─────┘ └─────┘ └─────┘ └─────┘ │ │
│ └─────────────────────────────────────────────────────┘ │
│ │ │
│ ┌───────────┴───────────┐ │
│ │ Binlog Dump Thread │ │
│ └───────────┬───────────┘ │
└──────────────────────────│──────────────────────────────────┘
│ 发送binlog事件
▼
┌─────────────────────────────────────────────────────────────┐
│ Slave │
│ ┌───────────┴───────────┐ │
│ │ I/O Thread │ │
│ └───────────┬───────────┘ │
│ │ 写入中继日志 │
│ ▼ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ Relay Log │ │
│ │ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ │ │
│ │ │Event│ │Event│ │Event│ │Event│ │Event│ │ │
│ │ └─────┘ └─────┘ └─────┘ └─────┘ └─────┘ │ │
│ └─────────────────────────────────────────────────────┘ │
│ │ │
│ ┌───────────┴───────────┐ │
│ │ SQL Thread │ │
│ └───────────┬───────────┘ │
│ │ 执行SQL │
│ ▼ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ Data │ │
│ └─────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘三个线程的职责：
- Binlog Dump Thread（主库）：当从库连接主库时，主库创建此线程，负责读取binlog并发送给从库
- I/O Thread（从库）：负责接收主库发送的binlog事件，并写入本地的Relay Log（中继日志）
- SQL Thread（从库）：负责读取Relay Log中的事件，并在从库执行，实现数据同步
复制方式
异步复制（Asynchronous）
默认的复制方式，主库执行完事务后立即返回，不等待从库确认。
主库提交事务 ──▶ 写入binlog ──▶ 返回客户端
│
└──▶ 从库异步拉取（可能有延迟）优点：性能高，主库不受从库影响
缺点：主库故障时可能丢失数据
半同步复制（Semi-synchronous）
主库提交事务后，等待至少一个从库确认收到binlog后才返回。
-- 主库安装半同步插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
-- 从库安装半同步插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
-- 主库启用半同步
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 10000; -- 超时时间（毫秒）
-- 从库启用半同步
SET GLOBAL rpl_semi_sync_slave_enabled = 1;MySQL 8.0增强半同步（AFTER_SYNC）：
-- 设置等待点为sync之后（更安全）
SET GLOBAL rpl_semi_sync_master_wait_point = 'AFTER_SYNC';组复制（Group Replication）
MySQL 5.7.17引入的多主复制方案，基于Paxos协议实现。
┌─────────┐ ┌─────────┐ ┌─────────┐
│ Member1 │◀──▶│ Member2 │◀──▶│ Member3 │
└─────────┘ └─────────┘ └─────────┘
▲ ▲ ▲
└──────────────┼──────────────┘
│
Group CommunicationBinlog格式
-- 查看当前binlog格式
SHOW VARIABLES LIKE 'binlog_format';
|
格式 |
说明 |
优点 |
缺点 |
|
STATEMENT |
记录SQL语句 |
日志量小 |
某些函数可能导致主从不一致 |
|
ROW |
记录行数据变化 |
数据一致性高 |
日志量大 |
|
MIXED |
混合模式 |
自动选择 |
可能有不一致风险 |
推荐使用ROW格式：
[mysqld]
binlog_format = ROWGTID复制
什么是GTID
GTID（Global Transaction Identifier）是MySQL 5.6引入的全局事务标识符，格式为：
GTID = source_id:transaction_id
示例：3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5- source_id：主库的server_uuid
- transaction_id：事务序号，从1开始递增
GTID的优势
- 简化复制配置：不需要手动指定binlog文件和位置
- 故障切换简单：从库可以自动找到正确的复制位置
- 数据一致性：同一事务在所有节点有一样的GTID
配置GTID复制
主库配置（my.cnf）
[mysqld]
# 服务器ID，必须唯一
server_id = 1
# 启用GTID
gtid_mode = ON
enforce_gtid_consistency = ON
# binlog配置
log_bin = mysql-bin
binlog_format = ROW
# 从库也记录binlog（用于级联复制）
log_slave_updates = ON从库配置（my.cnf）
[mysqld]
# 服务器ID，必须唯一
server_id = 2
# 启用GTID
gtid_mode = ON
enforce_gtid_consistency = ON
# binlog配置
log_bin = mysql-bin
binlog_format = ROW
log_slave_updates = ON
# 中继日志
relay_log = relay-bin
# 从库只读
read_only = ON
super_read_only = ON创建复制用户（主库）
-- 创建复制专用账户
CREATE USER 'repl'@'%' IDENTIFIED BY 'Repl@123456';
-- 授予复制权限
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';
-- 刷新权限
FLUSH PRIVILEGES;配置从库复制
-- 配置主库连接信息
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = '192.168.1.100',
SOURCE_PORT = 3306,
SOURCE_USER = 'repl',
SOURCE_PASSWORD = 'Repl@123456',
SOURCE_AUTO_POSITION = 1; -- 使用GTID自动定位
-- 启动复制
START REPLICA;
-- 查看复制状态
SHOW REPLICA STATUSG验证复制状态
-- 查看复制状态（关键字段）
SHOW REPLICA STATUSG
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.1.100
Source_User: repl
Source_Port: 3306
Replica_IO_Running: Yes -- IO线程运行中
Replica_SQL_Running: Yes -- SQL线程运行中
Seconds_Behind_Source: 0 -- 复制延迟（秒）
Retrieved_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-100
Executed_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-100传统复制配置
如果不使用GTID，可以使用基于binlog位置的传统复制。
主库配置
[mysqld]
server_id = 1
log_bin = mysql-bin
binlog_format = ROW获取主库binlog位置
-- 锁定表（防止数据变化）
FLUSH TABLES WITH READ LOCK;
-- 查看binlog位置
SHOW MASTER STATUS;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000003 | 785 |
+------------------+----------+
-- 备份数据（另开终端）
mysqldump -u root -p --all-databases --master-data=2 > backup.sql
-- 解锁
UNLOCK TABLES;从库配置复制
-- 导入备份数据
mysql -u root -p < backup.sql
-- 配置主库连接
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = '192.168.1.100',
SOURCE_PORT = 3306,
SOURCE_USER = 'repl',
SOURCE_PASSWORD = 'Repl@123456',
SOURCE_LOG_FILE = 'mysql-bin.000003',
SOURCE_LOG_POS = 785;
-- 启动复制
START REPLICA;复制过滤
可以选择性地复制某些数据库或表。
主库过滤
[mysqld]
# 只复制指定数据库的binlog
binlog-do-db = db1
binlog-do-db = db2
# 忽略指定数据库
binlog-ignore-db = test
binlog-ignore-db = mysql从库过滤
[mysqld]
# 只复制指定数据库
replicate-do-db = db1
replicate-do-db = db2
# 忽略指定数据库
replicate-ignore-db = test
# 只复制指定表
replicate-do-table = db1.users
replicate-do-table = db1.orders
# 忽略指定表
replicate-ignore-table = db1.logs
# 使用通配符
replicate-wild-do-table = db1.%
replicate-wild-ignore-table = db1.temp_%并行复制
MySQL 5.6开始支持并行复制，提升从库回放速度。
MySQL 5.6：基于Schema的并行
[mysqld]
slave_parallel_workers = 4
slave_parallel_type = DATABASEMySQL 5.7+：基于组提交的并行
[mysqld]
# 并行工作线程数
slave_parallel_workers = 8
# 基于逻辑时钟的并行复制
slave_parallel_type = LOGICAL_CLOCK
# 保持事务提交顺序
slave_preserve_commit_order = ONMySQL 8.0：增强并行复制
[mysqld]
# 并行工作线程数
replica_parallel_workers = 16
# 基于逻辑时钟
replica_parallel_type = LOGICAL_CLOCK
# 保持提交顺序
replica_preserve_commit_order = ON
# 写集合并行复制（更高并行度）
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64复制监控与维护
监控复制状态
-- 查看复制状态
SHOW REPLICA STATUSG
-- 查看复制延迟
SELECT * FROM performance_schema.replication_applier_status_by_worker;
-- 查看复制通道
SELECT * FROM performance_schema.replication_connection_status;常用维护命令
-- 停止复制
STOP REPLICA;
-- 启动复制
START REPLICA;
-- 重置复制（谨慎使用）
RESET REPLICA ALL;
-- 跳过一个事务（传统复制）
SET GLOBAL sql_slave_skip_counter = 1;
START REPLICA;
-- 跳过一个GTID事务
SET GTID_NEXT = '3E11FA47-71CA-11E1-9E33-C80AA9429562:123';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START REPLICA;复制延迟排查
-- 1. 查看延迟时间
SHOW REPLICA STATUSG
-- 关注 Seconds_Behind_Source
-- 2. 查看从库进程状态
SHOW PROCESSLIST;
-- 3. 查看IO线程状态
SELECT * FROM performance_schema.replication_connection_statusG
-- 4. 查看SQL线程状态
SELECT * FROM performance_schema.replication_applier_statusG
-- 5. 查看各Worker线程状态
SELECT * FROM performance_schema.replication_applier_status_by_workerG复制延迟优化
- 增加并行复制线程
- 优化从库硬件（SSD、更多内存）
- 减少大事务
- 主库binlog使用ROW格式
- 从库关闭binlog（如果不需要级联复制）
[mysqld]
# 从库关闭binlog
skip-log-bin读写分离
读写分离原理
┌─────────────┐
│ 应用程序 │
└──────┬──────┘
│
┌──────▼──────┐
│ 中间件/代理 │
└──────┬──────┘
│
┌───────────────┼───────────────┐
│ │ │
┌─────▼─────┐ ┌─────▼─────┐ ┌─────▼─────┐
│ Master │ │ Slave1 │ │ Slave2 │
│ (写) │ │ (读) │ │ (读) │
└───────────┘ └───────────┘ └───────────┘读写分离方案
方案一：应用层实现
使用Spring的AbstractRoutingDataSource实现动态数据源切换。
1. 定义数据源类型枚举
public enum DataSourceType {
MASTER,
SLAVE
}2. 数据源上下文
public class DataSourceContextHolder {
private static final ThreadLocal<DataSourceType> CONTEXT = new ThreadLocal<>();
public static void setDataSourceType(DataSourceType type) {
CONTEXT.set(type);
}
public static DataSourceType getDataSourceType() {
return CONTEXT.get();
}
public static void clear() {
CONTEXT.remove();
}
}3. 动态数据源
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.getDataSourceType();
}
}4. 数据源配置
@Configuration
public class DataSourceConfig {
@Bean
@ConfigurationProperties("spring.datasource.master")
public DataSource masterDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("spring.datasource.slave")
public DataSource slaveDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
@Primary
public DataSource dynamicDataSource(
@Qualifier("masterDataSource") DataSource master,
@Qualifier("slaveDataSource") DataSource slave) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DataSourceType.MASTER, master);
targetDataSources.put(DataSourceType.SLAVE, slave);
DynamicDataSource dynamicDataSource = new DynamicDataSource();
dynamicDataSource.setTargetDataSources(targetDataSources);
dynamicDataSource.setDefaultTargetDataSource(master);
return dynamicDataSource;
}
}5. 读写分离注解
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ReadOnly {
}6. AOP切面
@Aspect
@Component
@Order(-1) // 确保在事务之前执行
public class DataSourceAspect {
@Before("@annotation(readOnly)")
public void setReadDataSource(ReadOnly readOnly) {
DataSourceContextHolder.setDataSourceType(DataSourceType.SLAVE);
}
@Before("@annotation(org.springframework.transaction.annotation.Transactional)")
public void setWriteDataSource() {
DataSourceContextHolder.setDataSourceType(DataSourceType.MASTER);
}
@After("@annotation(readOnly) || @annotation(org.springframework.transaction.annotation.Transactional)")
public void clearDataSource(ReadOnly readOnly) {
DataSourceContextHolder.clear();
}
}7. 使用示例
@Service
public class UserService {
@Autowired
private UserMapper userMapper;
// 读操作走从库
@ReadOnly
public User getById(Long id) {
return userMapper.selectById(id);
}
// 写操作走主库
@Transactional
public void save(User user) {
userMapper.insert(user);
}
}方案二：ShardingSphere-JDBC
使用ShardingSphere实现透明化读写分离。
1. 添加依赖
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.3.2</version>
</dependency>
2. 配置文件
spring:
shardingsphere:
datasource:
names: master,slave1,slave2
master:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.1.100:3306/mydb
username: root
password: root123
slave1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.1.101:3306/mydb
username: root
password: root123
slave2:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.1.102:3306/mydb
username: root
password: root123
rules:
readwrite-splitting:
data-sources:
readwrite_ds:
static-strategy:
write-data-source-name: master
read-data-source-names: slave1,slave2
load-balancer-name: round_robin
load-balancers:
round_robin:
type: ROUND_ROBIN
props:
sql-show: true3. 强制走主库
@Service
public class OrderService {
@Autowired
private OrderMapper orderMapper;
// 需要读取最新数据时，强制走主库
public Order getLatestOrder(Long userId) {
// 使用Hint强制路由到主库
try (HintManager hintManager = HintManager.getInstance()) {
hintManager.setWriteRouteOnly();
return orderMapper.selectLatestByUserId(userId);
}
}
}方案三：MySQL Router
MySQL官方提供的中间件，支持读写分离和故障切换。
1. 安装MySQL Router
# Ubuntu/Debian
apt-get install mysql-router
# CentOS/RHEL
yum install mysql-router2. 配置文件（mysqlrouter.conf）
[DEFAULT]
logging_folder = /var/log/mysqlrouter
runtime_folder = /var/run/mysqlrouter
[logger]
level = INFO
[routing:primary]
bind_address = 0.0.0.0
bind_port = 6446
destinations = 192.168.1.100:3306
routing_strategy = first-available
mode = read-write
[routing:secondary]
bind_address = 0.0.0.0
bind_port = 6447
destinations = 192.168.1.101:3306,192.168.1.102:3306
routing_strategy = round-robin
mode = read-only3. 应用连接
# 写操作连接
spring:
datasource:
master:
url: jdbc:mysql://router-host:6446/mydb
slave:
url: jdbc:mysql://router-host:6447/mydb方案四：ProxySQL
高性能的MySQL代理，支持读写分离、查询缓存、故障切换。
1. 安装ProxySQL
# 添加仓库并安装
yum install proxysql
# 启动服务
systemctl start proxysql2. 配置ProxySQL
-- 连接ProxySQL管理端口
mysql -u admin -padmin -h 127.0.0.1 -P 6032
-- 添加后端MySQL服务器
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight)
VALUES (10, '192.168.1.100', 3306, 1); -- 主库
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight)
VALUES (20, '192.168.1.101', 3306, 1); -- 从库1
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight)
VALUES (20, '192.168.1.102', 3306, 1); -- 从库2
-- 配置读写分离规则
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT.*FOR UPDATE$', 10, 1); -- SELECT FOR UPDATE走主库
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (2, 1, '^SELECT', 20, 1); -- 普通SELECT走从库
-- 添加用户
INSERT INTO mysql_users(username, password, default_hostgroup)
VALUES ('app_user', 'password', 10);
-- 加载配置
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
-- 保存配置
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;
SAVE MYSQL USERS TO DISK;延迟复制
延迟复制可以让从库故意延迟一段时间再执行主库的事务，用于误操作恢复。
-- 设置从库延迟1小时
CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 3600;
-- 查看延迟设置
SHOW REPLICA STATUSG
-- SQL_Delay: 3600
-- SQL_Remaining_Delay: NULL (当前无延迟)应用场景：
- 误删除数据恢复
- 测试环境数据准备
- 审计和合规需求
多源复制
MySQL 5.7+支持一个从库从多个主库复制数据。
-- 配置第一个复制通道
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = '192.168.1.100',
SOURCE_USER = 'repl',
SOURCE_PASSWORD = 'password',
SOURCE_AUTO_POSITION = 1
FOR CHANNEL 'master1';
-- 配置第二个复制通道
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = '192.168.1.101',
SOURCE_USER = 'repl',
SOURCE_PASSWORD = 'password',
SOURCE_AUTO_POSITION = 1
FOR CHANNEL 'master2';
-- 启动所有通道
START REPLICA FOR CHANNEL 'master1';
START REPLICA FOR CHANNEL 'master2';
-- 查看各通道状态
SHOW REPLICA STATUS FOR CHANNEL 'master1'G
SHOW REPLICA STATUS FOR CHANNEL 'master2'G常见问题与解决方案
问题一：主从数据不一致
排查步骤：
-- 1. 使用pt-table-checksum检查
pt-table-checksum --host=master --user=root --password=xxx
--databases=mydb --tables=users
-- 2. 使用pt-table-sync修复
pt-table-sync --execute --sync-to-master
h=slave,D=mydb,t=users问题二：复制中断
-- 查看错误信息
SHOW REPLICA STATUSG
-- 关注 Last_Error 字段
-- 跳过错误（谨慎使用）
STOP REPLICA;
SET GLOBAL sql_slave_skip_counter = 1;
START REPLICA;
-- GTID模式跳过
SET GTID_NEXT = 'xxx:123';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START REPLICA;问题三：从库重建
# 使用xtrabackup进行全量备份
xtrabackup --backup --target-dir=/backup/full
--host=master --user=root --password=xxx
# 准备备份
xtrabackup --prepare --target-dir=/backup/full
# 恢复到从库
xtrabackup --copy-back --target-dir=/backup/full
# 配置复制
mysql> CHANGE REPLICATION SOURCE TO ...
mysql> START REPLICA;最佳实践
复制配置提议
[mysqld]
# 必须配置
server_id = 1 # 唯一ID
gtid_mode = ON # 启用GTID
enforce_gtid_consistency = ON # 强制GTID一致性
log_bin = mysql-bin # 启用binlog
binlog_format = ROW # 行格式
log_slave_updates = ON # 从库也记录binlog
# 性能优化
sync_binlog = 1 # 每次事务同步binlog
innodb_flush_log_at_trx_commit = 1 # 每次事务刷盘
# 并行复制
replica_parallel_workers = 8
replica_parallel_type = LOGICAL_CLOCK
replica_preserve_commit_order = ON
# 从库只读
read_only = ON
super_read_only = ON监控指标
|
指标 |
说明 |
告警阈值 |
|
Seconds_Behind_Master |
复制延迟 |
> 60秒 |
|
Slave_IO_Running |
IO线程状态 |
!= Yes |
|
Slave_SQL_Running |
SQL线程状态 |
!= Yes |
|
Relay_Log_Space |
中继日志空间 |
> 10GB |
故障切换检查清单
- 确认所有从库已同步完成
- 停止应用写入
- 选择数据最新的从库提升为主库
- 重新配置其他从库指向新主库
- 更新应用连接配置
- 恢复应用写入
总结
本文详细介绍了MySQL主从复制的原理、配置方法和读写分离方案：
- 复制原理：三个线程（Binlog Dump、I/O、SQL）协作完成数据同步
- GTID复制：推荐使用GTID模式，简化配置和故障切换
- 并行复制：通过多线程提升从库回放速度
- 读写分离：可选择应用层、ShardingSphere、MySQL Router或ProxySQL方案
- 监控维护：关注复制延迟，及时处理复制异常
主从复制是MySQL高可用架构的基础，下一篇我们将介绍更高级的高可用方案和分库分表策略。
© 版权声明
文章版权归作者所有，未经允许请勿转载。
相关文章
暂无评论...
