萬盛學電腦網

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

MySQL 主從同步配置全攻略:從單向複製到雙主架構的完整實踐

架構選型指引

架構寫入節點讀取擴展高可用複雜度適用場景
一主一從1✅需額外工具低讀多寫少、非核心業務
雙主2✅互為備份中雙活寫入、跨機房容災
一主多從1✅✅需額外工具低極高讀壓力、報表查詢

第一部分:一主一從配置步驟

環境準備

Master: 192.168.1.100 (server-id=1)
Slave:  192.168.1.101 (server-id=2)
MySQL版本: 5.7+ / 8.0+

步驟 1:主庫配置

# /etc/my.cnf
[mysqld]
server-id = 1
log-bin = /var/lib/mysql/mysql-bin
binlog_format = ROW          # 推薦 ROW 格式
expire_logs_days = 7
gtid_mode = ON               # MySQL 5.6+ 推薦啟用 GTID
enforce_gtid_consistency = ON

重啟後驗證:

mysql> SHOW VARIABLES LIKE 'log_bin';
mysql> SHOW VARIABLES LIKE 'server_id';

步驟 2:創建同步賬號

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

步驟 3:主庫備份與記錄位置

# 主庫加讀鎖(備份窗口)
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
# 記下 File 和 Position,如:mysql-bin.000004, 154

# 新終端執行備份
mysqldump -u root -p --all-databases --master-data=2 --single-transaction > /tmp/full_backup.sql

# 主庫解鎖
mysql> UNLOCK TABLES;

步驟 4:從庫導入備份

# 從庫配置 server-id=2,重啟
# 導入備份
mysql -u root -p < /tmp/full_backup.sql

步驟 5:配置並啟動同步

-- MySQL 5.6+ GTID 模式(推薦)
CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='StrongPass@123',
  MASTER_AUTO_POSITION=1;

-- 傳統位置模式
CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='StrongPass@123',
  MASTER_LOG_FILE='mysql-bin.000004',
  MASTER_LOG_POS=154;

START SLAVE;
SHOW SLAVE STATUS\G

驗證關鍵指標

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

第二部分:雙主架構配置

核心配置差異

# Master (192.168.1.100) my.cnf
server-id = 1
log-bin = mysql-bin
log-slave-updates = 1          # 關鍵:將從庫同步的數據寫入自己的 binlog
auto_increment_increment = 2   # 自增步長
auto_increment_offset = 1      # 起始偏移:1,3,5...
slave-skip-errors = 1032,1062,1007  # 忽略常見衝突錯誤

# Slave (192.168.1.101) my.cnf
server-id = 2
log-bin = mysql-bin
log-slave-updates = 1
auto_increment_increment = 2
auto_increment_offset = 2      # 起始偏移:2,4,6...
slave-skip-errors = 1032,1062,1007
# read-only = 1  # 雙主雙寫時必須註釋掉

雙向同步建立流程

  1. 先完成 A→B 單向同步
  2. 在 B 上 STOP SLAVE; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; 記錄位置
  3. 在 A 上執行 CHANGE MASTER TO ... MASTER_HOST='B_IP' ... 指向 B 的位置
  4. START SLAVE; 啟動 B→A 同步
  5. 雙向驗證:在 A 插入奇數 ID,在 B 插入偶數 ID,互相檢查同步

監控與維護必備命令

-- 查看同步延遲
SHOW SLAVE STATUS\G

-- 跳過報錯事務(謹慎使用)
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

-- 重置從庫(慎用)
STOP SLAVE;
RESET SLAVE ALL;

生產環境避坑清單

  • ✅ 必須設置 server-id 唯一且非 0
  • ✅ 生產環境強制 binlog_format=ROW + GTID
  • ✅ 從庫設置 read_only=1(單向同步時)防止誤寫入
  • ✅ 監控 Seconds_Behind_Master、Slave_IO_Running、Slave_SQL_Running
  • ✅ 定期校驗數據一致性(pt-table-checksum)
  • ❌ 禁止在從庫直接寫入業務數據(除雙主架構)
copyright © 萬盛學電腦網 all rights reserved