MySQL 读写分离的实现逻辑及步骤详解_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，稍后在从库中使用。

（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