您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 主從複寫搭建完整教程：配置、驗證、維護與故障切換

編輯：MySQL 資料庫相關知識

架構規劃

角色	IP	Server ID	關鍵配置
Master	192.168.1.22	1	log-bin, binlog_format=ROW
Slave	192.168.1.33	2	relay-log, read_only=1

Master 配置步驟


# 1. 建立複寫專用帳號
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'repl_pass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

# 2. 修改 my.cnf [mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=ROW
# 可選：只同步特定庫
# binlog-do-db=db_name
# binlog-ignore-db=mysql

# 3. 重啟
systemctl restart mysqld

# 4. 鎖表備份並記錄 Binlog 位置
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- 記下 File (mysql-bin.000001) 和 Position (106)
mysqldump --single-transaction --master-data=2 --all-databases > full_backup.sql
UNLOCK TABLES;

Slave 配置步驟


# 1. 修改 my.cnf [mysqld]
server-id=2
relay-log=slave-relay-bin
read_only=1
# 可選：只同步特定庫
# replicate-do-db=db_name

# 2. 重啟
systemctl restart mysqld

# 3. 匯入 Master 備份
mysql < full_backup.sql

# 4. 設定複寫來源並啟動
CHANGE MASTER TO
  MASTER_HOST='192.168.1.22',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl_pass',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=106;
START SLAVE;

驗證複寫狀態


SHOW SLAVE STATUS\G
-- 關鍵指標均為 Yes 表示正常
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Last_IO_Error: (空)
Last_SQL_Error: (空)

常見錯誤與解決

錯誤現象	原因	解決
Slave_IO_Running: Connecting	網路不通/帳號密碼錯/防火牆	telnet master 3306、檢查 grants、開放 3306
Slave_SQL_Running: No	SQL 執行錯誤 (主鍵衝突、外鍵、字符集)	看 Last_SQL_Error，手動修正後 `STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;`
Server IDs equal	Master/Slave server-id 相同	修改 slave my.cnf server-id=2 重啟
Seconds_Behind_Master 偏大	Slave 機能不足/大事務/長查詢阻塞	優化慢查詢、升級 Slave 規格、並行複寫 `slave_parallel_workers=4`

日常維護：手動同步一致性檢查


# Master
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS; -- 記下 File, Position
UNLOCK TABLES;

# Slave
SELECT MASTER_POS_WAIT('mysql-bin.000011', 260);
-- 返回 0 表示已追上

故障切換 (Master 宕機 → Slave 升主)

確認 Slave 已應用所有 Relay Log：STOP SLAVE IO_THREAD; 等待 Has read all relay log。
Slave 停止複寫並重置主庫狀態：
STOP SLAVE; RESET MASTER; rm -f master.info relay-log.info (8.0 自動清理)
應用端切換連線字串指向新 Master (VIP/Proxy/DNS)。
原 Master 修復後，重建為 Slave：
備份新 Master → 原 Master 還原 → CHANGE MASTER TO ... → START SLAVE。

GTID 模式 (MySQL 5.6+ 推薦)


# 兩側 my.cnf 共同開啟
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=1

# Slave 連線簡化 (無需手動指定 File/Pos)
CHANGE MASTER TO
  MASTER_HOST='192.168.1.22',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl_pass',
  MASTER_AUTO_POSITION=1;
START SLAVE;

優點：自動定位、支援多源複寫、故障切換無需手動對齊 Binlog 位置。