您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 雙主復制架構部署全流程：RHEL 環境下雙向同步配置、自增偏移量設置與故障切換驗證

編輯：MySQL 綜合教學

架構目標

兩台 MySQL 互為主從，**雙向同步**，實現寫入高可用。關鍵點：自增 ID 衝突避免、Binlog 格式統一、同步用戶隔離。

環境準備

OS：RHEL 6.4 x86_64 (最小化)
IP：Node A 192.168.131.129，Node B 192.168.131.130
MySQL：5.6.26 通用二進制包
關閉防火牆、SELinux

一、二進制安裝標準化 (雙節點同步執行)

tar xf mysql-5.6.26-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.6.26-linux-glibc2.5-x86_64 /usr/local/mysql
useradd -M -s /sbin/nologin mysql
chown -R root:root /usr/local/mysql/*
chown -R mysql:mysql /usr/local/mysql/data/
yum -y install libaio
cd /usr/local/mysql/scripts/
./mysql_install_db --datadir=/usr/local/mysql/data/ --user=mysql --basedir=/usr/local/mysql/
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
source /etc/profile

二、基礎配置 /etc/my.cnf (雙節點差異化)

Node A (192.168.131.129)

[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
user=mysql
symbolic-links=0
server-id=20
log-bin=mysql-bin
log-bin-index=mysql-bin.index
binlog-format=ROW
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
replicate-ignore-db=test
auto-increment-increment=2
auto-increment-offset=1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Node B (192.168.131.130)

[mysqld]
... (同上基礎項)
server-id=21
log-bin=mysql-bin
log-bin-index=mysql-bin.index
binlog-format=ROW
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
replicate-do-db=test
auto-increment-increment=2
auto-increment-offset=2

關鍵參數解讀

server-id：全局唯一，必須不同。
binlog-format=ROW：雙主**強制 ROW 格式**，避免 Statement 格式導致數據不一致。
auto-increment-increment=2 + offset=1/2：Node A 產生奇數 ID (1,3,5...)，Node B 產生偶數 ID (2,4,6...)，**從根本避免自增主鍵衝突**。
replicate-do-db=test (Node B) / replicate-ignore-db=test (Node A)：按需同步業務庫，系統庫不同步。

三、初始化數據同步與同步賬號

Node A：鎖表導出

service mysqld restart
mysql -uroot -p123456
mysql> FLUSH TABLES WITH READ LOCK;
# 新終端
mysqldump -uroot -p123456 -B test > test.sql
# 回主終端
mysql> UNLOCK TABLES;

Node A：創建同步用戶

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.131.130' IDENTIFIED BY 'repl_pwd';
mysql> FLUSH PRIVILEGES;

傳輸導出文件

scp test.sql 192.168.131.130:/root/

Node B：導入數據、創建同步用戶

mysql -uroot -p123456 < test.sql
mysql -uroot -p123456
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.131.129' IDENTIFIED BY 'repl_pwd';
mysql> FLUSH PRIVILEGES;

四、建立雙向復制關係

Node A → Node B (Node A 作 Slave 同步 Node B)

# Node B 查看主庫狀態
mysql -uroot -p123456 -h 192.168.131.130 -e "SHOW MASTER STATUS;"
# 記錄 File (如 mysql-bin.000002) 和 Position (如 420)

# Node A 配置
mysql -uroot -p123456
mysql> CHANGE MASTER TO
    MASTER_HOST='192.168.131.130',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='repl_pwd',
    MASTER_LOG_FILE='mysql-bin.000002',
    MASTER_LOG_POS=420;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
# 確認 Slave_IO_Running=Yes, Slave_SQL_Running=Yes

Node B → Node A (Node B 作 Slave 同步 Node A)

# Node A 查看主庫狀態
mysql -uroot -p123456 -h 192.168.131.129 -e "SHOW MASTER STATUS;"
# 記錄 File (如 mysql-bin.000002) 和 Position (如 689)

# Node B 配置
mysql -uroot -p123456
mysql> CHANGE MASTER TO
    MASTER_HOST='192.168.131.129',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='repl_pwd',
    MASTER_LOG_FILE='mysql-bin.000002',
    MASTER_LOG_POS=689;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

五、驗證測試

Node A 建表

mysql> USE test;
mysql> CREATE TABLE info (id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, name CHAR(20));
mysql> SHOW TABLES;

Node B 查看同步、建表

mysql> USE test;
mysql> SHOW TABLES; -- 應見 info
mysql> CREATE TABLE person (id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name CHAR(20));
mysql> SHOW TABLES;

Node A 驗證雙向同步

mysql> USE test;
mysql> SHOW TABLES; -- 應見 info, person
mysql> INSERT INTO info (name) VALUES ('from A');
mysql> INSERT INTO person (name) VALUES ('from A');
# Node B 查詢
mysql> SELECT * FROM info; -- 見 from A (id=1)
mysql> SELECT * FROM person; -- 見 from A (id=2)
# Node B 插入
mysql> INSERT INTO info (name) VALUES ('from B'); -- id=3 (奇數? 不，Node B offset=2 產生偶數 2,4... 但 info 表在 Node A 創建，自增屬於 Node A? 實測以創建表節點為準，建議統一用 UUID 或雪花算法主鍵)

注意：雙主自增偏移量僅保證**同一表在不同節點插入**不衝突。跨節點創建表、DDL 同步仍有風險。生產環境**強烈建議主鍵用 UUID / 雪花算法**，杜絕自增衝突。

六、運維監控要點

Seconds_Behind_Master：延遲監控，告警閾值 > 30s。
Slave_IO_Running / Slave_SQL_Running：任一 No 立即告警。
SHOW MASTER STATUS 定期備份 Binlog 位點，災難恢復用。
定期 pt-table-checksum 一致性校驗。
DDL 操作協調：雙主**嚴禁同時對同一表做 DDL**，建議指定單節點執行 DDL 並同步。

七、總結

MySQL 雙主架構通過 auto_increment_offset 解決主鍵衝突，ROW 格式 Binlog 保證數據一致性，server-id 唯一標識節點。部署相對簡單，但**寫入衝突解決、DDL 協調、故障切換腳本**需應用層配合。現代架構傾向 MGR (MySQL Group Replication) 或 InnoDB Cluster，提供自動故障轉移、一致性協議 (Paxos)、單主/多主模式，取代傳統雙主手動運維。