MySQL 5.5 主從同步配置完整實戰筆記

架構說明

• Master： 10.1.123.197 (server-id=1)
• Slave： 10.1.123.160 (server-id=2)

Master 端配置 (my.cnf)

[mysqld]
log-bin=mysql-bin
log-bin-index=mysql-bin.index
server-id = 1
sync_binlog=1
binlog_format=mixed

# 指定同步的資料庫，忽略系統庫
binlog-do-db = testdb
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema

[mysqldump]
max_allowed_packet = 32M  # 建議設大一點，如 100M

修改後執行 service mysql restart 重啟服務。

建立同步帳號

CREATE USER 'funsion'@'%' IDENTIFIED BY 'jb51.net';
GRANT REPLICATION SLAVE ON *.* TO 'funsion'@'%' IDENTIFIED BY 'jb51.net';
FLUSH PRIVILEGES;

Slave 端配置 (my.cnf)

[mysqld]
server-id=2
log-bin = mysql-bin
relay-log-index = slave-relay-bin.index
relay-log = slave-relay-bin

# 僅 MySQL 5.5 需要以下三行，增強同步可靠性
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1

重啟 Slave 端 MySQL 服務。

建立主從關聯

登入 Slave 的 MySQL 客戶端：

CHANGE MASTER TO 
  MASTER_HOST='10.1.123.233',
  MASTER_USER='funsion',
  MASTER_PORT=3306,
  MASTER_PASSWORD='ifunsion.com';

CHANGE MASTER TO MASTER_CONNECT_RETRY=30;
START SLAVE;

常見錯誤處理：同步無法啟動

1. 在 Master 執行 SHOW MASTER STATUS\G，記錄 File (如 mysql-bin.000001) 與 Position (如 107)
2. 在 Slave 執行：

   STOP SLAVE;
   CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
   START SLAVE;
   

3. 檢查 SHOW SLAVE STATUS\G 中 Slave_IO_Running 與 Slave_SQL_Running 均為 Yes 表示成功

時間同步 (關鍵步驟)

yum -y install ntpdate
ntpdate cn.pool.ntp.org
clock -w

主從伺服器時間必須同步，否則可能導致同步延遲或錯誤。

實用管理命令

進階場景：業務不停機為新 Slave 製作鏡像

場景： Master(1號) 已有 Slave(2號)，新增 3號 Slave，但 Master、2號均不能停機，同步進程不能停。

1. 在 Master 熱備份並記錄 binlog 位置：

   mysqldump -uroot -pxxxx -h127.0.0.1 --databases db1 db2 db3 --master-data=1 > bak.sql
   

   --master-data=1 會在導出文件末尾自動加入 CHANGE MASTER TO 語句。
2. 在 3號 Slave：SLAVE STOP;
3. 導入備份：mysql -uroot -p < bak.sql
4. 啟動同步：SLAVE START;

3號 Slave 會自動從導出那一刻的 binlog 位置開始同步。適用於資料庫較小 (如 6G 內) 的場景。

驗證同步狀態

SHOW SLAVE STATUS\G
# 重點關注：
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
# Seconds_Behind_Master: 0 (或較小值)