MySQL 5.5 主從同步配置實戰筆記：GTID 前身、binlog 格式、故障恢復、時間同步與大數據庫遷移技巧

架構拓撲

Master (10.1.123.197) ───binlog dump───► Slave (10.1.123.160)
  server-id=1                                    server-id=2
  log-bin=mysql-bin                              relay-log=slave-relay-bin
  binlog_format=mixed                            read_only=1 (推薦)
  sync_binlog=1                                  sync_relay_log=1

Master 關鍵配置 (/etc/my.cnf)

[mysqld]
server-id = 1
log-bin = mysql-bin
log-bin-index = mysql-bin.index
binlog_format = MIXED          -- 推薦：兼容 Statement/Row，減少主從不一致
sync_binlog = 1                -- 每次提交刷盤，最強持久化，性能略損
max_allowed_packet = 100M      -- 大事務/大字段必調大

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

[mysqldump]
max_allowed_packet = 100M

建立複製賬號

CREATE USER 'repl'@'%' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

Slave 關鍵配置

[mysqld]
server-id = 2
log-bin = mysql-bin                 -- Slave 開啟 binlog 利於鏈式複製/備份
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index
read_only = 1                       -- 防止誤寫入

-- 5.5 專用同步參數
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1

啟動同步標準流程

1. Master 執行 `FLUSH TABLES WITH READ LOCK;` (可選，小庫可跳過)
2. Master 記錄 `SHOW MASTER STATUS\G` → `File` + `Position`
3. 備份數據導入 Slave (mysqldump / xtrabackup / 物理拷貝)
4. Slave 配置同步起點：

   CHANGE MASTER TO 
     MASTER_HOST='10.1.123.197',
     MASTER_USER='repl',
     MASTER_PASSWORD='strong_password',
     MASTER_PORT=3306,
     MASTER_LOG_FILE='mysql-bin.000001',
     MASTER_LOG_POS=107;
   

5. `START SLAVE;`
6. 驗證：`SHOW SLAVE STATUS\G` → `Slave_IO_Running: Yes` + `Slave_SQL_Running: Yes` + `Seconds_Behind_Master: 0`

故障恢復：同步中斷重新定位

-- Master 端
SHOW MASTER STATUS\G;  -- 獲取最新 File, Position

-- Slave 端
STOP SLAVE;
CHANGE MASTER TO 
  MASTER_LOG_FILE='mysql-bin.000005',
  MASTER_LOG_POS=12345;
START SLAVE;

時間同步：必做項

yum -y install ntpdate
ntpdate cn.pool.ntp.org
hwclock -w
-- 主從時間差 > 1s 會導致 Seconds_Behind_Master 不準、基於時間的分區裁剪異常

大數據庫不停機遷移新 Slave (6G 實測)

# Master 熱備份帶 binlog 位置
mysqldump -uroot -pxxx -h127.0.0.1 \
  --databases db1 db2 db3 \
  --master-data=1 --single-transaction \
  > bak.sql

# 新 Slave 導入
mysql -uroot -pxxx < bak.sql
-- bak.sql 末尾自帶 CHANGE MASTER TO ... 語句

# 啟動同步
START SLAVE;

**關鍵參數：** `--master-data=1` 輸出 `CHANGE MASTER` 語句；`--single-transaction` 保證一致性視圖 (InnoDB)。

常用運維命令速查