MySQL 主從同步極簡配置指南：5 分鐘建立單向複製、狀態驗證與常見坑

拓撲與版本

• Master: 192.168.1.1 (server-id=1)
• Slave: 192.168.1.2 (server-id=2)
• 同步庫: `powerdns`
• MySQL 5.5+ / 5.6+ / 5.7 / 8.0 通用

Master 配置 (my.cnf)

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = powerdns          -- 僅同步此庫
binlog-ignore-db = mysql         -- 忽略系統庫
# binlog_format = ROW            -- 5.7+ 建議 ROW，避免非確定性函數導致不一致

重啟：`systemctl restart mysqld`

建立複製賬號 (僅允許 Slave IP)

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.1.2' IDENTIFIED BY 'strong_pass';
FLUSH PRIVILEGES;

獲取同步起點

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      309 | powerdns     | mysql            |
+------------------+----------+--------------+------------------+

記錄 `File` 和 `Position`。

Slave 配置 (my.cnf)

[mysqld]
server-id = 2
# log-bin = mysql-bin          # 可選：開啟利於鏈式/備份
# relay-log = relay-bin        # 可選：自定義 relay log 名稱
read_only = 1                  -- 強烈推薦防誤寫

重啟：`systemctl restart mysqld`

啟動同步

mysql> CHANGE MASTER TO
    MASTER_HOST='192.168.1.1',
    MASTER_USER='slave',
    MASTER_PASSWORD='strong_pass',
    MASTER_LOG_FILE='mysql-bin.000002',
    MASTER_LOG_POS=309;

mysql> START SLAVE;

驗證同步狀態

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
...

**核心指標：** 兩個 `Yes` + `Seconds_Behind_Master = 0` (或極小) = 同步正常。

常見坑速查

升級建議：GTID 複制 (MySQL 5.6+)

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = 1
-- Slave 啟動無需指定 File/Position，自動定位
CHANGE MASTER TO MASTER_HOST='...', MASTER_AUTO_POSITION=1;