MySQL 主從擴容實戰：mysqldump 與 xtrabackup 兩種不停機添加從庫方案

場景描述

生產環境一主一從（MySQL 5.5, InnoDB），業務增長需新增一台從庫（192.168.18.214），要求不重啟主庫服務，選擇低峰期操作。

方案一：mysqldump 邏輯備份（適合中小數據量）

1. 主庫配置

[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. 主庫備份（含 binlog 位置）

mysqldump -uroot -p123 --routines --single-transaction --master-data=2 --databases weibo > weibo.sql

關鍵參數：--single-transaction 保證一致性快照，--master-data=2 將 CHANGE MASTER 註釋寫入文件。

5. 模擬新增數據驗證同步

-- 主庫建表
CREATE TABLE test_tb(id INT, name VARCHAR(30));

6. 從庫導入並啟動同步

mysql -uroot -p123 -e 'CREATE DATABASE weibo;'
mysql -uroot -p123 weibo < weibo.sql
# 查看 binlog 位置
head -25 weibo.sql
# 配置同步
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。

方案二：xtrabackup 物理備份（推薦，大數據量首選）

1. 主庫物理備份

innobackupex --user=root --password=123 ./

生成目錄如 2015-07-01_16-49-43，含 xtrabackup_binlog_info 記錄 binlog 位置。

2. 從庫恢復

# 停止服務、替換數據目錄
sudo rm -rf /var/lib/mysql/
sudo mv 2015-07-01_16-49-43/ /var/lib/mysql
sudo chown mysql.mysql -R /var/lib/mysql
sudo /etc/init.d/mysql start

3. 獲取 binlog 位置並啟動同步

cat /var/lib/mysql/xtrabackup_info
# 關鍵行：binlog_pos = filename 'mysql-bin.000001', position 429
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;

核心差異對比

總結

小數據量用 mysqldump 簡單直觀；大數據量、高可用要求下，xtrabackup 物理備份+增量同步是生產環境標準做法。