萬盛學電腦網

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

MySQL 主從復制(Replication)架構實戰:從配置到故障排查的完整手冊

拓撲與版本

一主兩從:192.168.10.103 (Master) → 192.168.10.209 (Slave1, 可讀寫)、192.168.10.219 (Slave2, 唯讀)。測試庫 test。

配置文件關鍵差異

參數MasterSlave1Slave2
server-id123
log-binmysql-binmysql-binmysql-bin
binlog-do-dbtest——
replicate-do-db—testtest
log-slave-updates—ONON
read-only001
  • log-slave-updates 讓 Slave 也寫 Binlog,支援級聯復制或雙主。
  • read-only 僅對非 SUPER 權限用戶生效,防誤寫。

建立複製賬號(三節點同建)

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%' IDENTIFIED BY 'repl_pass';
FLUSH PRIVILEGES;

建立主從關聯

  1. Master:SHOW MASTER STATUS; 記錄 File、Position。
  2. Slave:
CHANGE MASTER TO
  MASTER_HOST='192.168.10.103',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl_pass',
  MASTER_LOG_FILE='mysql-bin.000004',
  MASTER_LOG_POS=2850;
START SLAVE;

驗證關鍵狀態

SHOW SLAVE STATUS\G
-- 必須為 Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-- 關鍵位點一致
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 2850
Relay_Master_Log_File: mysql-bin.000004
Exec_Master_Log_Pos: 2850
Seconds_Behind_Master: 0

測試同步

-- Master
CREATE TABLE `test` (`big_test` BIGINT DEFAULT 0) ENGINE=MyISAM;
-- Slave1/Slave2 應自動出現該表

故障排查速查表

現象排查方向常用命令
Slave_IO_Running=No網絡不通、認證失敗、Master Binlog 被清理START SLAVE IO_THREAD、檢查 Last_IO_Error
Slave_SQL_Running=NoSQL 執行報錯(鍵衝突、結構不一致)Last_Error、START SLAVE SQL_THREAD
Slave_IO_State=Connecting to master網絡/防火牆/主機名解析telnet master 3306
Slave_IO_State=Waiting for master to send event正常空閒狀態無需處理
Seconds_Behind_Master=NULLSQL 線程未運行檢查 Last_SQL_Error
延遲大大事務、慢查詢、硬體瓶頸SHOW PROCESSLIST、pt-query-digest 分析 Relay Log

Master/Slave 常用管理命令

-- Master
SHOW MASTER STATUS;
SHOW SLAVE HOSTS;              -- 連接的從庫列表
SHOW BINLOG EVENTS;            -- 解析 Binlog 事件
PURGE MASTER LOGS TO 'mysql-bin.000100';
RESET MASTER;                  -- 危險:清空所有 Binlog

-- Slave
START/STOP SLAVE [IO_THREAD|SQL_THREAD];
RESET SLAVE;                   -- 重置從庫狀態(慎用)
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;  -- 跳過 1 個錯誤事件
CHANGE MASTER TO ...;          -- 動態修改主庫信息

線程狀態解讀

  • Master Binlog Dump 線程:Sending binlog event to slave、Has sent all binlog to slave; waiting for binlog to be updated。
  • Slave IO 線程:Waiting for master to send event(正常)、Queueing master event to the relay log(寫 Relay Log)。
  • Slave SQL 線程:Reading event from the relay log、Has read all relay log; waiting for the slave I/O thread to update it。

最佳實踐清單

  1. 啟用 GTID(gtid_mode=ON),簡化故障切換與拓撲變更。
  2. binlog_format=ROW,避免 Statement 格式的非確定性問題。
  3. 雙端 sync_binlog=1、innodb_flush_log_at_trx_commit=1、relay_log_recovery=ON,雙重 Crash-Safe。
  4. 監控 Seconds_Behind_Master、Slave_*_Running、Relay_Log_Space。
  5. 定期演練 CHANGE MASTER TO MASTER_AUTO_POSITION=1 故障切換。
copyright © 萬盛學電腦網 all rights reserved