MySQL 跨服務器主從同步配置實戰：從啟用 Binlog 到驗證同步狀態

記錄一次生產環境主從搭建全過程，含主庫配置、從庫配置、賬號授權、數據初始化、啟動同步、狀態驗證。

環境規劃

1. Master 配置 (my.cnf)

[mysqld]
log-bin=mysql-bin              # 啟用 Binlog，文件前綴
server-id=1                    # 全局唯一，1~2^32-1

binlog-do-db=dbname            # 只同步指定庫（可選）
binlog-ignore-db=test          # 忽略庫（可選）
# 不寫以上兩行 = 同步所有庫

# InnoDB 穩妥雙寫（可選，寫入性能敏感可調 2）
innodb_flush_log_at_trx_commit=1
sync_binlog=1

# 確保未設 skip-networking

2. Master 建立同步賬號

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.2' IDENTIFIED BY 'repl_pass';
FLUSH PRIVILEGES;

SHOW MASTER STATUS;
-- 記錄 File (如 mysql-bin.000001) 和 Position (如 107)

3. Slave 配置 (my.cnf)

[mysqld]
server-id=2
# log-bin 可開可不開，視是否需級聯從庫
relay-log=relay-bin            # 建議顯式指定 relay log

4. 數據初始化（兩種方式）

方式 A：熱備份 + 恢復（推薦，無鎖）

# Master
xtrabackup --backup --target-dir=/backup/full --user=root --password=xxx

# 壓縮傳輸到 Slave
# Slave
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --copy-back --target-dir=/backup/full --datadir=/var/lib/mysql
chown -R mysql:mysql /var/lib/mysql

方式 B：FTWRL 鎖表導出（小庫可用）

# Master
FLUSH TABLES WITH READ LOCK;
mysqldump -u root -p --all-databases --master-data=2 > alldb.sql
UNLOCK TABLES;

# Slave
mysql -u root -p < alldb.sql

5. Slave 建立主從關係並啟動

CHANGE MASTER TO
    MASTER_HOST='192.168.1.1',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='repl_pass',
    MASTER_LOG_FILE='mysql-bin.000001',   -- Master SHOW MASTER STATUS 的 File
    MASTER_LOG_POS=107;                   -- Master 的 Position

START SLAVE;

6. 驗證同步狀態

SHOW SLAVE STATUS\G

-- 關鍵指標均為 Yes 即正常
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

常見故障排查

• IO_Running=No：網絡不通、賬號密碼錯、MASTER_LOG_FILE/POS 錯誤、防火牆攔截 3306。
• SQL_Running=No：從庫執行 relay log 報錯，查 Last_SQL_Error，常見主鍵衝突、表結構不一致。
• Seconds_Behind_Master 持續增大：從庫硬件較弱、大事務回放慢、網絡延遲。可考慮並行複製 slave_parallel_workers (5.6+)。

雙向主主（可選）

雙方均配置 log-bin、server-id、auto_increment_offset/increment 錯開自增 ID，互為 Master/Slave。極易產生衝突，非必要不建議。