MySQL 複寫 (Replication) 是高可用、讀寫分離、備份卸載的核心基礎設施。本文系統整理複寫原理、主從部署步驟、常見故障診斷與生產環境最佳實務,對比 NoSQL 複寫更具備 ACID 保障與彈性拓撲。

| 欄位 | 含義 |
|---|---|
| Master_Log_File / Read_Master_Log_Pos | I/O Thread 下一個要讀取的主庫 Binlog 位置 |
| Relay_Master_Log_File / Exec_Master_Log_Pos | SQL Thread 下一個要執行的主庫 Binlog 位置 |
| Relay_Log_File / Relay_Log_Pos | SQL Thread 下一個要讀取的本地 Relay Log 位置 |
理解三組座標對應關係,是故障診斷與手動修復的關鍵。
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'slave_host' IDENTIFIED BY 'strong_password';
-- 禁止使用 root 或 SUPER 權限帳號
[mysqld]
server_id = 100 # 全域唯一
log_bin = mysql-bin # 啟用 Binlog,指定固定檔名避免主機名變更導致中斷
log_bin_index = mysql-bin.index
sync_binlog = 1 # 每次 Commit 刷盤,保證不丟 Binlog
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 1 # XA 交易支援,配合雙 1 保證 Crash-safe
[mysqld]
server_id = 200
log_bin = mysql-bin
log_bin_index = mysql-bin.index
relay_log = mysql-relay-bin
relay_log_index = mysql-relay-bin.index
read_only = 1 # 防止誤寫入 (SUPER 權限用戶除外)
skip_slave_start = 1 # 手動 START SLAVE,避免啟動衝突
log_slave_updates = 1 # 從庫也記 Binlog,利於後續升主或級聯複寫
mysqldump --all-databases --master-data=1 --single-transaction > data.sql (InnoDB 必加 --single-transaction) → 從庫 mysql < data.sqlFLUSH TABLES WITH READ LOCK; → SHOW MASTER STATUS; 記錄 File/PositionCHANGE MASTER TO ... MASTER_LOG_FILE='...', MASTER_LOG_POS=...;SHOW SLAVE STATUS; 取 Relay_Master_Log_File / Exec_Master_Log_PosCHANGE MASTER TO ... MASTER_LOG_FILE='Relay_Master_Log_File', MASTER_LOG_POS=Exec_Master_Log_Pos;START SLAVE;
SHOW SLAVE STATUS\G
-- 確認 Slave_IO_Running=Yes, Slave_SQL_Running=Yes
# 檢查 Binlog 是否損壞
mysqlbinlog mysql-bin.000123 > /dev/null
# 檢查 Relay Log
mysqlbinlog mysql-relay-bin.000045 > /dev/null
SET GLOBAL sql_slave_skip_counter = 1; -- 跳過 1 個事件
START SLAVE;
-- 多個錯誤需重複執行,注意:會造成主從資料不一致
STOP SLAVE;
CHANGE MASTER TO
MASTER_LOG_FILE='Relay_Master_Log_File值',
MASTER_LOG_POS=Exec_Master_Log_Pos值;
START SLAVE;
-- 系統會丟棄現有 Relay Log 重新下載
server_id 全域唯一sync_binlog=1 + innodb_flush_log_at_trx_commit=1 (雙 1)read_only=1 + skip_slave_start=1 + log_slave_updates=1Seconds_Behind_Master、Slave_IO_Running、Slave_SQL_Running 告警pt-table-checksum 驗證主從資料一致性xtrabackup 熱備,避免影響主庫log_slave_updates=1遵循上述規範部署與運維,MySQL 複寫可穩定支撐核心業務高可用架構。