概述
MySQL主从复制是高可用架构的基石,通过Binlog实现数据同步,支持异步、半同步、组复制三种模式。读写分离在主从架构基础上,将写操作路由到主库、读操作路由到从库,实现读写负载分离,大幅提升系统吞吐量。本文深入剖析MySQL复制机制、GTID原理、主从延迟优化、ShardingSphere集成方案,结合电商订单系统实战案例,全面讲解从原理到实践的完整落地路径,帮助读者构建高性能、高可用的数据库架构。
一、理论知识与核心概念
1.1 为什么需要主从复制?
在生产环境中,单机MySQL存在以下问题:
性能瓶颈:
- 读写请求都集中在一台服务器
- 高并发场景下,单机CPU、内存、磁盘I/O成为瓶颈
- 无法水平扩展读能力
可用性风险:
- 单点故障(SPOF): 服务器宕机,整个系统不可用
- 硬件故障(磁盘损坏): 数据丢失,恢复时间长
- 维护升级: 需要停机,影响业务
数据安全:
- 实时备份需求: 传统备份存在数据丢失窗口
- 容灾需求: 需要异地容灾,防止区域性故障
主从复制解决方案:
主库 (Master) 从库 (Slave1, Slave2, ...)
| |
|-- 写操作 (INSERT/UPDATE/DELETE)
|-- 生成Binlog |-- 读取主库Binlog
|-- Dump线程发送Binlog |-- IO线程接收Binlog
|-- 写入Relay Log
|-- SQL线程重放Binlog
|-- 读操作 (SELECT)
优势:
- ✅ 读写分离: 主库写,从库读,大幅提升吞吐量
- ✅ 高可用: 主库故障,快速切换到从库
- ✅ 实时备份: 从库作为实时数据备份
- ✅ 异地容灾: 从库部署到异地机房
- ✅ 数据分析: 从库专门用于报表统计,不影响主库性能
1.2 主从复制核心概念
Binlog (Binary Log)
- 定义: MySQL Server层的逻辑日志,记录所有DDL和DML语句
- 作用: 主从复制的数据源,主库通过Binlog将数据变更同步到从库
- 格式: Statement (SQL语句) / Row (行变化) / Mixed (混合)
- 存储:
mysql-bin.000001,mysql-bin.000002... (追加写,不覆盖)
GTID (Global Transaction Identifier)
- 定义: 全局事务ID,格式为
server_uuid:transaction_id - 示例:
3E11FA47-71CA-11E1-9E33-C80AA9429562:23 - 优势:
- 每个事务唯一标识,全局统一
- 简化主从配置,无需指定Binlog文件和位置
- 支持自动故障转移,从库自动定位同步点
- MySQL 5.6+推荐使用
Relay Log (中继日志)
- 定义: 从库的中继日志,存储从主库接收的Binlog事件
- 作用: IO线程将主库Binlog写入Relay Log,SQL线程读取Relay Log执行
- 存储:
relay-bin.000001,relay-bin.000002...
复制线程
- Dump线程 (主库): 读取主库Binlog,发送给从库IO线程
- IO线程 (从库): 接收主库Binlog,写入Relay Log
- SQL线程 (从库): 读取Relay Log,重放SQL,完成数据同步
1.3 三种复制模式对比
异步复制 (Asynchronous Replication) - 默认模式
主库执行流程:
1. 执行SQL,修改数据
2. 写入Binlog
3. 提交事务
4. 立即返回客户端成功 ✓
5. 异步发送Binlog给从库 (不等待从库响应)
优点: 性能最高
缺点: 主库crash可能丢失未同步到从库的数据
适用: 对数据一致性要求不高的场景
半同步复制 (Semi-Synchronous Replication) - 推荐生产环境
主库执行流程:
1. 执行SQL,修改数据
2. 写入Binlog
3. 提交事务
4. 等待至少1个从库ACK ⏳
5. 收到ACK后返回客户端成功 ✓
优点: 数据安全性高,至少1个从库确认收到Binlog
缺点: 性能略低于异步复制(等待ACK)
适用: 生产环境强烈推荐
组复制 (Group Replication / MGR) - 高可用方案
基于Paxos分布式一致性协议:
1. 主节点接收写请求
2. 生成事务并广播到所有节点
3. 等待多数节点ACK (如3节点需要2个ACK)
4. 提交事务,返回客户端成功
优点: 高可用,强一致性,自动故障转移
缺点: 性能较低,配置复杂
适用: 核心业务的高可用场景
二、MySQL复制原理详解
2.1 Binlog复制机制
主库端流程
步骤1: 客户端执行SQL
UPDATE users SET name='张三' WHERE id=1;
步骤2: 执行器调用InnoDB引擎
- 修改Buffer Pool中的数据页
- 写入Undo Log (保存旧值,支持回滚)
- 写入Redo Log (prepare状态)
步骤3: 写入Binlog
- 事务提交时写入Binlog
- Row格式记录:
### UPDATE `test`.`users`
### WHERE
### @1=1 /* id */
### @2='李四' /* name (旧值) */
### SET
### @2='张三' /* name (新值) */
步骤4: 提交事务
- Redo Log状态改为commit
- 事务提交完成
步骤5: Dump线程发送Binlog
- 主库为每个从库创建一个Dump线程
- 读取Binlog事件,通过网络发送给从库IO线程
从库端流程
步骤6: IO线程接收Binlog
- 从库IO线程连接到主库
- 请求指定位置的Binlog (基于Position或GTID)
- 接收主库Dump线程发送的Binlog事件
步骤7: 写入Relay Log
- 将Binlog事件写入Relay Log
- 更新master.info文件,记录同步位置
步骤8: SQL线程重放Binlog
- SQL线程读取Relay Log
- 解析Binlog事件,还原为SQL语句
- 在从库上执行SQL,修改数据
步骤9: 更新同步位置
- 更新relay-log.info文件,记录执行位置
- 从库数据与主库保持一致
2.2 GTID复制原理
传统复制方式的问题
-- 从库配置 (传统方式)
CHANGE MASTER TO
MASTER_HOST='192.168.1.100',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000003', -- 必须指定Binlog文件
MASTER_LOG_POS=1273; -- 必须指定位置
问题:
1. 主库切换时,需要手动找到新主库的Binlog文件和位置
2. 故障转移复杂,容易出错
3. 多级复制配置繁琐
GTID复制方式
-- 从库配置 (GTID方式)
CHANGE MASTER TO
MASTER_HOST='192.168.1.100',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_AUTO_POSITION=1; -- 自动定位,无需指定文件和位置
优势:
1. 每个事务全局唯一标识
2. 从库自动定位同步点
3. 故障转移简单,自动选择新主库
4. 支持多源复制
GTID格式
格式: server_uuid:transaction_id
示例:
3E11FA47-71CA-11E1-9E33-C80AA9429562:1
3E11FA47-71CA-11E1-9E33-C80AA9429562:2
3E11FA47-71CA-11E1-9E33-C80AA9429562:3
GTID集合表示:
3E11FA47-71CA-11E1-9E33-C80AA9429562:1-10
表示server_uuid为3E11FA47的服务器,事务ID 1到10
GTID复制流程
主库:
1. 开启GTID: gtid_mode=ON
2. 每个事务自动分配GTID
3. 写入Binlog时记录GTID
从库:
1. 开启GTID: gtid_mode=ON
2. 维护已执行的GTID集合: gtid_executed
3. IO线程请求Binlog时,发送gtid_executed
4. 主库Dump线程跳过已执行的GTID,发送新事务
5. SQL线程执行后,更新gtid_executed
2.3 半同步复制详解
配置参数
-- 主库配置
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_wait_for_slave_count = 1; -- 等待1个从库ACK
SET GLOBAL rpl_semi_sync_master_timeout = 1000; -- 超时1秒
-- 从库配置
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
两种等待时机
AFTER_SYNC (默认,推荐):
1. 主库写Binlog
2. 等待从库ACK
3. 提交事务
4. 返回客户端
优点: 主库crash不会丢数据,从库一定有最新数据
缺点: 性能略低
AFTER_COMMIT:
1. 主库写Binlog
2. 提交事务
3. 等待从库ACK
4. 返回客户端
优点: 性能稍高
缺点: 主库crash可能丢失事务 (已提交但从库未收到)
配置方式
-- 推荐AFTER_SYNC
SET GLOBAL rpl_semi_sync_master_wait_point = 'AFTER_SYNC';
超时退化机制
场景: 从库网络故障,无法返回ACK
流程:
1. 主库等待从库ACK
2. 超时时间到达 (如1秒)
3. 自动退化为异步复制
4. 立即返回客户端成功
5. 记录警告日志
恢复:
- 从库恢复后,自动切换回半同步复制
- 查看状态: SHOW STATUS LIKE 'Rpl_semi_sync%';
2.4 组复制(MGR)原理
单主模式 (Single-Primary)
架构:
Primary节点 (主节点): 处理所有写操作
Secondary节点 (从节点): 处理读操作,同步数据
写操作流程:
1. 客户端连接到Primary节点
2. Primary执行写操作
3. 广播事务到所有节点 (基于Paxos协议)
4. 等待多数节点ACK (如3节点需要2个ACK)
5. 提交事务,返回客户端成功
故障转移:
- Primary节点故障,自动选举新Primary
- 选举时间: 通常10秒内完成
- 客户端自动重连到新Primary
多主模式 (Multi-Primary)
架构:
所有节点都是Primary节点,都可以处理写操作
写操作流程:
1. 客户端连接到任意节点
2. 节点执行写操作
3. 广播事务到所有节点
4. 冲突检测 (如两个节点同时修改同一行)
5. 提交或回滚
冲突处理:
- 使用first-commit-wins策略
- 后提交的事务回滚
- 适合写冲突较少的场景
配置参数
[mysqld]
# 开启组复制
plugin-load-add='group_replication.so'
# 组名 (UUID格式)
group_replication_group_name = "3E11FA47-71CA-11E1-9E33-C80AA9429562"
# 单主模式 (ON) 或 多主模式 (OFF)
group_replication_single_primary_mode = ON
# 组成员列表
group_replication_group_seeds = "192.168.1.100:33061,192.168.1.101:33061,192.168.1.102:33061"
# 是否引导组 (仅首个节点启动时设置为ON)
group_replication_bootstrap_group = OFF
# 本地地址
group_replication_local_address = "192.168.1.100:33061"
三、主从复制搭建实战
3.1 环境准备
服务器规划
主库 (Master):
- IP: 192.168.1.100
- 端口: 3306
- 角色: 处理所有写操作,部分读操作
从库1 (Slave1):
- IP: 192.168.1.101
- 端口: 3306
- 角色: 处理读操作
从库2 (Slave2):
- IP: 192.168.1.102
- 端口: 3306
- 角色: 处理读操作
软件版本
MySQL: 8.0.35+
操作系统: CentOS 7.9 / Ubuntu 20.04
3.2 主库配置
配置文件 /etc/my.cnf
[mysqld]
# ============ 基础配置 ============
server-id = 1 # 主库server-id,全局唯一
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
# ============ Binlog配置 ============
log-bin = mysql-bin # 开启Binlog
binlog_format = ROW # 使用Row格式 (推荐)
max_binlog_size = 1G # 单个Binlog文件最大1GB
binlog_expire_logs_seconds = 604800 # Binlog保留7天
sync_binlog = 1 # 每次提交刷盘,最安全
# ============ GTID配置 ============
gtid_mode = ON # 开启GTID
enforce_gtid_consistency = ON # 强制GTID一致性
# ============ 半同步复制配置 ============
plugin-load-add = rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled = 1 # 开启半同步复制
rpl_semi_sync_master_wait_for_slave_count = 1 # 等待1个从库ACK
rpl_semi_sync_master_timeout = 1000 # 超时1秒
rpl_semi_sync_master_wait_point = AFTER_SYNC # 推荐AFTER_SYNC
# ============ InnoDB配置 ============
innodb_flush_log_at_trx_commit = 1 # 每次提交刷Redo Log
innodb_buffer_pool_size = 4G # Buffer Pool大小
innodb_log_file_size = 1G # Redo Log文件大小
# ============ 其他配置 ============
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
创建复制用户
-- 创建复制用户
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'Repl@2024';
-- 授予REPLICATION SLAVE权限
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
-- 刷新权限
FLUSH PRIVILEGES;
-- 查看主库状态
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000001 | 1273 | | | 3e11fa47-71ca-11e1-9e33:1-10 |
+------------------+----------+--------------+------------------+-------------------------------------------+
3.3 从库配置
配置文件 /etc/my.cnf (Slave1: 192.168.1.101)
[mysqld]
# ============ 基础配置 ============
server-id = 2 # 从库server-id,全局唯一
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
# ============ Relay Log配置 ============
relay_log = relay-bin # Relay Log文件名
relay_log_recovery = ON # 开启Relay Log自动恢复
# ============ Binlog配置 (可选,从库级联复制需要) ============
log-bin = mysql-bin
binlog_format = ROW
max_binlog_size = 1G
# ============ GTID配置 ============
gtid_mode = ON
enforce_gtid_consistency = ON
# ============ 半同步复制配置 ============
plugin-load-add = rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled = 1 # 开启半同步复制
# ============ 并行复制配置 (MySQL 5.7+) ============
slave_parallel_type = LOGICAL_CLOCK # 逻辑时钟并行复制
slave_parallel_workers = 4 # 4个并行线程
# ============ 只读配置 ============
read_only = 1 # 普通用户只读
super_read_only = 1 # 超级用户也只读
# ============ InnoDB配置 ============
innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_size = 4G
innodb_log_file_size = 1G
# ============ 其他配置 ============
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
从库2配置类似,只需修改server-id=3
3.4 配置主从复制
从库执行 (GTID方式)
-- 停止复制 (如果之前配置过)
STOP SLAVE;
-- 配置主库信息
CHANGE MASTER TO
MASTER_HOST='192.168.1.100',
MASTER_USER='repl',
MASTER_PASSWORD='Repl@2024',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1; -- 使用GTID自动定位
-- 启动复制
START SLAVE;
-- 查看复制状态
SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1273
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 500
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes ← 必须是Yes
Slave_SQL_Running: Yes ← 必须是Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1273
Relay_Log_Space: 707
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0 ← 主从延迟,0表示无延迟
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 3e11fa47-71ca-11e1-9e33-c80aa9429562
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 3e11fa47-71ca-11e1-9e33:1-10
Executed_Gtid_Set: 3e11fa47-71ca-11e1-9e33:1-10
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
关键状态检查
-- 复制正常的必要条件:
✅ Slave_IO_Running: Yes
✅ Slave_SQL_Running: Yes
✅ Seconds_Behind_Master: 0 (或很小的值)
✅ Last_IO_Error: 空
✅ Last_SQL_Error: 空
3.5 验证主从复制
主库插入数据
-- 主库执行
CREATE DATABASE test_replication;
USE test_replication;
CREATE TABLE users (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie');
SELECT * FROM users;
+----+---------+---------------------+
| id | name | created_at |
+----+---------+---------------------+
| 1 | Alice | 2024-01-15 10:00:00 |
| 2 | Bob | 2024-01-15 10:00:01 |
| 3 | Charlie | 2024-01-15 10:00:02 |
+----+---------+---------------------+
从库查询数据
-- 从库执行
USE test_replication;
SELECT * FROM users;
+----+---------+---------------------+
| id | name | created_at |
+----+---------+---------------------+
| 1 | Alice | 2024-01-15 10:00:00 | ← 数据已同步!
| 2 | Bob | 2024-01-15 10:00:01 |
| 3 | Charlie | 2024-01-15 10:00:02 |
+----+---------+---------------------+
-- 查看GTID
SHOW VARIABLES LIKE 'gtid_executed';
+---------------+-------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------+
| gtid_executed | 3e11fa47-71ca-11e1-9e33:1-13 |
+---------------+-------------------------------------------+
主从复制搭建成功!
四、主从延迟问题分析与解决
4.1 主从延迟产生原因
Seconds_Behind_Master指标
SHOW SLAVE STATUS\G
Seconds_Behind_Master: 120 -- 从库延迟120秒
延迟原因分析
1. 主库写入压力大
场景: 电商大促,主库TPS达到10000
问题: 从库SQL线程单线程重放,TPS只有1000
结果: 延迟越来越大
主库: 10000 TPS (多线程并发写入)
从库: 1000 TPS (SQL线程单线程重放)
延迟: 每秒增加9000个事务的延迟
2. 从库硬件性能差
主库: SSD + 32核CPU + 128GB内存
从库: HDD + 8核CPU + 32GB内存
影响:
- HDD随机I/O性能远低于SSD
- CPU核心数不足,并行复制受限
- 内存小,Buffer Pool命中率低
3. 大事务导致延迟
-- 主库执行大事务
BEGIN;
DELETE FROM orders WHERE create_time < '2023-01-01'; -- 删除100万条数据
COMMIT;
问题:
1. 主库并发执行,但Binlog中是一个大事务
2. 从库SQL线程串行执行,需要很长时间
3. 这个大事务期间,后续事务全部阻塞
4. 延迟急剧增大
4. 锁冲突
场景:
主库: 事务A修改id=1的记录
从库: 正在重放修改id=1的事务B
冲突:
- 事务B持有id=1的行锁
- 事务A的重放被阻塞
- Seconds_Behind_Master增大
5. Binlog传输延迟
原因:
- 主从库跨地域部署 (如北京到上海)
- 网络带宽不足
- 网络抖动
影响:
- IO线程接收Binlog延迟
- Relay Log写入延迟
- 整体同步延迟
4.2 并行复制优化
MySQL 5.6并行复制 (基于Schema)
[mysqld]
# 按数据库(Schema)并行
slave_parallel_type = DATABASE
slave_parallel_workers = 4
限制: 只有不同数据库的事务才能并行,同一数据库内串行
MySQL 5.7+并行复制 (基于逻辑时钟)
[mysqld]
# 基于逻辑时钟并行复制 (推荐)
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
# 并行复制保持提交顺序
slave_preserve_commit_order = ON
原理:
1. 主库并发执行的事务,从库也可以并行重放
2. 通过last_committed和sequence_number判断
3. last_committed相同的事务可以并行
查看并行复制状态
-- 查看并行复制worker状态
SELECT * FROM performance_schema.replication_applier_status_by_worker\G
*************************** 1. row ***************************
CHANNEL_NAME:
WORKER_ID: 1
THREAD_ID: 100
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION: 3e11fa47-71ca-11e1-9e33:100
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2024-01-15 10:00:00.000000
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2024-01-15 10:00:00.000000
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2024-01-15 10:00:00.100000
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2024-01-15 10:00:00.200000
APPLYING_TRANSACTION:
APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
-- 有8个worker,说明并行复制已启用
4.3 大事务拆分
问题示例
-- 一次性删除100万条数据
DELETE FROM orders WHERE create_time < '2023-01-01';
影响:
- Binlog中记录100万行删除
- 从库SQL线程重放需要很长时间
- 后续事务全部阻塞
优化方案: 拆分为小事务
-- 每次删除1000条
DELIMITER $$
CREATE PROCEDURE batch_delete_orders()
BEGIN
DECLARE rows_deleted INT DEFAULT 1000;
WHILE rows_deleted = 1000 DO
DELETE FROM orders
WHERE create_time < '2023-01-01'
LIMIT 1000;
SET rows_deleted = ROW_COUNT();
-- 每次删除后commit,避免大事务
COMMIT;
-- 休眠10ms,降低主库压力
SELECT SLEEP(0.01);
END WHILE;
END$$
DELIMITER ;
-- 执行存储过程
CALL batch_delete_orders();
优点:
1. 每次只删除1000条,事务小
2. 从库重放快,不会长时间阻塞
3. 主库压力分散
4. 可以随时中断
4.4 延迟监控与告警
监控脚本
#!/bin/bash
# 监控主从延迟,超过阈值发送告警
MYSQL_USER="monitor"
MYSQL_PASSWORD="password"
THRESHOLD=60 # 延迟超过60秒告警
delay=$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')
if [ "$delay" == "NULL" ]; then
echo "ERROR: Replication stopped!"
# 发送告警
send_alert "MySQL复制已停止"
elif [ $delay -gt $THRESHOLD ]; then
echo "WARNING: Replication delay is $delay seconds"
# 发送告警
send_alert "MySQL主从延迟${delay}秒"
else
echo "OK: Replication delay is $delay seconds"
fi
Prometheus监控
# mysqld-exporter配置
global:
scrape_interval: 15s
scrape_configs:
- job_name: 'mysql'
static_configs:
- targets: ['192.168.1.101:9104', '192.168.1.102:9104']
# 告警规则
groups:
- name: mysql_replication
rules:
- alert: MysqlReplicationLag
expr: mysql_slave_status_seconds_behind_master > 60
for: 5m
labels:
severity: warning
annotations:
summary: "MySQL主从延迟超过60秒"
description: "从库{{ $labels.instance }}延迟{{ $value }}秒"
- alert: MysqlReplicationStopped
expr: mysql_slave_status_slave_sql_running == 0
for: 1m
labels:
severity: critical
annotations:
summary: "MySQL复制已停止"
description: "从库{{ $labels.instance }}的SQL线程已停止"
五、读写分离架构设计
5.1 读写分离原理
核心思想
写操作 (INSERT/UPDATE/DELETE) → 主库
读操作 (SELECT) → 从库 (负载均衡)
优势:
1. 主库专注写操作,减轻压力
2. 从库分担读操作,大幅提升吞吐量
3. 读写分离后,可以根据读写比例独立扩展
典型场景
电商订单系统:
- 读写比例: 9:1 (90%读,10%写)
- 架构: 1主 + 3从
- 效果: 读能力提升3倍
内容网站:
- 读写比例: 99:1 (99%读,1%写)
- 架构: 1主 + 10从
- 效果: 读能力提升10倍
5.2 负载均衡算法
1. 轮询 (Round Robin) - 最常用
public class RoundRobinLoadBalancer {
private List<DataSource> slaves = Arrays.asList(slave1, slave2, slave3);
private AtomicInteger counter = new AtomicInteger(0);
public DataSource getReadDataSource() {
int index = counter.getAndIncrement() % slaves.size();
return slaves.get(index);
}
}
特点:
- 请求均匀分配: slave1 → slave2 → slave3 → slave1 ...
- 简单高效
- 不考虑从库负载差异
2. 随机 (Random)
public class RandomLoadBalancer {
private List<DataSource> slaves = Arrays.asList(slave1, slave2, slave3);
private Random random = new Random();
public DataSource getReadDataSource() {
int index = random.nextInt(slaves.size());
return slaves.get(index);
}
}
特点:
- 随机选择从库
- 长期运行后近似均匀分布
- 实现简单
3. 权重 (Weighted Round Robin) - 推荐
public class WeightedLoadBalancer {
private List<WeightedDataSource> slaves = Arrays.asList(
new WeightedDataSource(slave1, 60), // 权重60%
new WeightedDataSource(slave2, 30), // 权重30%
new WeightedDataSource(slave3, 10) // 权重10%
);
public DataSource getReadDataSource() {
int totalWeight = slaves.stream().mapToInt(WeightedDataSource::getWeight).sum();
int randomWeight = ThreadLocalRandom.current().nextInt(totalWeight);
int currentWeight = 0;
for (WeightedDataSource slave : slaves) {
currentWeight += slave.getWeight();
if (randomWeight < currentWeight) {
return slave.getDataSource();
}
}
return slaves.get(0).getDataSource();
}
}
适用场景:
- 从库硬件配置不同
- slave1: 高配服务器,权重60%
- slave2: 中配服务器,权重30%
- slave3: 低配服务器,权重10%
4. 最少连接 (Least Connections)
public class LeastConnectionsLoadBalancer {
private List<DataSource> slaves = Arrays.asList(slave1, slave2, slave3);
public DataSource getReadDataSource() {
return slaves.stream()
.min(Comparator.comparingInt(this::getActiveConnections))
.orElse(slaves.get(0));
}
private int getActiveConnections(DataSource ds) {
// 获取当前活跃连接数
HikariDataSource hikari = (HikariDataSource) ds;
return hikari.getHikariPoolMXBean().getActiveConnections();
}
}
适用场景:
- 长连接场景
- 连接持续时间差异大
5.3 ShardingSphere-JDBC集成
引入依赖
<!-- pom.xml -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.3.2</version>
</dependency>
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>5.0.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
完整配置 application.yml
spring:
shardingsphere:
# 数据源配置
datasource:
names: master,slave0,slave1
# 主库配置
master:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.1.100:3306/ecommerce?useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
username: root
password: password
hikari:
maximum-pool-size: 50
minimum-idle: 10
connection-timeout: 30000
idle-timeout: 600000
max-lifetime: 1800000
# 从库0配置
slave0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.1.101:3306/ecommerce?useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
username: root
password: password
hikari:
maximum-pool-size: 50
minimum-idle: 10
connection-timeout: 30000
idle-timeout: 600000
max-lifetime: 1800000
# 从库1配置
slave1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.1.102:3306/ecommerce?useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
username: root
password: password
hikari:
maximum-pool-size: 50
minimum-idle: 10
connection-timeout: 30000
idle-timeout: 600000
max-lifetime: 1800000
# 读写分离规则
rules:
readwrite-splitting:
data-sources:
# 逻辑数据源名称
myds:
static-strategy:
write-data-source-name: master # 写操作路由到master
read-data-source-names: slave0,slave1 # 读操作路由到slave0,slave1
load-balancer-name: round-robin # 负载均衡算法
# 负载均衡算法配置
load-balancers:
round-robin:
type: ROUND_ROBIN # 轮询算法
random:
type: RANDOM # 随机算法
weight:
type: WEIGHT # 权重算法
props:
slave0: 60 # slave0权重60%
slave1: 40 # slave1权重40%
# 属性配置
props:
sql-show: true # 打印SQL日志,便于调试
业务代码 (完全透明)
// Controller层
@RestController
@RequestMapping("/api/orders")
public class OrderController {
@Autowired
private OrderService orderService;
// 创建订单 (写操作,自动路由到主库)
@PostMapping("/create")
public Response<OrderDTO> createOrder(@RequestBody OrderCreateRequest request) {
OrderDTO order = orderService.createOrder(request);
return Response.success(order);
}
// 查询订单 (读操作,自动路由到从库)
@GetMapping("/{orderId}")
public Response<OrderDTO> getOrderById(@PathVariable Long orderId) {
OrderDTO order = orderService.getOrderById(orderId);
return Response.success(order);
}
// 订单列表 (读操作,自动路由到从库)
@GetMapping("/list")
public Response<List<OrderDTO>> listOrders(@RequestParam Long userId) {
List<OrderDTO> orders = orderService.listOrdersByUserId(userId);
return Response.success(orders);
}
}
// Service层
@Service
public class OrderServiceImpl implements OrderService {
@Autowired
private OrderRepository orderRepository;
@Override
@Transactional(rollbackFor = Exception.class)
public OrderDTO createOrder(OrderCreateRequest request) {
// INSERT操作,自动路由到主库
Order order = new Order();
order.setUserId(request.getUserId());
order.setTotalAmount(request.getTotalAmount());
orderRepository.save(order);
return convertToDTO(order);
}
@Override
public OrderDTO getOrderById(Long orderId) {
// SELECT操作,自动路由到从库
Order order = orderRepository.findById(orderId);
return convertToDTO(order);
}
@Override
public List<OrderDTO> listOrdersByUserId(Long userId) {
// SELECT操作,自动路由到从库
List<Order> orders = orderRepository.findByUserId(userId);
return orders.stream().map(this::convertToDTO).collect(Collectors.toList());
}
}
5.4 强制路由到主库
场景: 写后立即读
问题:
1. 用户下单成功 (写入主库)
2. 立即跳转订单详情页 (读取从库)
3. 由于主从延迟,从库可能还没同步数据
4. 用户看不到刚下的订单 ❌
解决: 强制路由到主库
使用HintManager
@Service
public class OrderServiceImpl implements OrderService {
@Override
@Transactional(rollbackFor = Exception.class)
public OrderDTO createOrder(OrderCreateRequest request) {
// 1. 创建订单 (写入主库)
Order order = new Order();
order.setUserId(request.getUserId());
order.setTotalAmount(request.getTotalAmount());
orderRepository.save(order);
// 2. 立即查询订单 (需要从主库读取)
OrderDTO orderDTO;
try (HintManager hintManager = HintManager.getInstance()) {
// 强制路由到主库
hintManager.setWriteRouteOnly();
// 这次查询走主库
Order savedOrder = orderRepository.findById(order.getId());
orderDTO = convertToDTO(savedOrder);
} // 自动关闭,后续查询恢复正常路由
return orderDTO;
}
}
另一种方案: 延迟查询
@Service
public class OrderServiceImpl implements OrderService {
@Override
@Transactional(rollbackFor = Exception.class)
public OrderDTO createOrder(OrderCreateRequest request) {
// 1. 创建订单
Order order = new Order();
order.setUserId(request.getUserId());
order.setTotalAmount(request.getTotalAmount());
orderRepository.save(order);
// 2. 不立即查询,直接返回
return convertToDTO(order);
}
}
// 前端处理
// 用户下单后,延迟500ms再查询订单详情
setTimeout(() => {
fetchOrderDetail(orderId);
}, 500);
六、实战场景应用
6.1 电商订单系统架构
业务需求
订单系统读写比例: 9:1
- 日订单量: 100万
- 写操作: 10万次/天 (下单、支付、发货)
- 读操作: 90万次/天 (订单查询、列表、详情)
性能要求:
- 写操作响应时间 < 200ms
- 读操作响应时间 < 100ms
- 高峰期QPS: 5000
架构方案
数据库架构:
- 1个主库: 处理所有写操作
- 3个从库: 分担读操作
- 读写分离 + 负载均衡
应用层:
- ShardingSphere-JDBC实现读写分离
- 自动路由,业务代码无感知
监控告警:
- Prometheus监控主从延迟
- 延迟超过10秒告警
完整代码示例
Entity层
@Data
@TableName("t_order")
public class Order {
@TableId(type = IdType.ASSIGN_ID)
private Long id;
private Long userId;
private String orderNo;
private BigDecimal totalAmount;
private Integer status; // 0-待支付,1-已支付,2-已发货,3-已完成
private Integer isDeleted;
private Long createdBy;
private LocalDateTime createdAt;
private Long updatedBy;
private LocalDateTime updatedAt;
}
Repository层
public interface OrderRepository {
void save(Order order);
Order findById(Long orderId);
Order findByOrderNo(String orderNo);
List<Order> findByUserId(Long userId);
PageResult<Order> pageByUserId(Long userId, Integer pageNum, Integer pageSize);
void updateStatus(Long orderId, Integer status);
}
@Repository
public class OrderRepositoryImpl extends ServiceImpl<OrderMapper, Order>
implements OrderRepository {
@Override
public void save(Order order) {
super.save(order);
}
@Override
public Order findById(Long orderId) {
return super.lambdaQuery()
.eq(Order::getId, orderId)
.eq(Order::getIsDeleted, 0)
.one();
}
@Override
public Order findByOrderNo(String orderNo) {
return super.lambdaQuery()
.eq(Order::getOrderNo, orderNo)
.eq(Order::getIsDeleted, 0)
.one();
}
@Override
public List<Order> findByUserId(Long userId) {
return super.lambdaQuery()
.eq(Order::getUserId, userId)
.eq(Order::getIsDeleted, 0)
.orderByDesc(Order::getCreatedAt)
.list();
}
@Override
public PageResult<Order> pageByUserId(Long userId, Integer pageNum, Integer pageSize) {
Page<Order> page = new Page<>(pageNum, pageSize);
Page<Order> result = super.lambdaQuery()
.eq(Order::getUserId, userId)
.eq(Order::getIsDeleted, 0)
.orderByDesc(Order::getCreatedAt)
.page(page);
return new PageResult<>(result.getRecords(), result.getTotal());
}
@Override
public void updateStatus(Long orderId, Integer status) {
super.lambdaUpdate()
.eq(Order::getId, orderId)
.eq(Order::getIsDeleted, 0)
.set(Order::getStatus, status)
.set(Order::getUpdatedAt, LocalDateTime.now())
.update();
}
}
Service层
@Service
public class OrderServiceImpl implements OrderService {
private static final TraceLogger log = TraceLogger.getLogger(OrderServiceImpl.class);
@Autowired
private OrderRepository orderRepository;
@Autowired
private InventoryService inventoryService;
@Override
@Transactional(rollbackFor = Exception.class)
public OrderDTO createOrder(OrderCreateRequest request) {
log.info("Create order, userId={}, amount={}", request.getUserId(), request.getTotalAmount());
// 1. 扣减库存 (调用库存服务)
boolean success = inventoryService.deduct(request.getSkuId(), request.getQuantity());
if (!success) {
throw new BusinessException("库存不足");
}
// 2. 创建订单 (写操作,自动路由到主库)
Order order = new Order();
order.setUserId(request.getUserId());
order.setOrderNo(generateOrderNo());
order.setTotalAmount(request.getTotalAmount());
order.setStatus(0); // 待支付
orderRepository.save(order);
log.info("Order created successfully, orderId={}, orderNo={}", order.getId(), order.getOrderNo());
// 3. 立即查询订单 (强制路由到主库,避免主从延迟)
OrderDTO orderDTO;
try (HintManager hintManager = HintManager.getInstance()) {
hintManager.setWriteRouteOnly();
Order savedOrder = orderRepository.findById(order.getId());
orderDTO = convertToDTO(savedOrder);
}
return orderDTO;
}
@Override
public OrderDTO getOrderById(Long orderId) {
log.info("Get order by id, orderId={}", orderId);
// 读操作,自动路由到从库
Order order = orderRepository.findById(orderId);
if (order == null) {
throw new BusinessException("订单不存在");
}
return convertToDTO(order);
}
@Override
public PageResult<OrderDTO> pageOrdersByUserId(Long userId, Integer pageNum, Integer pageSize) {
log.info("Page orders by userId, userId={}, pageNum={}, pageSize={}", userId, pageNum, pageSize);
// 读操作,自动路由到从库
PageResult<Order> pageResult = orderRepository.pageByUserId(userId, pageNum, pageSize);
List<OrderDTO> orderDTOs = pageResult.getRecords().stream()
.map(this::convertToDTO)
.collect(Collectors.toList());
return new PageResult<>(orderDTOs, pageResult.getTotal());
}
@Override
@Transactional(rollbackFor = Exception.class)
public void payOrder(Long orderId) {
log.info("Pay order, orderId={}", orderId);
// 更新订单状态 (写操作,自动路由到主库)
orderRepository.updateStatus(orderId, 1); // 已支付
log.info("Order paid successfully, orderId={}", orderId);
}
private String generateOrderNo() {
return "ORD" + System.currentTimeMillis() + RandomStringUtils.randomNumeric(6);
}
private OrderDTO convertToDTO(Order order) {
OrderDTO dto = new OrderDTO();
dto.setId(order.getId());
dto.setOrderNo(order.getOrderNo());
dto.setUserId(order.getUserId());
dto.setTotalAmount(order.getTotalAmount());
dto.setStatus(order.getStatus());
dto.setCreatedAt(order.getCreatedAt());
return dto;
}
}
性能测试结果
测试工具: JMeter
并发用户: 1000
测试时长: 10分钟
架构对比:
单库架构:
- 读操作QPS: 1000
- 写操作QPS: 100
- 总QPS: 1100
- 平均响应时间: 500ms
- P99响应时间: 2000ms
1主3从读写分离:
- 读操作QPS: 3000 (提升3倍 ✅)
- 写操作QPS: 100
- 总QPS: 3100 (提升2.8倍 ✅)
- 平均响应时间: 150ms (降低70% ✅)
- P99响应时间: 500ms (降低75% ✅)
结论: 读写分离大幅提升系统性能!
七、生产案例与故障排查
7.1 案例1: 主从延迟导致用户看不到订单
故障现象
时间: 2024-01-15 18:00 (晚高峰)
用户反馈: 下单成功后,订单列表看不到刚下的订单
影响范围: 20%用户
问题分析
步骤1: 检查主从延迟
-- 从库执行
SHOW SLAVE STATUS\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 15 ← 延迟15秒!
步骤2: 分析延迟原因
-- 查看当前正在执行的事务
SELECT * FROM information_schema.processlist WHERE command != 'Sleep'\G
*************************** 1. row ***************************
Id: 100
User: system user
Host:
db: ecommerce
Command: Connect
Time: 15
State: Reading event from the relay log
Info: UPDATE orders SET status=1 WHERE id IN (1,2,3,...,10000) ← 大事务!
发现主库执行了一个大事务,一次性更新10000条订单状态。
步骤3: 查看主库操作记录
-- 主库执行
SELECT * FROM mysql.slow_log WHERE sql_text LIKE '%UPDATE orders%' ORDER BY start_time DESC LIMIT 1\G
start_time: 2024-01-15 17:59:30
query_time: 00:00:12.500000 ← 主库执行了12.5秒
lock_time: 00:00:00.100000
rows_examined: 10000
rows_sent: 0
sql_text: UPDATE orders SET status=1 WHERE id IN (1,2,3,...,10000)
根本原因
运维误操作:
- 批量更新10000条订单状态
- 主库执行12.5秒
- 从库SQL线程单线程重放,也需要12.5秒
- 这段时间内,后续事务全部阻塞
- 用户查询订单时,从库数据延迟15秒
临时解决方案
// 紧急修改代码,强制路由到主库
@Service
public class OrderServiceImpl implements OrderService {
@Override
public List<OrderDTO> listOrdersByUserId(Long userId) {
// 临时强制路由到主库
try (HintManager hintManager = HintManager.getInstance()) {
hintManager.setWriteRouteOnly();
List<Order> orders = orderRepository.findByUserId(userId);
return orders.stream().map(this::convertToDTO).collect(Collectors.toList());
}
}
}
// 发布上线,10分钟内解决
永久解决方案
1. ✅ 禁止大事务操作
- 运维规范: 批量操作必须拆分为小事务
- 示例: 每次更新1000条,循环执行
2. ✅ 开启并行复制
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
3. ✅ 监控告警
- Seconds_Behind_Master > 5秒: 警告
- Seconds_Behind_Master > 10秒: 严重告警
4. ✅ 写后立即读强制路由主库
- 使用HintManager.setWriteRouteOnly()
- 或者延迟500ms再查询
效果
- 主从延迟从15秒降到1秒以内
- 用户订单列表实时展示
- 故障解决
7.2 案例2: 从库宕机导致服务不可用
故障现象
时间: 2024-01-16 10:30
监控告警: slave1 (192.168.1.101) 无响应
影响: 部分读请求失败,报错500
问题分析
步骤1: 检查从库状态
# SSH登录从库
ssh root@192.168.1.101
# 无法连接!
ssh: connect to host 192.168.1.101 port 22: No route to host
步骤2: 检查应用日志
2024-01-16 10:30:15 ERROR c.z.h.p.HikariPool - HikariPool-1 - Exception during pool initialization.
java.sql.SQLNonTransientConnectionException: Could not connect to address=(host=192.168.1.101)(port=3306)(type=master) : Socket fail to connect to host:192.168.1.101, port:3306. Connection refused
步骤3: 检查ShardingSphere配置
spring:
shardingsphere:
datasource:
names: master,slave0,slave1 ← slave1配置存在
slave0: ...
slave1: ... ← slave1宕机!
问题: ShardingSphere未配置故障转移,slave1宕机后仍然路由请求
临时解决方案
# 紧急修改application.yml,移除slave1
spring:
shardingsphere:
datasource:
names: master,slave0 ← 移除slave1
rules:
readwrite-splitting:
data-sources:
myds:
static-strategy:
write-data-source-name: master
read-data-source-names: slave0 ← 只保留slave0
# 重启应用,5分钟内恢复
永久解决方案
1. ✅ 配置健康检查
spring:
shardingsphere:
datasource:
slave0:
hikari:
connection-test-query: SELECT 1 # 连接测试SQL
validation-timeout: 3000 # 验证超时3秒
slave1:
hikari:
connection-test-query: SELECT 1
validation-timeout: 3000
2. ✅ 使用动态数据源
- 改用MySQL Router或ProxySQL
- 自动检测从库健康状态
- 故障从库自动摘除
3. ✅ 监控告警
- MySQL存活性监控
- 连接池可用性监控
- 应用错误率监控
4. ✅ 高可用架构
- 增加从库数量 (至少3个)
- 1个从库宕机,其他从库继续服务
效果
- 故障从库自动摘除
- 读请求路由到健康从库
- 服务快速恢复
7.3 案例3: 主从数据不一致
故障现象
用户反馈: 订单状态不一致
- 主库查询: status=1 (已支付)
- 从库查询: status=0 (待支付)
问题分析
步骤1: 检查主从复制状态
-- 从库执行
SHOW SLAVE STATUS\G
Slave_IO_Running: Yes
Slave_SQL_Running: No ← SQL线程停止!
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table ecommerce.orders; Can't find record in 'orders'
步骤2: 分析错误
错误码1032: 找不到要更新的记录
原因:
1. 主库执行UPDATE,修改id=12345的订单
2. 从库重放UPDATE时,发现id=12345的记录不存在
3. SQL线程报错停止
4. 后续所有事务无法同步
步骤3: 查找根本原因
-- 主库查询
SELECT * FROM orders WHERE id=12345;
+-------+---------+--------+
| id | user_id | status |
+-------+---------+--------+
| 12345 | 1001 | 1 |
+-------+---------+--------+
-- 从库查询
SELECT * FROM orders WHERE id=12345;
Empty set ← 从库没有这条记录!
根本原因
运维误操作:
1. 从库配置了read_only=1
2. 运维使用super权限在从库直接执行DELETE
3. 从库删除了id=12345的记录
4. 主库后续UPDATE该记录
5. 从库重放时找不到记录,报错1032
解决方案
方案1: 跳过错误 (不推荐,数据会丢失)
-- 从库执行
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1; -- 跳过1个事务
START SLAVE;
问题: id=12345的订单数据永久丢失
方案2: 手动修复数据 (推荐)
-- 1. 停止从库复制
STOP SLAVE;
-- 2. 从主库导出缺失数据
-- 主库执行
SELECT * FROM orders WHERE id=12345\G
*************************** 1. row ***************************
id: 12345
user_id: 1001
order_no: ORD20240116123456
total_amount: 99.00
status: 1
is_deleted: 0
created_at: 2024-01-16 10:00:00
-- 3. 从库插入缺失数据
-- 从库执行
INSERT INTO orders (id, user_id, order_no, total_amount, status, is_deleted, created_at)
VALUES (12345, 1001, 'ORD20240116123456', 99.00, 0, 0, '2024-01-16 10:00:00');
-- 4. 重启复制
START SLAVE;
-- 5. 检查状态
SHOW SLAVE STATUS\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes ← 恢复正常!
Seconds_Behind_Master: 0
预防措施
1. ✅ 配置super_read_only=1
- 防止super权限用户在从库写入
2. ✅ 收回从库写权限
- 运维账号只授予SELECT权限
- 禁止在从库执行DML
3. ✅ 监控主从一致性
- 定期执行pt-table-checksum校验
- 发现不一致立即告警
4. ✅ 开启GTID
- GTID模式下,数据一致性更有保障
八、常见问题与避坑指南
8.1 主从复制有哪些模式?如何选择?
三种模式对比
| 模式 | 数据安全性 | 性能 | 适用场景 |
|---|---|---|---|
| 异步复制 | 低 ❌ | 最高 ✅ | 对数据一致性要求不高 |
| 半同步复制 | 中等 ✅ | 中等 | 生产环境推荐 ⭐⭐⭐⭐⭐ |
| 组复制 | 高 ✅ | 较低 | 核心业务高可用 |
推荐配置
# 生产环境推荐半同步复制
[mysqld]
plugin-load-add = rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_wait_for_slave_count = 1
rpl_semi_sync_master_timeout = 1000
8.2 GTID和传统复制有什么区别?
传统复制 (基于Position)
CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=1273;
缺点:
- 主库切换时,需要手动找Binlog文件和位置
- 配置复杂,容易出错
GTID复制
CHANGE MASTER TO
MASTER_AUTO_POSITION=1;
优点:
- 自动定位同步点
- 主库切换简单
- 支持多源复制
推荐: 生产环境使用GTID
8.3 如何监控主从延迟?
-- 关键指标: Seconds_Behind_Master
SHOW SLAVE STATUS\G
Seconds_Behind_Master: 0 ← 延迟秒数
告警阈值:
- 5秒: 警告
- 10秒: 严重告警
- NULL: 复制已停止,紧急告警
8.4 读写分离后,写后立即读怎么办?
问题
用户下单 (写主库) → 跳转订单详情 (读从库) → 看不到订单 (主从延迟)
解决方案
方案1: 强制路由到主库
try (HintManager hintManager = HintManager.getInstance()) {
hintManager.setWriteRouteOnly();
Order order = orderRepository.findById(orderId);
}
方案2: 延迟查询
// 前端延迟500ms再查询
setTimeout(() => {
fetchOrderDetail(orderId);
}, 500);
方案3: 缓存
// 写入主库时,同时写入Redis
redisTemplate.opsForValue().set("order:" + orderId, orderDTO, 10, TimeUnit.MINUTES);
// 查询时先查Redis
OrderDTO order = redisTemplate.opsForValue().get("order:" + orderId);
if (order == null) {
order = orderRepository.findById(orderId);
}
8.5 从库可以写入数据吗?
❌ 强烈不推荐
从库写入问题:
1. 主从数据不一致
2. 主库同步相同数据到从库,冲突
3. 复制中断,SQL线程报错
示例:
- 从库插入id=100的订单
- 主库也插入id=100的订单
- 从库重放时,主键冲突,报错1062
- 复制停止
配置只读
[mysqld]
read_only = 1 # 普通用户只读
super_read_only = 1 # 超级用户也只读
8.6 主从复制支持跨版本吗?
支持,但有限制
MySQL主从版本规则:
- 从库版本 ≥ 主库版本
- 示例:
✅ 主库5.7 → 从库8.0 (支持)
❌ 主库8.0 → 从库5.7 (不支持)
跨版本升级方案:
1. 升级从库到8.0
2. 验证从库正常同步
3. 主从切换 (从库提升为主库)
4. 升级原主库到8.0
5. 重新配置主从
8.7 如何处理主从不一致?
排查工具: pt-table-checksum
# 安装Percona Toolkit
yum install percona-toolkit
# 校验主从一致性
pt-table-checksum --host=192.168.1.100 --user=root --password=password \
--databases=ecommerce \
--replicate=percona.checksums
输出:
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME
01-16T10:00:00 0 1 10000 100 10 0 5.2
-- 发现100行数据不一致!
# 修复不一致数据
pt-table-sync --execute --sync-to-master \
h=192.168.1.101,u=root,p=password,D=ecommerce,t=orders
8.8 Binlog格式如何选择?
三种格式对比
| 格式 | 日志大小 | 主从一致性 | 推荐度 |
|---|---|---|---|
| Statement | 小 ✅ | 可能不一致 ❌ | ⭐⭐ |
| Row | 大 ❌ | 完全一致 ✅ | ⭐⭐⭐⭐⭐ |
| Mixed | 中等 | 一致 ✅ | ⭐⭐⭐⭐ |
推荐: Row格式
[mysqld]
binlog_format = ROW
原因:
- 保证主从数据一致性
- 可以准确恢复数据
- 支持所有场景 (触发器、存储过程、函数)
九、最佳实践与总结
9.1 主从复制最佳实践
配置推荐
[mysqld]
# ============ 主库配置 ============
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_wait_for_slave_count = 1
rpl_semi_sync_master_timeout = 1000
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
# ============ 从库配置 ============
server-id = 2
relay_log = relay-bin
relay_log_recovery = ON
gtid_mode = ON
enforce_gtid_consistency = ON
rpl_semi_sync_slave_enabled = 1
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
read_only = 1
super_read_only = 1
架构建议
1. ✅ 至少1主2从
- 1个从库宕机,另一个从库继续服务
2. ✅ 使用半同步复制
- 数据安全性有保障
- 性能可接受
3. ✅ 开启GTID
- 简化主从配置
- 支持自动故障转移
4. ✅ 开启并行复制
- 减少主从延迟
- 提升从库性能
5. ✅ 从库只读
- read_only = 1
- super_read_only = 1
9.2 读写分离最佳实践
ShardingSphere配置
spring:
shardingsphere:
datasource:
names: master,slave0,slave1
rules:
readwrite-splitting:
data-sources:
myds:
static-strategy:
write-data-source-name: master
read-data-source-names: slave0,slave1
load-balancer-name: round-robin
props:
sql-show: true
业务代码规范
1. ✅ 业务代码无需修改
- ShardingSphere自动路由
- 完全透明
2. ✅ 写后立即读强制路由主库
- 使用HintManager.setWriteRouteOnly()
- 或者延迟查询
3. ✅ 长事务优化
- 拆分为小事务
- 避免阻塞从库复制
4. ✅ 监控告警
- 主从延迟监控
- 从库健康检查
9.3 监控指标
关键指标
-- 1. 主从延迟
SHOW SLAVE STATUS\G
Seconds_Behind_Master: 0
-- 2. 复制状态
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-- 3. 复制错误
Last_IO_Errno: 0
Last_SQL_Errno: 0
-- 4. GTID进度
Retrieved_Gtid_Set: 3e11fa47-71ca-11e1-9e33:1-100
Executed_Gtid_Set: 3e11fa47-71ca-11e1-9e33:1-100
-- 5. 并行复制worker状态
SELECT * FROM performance_schema.replication_applier_status_by_worker;
告警阈值
- Seconds_Behind_Master > 5秒: 警告
- Seconds_Behind_Master > 10秒: 严重告警
- Slave_IO_Running != Yes: 紧急告警
- Slave_SQL_Running != Yes: 紧急告警
- Last_IO_Errno != 0: 紧急告警
- Last_SQL_Errno != 0: 紧急告警
9.4 故障处理Checklist
主从复制中断
1. ✅ 检查网络连通性
ping 192.168.1.100
2. ✅ 检查复制状态
SHOW SLAVE STATUS\G
3. ✅ 查看错误日志
tail -100 /var/log/mysqld.log
4. ✅ 常见错误处理
- 1062: 主键冲突,跳过或手动修复
- 1032: 找不到记录,补充数据
- 2003: 网络不通,检查防火墙
5. ✅ 重启复制
STOP SLAVE; START SLAVE;
主从延迟
1. ✅ 检查延迟原因
- 大事务?
- 从库性能差?
- 锁冲突?
2. ✅ 开启并行复制
slave_parallel_workers = 8
3. ✅ 优化大事务
- 拆分为小事务
4. ✅ 升级从库硬件
- 更换SSD
- 增加CPU/内存
9.5 核心要点总结
主从复制原理
- Binlog: 主库记录数据变更,发送给从库
- IO线程: 从库接收Binlog,写入Relay Log
- SQL线程: 从库重放Relay Log,完成同步
- GTID: 全局事务ID,简化主从配置
三种复制模式
- 异步复制: 性能最高,可能丢数据
- 半同步复制: 推荐生产环境,数据安全
- 组复制: 高可用,强一致性
读写分离架构
- 核心思想: 主库写,从库读,负载分离
- ShardingSphere: 轻量级Java框架,透明集成
- 负载均衡: 轮询、随机、权重、最少连接
- 强制路由: HintManager解决写后立即读问题
主从延迟优化
- 并行复制: slave_parallel_workers=8
- 大事务拆分: 避免单个大事务阻塞
- 硬件升级: SSD + 高性能CPU
- 监控告警: Seconds_Behind_Master实时监控
生产最佳实践
- ✅ 至少1主2从架构
- ✅ 使用半同步复制 + GTID
- ✅ 开启并行复制
- ✅ 配置只读: super_read_only=1
- ✅ 完善监控告警
- ✅ 定期校验主从一致性
通过深入理解MySQL主从复制机制和读写分离架构,结合ShardingSphere等成熟框架,能够构建高性能、高可用的数据库系统,支撑大规模电商业务的稳定运行。
参考资料
- 《高性能MySQL》(第4版) - Baron Schwartz等
- 《MySQL技术内幕: InnoDB存储引擎》(第2版) - 姜承尧
- MySQL官方文档: dev.mysql.com/doc/refman/…
- ShardingSphere官方文档: shardingsphere.apache.org/
