CentOS 7 MySQL 5.7 主從同步部署全流程：從配置到驗證、故障切換、運維腳本

環境規劃

Master 端部署

1. 創建復制賬號

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.33' IDENTIFIED BY 'repl_pass';
mysql> FLUSH PRIVILEGES;

安全建議：限制 Slave IP，生產環境強制 SSL：REQUIRE SSL

2. 配置 /etc/my.cnf

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
expire_logs_days = 7
max_binlog_size = 100M
# 可選：只同步特定庫
# binlog-do-db = business_db

關鍵：binlog_format=ROW 避免非確定性函數導致主從不一致；開啟 GTID 為後續故障切換鋪路。

3. 啟動並鎖表備份

# 啟動
mysqld_safe --user=mysql &

# 鎖表（阻塞寫入）
mysql> FLUSH TABLES WITH READ LOCK;

# 記錄 Binlog 位點
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      154 |              |                  |
+------------------+----------+--------------+------------------+

# 另開終端備份
$ mysqldump --single-transaction --master-data=2 --all-databases > /backup/master_full_$(date +%F).sql

# 解鎖
mysql> UNLOCK TABLES;

Slave 端部署

1. 配置 /etc/my.cnf

[mysqld]
server-id = 2
log-bin = mysql-bin
relay_log = mysql-relay-bin
log-slave-updates = 1
read_only = 1
gtid_mode = ON
enforce_gtid_consistency = ON
# replicate-do-db = business_db

2. 導入備份數據

$ mysql -u root -p < /backup/master_full_2024-01-15.sql

3. 配置同步並啟動

mysql> CHANGE MASTER TO
    MASTER_HOST='192.168.1.22',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_pass',
    MASTER_AUTO_POSITION = 1;  -- GTID 模式，無需手動指定 File/Pos

mysql> START SLAVE;

驗證同步狀態

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.22
Master_User: repl
Master_Port: 3306
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
...

成功標誌：Slave_IO_Running: Yes + Slave_SQL_Running: Yes + Seconds_Behind_Master: 0

功能測試

-- Master 執行
mysql> USE test; CREATE TABLE t_sync (id INT PRIMARY KEY, val VARCHAR(20));
mysql> INSERT INTO t_sync VALUES (1, 'from_master');

-- Slave 驗證
mysql> USE test; SELECT * FROM t_sync;
+----+--------------+
| id | val          |
+----+--------------+
| 1  | from_master  |
+----+--------------+

常見問題速查

運維進階：手動同步校驗與切換

低峰期手動同步（修復漂移）

-- Master
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;  -- 記錄 File, Pos

-- Slave
SELECT MASTER_POS_WAIT('mysql-bin.000001', 154);  -- 等待追趕

-- Master
UNLOCK TABLES;

主從切換（Master 故障時提升 Slave 為 Master）

1. 確保 Slave 已應用所有 Relay Log：
   STOP SLAVE IO_THREAD;
SHOW PROCESSLIST; 等待 State: Has read all relay log
2. Slave 端：
   STOP SLAVE;
RESET MASTER;（清空本地 Binlog，重新開始）
3. 刪除 master.info、relay-log.info 防止重啟後仍以 Slave 身份啟動
4. 應用層切換寫入地址到新 Master
5. 原 Master 恢復後：重新配置為 Slave 指向新 Master

自動化巡檢腳本

#!/bin/bash
# mysql_slave_monitor.sh
HOST=192.168.1.33
USER=monitor
PASS=xxx

STATUS=$(mysql -h$HOST -u$USER -p$PASS -e "SHOW SLAVE STATUS\G")
IO=$(echo "$STATUS" | grep Slave_IO_Running | awk '{print $2}')
SQL=$(echo "$STATUS" | grep Slave_SQL_Running | awk '{print $2}')
LAG=$(echo "$STATUS" | grep Seconds_Behind_Master | awk '{print $2}')

if [ "$IO" != "Yes" ] || [ "$SQL" != "Yes" ]; then
    echo "CRITICAL: Slave threads down!" | mail -s "MySQL Slave Alert" admin@company.com
    exit 2
elif [ "$LAG" -gt 300 ]; then
    echo "WARNING: Slave lag ${LAG}s" | mail -s "MySQL Slave Lag" admin@company.com
    exit 1
else
    echo "OK: Slave healthy, lag ${LAG}s"
    exit 0
fi

總結

CentOS 下 MySQL 主從部署核心路徑：
GTID 模式 + ROW 格式 + server-id 唯一 + 專用賬號 + 自動化監控。
將「部署文檔化、變更腳本化、故障演練化」納入運維體系，才能在故障發生時從容應對。