| 架構 | 寫入節點 | 讀取擴展 | 高可用 | 複雜度 | 適用場景 |
|---|---|---|---|---|---|
| 一主一從 | 1 | ✅ | 需額外工具 | 低 | 讀多寫少、非核心業務 |
| 雙主 | 2 | ✅ | 互為備份 | 中 | 雙活寫入、跨機房容災 |
| 一主多從 | 1 | ✅✅ | 需額外工具 | 低 | 極高讀壓力、報表查詢 |
Master: 192.168.1.100 (server-id=1)
Slave: 192.168.1.101 (server-id=2)
MySQL版本: 5.7+ / 8.0+
# /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';
CREATE USER 'repl_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass@123';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.%';
FLUSH PRIVILEGES;
# 主庫加讀鎖(備份窗口)
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;
# 從庫配置 server-id=2,重啟
# 導入備份
mysql -u root -p < /tmp/full_backup.sql
-- 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 # 雙主雙寫時必須註釋掉
STOP SLAVE; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; 記錄位置CHANGE MASTER TO ... MASTER_HOST='B_IP' ... 指向 B 的位置START SLAVE; 啟動 B→A 同步-- 查看同步延遲
SHOW SLAVE STATUS\G
-- 跳過報錯事務(謹慎使用)
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;
-- 重置從庫(慎用)
STOP SLAVE;
RESET SLAVE ALL;
server-id 唯一且非 0binlog_format=ROW + GTIDread_only=1(單向同步時)防止誤寫入Seconds_Behind_Master、Slave_IO_Running、Slave_SQL_Runningpt-table-checksum)