MySQL 读写分离的实现逻辑

MySQL 读写分离的实现逻辑

引用

CSDN

1.

https://m.blog.csdn.net/weixin_45428910/article/details/145723384

读写分离是数据库架构优化的一种常见策略，主要用于提高数据库的吞吐能力和查询性能。本文将详细介绍MySQL读写分离的核心思想、基本架构、实现步骤以及可能遇到的问题和解决方案。

MySQL 读写分离的核心思想是：

• 写操作（INSERT、UPDATE、DELETE）只在 主库（Master） 上执行。
• 读操作（SELECT）在 从库（Slave） 上执行。
• 通过 主从复制（Master-Slave Replication）保持数据一致性。

一、读写分离的基本架构

通常采用 一主多从（Master-Slave）的架构，即：

• Master（主库） 负责处理所有写请求，并将数据变更同步到从库。
• Slave（从库） 负责处理读请求，提高查询性能。
• 中间件或代理（如 MySQL Proxy、MyCat、ShardingSphere-Proxy）用于路由 SQL 请求。

二、MySQL 读写分离的实现步骤

1. 配置 MySQL 主从复制

（1）在主库（Master）上配置

① 修改 MySQL 配置文件 (my.cnf 或 my.ini)：

[mysqld]
server-id=1  # 设置唯一的服务器ID
log-bin=mysql-bin  # 启用二进制日志（binlog），用于数据同步
binlog-format=ROW  # 推荐使用行格式（ROW）以保证数据一致性

② 创建用于复制的账号：

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

③ 查看 Master 的二进制日志信息：

SHOW MASTER STATUS;

输出示例：

±-----------------±---------±-------------±-----------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
±-----------------±---------±-------------±-----------------+
| mysql-bin.000001 | 157 | testdb | |
±-----------------±---------±-------------±-----------------+

记住 File 和 Position，稍后在从库中使用。

（2）在从库（Slave）上配置

① 修改 MySQL 配置文件 (my.cnf 或 my.ini)：

server-id=2  # 每个从库都需要唯一的 server-id
relay-log=relay-bin  # 设定 relay log 用于主从同步
read-only=1  # 设定为只读，防止误写

② 配置从库连接主库：

CHANGE MASTER TO
    MASTER_HOST='主库IP',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',  -- Master 服务器上 SHOW MASTER STATUS 查询得到的 File
    MASTER_LOG_POS=157;  -- Master 服务器上 SHOW MASTER STATUS 查询得到的 Position

③ 启动复制进程：

START SLAVE;

④ 检查主从同步状态：

SHOW SLAVE STATUS\G;

如果 Slave_IO_Running 和 Slave_SQL_Running 都是 Yes，表示复制正常。

2. 配置读写分离

主从复制完成后，需要将 写请求发往主库，读请求发往从库。实现方式有：

• 应用层代码控制（手动选择数据库连接）
• MySQL 代理中间件（MySQL Router、MyCat、ShardingSphere-Proxy）
• 数据库连接池方案（如 C3P0、HikariCP）

（1）应用层代码控制

在 Java 代码中，可以使用不同的数据源进行读写分离：

// 写操作 - 连接 Master
try (Connection conn = masterDataSource.getConnection()) {
    String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
    try (PreparedStatement stmt = conn.prepareStatement(sql)) {
        stmt.setString(1, "Amy");
        stmt.setString(2, "amy@example.com");
        stmt.executeUpdate();
    }
}
// 读操作 - 连接 Slave
try (Connection conn = slaveDataSource.getConnection()) {
    String sql = "SELECT * FROM users";
    try (Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(sql)) {
        while (rs.next()) {
            System.out.println("User: " + rs.getString("name"));
        }
    }
}

（2）使用 MySQL Router

MySQL Router 是官方的读写分离代理工具：

安装 MySQL Router：

sudo apt install mysql-router

配置路由规则（mysqlrouter.conf）：

[routing:read_write]
bind_address = 0.0.0.0
bind_port = 3306
routing_strategy = first-available
destinations = master_ip:3306

[routing:read_only]
bind_address = 0.0.0.0
bind_port = 3307
routing_strategy = round-robin
destinations = slave1_ip:3306,slave2_ip:3306

应用程序连接：

• 写请求 连接 127.0.0.1:3306
• 读请求 连接 127.0.0.1:3307

（3）使用 ShardingSphere-JDBC

Spring Boot 可使用 ShardingSphere-JDBC 进行自动读写分离：

spring:
  shardingsphere:
    datasource:
      names: master, slave
      master:
        url: jdbc:mysql://master_ip:3306/testdb
        username: root
        password: password
      slave:
        url: jdbc:mysql://slave_ip:3306/testdb
        username: root
        password: password
    rules:
      readwrite-splitting:
        data-sources:
          readwrite_ds:
            type: Static
            props:
              write-data-source-name: master
              read-data-source-names: slave

三、可能遇到的问题及解决方案

略

四、总结

1. 主从复制 通过 binlog 机制同步数据，为读写分离提供基础。
   
2. 读写分离策略：

• 代码层手动控制
• 代理中间件（MySQL Router、MyCat）
• 数据库连接池（ShardingSphere-JDBC）

1. 优化点：

• 通过 负载均衡 分配从库查询压力
• 避免 复制延迟 影响查询结果
• 采用 事务管理策略，确保数据一致性

这样可以大幅提高 MySQL 读查询性能，减少主库压力，提高整体数据库系统的可扩展性。