| 項目 | Master | Slave |
|---|---|---|
| IP | 192.168.0.1 | 192.168.0.2 |
| MySQL 版本 | 5.1.34 | |
| Server ID | 1 | 2 |
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = business_db
binlog-ignore-db = mysql,test
log-slave-updates = 1
slave-skip-errors = 1062,1032 # 謹慎使用[mysqld]
server-id = 2
log-bin = mysql-bin
master-host = 192.168.0.1
master-user = slave_user
master-password = slave_pass
master-port = 3306
replicate-do-db = business_db
replicate-ignore-db = mysql,test
master-connect-retry = 60
log-slave-updates = 1
slave-skip-errors = 1062,1032GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'192.168.0.2' IDENTIFIED BY 'slave_pass';FLUSH TABLES WITH READ LOCK;SHOW MASTER STATUS; 記錄 File, Positiontar czf /backup/master.tar.gz /var/lib/mysqlUNLOCK TABLES;scp 192.168.0.1:/backup/master.tar.gz .tar xzf master.tar.gz -C /var/lib/mysqlchown -R mysql:mysql /var/lib/mysqlSTOP SLAVE;CHANGE MASTER TO MASTER_HOST='192.168.0.1', MASTER_USER='slave_user', MASTER_PASSWORD='slave_pass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;START SLAVE;SHOW SLAVE STATUS\G 確認 Slave_IO_Running: Yes、Slave_SQL_Running: Yes| 分類 | 命令 | 用途 |
|---|---|---|
| Master 端 | SHOW MASTER STATUS | 查看當前 Binlog 文件/位點 |
| SHOW SLAVE HOSTS | 查看連接的 Slave 列表 | |
| SHOW BINLOG EVENTS | 解析 Binlog 內容 | |
| PURGE LOGS TO 'file' | 清理指定文件前的 Binlog | |
| RESET MASTER | 清空所有 Binlog（謹慎） | |
| SET SQL_LOG_BIN=0/1 | 動態關閉/開啟 Binlog 記錄 | |
| Slave 端 | START SLAVE | 啟動同步 |
| STOP SLAVE | 停止同步 | |
| START SLAVE IO_THREAD | 單獨啟動 IO 線程 | |
| STOP SLAVE SQL_THREAD | 單獨停止 SQL 線程 | |
| RESET SLAVE | 清空同步位點、重建 master.info | |
| SET GLOBAL SQL_SLAVE_SKIP_COUNTER=N | 跳過 N 個錯誤事件 | |
| CHANGE MASTER TO ... | 動態修改同步源配置 | |
| SHOW SLAVE STATUS | 查看同步狀態（核心巡檢） | |
| LOAD TABLE FROM MASTER | 重新同步單表（需 RELOAD/SUPER 權限） | |
| LOAD DATA FROM MASTER | 全量重新同步（慎用，鎖表久） |
Sending binlog event to slave：正常發送Has sent all binlog to slave; waiting for binlog to be updated：空閒等待新寫入Waiting for master to send event：正常等待Connecting to master：連接中Waiting to reconnect after failed binlog dump：重試中Reading event from the relay log：解析執行中Has read all relay log; waiting for slave I/O thread to update it：追上進度，等待新事件master.info、relay-log.infomaster.info、relay-log.infoCHANGE MASTER TO → START SLAVESTOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
-- 觀察 Seconds_Behind_Master 歸零[mysqld]
skip-name-resolve = 1 # 跳過 DNS 解析，加速連接
max_connections = 1000 # 提高最大連接數 (默認 100)
max_connect_errors = 1000 # 防止誤封禁 (默認 10)
slave_net_timeout = 60 # Slave 判定 Master 超時時間
relay_log_space_limit = 2G # 限制 Relay Log 總大小#!/bin/bash
# slave_monitor.sh
HOST=192.168.0.2
USER=monitor
PASS=xxx
STATUS=$(mysql -h$HOST -u$USER -p$PASS -e "SHOW SLAVE STATUS\G")
IO=$(echo "$STATUS" | awk '/Slave_IO_Running:/ {print $2}')
SQL=$(echo "$STATUS" | awk '/Slave_SQL_Running:/ {print $2}')
LAG=$(echo "$STATUS" | awk '/Seconds_Behind_Master:/ {print $2}')
if [ "$IO" != "Yes" ] || [ "$SQL" != "Yes" ]; then
echo "CRITICAL: Slave thread down!" | mail -s "MySQL Slave Alert" dba@company.com
elif [ -n "$LAG" ] && [ "$LAG" -gt 300 ]; then
echo "WARNING: Slave lag ${LAG}s" | mail -s "MySQL Slave Lag" dba@company.com
else
echo "OK: Slave healthy"
fi⚠️ 啟動 Slave 前必須刪除舊 master.info/relay-log.info，否則 my.cnf 修改不生效！
⚠️ binlog-do-db 建議在 Master 不設置，在 Slave 用 replicate-do-db 過濾，避免跨庫操作不同步。
⚠️ slave-skip-errors 僅限明確知道錯誤原因且業務允許不一致時使用，濫用會掩蓋真實問題。
MySQL 主從同步的穩定性來自於：標準化部署 SOP、全方位監控覆蓋、故障處理預案、定期一致性校驗。將上述命令、腳本、參數固化為團隊運維手冊，新人也能從容應對突發故障。
