MySQL 主從複寫完整部署實戰：從 my.cnf 設定到驗證同步

MySQL 非同步複寫 (Replication) 是高可用、讀寫分離的基礎架構。本文以雙節點 (Master/Slave) 為例，記錄從設定檔修改、建立複寫帳號、資料同步到驗證的完整步驟。

環境資訊

• OS: Linux 2.6.18 (CentOS 5 風格)
• MySQL: 5.6.19 Enterprise
• Master IP: 192.168.92.11 (server_id=11)
• Slave IP: 192.168.92.111 (server_id=111)

1. 修改 my.cnf 並重啟

[mysqld]
# Master
log_bin = master_bin
server_id = 11

# Slave
server_id = 111
# 可選：relay_log = slave_relay_bin
# 可選：read_only = 1

重啟：service mysql restart

2. Master 建立複寫帳號

CREATE USER 'repl'@'192.168.92.111' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.92.111';
FLUSH PRIVILEGES;

3. 驗證 Master 狀態

SHOW VARIABLES LIKE 'server_id';     -- 11
SHOW VARIABLES LIKE 'log_bin';       -- ON
SHOW MASTER STATUS\G
-- 記錄 File (master_bin.000001) 與 Position (589)

4. Master 備份資料並傳至 Slave

# Master 建立測試庫並匯出
CREATE DATABASE cddl;
mysqldump -h 192.168.92.11 -u root -p cddl > cddl.sql

# Slave 匯入
mysql -h 192.168.92.111 -u root -p cddl < cddl.sql

5. Slave 設定複寫參數並啟動

CHANGE MASTER TO
    MASTER_HOST='192.168.92.11',
    MASTER_USER='repl',
    MASTER_PASSWORD='slavepass',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='master_bin.000001',
    MASTER_LOG_POS=589,
    MASTER_CONNECT_RETRY=10;

START SLAVE;

6. 驗證複寫狀態

SHOW SLAVE STATUS\G
-- 關鍵指標：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0  -- 0 表示追上

7. 功能測試

-- Master
CREATE TABLE TT (id INT, name VARCHAR(30));
INSERT INTO TT VALUES (1,'AAA'),(2,'BBB');

-- Slave
SELECT * FROM TT;  -- 應立即出現兩筆

複寫類型補充

常見故障排查

• Slave_IO_Running: No → 檢查網路、防火牆、帳號密碼、MASTER_LOG_FILE/POS 是否正確。
• Slave_SQL_Running: No → 查 Last_SQL_Error，通常為鍵衝突或結構不符，需手動修正後 START SLAVE。
• 延遲過大 → 檢查 Slave 硬體、innodb_flush_log_at_trx_commit、網路頻寬、長交易阻塞。