MYSQL中怎么实现主从复制和读写分离

# MySQL中怎么实现主从复制和读写分离

## 一、前言

在现代互联网应用中，数据库作为核心数据存储组件，其性能和可靠性直接影响整个系统的稳定性。随着业务量增长，单机MySQL数据库往往会面临以下挑战：

- 读写请求量超过单机处理能力
- 单点故障风险导致服务不可用
- 备份操作影响线上业务性能

为解决这些问题，MySQL提供了主从复制(Master-Slave Replication)技术，配合读写分离策略，可以实现：
1. 负载均衡：将读请求分散到多个从库
2. 高可用：主库故障时可快速切换到从库
3. 数据备份：从库作为实时备份数据源
4. 业务解耦：报表分析等业务使用从库，避免影响主库

本文将详细介绍MySQL主从复制的实现原理、配置步骤，以及如何结合中间件实现读写分离。

## 二、MySQL主从复制原理

### 2.1 复制的基本流程

MySQL主从复制基于二进制日志(binlog)实现，整体流程如下：

1. **主库记录变更**：所有DDL和DML操作以事件形式写入binlog
2. **从库IO线程拉取日志**：从库IO线程连接主库获取binlog
3. **从库保存日志**：获取的binlog写入从库的中继日志(relay log)
4. **从库SQL线程重放**：SQL线程读取relay log并执行其中的SQL事件

```mermaid
graph TD
    A[Master] -->|Binary Log| B[Slave IO Thread]
    B --> C[Relay Log]
    C --> D[Slave SQL Thread]
    D --> E[Slave Database]

2.2 复制格式类型

MySQL提供三种binlog格式，通过binlog_format参数配置：

格式类型	特点	适用场景
STATEMENT	记录SQL语句，日志量小	大部分场景，但不支持某些函数
ROW	记录行数据变化，精度高	数据安全要求高的场景
MIXED	混合模式，根据情况自动选择STATEMENT或ROW	通用场景

2.3 复制拓扑结构

根据业务需求，可以构建不同的复制拓扑：

1. 一主一从：基础配置，适合中小业务
2. 一主多从：读扩展场景，需注意从库延迟
3. 级联复制：Master -> Slave1 -> Slave2，减轻主库压力
4. 双主复制：互为主从，需要处理冲突问题

三、主从复制配置实战

3.1 环境准备

假设我们有以下服务器： - 主库：192.168.1.100 - 从库：192.168.1.101

MySQL版本：5.7+（推荐8.0+）

3.2 主库配置

1. 修改my.cnf配置文件：

[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
sync_binlog = 1
expire_logs_days = 7

关键参数说明： - server-id：集群内唯一ID - sync_binlog：1表示每次事务提交都刷盘，最安全但性能影响较大

1. 创建复制账号：

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

1. 查看主库状态：

SHOW MASTER STATUS;

记录返回的File和Position值，从库配置时需要用到。

3.3 从库配置

1. 修改my.cnf配置文件：

[mysqld]
server-id = 2
relay_log = mysql-relay-bin
read_only = 1
log_slave_updates = 1  # 如果作为级联从库需要开启

1. 配置复制链路：

CHANGE MASTER TO
MASTER_HOST='192.168.1.100',
MASTER_USER='repl',
MASTER_PASSWORD='Repl@1234',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;

1. 启动复制：

START SLAVE;

1. 检查复制状态：

SHOW SLAVE STATUS\G

重点关注以下字段： - Slave_IO_Running: Yes - Slave_SQL_Running: Yes - Seconds_Behind_Master: 从库延迟秒数

3.4 常见问题排查

1. 连接失败：

   • 检查网络连通性
   • 验证复制账号权限
   • 查看防火墙设置

2. 复制中断：

   STOP SLAVE;
   SET GLOBAL sql_slave_skip_counter = 1;
   START SLAVE;
   

   或配置slave_skip_errors跳过特定错误

3. 数据不一致： 使用pt-table-checksum工具检测，pt-table-sync修复

四、读写分离实现方案

4.1 应用层实现

在代码中区分读写操作：

// Spring配置示例
@Bean
public DataSource dataSource() {
    AbstractRoutingDataSource ds = new AbstractRoutingDataSource() {
        @Override
        protected Object determineCurrentLookupKey() {
            return TransactionSynchronizationManager.isCurrentTransactionReadOnly() 
                   ? "read" : "write";
        }
    };
    
    Map<Object, Object> dataSources = new HashMap<>();
    dataSources.put("write", masterDataSource());
    dataSources.put("read", slaveDataSource());
    
    ds.setTargetDataSources(dataSources);
    return ds;
}

优点：灵活可控 缺点：需要修改代码，各语言需单独实现

4.2 中间件方案

4.2.1 MySQL Router

MySQL官方提供的轻量级中间件，适合简单场景。

配置文件示例：

[routing:read_write]
bind_address = 0.0.0.0
destinations = master:3306,slave1:3306,slave2:3306
routing_strategy = round-robin

4.2.2 ProxySQL

功能强大的开源中间件，支持： - 读写自动路由 - 查询缓存 - 故障自动转移

配置步骤： 1. 添加服务器到主机组：

INSERT INTO mysql_servers(hostgroup_id,hostname,port) 
VALUES (10,'192.168.1.100',3306), (20,'192.168.1.101',3306);

1. 配置路由规则：

INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) 
VALUES (1,1,'^SELECT.*FOR UPDATE',10,1), (2,1,'^SELECT',20,1);

1. 负载均衡配置：

UPDATE mysql_servers SET weight=1000 WHERE hostgroup_id=10;
UPDATE mysql_servers SET weight=100 WHERE hostgroup_id=20;

4.2.3 ShardingSphere-JDBC

面向Java应用的分布式数据库中间件，配置示例：

spring:
  shardingsphere:
    datasource:
      names: master,slave1,slave2
      master:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://master:3306/db
        username: root
        password: 123456
    masterslave:
      load-balance-algorithm-type: round_robin
      name: ms
      master-data-source-name: master
      slave-data-source-names: slave1,slave2
    props:
      sql.show: true

五、高级主题与优化

5.1 半同步复制

默认异步复制可能丢失数据，半同步复制要求至少一个从库接收binlog后主库才返回成功。

配置方法： 1. 主库安装插件：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;

1. 从库安装插件：

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

5.2 GTID复制

全局事务标识符(GTID)简化了故障恢复和主从切换。

配置步骤： 1. 主从库my.cnf添加：

gtid_mode = ON
enforce_gtid_consistency = ON

1. 从库配置：

CHANGE MASTER TO 
MASTER_AUTO_POSITION = 1;

5.3 延迟问题优化

1. 并行复制：

   slave_parallel_workers = 8
   slave_parallel_type = LOGICAL_CLOCK
   

2. 大事务拆分：避免单事务操作过多数据

3. 监控延迟：

   SHOW SLAVE STATUS\G
   

六、监控与维护

6.1 关键监控指标

1. 复制延迟：Seconds_Behind_Master
2. 线程状态：Slave_IO_Running, Slave_SQL_Running
3. 错误日志：Last_IO_Error, Last_SQL_Error

6.2 常用维护命令

1. 主从切换： “`sql – 原主库 SET GLOBAL read_only = 1;

– 新主库 STOP SLAVE; RESET MASTER; SET GLOBAL read_only = 0;

2. 重建复制：
   ```bash
   mysqldump --master-data=2 -h master -u root -p dbname > dump.sql
   mysql -h slave -u root -p dbname < dump.sql

七、总结

MySQL主从复制与读写分离是构建高可用数据库架构的基础技术栈。通过本文的实践指导，您可以：

1. 正确配置主从复制环境
2. 根据业务特点选择合适的读写分离方案
3. 处理常见的复制问题
4. 实施性能优化策略

实际生产环境中，建议结合监控系统（如Prometheus+Granfa）和自动化运维工具，持续关注复制状态和性能指标。对于更复杂的场景，可考虑使用MGR（MySQL Group Replication）或Galera Cluster等多主复制方案。

注：本文示例基于MySQL 5.⁷⁄₈.0版本，不同版本配置可能略有差异，请以官方文档为准。 “`

该文档共计约4700字，包含： 1. 主从复制原理图解 2. 分步骤配置指南 3. 多种读写分离方案对比 4. 常见问题解决方案 5. 性能优化建议 6. 监控维护方法

格式采用标准的Markdown语法，支持代码块、表格、流程图等元素，可直接用于技术文档发布。