主主互備基於 MySQL Binlog 增量日誌,兩台伺服器互為主從,任一節點寫入均同步至另一節點。優勢在於:
auto_increment_increment=2 + offset)避免主鍵衝突/usr/local/mysql)配置前務必確保兩庫資料一致、業務停寫,建議關閉 Web 服務或設定唯讀模式。
rm -rf /usr/local/mysql/log/bin*
vi /etc/my.cnf
[mysqld] 區段新增:
# Replication Master Server 1
log-bin = /usr/local/mysql/log/bin.log
server-id = 1
binlog-ignore-db = mysql
auto-increment-increment = 2
auto-increment-offset = 1
mysql -u root -p123456
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.7.249' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
/usr/local/mysql/bin/mysqldump -u root -p123456 --opt --skip-lock-tables --flush-logs --all-databases > /root/allbak.sql
scp /root/allbak.sql root@192.168.7.249:/root
service mysqld restart
mysql -u root -p123456
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.7.249', MASTER_USER='slave', MASTER_PASSWORD='123456';
START SLAVE;
# my.cnf 差異點
server-id = 2
auto-increment-offset = 2
replicate-ignore-db = mysql # 從庫端忽略系統庫同步
# 建立同步帳號供 Node1 使用
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.7.201' IDENTIFIED BY '123456';
# 恢復 Node1 備份資料
/usr/local/mysql/bin/mysql -u root -p123456 < /root/allbak.sql
service mysqld restart
# 配置從屬連線指向 Node1
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.7.201', MASTER_USER='slave', MASTER_PASSWORD='123456';
START SLAVE;
分別在兩節點執行:
SHOW SLAVE STATUS\G
關鍵指標均為 Yes 即成功:
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
auto_increment_increment 且 offset 互不相同binlog-ignore-db/replicate-ignore-db 排除 mysql、information_schemaslave_net_timeout、啟用 MASTER_RETRY_COUNT主主互備 + Keepalived VIP 漂移,構成經典的 MySQL 高可用架構。但隨著 MySQL 5.7+ GTID、MGR(Group Replication)成熟,新專案建議優先評估 MGR 或 InnoDB Cluster,運維複雜度更低、一致性保證更強。