萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 主從復制搭建完整教程:配置、驗證與監控腳本

架構概覽

一主一從,異步復制。主庫寫入 Binlog,從庫 IO 線程拉取 → Relay Log,SQL 線程回放。

環境準備

角色IPServer ID備註
Master192.168.145.222222啟用 Binlog
Slave192.168.145.226226可選啟用 Binlog(做雙主或級聯)
  • 版本一致(建議同主版本)。
  • 初始化數據目錄、啟動服務、修改 root 密碼。
  • 關閉防火牆/安全組放行 3306。

步驟 1:主庫配置

# /etc/my.cnf
[mysqld]
log-bin = mysql-bin          # 必須:啟用二進制日志
server-id = 222              # 必須:唯一正整數,建議取 IP 末段
# 可選:binlog_format = ROW  # 推薦 ROW 格式
# 可選:expire_logs_days = 7 # 自動清理舊日志
systemctl restart mysqld

步驟 2:從庫配置

# /etc/my.cnf
[mysqld]
log-bin = mysql-bin          # 非必須,但建議開啟(便於後續升主)
server-id = 226
systemctl restart mysqld

步驟 3:主庫創建復制賬號並授權

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'StrongPass#123';
-- 生產環境建議限制從庫 IP:'repl_user'@'192.168.145.226'
mysql> FLUSH PRIVILEGES;

步驟 4:查看主庫 Binlog 位點

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      308 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

關鍵: 記錄 File 與 Position,後續配置從庫同步起點。此後勿在主庫寫入新數據,防止位點漂移。

步驟 5:從庫配置同步起點並啟動

mysql> CHANGE MASTER TO
    MASTER_HOST = '192.168.145.222',
    MASTER_USER = 'repl_user',
    MASTER_PASSWORD = 'StrongPass#123',
    MASTER_LOG_FILE = 'mysql-bin.000004',
    MASTER_LOG_POS = 308;

mysql> START SLAVE;

步驟 6:驗證復制狀態

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.145.222
                  Master_User: repl_user
                  Master_Port: 3306
            Master_Log_File: mysql-bin.000004
        Read_Master_Log_Pos: 600
             Relay_Log_File: slave-relay-bin.000003
              Relay_Log_Pos: 251
      Relay_Master_Log_File: mysql-bin.000004
           Slave_IO_Running: Yes   <-- 必須 Yes
          Slave_SQL_Running: Yes   <-- 必須 Yes
                Seconds_Behind_Master: 0
...
  • Slave_IO_Running + Slave_SQL_Running 均為 Yes 才算正常。
  • Seconds_Behind_Master 為延遲秒數,0 表示實時同步。

步驟 7:功能測試

-- 主庫
mysql> CREATE DATABASE test_repl;
mysql> USE test_repl;
mysql> CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(20));
mysql> INSERT INTO t1 VALUES (1, 'repl_test');

-- 從庫
mysql> SHOW DATABASES LIKE 'test_repl';  -- 可見
mysql> USE test_repl;
mysql> SELECT * FROM t1;  -- 可見 id=1, name='repl_test'

步驟 8:監控腳本(Nagios/Zabbix/Prometheus)

#!/bin/bash
# check_mysql_slave.sh
STATUS=$(mysql -urepl_monitor -p'MonPass' -h 192.168.145.226 -e "SHOW SLAVE STATUS\G" 2>/dev/null)
IO_RUN=$(echo "$STATUS" | grep "Slave_IO_Running:" | awk '{print $2}')
SQL_RUN=$(echo "$STATUS" | grep "Slave_SQL_Running:" | awk '{print $2}')
LAG=$(echo "$STATUS" | grep "Seconds_Behind_Master:" | awk '{print $2}')

if [[ "$IO_RUN" != "Yes" || "$SQL_RUN" != "Yes" ]]; then
    echo "CRITICAL: Slave IO/SQL not running (IO=$IO_RUN, SQL=$SQL_RUN)"
    exit 2
elif [[ "$LAG" -gt 300 ]]; then
    echo "WARNING: Slave lag $LAG seconds"
    exit 1
else
    echo "OK: Slave running, lag $LAG s"
    exit 0
fi

部署至監控系統,異常即時告警。

常見故障排查

現象可能原因處理
Slave_IO_Running: No網絡不通/賬號密碼錯/主庫 Binlog 被清理檢查連通性、重設 CHANGE MASTER TO 新位點、補全 Binlog
Slave_SQL_Running: NoSQL 執行錯誤(主鍵衝突、表結構差異、外鍵失敗)SHOW SLAVE STATUS\G 看 Last_SQL_Error,修復數據後 STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;(慎用)
Seconds_Behind_Master 偏大從庫硬件弱/大事務/長鎖/並行復制未開開啟並行復制、優化慢 SQL、升級從庫配置
copyright © 萬盛學電腦網 all rights reserved