MySQL 主从复制与高可用

一、主从复制概述

1.1 什么是主从复制

主从复制（Master-Slave Replication）是 MySQL 的核心功能之一，通过将主库的数据变更同步到一个或多个从库，实现数据的冗余和读写分离。

主从复制架构说明：

👑 Master（主库）：

• 处理所有写操作
• 记录数据变更到 Binary Log

📝 Binary Log（二进制日志）：

• 记录主库的所有数据变更
• 作为从库复制的数据源

📦 Slave（从库）：

• 从 Binary Log 读取数据变更
• 应用到本地数据库
• 处理读操作，实现读写分离

1.2 主从复制的作用

作用	说明
读写分离	主库处理写请求，从库处理读请求，分散压力
数据备份	从库作为数据备份，不影响主库性能
高可用	主库故障时，从库可以提升为主库
数据分析	在从库上进行数据分析，不影响业务

1.3 复制原理

主从复制三个线程：

主从复制三个线程说明：

👑 Master 端：

• Binlog Dump 线程：负责读取 Binary Log 并发送给从库

📦 Slave 端：

• I/O 线程：接收 Master 发送的 Binlog 事件，写入 Relay Log
• SQL 线程：读取 Relay Log 并重放 SQL 语句，实现数据同步

复制流程：

1. 从库执行 CHANGE MASTER TO 配置主库连接信息
2. 从库 I/O 线程连接主库，请求 Binlog
3. 主库 Binlog Dump 线程读取 Binlog 并发送
4. 从库 I/O 线程接收 Binlog 写入 Relay Log
5. 从库 SQL 线程读取 Relay Log 并回放执行

二、主从复制配置

2.1 主库配置

# /etc/my.cnf

[mysqld]
# 服务器唯一标识
server-id = 1

# 开启 Binary Log
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW

# 二进制日志过期时间
binlog_expire_logs_seconds = 604800  # 7天

# 需要同步的数据库（可选）
binlog-do-db = mydb

# 不需要同步的数据库（可选）
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema

# GTID 模式（推荐）
gtid_mode = ON
enforce_gtid_consistency = ON

-- 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'ReplicaPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

-- 查看主库状态
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      154 |              |                  |
+------------------+----------+--------------+------------------+

2.2 从库配置

# /etc/my.cnf

[mysqld]
# 服务器唯一标识（必须与主库不同）
server-id = 2

# 开启 Relay Log
relay_log = /var/log/mysql/relay-bin

# 从库只读（可选但推荐）
read_only = ON
super_read_only = ON

# GTID 模式
gtid_mode = ON
enforce_gtid_consistency = ON

# 复制过滤（可选）
replicate-do-db = mydb

-- 配置主库连接（传统模式）
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.100',
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'ReplicaPassword123!',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154;

-- 配置主库连接（GTID 模式，推荐）
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.100',
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'ReplicaPassword123!',
    MASTER_AUTO_POSITION = 1;

-- 启动从库复制
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_Log_File: mysql-bin.000001
         Read_Master_Log_Pos: 154
              Relay_Log_File: relay-bin.000002
               Relay_Log_Pos: 320
       Relay_Master_Log_File: mysql-bin.000001
            Slave_IO_Running: Yes    ← 关键指标
           Slave_SQL_Running: Yes    ← 关键指标
         Seconds_Behind_Master: 0    ← 复制延迟

2.3 主从同步验证

-- 主库创建测试数据
USE mydb;
CREATE TABLE test_repl (id INT PRIMARY KEY, data VARCHAR(100));
INSERT INTO test_repl VALUES (1, 'test data');

-- 从库验证
USE mydb;
SELECT * FROM test_repl;

三、复制模式

3.1 异步复制（Asynchronous）

默认模式，主库不等待从库确认。

特点：

• 性能最好
• 可能丢失数据（主库故障时）

3.2 半同步复制（Semi-Synchronous）

主库等待至少一个从库确认收到 Binlog。

-- 主库安装插件
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 = 10000;  -- 10秒超时

-- 从库安装插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

-- 查看半同步状态
SHOW STATUS LIKE 'Rpl_semi_sync%';

特点：

• 数据安全性更高
• 性能略有下降
• 超时后降级为异步复制

3.3 组复制（Group Replication）

MySQL 5.7.17+ 引入，基于 Paxos 协议的多主复制。

-- 配置组复制（单主模式）
[mysqld]
server_id = 1
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_checksum = NONE

# 组复制配置
plugin_load_add = 'group_replication.so'
group_replication_group_name = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
group_replication_start_on_boot = OFF
group_replication_local_address = "192.168.1.100:33061"
group_replication_group_seeds = "192.168.1.100:33061,192.168.1.101:33061,192.168.1.102:33061"
group_replication_single_primary_mode = ON

四、GTID 复制

4.1 什么是 GTID

GTID（Global Transaction Identifier）是全局事务标识符，格式为 source_id:transaction_id。

GTID 格式：
3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5

源服务器 UUID : 事务编号范围

4.2 GTID 优势

1. 简化故障转移：不需要记录 binlog 文件名和位置
2. 确保一致性：每个事务有唯一标识
3. 简化复制配置：MASTER_AUTO_POSITION = 1

4.3 GTID 配置

[mysqld]
# 开启 GTID
gtid_mode = ON
enforce_gtid_consistency = ON

# 记录 GTID
log_slave_updates = ON

-- 查看 GTID 状态
SHOW VARIABLES LIKE 'gtid%';
SHOW GLOBAL VARIABLES LIKE 'gtid_executed';

-- GTID 方式配置从库
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.100',
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'password',
    MASTER_AUTO_POSITION = 1;

五、读写分离

5.1 读写分离架构

读写分离架构说明：

👤 应用程序：

• 发送所有数据库请求到中间件
• 不需要关心读写分离的具体实现

🔀 数据库中间件（代理层）：

• 自动识别读/写请求
• 写请求路由到 Master 主库
• 读请求分发到 Slave 从库（负载均衡）

👑 Master（主库）：

• 处理所有写操作
• 数据变更同步到从库

📦 Slave（从库）：

• 处理读操作
• 多个从库可分担读压力

5.2 实现方式

方式一：应用层实现

// Java 示例：使用 AbstractRoutingDataSource
public class DynamicDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSourceType();
    }
}

// 使用注解标记读写
@ReadOnly
public User findById(Long id) {
    return userMapper.selectById(id);
}

@Transactional
public void save(User user) {
    userMapper.insert(user);
}

方式二：中间件实现

常用中间件：

• MyCat：阿里开源
• ShardingSphere-Proxy：Apache 项目
• ProxySQL：高性能代理
• MySQL Router：官方中间件

# ShardingSphere 配置示例
dataSources:
  master:
    url: jdbc:mysql://192.168.1.100:3306/mydb
    username: root
    password: password
  slave0:
    url: jdbc:mysql://192.168.1.101:3306/mydb
    username: root
    password: password
  slave1:
    url: jdbc:mysql://192.168.1.102:3306/mydb
    username: root
    password: password

rules:
  - !READWRITE_SPLITTING
    dataSources:
      readwrite_ds:
        writeDataSourceName: master
        readDataSourceNames:
          - slave0
          - slave1
        loadBalancerName: round_robin
    loadBalancers:
      round_robin:
        type: ROUND_ROBIN

5.3 主从延迟处理

-- 查看从库延迟
SHOW SLAVE STATUS\G
-- 关注 Seconds_Behind_Master

-- 判断从库是否追上主库
SELECT @@gtid_executed;  -- 在从库执行

延迟解决方案：

1. 强制读主库：对一致性要求高的查询
2. 等待 GTID：等待从库执行到指定 GTID
3. 使用缓存：写入后更新缓存
4. 优化从库：增加从库配置

-- MySQL 5.7+ 等待 GTID
SELECT WAIT_FOR_EXECUTED_GTID_SET('3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5', 1);
-- 返回 0 表示成功，1 表示超时

六、高可用架构

6.1 MHA（Master High Availability）

MHA 是一套优秀的 MySQL 高可用方案。

MHA 架构说明：

🎛️ MHA Manager（管理节点）：

• 监控所有 MySQL 节点的健康状态
• 检测到 Master 故障时自动触发故障转移
• 选举数据最新的 Slave 作为新 Master
• 协调其他 Slave 指向新 Master

👑 Master Node（主节点）：

• 处理所有写操作
• 数据复制到从库

📦 Slave Node（从节点）：

• 处理读操作
• 接收 Master 的数据复制
• 故障时可被提升为新 Master

⚡ 故障转移流程：

1. Manager 检测到 Master 故障
2. 选举新 Master（数据最新的 Slave）
3. 其他 Slave 指向新 Master
4. 应用切换到新 Master

6.2 MySQL InnoDB Cluster

MySQL 官方高可用解决方案，基于组复制。

InnoDB Cluster 架构说明：

🔀 MySQL Router（路由层）：

• 智能路由客户端请求
• 自动故障转移
• 读写分离
• 负载均衡

👑 Primary（主节点）：

• 处理读写操作
• 数据同步到 Secondary 节点
• 组复制协调者

📦 Secondary（从节点）：

• 处理只读操作
• 接收 Primary 的数据同步
• 参与故障仲裁

🌐 Group Replication（组复制）：

• 基于 Paxos 协议的组通信
• 自动故障检测和切换
• 数据一致性保证

⭐ 核心特性：

1. 自动故障检测和切换
2. 自动成员管理
3. 内置智能路由

-- 使用 MySQL Shell 创建集群
mysqlsh
\connect root@192.168.1.100:3306

-- 创建集群
var cluster = dba.createCluster('myCluster');

-- 添加节点
cluster.addInstance('root@192.168.1.101:3306');
cluster.addInstance('root@192.168.1.102:3306');

-- 查看集群状态
cluster.status();

6.3 主主复制

两个节点互为主从。

双主架构说明：

👑 Master 1（活跃主库）：

• 接收应用程序的写入请求
• 数据同步到 Master 2
• 通过 VIP 提供服务

👑 Master 2（备用主库）：

• 接收 Master 1 的数据同步
• 故障时可切换为活跃主库
• 通过 VIP 提供服务

⚠️ 注意事项：

1. 只能有一个节点接收写入
2. 使用自增步长避免主键冲突
3. 配合 Keepalived 实现 VIP 漂移

# Master1 配置
[mysqld]
server-id = 1
auto_increment_offset = 1
auto_increment_increment = 2

# Master2 配置
[mysqld]
server-id = 2
auto_increment_offset = 2
auto_increment_increment = 2

6.4 Keepalived + MySQL

使用 Keepalived 实现 VIP 漂移。

# /etc/keepalived/keepalived.conf

vrrp_script check_mysql {
    script "/usr/local/bin/check_mysql.sh"
    interval 2
    weight -20
}

vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1

    authentication {
        auth_type PASS
        auth_pass 1111
    }

    virtual_ipaddress {
        192.168.1.200
    }

    track_script {
        check_mysql
    }
}

#!/bin/bash
# /usr/local/bin/check_mysql.sh

mysql -u root -p'password' -e "SELECT 1" > /dev/null 2>&1
if [ $? -eq 0 ]; then
    exit 0
else
    exit 1
fi

七、分库分表

7.1 分库分表策略

7.2 分片策略

策略	说明	适用场景
取模	hash(key) % n	数据均匀分布
范围	按范围划分	按时间归档
一致性哈希	哈希环	动态扩容
目录	查表映射	灵活但需要维护

7.3 ShardingSphere 示例

# 分库分表配置
dataSources:
  ds0:
    url: jdbc:mysql://192.168.1.100:3306/db0
  ds1:
    url: jdbc:mysql://192.168.1.101:3306/db1

rules:
  - !SHARDING
    tables:
      orders:
        actualDataNodes: ds${0..1}.orders_${0..2}
        databaseStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: database_inline
        tableStrategy:
          standard:
            shardingColumn: order_id
            shardingAlgorithmName: table_inline
    shardingAlgorithms:
      database_inline:
        type: INLINE
        props:
          algorithm-expression: ds${user_id % 2}
      table_inline:
        type: INLINE
        props:
          algorithm-expression: orders_${order_id % 3}

八、故障处理

8.1 主库故障处理

# 1. 确认主库故障
mysqladmin -u root -p ping

# 2. 检查从库状态
mysql -e "SHOW SLAVE STATUS\G"

# 3. 选择数据最新的从库作为新主库
# 比较 Executed_Gtid_Set 或 Read_Master_Log_Pos

# 4. 提升从库为主库
mysql -e "STOP SLAVE;"
mysql -e "RESET SLAVE ALL;"
mysql -e "SET GLOBAL read_only = OFF;"

# 5. 其他从库指向新主库
mysql -e "STOP SLAVE;"
mysql -e "CHANGE MASTER TO MASTER_HOST='new_master_ip', MASTER_AUTO_POSITION=1;"
mysql -e "START SLAVE;"

# 6. 更新应用配置或 VIP 漂移

8.2 从库故障处理

-- 1. 查看复制错误
SHOW SLAVE STATUS\G
-- 查看 Last_Error 和 Last_SQL_Error

-- 2. 跳过错误（慎用）
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

-- 3. 重新同步
STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
START SLAVE;

-- 4. 完全重建从库
-- 使用 mysqldump 或 xtrabackup 重新同步

8.3 主从不一致处理

# 使用 pt-table-checksum 检查一致性
pt-table-checksum \
    --host=master \
    --user=root \
    --password=xxx \
    --databases=mydb

# 使用 pt-table-sync 修复不一致
pt-table-sync \
    --execute \
    --sync-to-master \
    h=slave,D=mydb,t=users

九、监控与运维

9.1 复制监控

-- 监控复制状态
SELECT
    CHANNEL_NAME,
    SERVICE_STATE,
    LAST_ERROR_NUMBER,
    LAST_ERROR_MESSAGE
FROM performance_schema.replication_connection_status;

-- 监控复制延迟
SELECT
    CHANNEL_NAME,
    REMAINING_DELAY
FROM performance_schema.replication_applier_status;

-- 监控 GTID
SELECT
    @@gtid_executed,
    @@gtid_purged;

9.2 常用运维命令

-- 暂停/恢复复制
STOP SLAVE;
START SLAVE;
STOP SLAVE IO_THREAD;
STOP SLAVE SQL_THREAD;

-- 重置复制
RESET SLAVE;
RESET SLAVE ALL;

-- 跳过事务
SET GTID_NEXT='3E11FA47-71CA-11E1-9E33-C80AA9429562:5';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';

-- 清除 Binlog
PURGE BINARY LOGS TO 'mysql-bin.000010';
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';

十、总结

主从复制要点

1. GTID 模式：简化复制管理，推荐使用
2. 半同步复制：提高数据安全性
3. 监控延迟：及时发现和处理问题

高可用架构选择

方案	复杂度	适用场景
主从复制	低	读写分离，备份
MHA	中	自动故障转移
InnoDB Cluster	中	官方解决方案
双主 + Keepalived	中	简单高可用

最佳实践

1. 开启 GTID 模式
2. 使用半同步复制
3. 配置合理的复制过滤
4. 监控复制状态和延迟
5. 定期验证数据一致性
6. 制定故障转移预案