萬盛學電腦網

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

MySQL 主從複寫搭建完整實戰:配置檔、權限授予、狀態驗證與常見故障排查

架構規劃

角色IPServer ID說明
Master192.168.0.17111寫入節點,開啟 Binlog
Slave192.168.0.119112讀取節點,Relay Log 重放

原則:Slave 版本 ≥ Master 版本,避免不相容。

Master 配置

[mysqld]
server-id = 111
log-bin = mysql-bin
binlog-do-db = a111cnnet      # 只同步指定庫
binlog-ignore-db = mysql      # 忽略系統庫

重啟後建立複寫帳號:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* 
TO 'backup2'@'192.168.0.119' IDENTIFIED BY 'backup2';
FLUSH PRIVILEGES;

Slave 配置

[mysqld]
server-id = 112
master-host = 192.168.0.17
master-user = backup2
master-password = backup2
master-connect-retry = 60
replicate-do-db = a111cnnet
replicate-ignore-db = mysql

MySQL 5.6+ 建議改用 CHANGE MASTER TO 動態設定,不寫死在 my.cnf。

啟動與驗證

  1. 重啟 Master,再重啟 Slave。
  2. Slave 執行:START SLAVE;
  3. 檢查狀態:SHOW SLAVE STATUS\G

關鍵指標:

Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

皆為 Yes 即同步正常。

驗證同步

在 Master 建表/插資料,Slave 秒級同步出現。

故障排查清單

  1. Slave_IO_Running: No → 檢查網路、帳號密碼、Master Binlog 開啟、防火牆 3306。
  2. Slave_SQL_Running: No → SHOW SLAVE STATUS 看 Last_SQL_Error,常見主鍵衝突、字元集不符。
  3. 跳過錯誤(謹慎):SET GLOBAL sql_slave_skip_counter = 1; START SLAVE;
  4. 重新同步:停 Slave、備份 Master 資料(mysqldump --master-data=2)、導入 Slave、重設 CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...;。
  5. 檢查 Seconds_Behind_Master 延遲秒數,持續增大需優化 SQL/硬體。

GTID 複寫(MySQL 5.6+ 推薦)

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log-slave-updates = 1

啟用後 CHANGE MASTER TO MASTER_AUTO_POSITION = 1,無需手動指定 Binlog 位點,故障切換更簡單。

copyright © 萬盛學電腦網 all rights reserved