主從複制作用
- 高可用：Master 故障可提升 Slave 為新 Master。
- 讀寫分離：Slave 承擔查詢流量，減輕 Master 壓力。
- 熱備份：XtraBackup 不鎖表（極短鎖表）備份 Master，不影響業務。
環境規劃
| 角色 | IP | MySQL 版本 | 備註 |
|---|
| Master | 192.168.1.2 | 5.5.3+ | 寫入 |
| Slave | 192.168.1.3 | 5.5.3+ | 讀取/備份 |
1. Master 配置（/etc/my.cnf）
[mysqld]
server-id = 1
log-bin = /data/mysql/binlog/binlog
binlog-do-db = blog
binlog-ignore-db = mysql
binlog_format = MIXED
expire_logs_days = 30
max_binlog_size = 20M
# InnoDB 關鍵參數
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
2. 建立複制帳號
GRANT REPLICATION SLAVE ON *.* TO 'admin'@'192.168.1.3' IDENTIFIED BY '12345678';
FLUSH PRIVILEGES;
3. Master 熱備份並傳輸到 Slave（XtraBackup）
# Master 執行
innobackupex --user=root --password='xxx' --stream=tar ./ | ssh 192.168.1.3 "mkdir -p /tmp/db && tar xfi - -C /tmp/db/"
# 產生 xtrabackup_binlog_info 記錄 binlog 位置
4. Slave 恢復備份
# Slave 執行
innobackupex --apply-log /tmp/db
innobackupex --copy-back /tmp/db
chown -R mysql:mysql /var/lib/mysql/
systemctl start mysqld
5. Slave 配置（/etc/my.cnf）
[mysqld]
server-id = 2
log-bin = /data/mysql/binlog/binlog
relay-log = /data/mysql/relaylog/relaylog
relay-log-index = /data/mysql/relaylog/relaylog
relay-log-info-file = /data/mysql/relaylog/relaylog
replicate-do-db = blog
replicate-ignore-db = mysql
log-slave-updates = 1
slave-skip-errors = 1032,1062,126,1114,1146,1048,1396
read-only = 1
6. 啟動複制（MySQL 5.5+ GTID 前身：手動指定位置）
# 查看 Master 狀態
SHOW MASTER STATUS;
-- File: binlog.000005, Position: 592
# Slave 配置
CHANGE MASTER TO
MASTER_HOST='192.168.1.2',
MASTER_USER='admin',
MASTER_PASSWORD='12345678',
MASTER_LOG_FILE='binlog.000005',
MASTER_LOG_POS=592;
START SLAVE;
7. 驗證
SHOW SLAVE STATUS\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
8. 常見錯誤與解決
| 錯誤 | 原因 | 解決 |
|---|
| ERROR 2002: Can't connect to local MySQL server through socket | mysqld 未啟動 | systemctl start mysqld |
| Last_SQL_Error / Last_IO_Error | Binlog 位置不對 | 重新 CHANGE MASTER TO 正確位置 |
| Slave I/O: error connecting to master ... Error_code: 2003 | 防火牆阻擋 3306 | iptables -I INPUT -p tcp --dport 3306 -j ACCEPT |
| ERROR 2013: Lost connection during query | DNS 反解慢 | my.cnf 加 skip-name-resolve |
| unknown variable 'master-host' | MySQL 5.5+ 棄用配置檔方式 | 改用 CHANGE MASTER TO 命令 |
9. 監控腳本
#!/bin/bash
IO=$(mysql -N -e "SHOW SLAVE STATUS" | awk '{print $11}')
SQL=$(mysql -N -e "SHOW SLAVE STATUS" | awk '{print $12}')
LAG=$(mysql -N -e "SHOW SLAVE STATUS" | awk '{print $33}')
if [ "$IO" = "Yes" ] && [ "$SQL" = "Yes" ] && [ "$LAG" -lt 30 ]; then
echo "OK"
else
echo "CRITICAL: IO=$IO SQL=$SQL LAG=$LAG"
exit 1
fi
XtraBackup + 手動 CHANGE MASTER TO 是 MySQL 5.5/5.6 時代最成熟的主從搭建方案，生產經過大規模驗證。
