MySQL 主從複製原理、配置步驟與驗證全流程

MySQL 主從複製原理、配置步驟與驗證全流程

主從複製是 MySQL 高可用、讀寫分離、備份卸載的基石。本文系統梳理權限授權、Binlog、主從配置四大核心環節，含完整命令與驗證截圖說明。

一、核心組件與原理

1. 主庫 Binlog：記錄所有數據變更（INSERT/UPDATE/DELETE），SHOW MASTER STATUS 獲取文件名+位置
2. 從庫 I/O 線程：連接主庫，請求 Binlog 事件，寫入本地 Relay Log
3. 從庫 SQL 線程：讀取 Relay Log，重放事件，同步數據

二、前置：用戶授權

-- 主庫創建同步賬號
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

三、Binlog 管理

1. 啟用 Binlog（主庫 my.cnf）

[mysqld]
log-bin = mysql-bin
server-id = 1

重啟後驗證：SHOW VARIABLES LIKE 'log_bin'; → ON

2. Binlog 相關操作

只有 DML（增刪改）會記錄 Binlog，查詢不記錄。

四、主從配置步驟

1. 主庫配置

[mysqld]
server-id = 1
log-bin = mysql-bin
# 可選：只同步指定庫
binlog-do-db = business_db

重啟主庫，記錄 SHOW MASTER STATUS 輸出的 File、Position。

2. 從庫配置

[mysqld]
server-id = 2
relay-log = relay-bin
# 可選：只同步指定庫
replicate-do-db = business_db

重啟從庫。

3. 建立主從關聯（從庫執行）

CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_password',
    MASTER_LOG_FILE='mysql-bin.000003',
    MASTER_LOG_POS=154;

4. 啟動同步

START SLAVE;

5. 驗證狀態

SHOW SLAVE STATUS\G

關鍵指標均為 Yes 即成功：

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes

五、驗證同步效果

1. 主庫建表、插入數據
2. 從庫查詢，數據一致

六、常見問題與排查

七、生產級建議

• GTID 模式（5.6+）：gtid_mode=ON, enforce_gtid_consistency=ON，故障切換自動定位
• 半同步複製：rpl_semi_sync_master=ON，減少數據丟失風險
並行複製：slave_parallel_workers=4（基於庫/邏輯時鐘）
• 監控告警：Seconds_Behind_Master、Slave_IO_Running、Slave_SQL_Running、Relay Log 磁盤佔用

主從複製「配置易、維護難」，建立標準化 SOP 與監控體系，才能在故障時從容應對。