MySQL 5.5 主從同步複制完整實戰配置教學

MySQL 5.5 主從同步複制完整實戰配置教學

本文以 CentOS 6.5 + MySQL 5.5.39 為例，詳細記錄從規劃、主庫配置、建立複制帳號、鎖表備份、從庫配置、啟動同步到驗證的完整 12 步驟流程。

環境規劃

目標同步資料庫：ilanni

步驟 1：主庫配置 /etc/my.cnf

[mysqld]
log-bin=mysql-bin              # 必須開啟二進制日誌
max_binlog_size=500M           # 單檔上限
server-id=1                    # 全域唯一
binlog-do-db=ilanni            # 只同步 ilanni (可多行)
binlog-ignore-db=mysql         # 忽略系統庫 (可多行)
# 若不指定 do/ignore 則同步全部庫

修改後 重啟 mysqld 生效。

步驟 2：主庫建立複制帳號並授權

GRANT REPLICATION SLAVE ON *.* TO 'ilanni'@'%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;

驗證：SELECT User, Repl_slave_priv FROM mysql.user WHERE User='ilanni'; 應顯示 Y。
從 Slave 測試連線：mysql -h 192.168.1.213 -u ilanni -p123456。

步驟 3：主庫鎖表、記錄 Binlog 位置、導出資料

-- Master 終端 1
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |     1616 |              |                  |
+------------------+----------+--------------+------------------+
-- 記住 File 與 Position (此處 1616)

-- Master 終端 2 (另開視窗)
mysqldump -uroot -p123456 ilanni > /root/ilanni.sql
scp /root/ilanni.sql root@192.168.1.214:/root/

關鍵： 鎖表終端**不可退出**，否則鎖釋放導致資料不一致。

步驟 4：從庫配置 /etc/my.cnf

[mysqld]
server-id=2  # 唯一即可，無需 log-bin (但建議開啟利於鏈式複制)

步驟 5：從庫建庫、導入備份

mysql -uroot -p123456 -e "CREATE DATABASE ilanni;"
mysql -uroot -p123456 ilanni < /root/ilanni.sql

步驟 6：主庫解鎖

UNLOCK TABLES;

步驟 7：從庫設定複制參數並啟動

CHANGE MASTER TO
  MASTER_HOST='192.168.1.213',
  MASTER_USER='ilanni',
  MASTER_PASSWORD='123456',
  MASTER_LOG_FILE='mysql-bin.000003',  -- 步驟 3 記錄的 File
  MASTER_LOG_POS=1616,                 -- 步驟 3 記錄的 Position
  MASTER_CONNECT_RETRY=100;            -- 重試間隔秒數

START SLAVE;

步驟 8：驗證同步狀態

SHOW SLAVE STATUS\G

重點檢查兩欄均為 Yes：

• Slave_IO_Running: Yes — IO 執行緒正常從 Master 拉取 Binlog
• Slave_SQL_Running: Yes — SQL 執行緒正常重放 Relay Log

若 Slave_IO_Running=No → 檢查網路/防火牆/帳號密碼；
若 Slave_SQL_Running=No → 檢查資料衝突/缺表/權限，查看 Last_SQL_Error。

步驟 9：檢查 Relay Log 與 master.info

# Relay Log 內容
mysqlbinlog /var/lib/mysql/ilanni-relay-bin.000002

# master.info 記錄的同步座標
cat /var/lib/mysql/master.info

步驟 10：實測同步

-- Master
INSERT INTO ilanni.ilannitable VALUES (6);
DELETE FROM ilanni.ilannitable WHERE id=2;
SELECT * FROM ilanni.ilannitable;
-- 結果: 1,3,4,5,6

-- Slave
SELECT * FROM ilanni.ilannitable;
-- 應與 Master 完全一致

重要提醒

• MySQL 5.1.7+ **不支援** master-connect-retry 等舊參數寫入 my.cnf，會導致啟動報錯，請僅在 CHANGE MASTER TO 中指定。
• 生產環境建議開啟 sync_binlog=1、innodb_flush_log_at_trx_commit=1 確保主庫不丟交易。
• 監控指標：Seconds_Behind_Master、Slave_IO_Running、Slave_SQL_Running、Relay Log 磁碟佔用。