萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 主從同步配置:從搭建到故障跳過完整教程

MySQL 主從複製 (Replication) 完整部署與運維指南

主從同步是 MySQL 高可用、讀寫分離、備份的基石。本文涵蓋配置、啟動、狀態檢查、錯誤跳過、常用命令全流程。

架構說明

  • Master:寫入主庫,開啟 binlog
  • Slave:讀取 relay log 重放,保持數據一致
  • 非對稱:Master 可寫可讀,Slave 建議唯讀

一、Master 配置 (my.cnf)

[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

二、建立同步用戶 (Master 執行)

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

三、獲取 Master 同步坐標

-- 鎖表防止寫入 (僅初始化時)
FLUSH TABLES WITH READ LOCK;

-- 查看位置
SHOW MASTER STATUS\G
-- 記錄 File (如 mysql-bin.000001) 和 Position (如 154)

-- 解鎖
UNLOCK TABLES;

四、Slave 配置 (my.cnf)

[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

五、Slave 連線配置 (兩種方式)

方式 A:配置檔 (舊版/靜態)

[mysqld]
master-host = 192.168.0.1
master-user = repl_user
master-password = strong_password
master-port = 3306
master-connect-retry = 10

方式 B:CHANGE MASTER TO (推薦,動態、5.1+)

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_RunningYesNo → 檢查網路、用戶、防火牆、MASTER_LOG_FILE/POS
Slave_SQL_RunningYesNo → 執行錯誤,查看 Last_SQL_Error
Seconds_Behind_Master0 或小數值大 → 延遲,檢查慢查詢、硬體、並行複製
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;

並行複製 (MySQL 5.6+ 提升效能)

[mysqld]
slave_parallel_workers = 4          # 並行 worker 數
slave_parallel_type = LOGICAL_CLOCK # 基於組提交並行
master_info_repository = TABLE
relay_log_info_repository = TABLE

八、常用管理命令速查

命令執行端用途
STOP SLAVESlave停止同步
START SLAVESlave啟動同步
RESET SLAVESlave清除同步位置,重新生成 master.info
RESET MASTERMaster清除所有 binlog
SHOW MASTER STATUSMaster查看 binlog 位置
SHOW SLAVE STATUSSlave查看同步狀態
CHANGE MASTER TOSlave修改主庫連線參數
LOAD DATA FROM MASTERSlave從主庫重讀所有數據 (慎用,鎖表久)
PURGE BINARY LOGS TO 'file'Master手動清理 binlog

九、GTID 模式 (MySQL 5.6+ 推薦)

[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、定期驗證數據一致性,是主從穩定運行的關鍵。

copyright © 萬盛學電腦網 all rights reserved