MySQL主从复制原理与配置
MySQL 数据库
MySQL
发布日期:   2024-07-22
作者:   小乐
文章字数:   1.9k
阅读时长:   8 分
阅读次数:  

MySQL主从复制原理与配置

MySQL 是业务系统中最常见的数据库，但用好它并不容易。索引设计、SQL 优化、事务处理都是日常开发中需要关注的点。本文从实际场景出发，讲常见问题和解决思路。

一、复制架构概述

#

1.1 复制的作用

  • 读写分离：主库写，从库读，分摊压力
  • 数据备份：从库作为实时备份
  • 高可用：主库故障时切换到从库
  • 统计分析：报表查询在从库执行

#

1.2 复制模式演进

MySQL 3.23: 异步复制（Asynchronous Replication）
MySQL 5.5: 半同步复制（Semi-Synchronous Replication）
MySQL 5.6: GTID复制（Global Transaction Identifier）
MySQL 5.7: 组复制（Group Replication）
MySQL 8.0: 增强GTID、克隆插件

二、复制原理

#

2.1 核心流程

Master                      Slave
  │                           │
  │  1. 写操作               │
  │ ──────────────────────>   │
  │                           │
  │  2. 写binlog             │
  │  ┌─────────────┐         │
  │  │  binlog     │         │
  │  └─────────────┘         │
  │                           │
  │  3. Dump线程发送binlog   │
  │ ──────────────────────>   │
  │                           │ 4. I/O线程接收写入relay log
  │                           │
  │                           │ 5. SQL线程重放relay log
  │                           │

#

2.2 复制线程

主库线程：

  • Binlog Dump Thread：发送binlog给从库

从库线程：

  • I/O Thread：连接主库，接收binlog，写入relay log
  • SQL Thread：读取relay log，重放SQL

#

2.3 binlog格式

格式 特点 优点 缺点
STATEMENT 记录SQL语句 日志小 某些语句不一致（UUID、NOW()等）
ROW 记录行变更 精确、安全 日志大
MIXED 混合模式 平衡 复杂

推荐：MySQL 5.7+ 使用ROW格式

[mysqld]
binlog_format = ROW

#

2.4 复制方式

异步复制（Asynchronous）：

  • 主库写完binlog即返回，不等待从库
  • 性能最好，但存在数据延迟和丢失风险

半同步复制（Semi-Synchronous）：

  • 主库等待至少一个从库确认收到binlog
  • 平衡性能和数据安全

组复制（Group Replication）：

  • 基于Paxos协议
  • 多数节点确认才提交
  • 自动故障切换

三、异步复制配置

#

3.1 主库配置

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

# 开启binlog
log-bin = mysql-bin

# binlog格式
binlog_format = ROW

# 保留binlog天数
expire_logs_days = 7

# 同步binlog到磁盘（1=每次事务，0=依赖OS，2=每秒）
sync_binlog = 1

# InnoDB redo log同步（1=每次事务）
innodb_flush_log_at_trx_commit = 1

创建复制账号：

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

查看主库状态：

SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     154  |              |                  |
+------------------+----------+--------------+------------------+

#

3.2 从库配置

[mysqld]
server-id = 2

# relay log配置
relay-log = mysql-relay-bin

# 只读（从库推荐开启）
read_only = ON

# 不记录从库binlog（如果从库不再作为其他库的主库）
log-bin = OFF

配置主库连接：

CHANGE MASTER TO
    MASTER_HOST = 'master_host',
    MASTER_PORT = 3306,
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154;

START SLAVE;

#

3.3 验证复制状态

SHOW SLAVE STATUS \G;

关键字段：
| 字段 | 正常状态 |
|——|———|
| Slave_IO_Running | Yes |
| Slave_SQL_Running | Yes |
| Seconds_Behind_Master | 0或较小数字 |
| Last_IO_Error | 空 |
| Last_SQL_Error | 空 |

四、GTID复制

#

4.1 什么是GTID

GTID（Global Transaction Identifier）是全局事务标识符：

GTID = source_id:transaction_id

示例：
3E11FA47-71CA-11E1-9E33-C80AA9429562:23

#

4.2 GTID优势

  • 定位方便：直接知道事务在哪个库执行
  • 切换简单：不需要指定binlog文件名和位置
  • 一致性强：避免重复执行或遗漏事务

#

4.3 GTID配置

主库和从库都需配置：

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW

# 开启GTID
gtid_mode = ON
enforce_gtid_consistency = ON

从库配置：

CHANGE MASTER TO
    MASTER_HOST = 'master_host',
    MASTER_PORT = 3306,
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'password',
    MASTER_AUTO_POSITION = 1;  -- 使用GTID自动定位

START SLAVE;

#

4.4 GTID相关操作

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

-- 查看已执行的GTID集合
SHOW MASTER STATUS;  -- Executed_Gtid_Set

-- 查看从库执行的GTID
SHOW SLAVE STATUS \G;  -- Retrieved_Gtid_Set, Executed_Gtid_Set

-- 跳过事务（谨慎使用）
SET GTID_NEXT='3E11FA47-71CA-11E1-9E33-C80AA9429562:23';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';

五、半同步复制

#

5.1 安装插件

-- 主库安装
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

-- 从库安装
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

#

5.2 启用半同步

-- 主库
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;  -- 超时1秒降级为异步

-- 从库
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

#

5.3 验证半同步

-- 主库
SHOW STATUS LIKE 'Rpl_semi_sync_master_status';
SHOW STATUS LIKE 'Rpl_semi_sync_master_clients';

-- 从库
SHOW STATUS LIKE 'Rpl_semi_sync_slave_status';

六、常见复制问题

#

6.1 主从延迟

原因：

  • 从库性能差
  • 大事务
  • 锁等待
  • 单线程SQL线程（5.6之前）

查看延迟：

SHOW SLAVE STATUS \G;
-- Seconds_Behind_Master

解决方案：

  1. 并行复制（MySQL 5.6+）

    slave_parallel_type = LOGICAL_CLOCK
    slave_parallel_workers = 4

  2. 分离大事务

  3. 升级从库硬件

  4. 使用缓存减少从库读压力

#

6.2 主从数据不一致

检测：

# 使用pt-table-checksum工具
pt-table-checksum --host=master --user=root --password=xxx \
    --databases=mydb --replicate=mydb.checksums

修复：

# 使用pt-table-sync工具
pt-table-sync --execute --replicate mydb.checksums \
    h=master,u=root,p=xxx h=slave,u=root,p=xxx

#

6.3 复制中断

I/O线程中断：

SHOW SLAVE STATUS \G;
-- 查看Last_IO_Error

常见原因和解决：

  • 网络中断 → 检查网络，重启slave
  • 主库binlog被清理 → 重新同步数据
  • 权限问题 → 检查复制账号权限

SQL线程中断：

SHOW SLAVE STATUS \G;
-- 查看Last_SQL_Error

常见原因和解决：

  • 主从数据不一致 → 跳过错误事务或重新同步
  • 表不存在 → 检查是否遗漏DDL
-- 跳过错误（谨慎！）
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

-- GTID方式跳过
STOP SLAVE;
SET GTID_NEXT='xxx:xxx';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;

#

6.4 复制过滤

# 主库（不推荐，会导致binlog不完整）
binlog-do-db = db1
binlog-ignore-db = test

# 从库
replicate-do-db = db1
replicate-ignore-db = test
replicate-do-table = db1.table1
replicate-wild-do-table = db1.%

七、读写分离实现

#

7.1 应用层实现

@Configuration
public class DataSourceConfig {
    
    @Bean
    public DataSource routingDataSource() {
        DynamicRoutingDataSource routingDataSource = new DynamicRoutingDataSource();
        
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("master", masterDataSource());
        targetDataSources.put("slave", slaveDataSource());
        
        routingDataSource.setTargetDataSources(targetDataSources);
        routingDataSource.setDefaultTargetDataSource(masterDataSource());
        
        return routingDataSource;
    }
}

// 注解切换数据源
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface DataSource {
    String value() default "master";
}

// 使用
@DataSource("slave")
public List<User> getUsers() {
    return userMapper.selectAll();
}

#

7.2 中间件方案

中间件 特点
MyCat 功能全面，配置复杂
ShardingSphere Apache项目，生态好
ProxySQL 轻量级，性能好
MaxScale MariaDB官方

#

7.3 延迟处理

@Service
public class OrderService {
    
    public void createOrder(Order order) {
        // 写主库
        orderMapper.insert(order);
        
        // 强制走主库查询（避免延迟）
        Order freshOrder = orderMapper.selectByIdFromMaster(order.getId());
        
        // 缓存预热
        cache.put(order.getId(), freshOrder);
    }
}

八、复制监控

#

8.1 关键监控指标

-- 复制延迟
SHOW SLAVE STATUS \G;

-- 复制线程状态
SELECT 
    THREAD_ID,
    NAME,
    PROCESSLIST_COMMAND,
    PROCESSLIST_STATE
FROM performance_schema.threads 
WHERE NAME LIKE '%slave%';

-- binlog大小和增长
SHOW MASTER LOGS;

#

8.2 自动化监控脚本

#!/bin/bash
# check_replication.sh

DELAY=$(mysql -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')

if [ "$DELAY" = "NULL" ] || [ "$DELAY" -gt 60 ]; then
    echo "复制异常，延迟: ${DELAY}秒" | mail -s "MySQL复制告警" dba@company.com
fi

九、总结

复制模式 数据安全 性能 适用场景
异步复制 低 高 读扩展、备份
半同步复制 中 中 一般生产环境
组复制 高 低 金融级高可用
配置项 建议
binlog_format ROW
sync_binlog 1（半同步）/ 1000（异步）
gtid_mode ON
read_only 从库开启
slave_parallel_workers CPU核数的一半

主从复制的核心要点：

  1. 选择合适的复制模式（异步/半同步/GTID）
  2. 监控复制延迟，及时发现和处理问题
  3. 合理设计读写分离策略
  4. 定期校验主从数据一致性

核心要点

  1. 索引设计的原则：最左前缀原则、避免索引列参与计算、选择合适的索引类型

  2. SQL 优化的方法：使用 EXPLAIN 分析执行计划、避免 SELECT *、优化 JOIN

  3. 事务隔离级别的选择：根据业务需求选择合适的级别

  4. 常见的锁问题：行锁、表锁、死锁的处理

总结

MySQL 优化是一个持续的过程，需要结合业务特点和数据量来调整。在实际项目中，定期分析慢查询日志、优化索引、调整配置参数，都能提升数据库性能。

赏

   转载规则


《MySQL主从复制原理与配置》 由 小乐 采用 知识共享署名 4.0 国际许可协议 进行许可。
  目录