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

MySQL 雙主熱備架構搭建詳解：從二進制安裝到雙向復制驗證的完整運維手冊

編輯：MySQL 綜合教學

項目背景

記錄 RHEL 6.4 + MySQL 5.6.26 雙主熱備生產級部署全過程，作為運維團隊標準作業程序 (SOP) 存檔。

第 0 步：前置條件核對

兩台服務器時間同步 (ntpdate/chrony)。
主機名解析 (/etc/hosts 互寫 IP/hostname)。
防火牆開放 3306 或全關 (iptables -F)。
SELinux setenforce 0 + /etc/selinux/config disabled。
磁盤空間、IOPS 評估 (RAID10 + BBU / NVMe)。

第 1 步：二進制安裝標準化 (雙機同步)

# 解壓
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
chkconfig mysqld on
# 環境變量
echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile && source /etc/profile
# 啟動
service mysqld start
# 設置 root 密碼
mysqladmin -uroot password '123456'
# 驗證端口
ss -tunlp | grep 3306

第 2 步：雙主專用 my.cnf 差異化配置

Node A (192.168.131.129) - /etc/my.cnf

[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
user=mysql
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
# 性能優化建議
innodb_buffer_pool_size=4G
innodb_log_file_size=512M
innodb_flush_log_at_trx_commit=1
sync_binlog=1
max_connections=1000
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Node B (192.168.131.130) - /etc/my.cnf

[mysqld]
... (基礎項同 Node A)
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
innodb_buffer_pool_size=4G
innodb_log_file_size=512M
innodb_flush_log_at_trx_commit=1
sync_binlog=1
max_connections=1000
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

重啟生效：service mysqld restart (雙機)。

第 3 步：數據初始同步 (冷備份 + 鎖表)

Node A：全局鎖 + 導出

mysql -uroot -p123456
mysql> FLUSH TABLES WITH READ LOCK;
# 保持會話不斷開，新開終端
mysqldump -uroot -p123456 -B test --master-data=2 --single-transaction --routines --triggers > /root/test_full.sql
# 記錄 SHOW MASTER STATUS 輸出的 File/Position (備用)
mysql> UNLOCK TABLES;

--master-data=2：導出文件含 CHANGE MASTER TO 註釋，便於從庫定位。

傳輸並導入 Node B

scp /root/test_full.sql 192.168.131.130:/root/
# Node B
mysql -uroot -p123456 < /root/test_full.sql

第 4 步：創建專用復制賬號 (雙向)

Node A

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

Node B

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.131.129' IDENTIFIED BY 'Repl@2023';
mysql> FLUSH PRIVILEGES;

第 5 步：建立雙向復制鏈路

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

# Node B 查看主庫位點
mysql -uroot -p123456 -h 192.168.131.130 -e "SHOW MASTER STATUS;"
# 假設 File=mysql-bin.000002, Pos=420

mysql -uroot -p123456 -h 192.168.131.129
mysql> CHANGE MASTER TO
    MASTER_HOST='192.168.131.130',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='Repl@2023',
    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 查看主庫位點
mysql -uroot -p123456 -h 192.168.131.129 -e "SHOW MASTER STATUS;"
# 假設 File=mysql-bin.000002, Pos=689

mysql -uroot -p123456 -h 192.168.131.130
mysql> CHANGE MASTER TO
    MASTER_HOST='192.168.131.129',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='Repl@2023',
    MASTER_LOG_FILE='mysql-bin.000002',
    MASTER_LOG_POS=689;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

第 6 步：雙寫驗證測試

# Node A
mysql> USE test;
mysql> CREATE TABLE t_a (id INT AUTO_INCREMENT PRIMARY KEY, val VARCHAR(20));
mysql> INSERT INTO t_a (val) VALUES ('A-1'), ('A-2');

# Node B
mysql> USE test;
mysql> SHOW TABLES; -- 見 t_a
mysql> SELECT * FROM t_a; -- 見 A-1(id=1), A-2(id=3) 奇數
mysql> CREATE TABLE t_b (id INT AUTO_INCREMENT PRIMARY KEY, val VARCHAR(20));
mysql> INSERT INTO t_b (val) VALUES ('B-1'), ('B-2');

# Node A
mysql> SHOW TABLES; -- 見 t_a, t_b
mysql> SELECT * FROM t_b; -- 見 B-1(id=2), B-2(id=4) 偶數
mysql> INSERT INTO t_a (val) VALUES ('A-3'); -- id=5
mysql> INSERT INTO t_b (val) VALUES ('A-on-B'); -- id=6 (在 Node B 創建的表上插入，自增由 Node B 控制產生偶數)

驗證點：表結構同步、數據雙向同步、自增 ID 奇偶分離無衝突。

第 7 步：運維監控 SOP

每分鐘巡檢：SHOW SLAVE STATUS\G 關注 Slave_IO_Running, Slave_SQL_Running, Seconds_Behind_Master。
每日一致性校驗：pt-table-checksum --databases=test --recurse=1 --recursion-method=hosts。
Binlog 定期備份：mysqlbinlog --read-from-remote-server --host=... --raw --stop-never 遠程備份 Binlog。
DDL 變更協議：指定 Node A 為 DDL 執行節點，Node B 只同步執行，嚴禁雙邊並發 DDL。
故障切換演練：季度演練 STOP SLAVE、提升從庫為主、應用切換連接串、重建雙主鏈路。