主從同步是 MySQL 高可用、讀寫分離、備份的基石。本文涵蓋配置、啟動、狀態檢查、錯誤跳過、常用命令全流程。
[mysqld]
server-id = 1 # 唯一 ID,1~2^32-1
log-bin = /home/log/mysql-bin # 二進制日誌路徑/前綴
binlog-format = ROW # 推薦 ROW 格式
binlog-ignore-db = mysql # 不同步系統庫
binlog-ignore-db = test
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
expire_logs_days = 7 # 自動清理 7 天前 binlog
max_binlog_size = 100M # 單文件大小限制
sync_binlog = 1 # 每次提交刷盤 (最高安全)
innodb_flush_log_at_trx_commit = 1
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;
-- 鎖表防止寫入 (僅初始化時)
FLUSH TABLES WITH READ LOCK;
-- 查看位置
SHOW MASTER STATUS\G
-- 記錄 File (如 mysql-bin.000001) 和 Position (如 154)
-- 解鎖
UNLOCK TABLES;
[mysqld]
server-id = 2 # 唯一,不可與 Master 衝突
relay-log = /home/log/relay-bin # 中繼日誌
relay-log-index = /home/log/relay-bin.index
read-only = 1 # 唯讀模式 (防止誤寫入)
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
master-info-repository = TABLE # 存儲於表而非文件 (5.6+)
relay-log-info-repository = TABLE
[mysqld]
master-host = 192.168.0.1
master-user = repl_user
master-password = strong_password
master-port = 3306
master-connect-retry = 10
STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST = '192.168.0.1',
MASTER_USER = 'repl_user',
MASTER_PASSWORD = 'strong_password',
MASTER_PORT = 3306,
MASTER_LOG_FILE = 'mysql-bin.000001',
MASTER_LOG_POS = 154,
MASTER_CONNECT_RETRY = 10;
START SLAVE;
SHOW SLAVE STATUS\G
| 欄位 | 正常值 | 異常處理 |
|---|---|---|
| Slave_IO_Running | Yes | No → 檢查網路、用戶、防火牆、MASTER_LOG_FILE/POS |
| Slave_SQL_Running | Yes | No → 執行錯誤,查看 Last_SQL_Error |
| Seconds_Behind_Master | 0 或小數值 | 大 → 延遲,檢查慢查詢、硬體、並行複製 |
| Last_IO_Error / Last_SQL_Error | 空 | 有錯誤信息 → 具體分析 |
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N;
-- Master 執行
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS\G; -- 記錄 File, Position
UNLOCK TABLES;
-- Slave 執行
STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...;
START SLAVE;
[mysqld]
slave_parallel_workers = 4 # 並行 worker 數
slave_parallel_type = LOGICAL_CLOCK # 基於組提交並行
master_info_repository = TABLE
relay_log_info_repository = TABLE
| 命令 | 執行端 | 用途 |
|---|---|---|
| STOP SLAVE | Slave | 停止同步 |
| START SLAVE | Slave | 啟動同步 |
| RESET SLAVE | Slave | 清除同步位置,重新生成 master.info |
| RESET MASTER | Master | 清除所有 binlog |
| SHOW MASTER STATUS | Master | 查看 binlog 位置 |
| SHOW SLAVE STATUS | Slave | 查看同步狀態 |
| CHANGE MASTER TO | Slave | 修改主庫連線參數 |
| LOAD DATA FROM MASTER | Slave | 從主庫重讀所有數據 (慎用,鎖表久) |
| PURGE BINARY LOGS TO 'file' | Master | 手動清理 binlog |
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = 1
-- Slave 簡單配置
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
GTID 免去手動維護 File/Position,故障切換更簡單。
規範部署、監控 Seconds_Behind_Master、定期驗證數據一致性,是主從穩定運行的關鍵。