一主一從,異步復制。主庫寫入 Binlog,從庫 IO 線程拉取 → Relay Log,SQL 線程回放。
| 角色 | IP | Server ID | 備註 |
|---|---|---|---|
| Master | 192.168.145.222 | 222 | 啟用 Binlog |
| Slave | 192.168.145.226 | 226 | 可選啟用 Binlog(做雙主或級聯) |
# /etc/my.cnf [mysqld] log-bin = mysql-bin # 必須:啟用二進制日志 server-id = 222 # 必須:唯一正整數,建議取 IP 末段 # 可選:binlog_format = ROW # 推薦 ROW 格式 # 可選:expire_logs_days = 7 # 自動清理舊日志
systemctl restart mysqld
# /etc/my.cnf [mysqld] log-bin = mysql-bin # 非必須,但建議開啟(便於後續升主) server-id = 226
systemctl restart mysqld
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'StrongPass#123'; -- 生產環境建議限制從庫 IP:'repl_user'@'192.168.145.226' mysql> FLUSH PRIVILEGES;
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,後續配置從庫同步起點。此後勿在主庫寫入新數據,防止位點漂移。
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;
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 表示實時同步。-- 主庫 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'
#!/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: No | SQL 執行錯誤(主鍵衝突、表結構差異、外鍵失敗) | SHOW SLAVE STATUS\G 看 Last_SQL_Error,修復數據後 STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;(慎用) |
| Seconds_Behind_Master 偏大 | 從庫硬件弱/大事務/長鎖/並行復制未開 | 開啟並行復制、優化慢 SQL、升級從庫配置 |