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

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

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

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

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

一、讀寫分離的基本架構

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

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

三、可能遇到的問題及解決方案

四、總結

主從復制通過 binlog 機制同步數(shù)據(jù)，為讀寫分離提供基礎。

讀寫分離策略：

代碼層手動控制
代理中間件（MySQL Router、MyCat）
數(shù)據(jù)庫連接池（ShardingSphere-JDBC）

優(yōu)化點：

通過負載均衡分配從庫查詢壓力
避免復制延遲影響查詢結果
采用事務管理策略，確保數(shù)據(jù)一致性

這樣可以大幅提高 MySQL 讀查詢性能，減少主庫壓力，提高整體數(shù)據(jù)庫系統(tǒng)的可擴展性。

到此這篇關于MySQL 讀寫分離的實現(xiàn)邏輯的文章就介紹到這了,更多相關MySQL 讀寫分離內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

Mysql的MHA高可用及故障切換問題小結
MHA是基于MySQL主從復制的高可用解決方案,通過自動切換到從節(jié)點并提升其為新主,實現(xiàn)數(shù)據(jù)庫的高可用和故障恢復,配置包括主從復制、MHA組件、VIP管理等,通過配置無密碼認證和測試連接,可以確保MHA正常運行,感興趣的朋友一起看看吧
2024-12-12
MySQL ERROR 1045 (28000): Access denied for user ''root''@''
這篇文章主要介紹了MySQL ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 的原因分解決辦法的相關資料,需要的朋友可以參考下
2016-05-05
Mysql?InnoDB引擎中的數(shù)據(jù)頁結構詳解
這篇文章主要為大家介紹了Mysql?InnoDB引擎中的數(shù)據(jù)頁結構詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪
2022-05-05
xampp中修改mysql默認空密碼（root密碼）的方法分享
以前開發(fā)我一直都是用的phpnow做php開發(fā)環(huán)境，phpnow的特點就是一鍵安裝，安裝的時候會要求用戶輸入mysql的root密碼。今天由于客戶機器使用的xampp作為開發(fā)環(huán)境，所以碰到了修改mysql默認空密碼的問題
2014-04-04
簡單了解mysql InnoDB MyISAM相關區(qū)別
這篇文章主要介紹了簡單了解mysql InnoDB MyISAM相關區(qū)別,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下
2020-09-09
Mysql誤操作后利用binlog2sql快速回滾的方法詳解
相信每個和數(shù)據(jù)庫打交道的程序員都會碰一個問題，MySQL誤操作后如何快速回滾？那么下面這篇文章主要給大家介紹了關于Mysql誤操作后利用binlog2sql快速回滾的相關資料，文中介紹的非常詳細，需要的朋友可以參考下。
2017-07-07
Mysql如何避免全表掃描的方法
如果MySQL需要做一次全表掃描來處理查詢時，在 EXPLAIN 的結果中 type 字段的值是 ALL。
2008-10-10
prometheus監(jiān)控MySQL并實現(xiàn)可視化的操作詳解
mysqld_exporter?是一個用于監(jiān)控?MySQL?服務器的開源工具,它是由?Prometheus?社區(qū)維護的一個官方?Exporter,本文給大家介紹了prometheus監(jiān)控MySQL并實現(xiàn)可視化的操作,文中通過代碼和圖文講解的非常詳細,需要的朋友可以參考下
2024-04-04
MySQL延遲問題和數(shù)據(jù)刷盤策略流程分析
這篇文章主要介紹了MySQL延遲問題和數(shù)據(jù)刷盤策略流程分析,本文要給大家提到了mysql復制流程，需要的朋友可以參考下
2020-02-02
MySQL高級開發(fā)中視圖的詳細教程
對MySQL數(shù)據(jù)庫的查詢,除了基本的查詢外,有時候需要對查詢的結果集進行處理,下面這篇文章主要給大家介紹了關于MySQL數(shù)據(jù)庫基本SQL語句教程之高級操作中視圖的相關資料,需要的朋友可以參考下
2023-01-01