快捷導(dǎo)航

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

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

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

MySQL 讀寫(xiě)分離的核心思想是：

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

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

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

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

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

1. 配置 MySQL 主從復(fù)制

（1）在主庫(kù)（Master）上配置

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

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

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

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

③ 查看 Master 的二進(jìn)制日志信息：

SHOW MASTER STATUS;

輸出示例：

記住 File 和 Position，稍后在從庫(kù)中使用。

（2）在從庫(kù)（Slave）上配置

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

server-id=2  # 每個(gè)從庫(kù)都需要唯一的 server-id
relay-log=relay-bin  # 設(shè)定 relay log 用于主從同步
read-only=1  # 設(shè)定為只讀，防止誤寫(xiě)

② 配置從庫(kù)連接主庫(kù)：

CHANGE MASTER TO
    MASTER_HOST='主庫(kù)IP',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',  -- Master 服務(wù)器上 SHOW MASTER STATUS 查詢得到的 File
    MASTER_LOG_POS=157;  -- Master 服務(wù)器上 SHOW MASTER STATUS 查詢得到的 Position

③ 啟動(dòng)復(fù)制進(jìn)程：

START SLAVE;

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

SHOW SLAVE STATUS\G;

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

2. 配置讀寫(xiě)分離

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

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

（1）應(yīng)用層代碼控制

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

// 寫(xiě)操作 - 連接 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 是官方的讀寫(xiě)分離代理工具：

安裝 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

應(yīng)用程序連接：

寫(xiě)請(qǐng)求連接 127.0.0.1:3306
讀請(qǐng)求連接 127.0.0.1:3307

（3）使用 ShardingSphere-JDBC

Spring Boot 可使用 ShardingSphere-JDBC 進(jìn)行自動(dòng)讀寫(xiě)分離：

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