CentOS 6.5 MySQL 主從復制與讀寫分離完整配置教程

CentOS 6.5 MySQL 主從復制與讀寫分離完整配置教程

本文記錄在 CentOS 6.5 環境下，基於 MySQL 實現主從復制（Master-Slave）及讀寫分離的完整步驟，適用於生產環境高可用架構部署。

一、環境規劃

二、主庫配置

1. 安裝 MySQL：yum install mysql-server
2. 編輯 /etc/my.cnf：

   [mysqld]
   server-id=200
   log-bin=mysql-bin-200
   binlog-do-db=your_db_name   # 指定同步庫，不寫則同步所有
   innodb_flush_log_at_trx_commit=2
   sync_binlog=1

3. 重啟並授權同步帳號：

   service mysqld restart
   mysql -uroot -p
   GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.201' IDENTIFIED BY 'repl_pass';
   FLUSH PRIVILEGES;
   SHOW MASTER STATUS;  -- 記錄 File 與 Position

三、從庫配置

1. 編輯 /etc/my.cnf：

   [mysqld]
   server-id=201
   log-bin=mysql-bin-201
   innodb_flush_log_at_trx_commit=2
   sync_binlog=1

2. 重啟並設定同步來源：

   service mysqld restart
   mysql -uroot -p
   CHANGE MASTER TO
     MASTER_HOST='192.168.1.200',
     MASTER_USER='repl_user',
     MASTER_PASSWORD='repl_pass',
     MASTER_LOG_FILE='mysql-bin-200.000002',  -- 從 Master SHOW MASTER STATUS 獲取
     MASTER_LOG_POS=1167;
   START SLAVE;
   SHOW SLAVE STATUS\G

四、驗證主從同步

檢查 Slave_IO_Running 與 Slave_SQL_Running 均為 Yes 即成功。在 Master 建立資料庫/表/插入資料，觀察 Slave 是否同步出現。

五、讀寫分離架構（MySQL Proxy / 中間件）

主從搭建完成後，需引入中間件實現讀寫分離。常見方案：

• MySQL Router（官方輕量級，支援讀寫分離、故障轉移）。
• ProxySQL（高效能、支援查詢路由、緩存、熔斷，生產首選）。
• MaxScale（MariaDB 官方，功能豐富）。
• 應用層路由：程式碼中區分讀寫連接池（簡單但耦合度高）。

ProxySQL 快速範例：

-- 安裝 ProxySQL 後配置
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, '192.168.1.200', 3306);  -- Master 寫入組
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '192.168.1.201', 3306);  -- Slave 讀取組
-- 設定讀寫分離規則
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT.*', 1, 1),
       (2, 1, '^(INSERT|UPDATE|DELETE|REPLACE).*', 0, 1);
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

六、常見問題排查

• 同步延遲：檢查網絡、Slave 機器負載、sync_binlog/innodb_flush_log_at_trx_commit 設定。
• 主鍵衝突：確保 auto_increment_offset/auto_increment_increment 錯開，或使用 UUID/雪花算法。
• 從庫只讀：設定 read_only=1 防止誤寫入。
• GTID 模式：MySQL 5.6+ 建議啟用 GTID (gtid_mode=ON)，簡化故障切換。

主從復制是高可用基石，讀寫分離則能線性擴展讀取吞吐。生產環境建議搭配 MHA/Orchestrator 實現自動故障轉移。