[mysqld]
log-bin=mysql-bin # 必須:啟用二進制日誌
server-id=1 # 必須:唯一正整數,建議取 IP 尾段
binlog-do-db=wordpress # 可選:只同步指定庫
binlog-ignore-db=mysql # 可選:忽略系統庫
[mysqld]
log-bin=mysql-bin # 建議開啟,利於 Slave 升主或級聯
server-id=2 # 唯一,與 Master 不同
systemctl restart mysqld
GRANT REPLICATION SLAVE ON *.* TO 'sync'@'%' IDENTIFIED BY 'strong_password';
-- 生產建議:'sync'@'192.168.145.%' 限制來源 IP
SHOW MASTER STATUS;
+------------------+----------+-----------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+-----------------------+------------------+
| mysql-bin.000004 | 106 | wordpress,wordpress | mysql,mysql |
+------------------+----------+-----------------------+------------------+
CHANGE MASTER TO
MASTER_HOST='master_ip',
MASTER_USER='sync',
MASTER_PASSWORD='strong_password',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=106;
START SLAVE;
SHOW SLAVE STATUS\G
關鍵指標必須為 Yes:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
| 現象 | 可能原因 | 排查 |
|---|---|---|
| Slave_IO_Running=No | 網路不通/帳號密碼錯/防火牆/MASTER_LOG_FILE/POS 錯 | Slave 端 mysql -h master_ip -u sync -p 測試連線;檢查 Last_IO_Error |
| Slave_SQL_Running=No | 重放報錯(鍵衝突、結構差異、觸發器) | 檢查 Last_SQL_Error,STOP SLAVE; SET GLOBAL sql_slave_skip_counter=1; START SLAVE; 跳過單一錯誤(謹慎) |
| Seconds_Behind_Master=NULL | IO 或 SQL 線程未運行 | 先解決上兩項 |
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='new_ip';
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=123;
START SLAVE;
Python/Shell 定時檢查 Slave_IO_Running、Slave_SQL_Running、Seconds_Behind_Master,異常郵件/企業微信告警。