場景背景
- 架構：一主(192.168.18.212) 一從(192.168.18.213) → 新增從庫(192.168.18.214)
- 版本：MySQL 5.5，引擎：InnoDB
- 要求：業務不停機、低峰期操作
方案一：mysqldump 邏輯備份（適合中小數據量）
1. 主庫配置
# /etc/my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
auto-increment-increment = 1
auto-increment-offset = 1
slave-skip-errors = all
2. 創建同步賬號
GRANT ALL ON *.* TO 'sync'@'192.168.18.%' IDENTIFIED BY 'sync';
3. 從庫配置
server-id = 3
log-bin = mysql-bin
# 其餘同主庫
4. 熱備份主庫（關鍵參數）
mysqldump -uroot -p123 \
--routines \
--single-transaction \
--master-data=2 \
--databases weibo > weibo.sql
| 參數 | 作用 |
|---|
| --routines | 導出存儲過程/函數 |
| --single-transaction | InnoDB 熱備份，不鎖表 |
| --master-data=2 | 輸出 CHANGE MASTER 註釋行（含 binlog 位點） |
5. 模擬增量數據
-- 備份完成後，主庫新建表
CREATE TABLE test_tb (id INT, NAME VARCHAR(30));
6. 從庫恢復與同步
# 建庫導入
mysql -uroot -p123 -e 'CREATE DATABASE weibo;'
mysql -uroot -p123 weibo < weibo.sql
# 從備份文件提取位點
head -25 weibo.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
CHANGE MASTER TO
MASTER_HOST='192.168.18.212',
MASTER_USER='sync',
MASTER_PASSWORD='sync',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;
START SLAVE;
SHOW SLAVE STATUS\G;
-- 驗證：Slave_IO_Running=Yes, Slave_SQL_Running=Yes
7. 驗證同步
SHOW TABLES;
-- 應包含 test_tb（備份後主庫新增的表）
方案二：xtrabackup 物理備份（推薦大數據量）
1. 主庫物理備份
innobackupex --user=root --password=123 ./
# 生成目錄：2015-07-01_16-49-43/
2. 從庫部署備份文件
# 停止 MySQL
systemctl stop mysql
# 替換數據目錄
rm -rf /var/lib/mysql
mv 2015-07-01_16-49-43 /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
# 啟動
systemctl start mysql
3. 獲取精確位點
cat /var/lib/mysql/xtrabackup_info
# 關鍵行：
# binlog_pos = filename 'mysql-bin.000001', position 429
4. 配置同步並驗證
CHANGE MASTER TO
MASTER_HOST='192.168.18.212',
MASTER_USER='sync',
MASTER_PASSWORD='sync',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=429;
START SLAVE;
SHOW SLAVE STATUS\G;
5. 驗證增量同步
-- 主庫新建 test_tb2
CREATE TABLE test_tb2 (id INT, NAME VARCHAR(30));
-- 從庫查看
SHOW TABLES;
-- 應包含 test_tb 和 test_tb2
方案對比與選型指南
| 維度 | mysqldump | xtrabackup |
|---|
| 備份類型 | 邏輯（SQL） | 物理（文件拷貝） |
| 速度 | 慢（需解析/重建） | 快（流式拷貝） |
| 鎖表 | --single-transaction 無鎖 | 極短鎖定（僅刷新日志） |
| 還原速度 | 慢（重放 SQL） | 快（直接啟動） |
| 跨版本遷移 | 良好（SQL 標準） | 需版本兼容 |
| 適用場景 | < 100GB、異構遷移 | > 100GB、同版本擴容 |
核心避坑指南
- server-id 唯一：主/從/新從 必須各不相同
- binlog 格式：主從建議統一
ROW 模式
- 防火牆/安全組：3306 端口雙向通信
- 時區一致：
SET GLOBAL time_zone='+8:00';
- 驗證二進制日志：
SHOW MASTER LOGS; 確認文件存在
生產環境擴展從庫，**數據量 < 50GB 首選 mysqldump 靈活安全，> 100GB 必選 xtrabackup 極速部署**。兩套方案並行演練，關鍵時刻游刃有餘。
