MySQL?讀寫分離的實現(xiàn)邏輯及步驟詳解

更新時間：2025年02月19日 14:27:32 作者：Smile?sea?breeze

文章介紹了MySQL讀寫分離的架構(gòu)、實現(xiàn)步驟、可能遇到的問題及解決方案,并總結(jié)了優(yōu)化點,以提高MySQL數(shù)據(jù)庫的性能和可擴展性,感興趣的朋友一起看看吧

MySQL 讀寫分離的核心思想是：

寫操作（INSERT、UPDATE、DELETE）只在主庫（Master）上執(zhí)行。
讀操作（SELECT）在從庫（Slave）上執(zhí)行。
通過主從復制（Master-Slave Replication）保持數(shù)據(jù)一致性。

一、讀寫分離的基本架構(gòu)

通常采用一主多從（Master-Slave）的架構(gòu)，即：

Master（主庫）負責處理所有寫請求，并將數(shù)據(jù)變更同步到從庫。
Slave（從庫）負責處理讀請求，提高查詢性能。
中間件或代理（如 MySQL Proxy、MyCat、ShardingSphere-Proxy）用于路由 SQL 請求。

二、MySQL 讀寫分離的實現(xiàn)步驟

1. 配置 MySQL 主從復制

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

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

[mysqld]
server-id=1  # 設置唯一的服務器ID
log-bin=mysql-bin  # 啟用二進制日志（binlog），用于數(shù)據(jù)同步
binlog-format=ROW  # 推薦使用行格式（ROW）以保證數(shù)據(jù)一致性

② 創(chuàng)建用于復制的賬號：

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;

④ 檢查主從同步狀態(tài)：

SHOW SLAVE STATUS\G;

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

2. 配置讀寫分離

主從復制完成后，需要將寫請求發(fā)往主庫，讀請求發(fā)往從庫。實現(xiàn)方式有：

應用層代碼控制（手動選擇數(shù)據(jù)庫連接）
MySQL 代理中間件（MySQL Router、MyCat、ShardingSphere-Proxy）
數(shù)據(jù)庫連接池方案（如 C3P0、HikariCP）

（1）應用層代碼控制

在 Java 代碼中，可以使用不同的數(shù)據(jù)源進行讀寫分離：

// 寫操作 - 連接 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

配置路由規(guī)則（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