| 角色 | IP | Server ID | 說明 |
|---|---|---|---|
| Master | 192.168.0.17 | 111 | 寫入節點,開啟 Binlog |
| Slave | 192.168.0.119 | 112 | 讀取節點,Relay Log 重放 |
原則:Slave 版本 ≥ Master 版本,避免不相容。
[mysqld]
server-id = 111
log-bin = mysql-bin
binlog-do-db = a111cnnet # 只同步指定庫
binlog-ignore-db = mysql # 忽略系統庫
重啟後建立複寫帳號:
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
TO 'backup2'@'192.168.0.119' IDENTIFIED BY 'backup2';
FLUSH PRIVILEGES;
[mysqld]
server-id = 112
master-host = 192.168.0.17
master-user = backup2
master-password = backup2
master-connect-retry = 60
replicate-do-db = a111cnnet
replicate-ignore-db = mysql
MySQL 5.6+ 建議改用 CHANGE MASTER TO 動態設定,不寫死在 my.cnf。
START SLAVE;SHOW SLAVE STATUS\G關鍵指標:
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
皆為 Yes 即同步正常。
在 Master 建表/插資料,Slave 秒級同步出現。
Slave_IO_Running: No → 檢查網路、帳號密碼、Master Binlog 開啟、防火牆 3306。Slave_SQL_Running: No → SHOW SLAVE STATUS 看 Last_SQL_Error,常見主鍵衝突、字元集不符。SET GLOBAL sql_slave_skip_counter = 1; START SLAVE;mysqldump --master-data=2)、導入 Slave、重設 CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...;。Seconds_Behind_Master 延遲秒數,持續增大需優化 SQL/硬體。[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log-slave-updates = 1
啟用後 CHANGE MASTER TO MASTER_AUTO_POSITION = 1,無需手動指定 Binlog 位點,故障切換更簡單。