傳統 mysqldump 建立從庫需全局鎖表(FLUSH TABLES WITH READ LOCK),大數據量下業務長時間不可寫,不可接受。Percona XtraBackup 支援 **InnoDB 熱備份(不鎖表、不停庫)**,是生產環境搭建主從、克隆實例的標準工具。本文以 MariaDB 10.0 / CentOS 6.6 為例,記錄完整實施流程。
| 角色 | IP | Server ID | 數據目錄 |
|---|---|---|---|
| Master | 172.16.6.100 | 1 | /data/mariadb |
| Slave | 172.16.6.101 | 2 | /data/mariadb |
關鍵配置(雙節點一致,僅 server-id 不同):
[mysqld]
log_bin = mysql-bin
binlog_format = mixed
server-id = 1 # Slave 改為 2
gtid_mode = OFF # 本例用傳統位點複製
mysql -uroot -plookback -e "SHOW GLOBAL VARIABLES LIKE 'log_bin';"
# 兩節點均應返回 ON
wget https://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
yum install percona-release-0.1-3.noarch.rpm
yum install percona-xtrabackup-21 percona-xtrabackup-21-debuginfo percona-xtrabackup-test-21 -y

mkdir -p /data/backup/innobackupex
innobackupex --user=root --password=lookback --defaults-file=/etc/my.cnf /data/backup/innobackupex
輸出關鍵信息:
MySQL binlog position: filename 'mysql-bin.000003', position 3025154
completed OK!

innobackupex --apply-log /data/backup/innobackupex/2015-07-10_22-57-51
# 輸出 completed OK! 表示備份集一致、可直接還原
CREATE USER 'legion'@'172.16.%.%' IDENTIFIED BY 'legionpasswd';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'legion'@'172.16.%.%';
mysql -ulegion -plegionpasswd -h172.16.6.100 -e 'SHOW DATABASES;'
# 能看到 information_schema 即網絡/權限正常
# Master 推送
scp -r /data/backup/innobackupex/2015-07-10_22-57-51 172.16.6.101:/data/backup
# Slave 端操作
service mysqld stop
cp -a /data/mariadb /data/mariadb-$(date +%F)-backup # 保留原數據目錄
mv /data/backup/2015-07-10_22-57-51 /data/mariadb
chown -R mysql.mysql /data/mariadb/*
service mysqld start

# 獲取 Master 備份時刻 Binlog 位點
ssh root@172.16.6.100 "cat /data/backup/innobackupex/2015*/xtrabackup_binlog_info"
# 輸出:mysql-bin.000003 3025154
mysql -uroot -plookback
CHANGE MASTER TO
MASTER_HOST='172.16.6.100',
MASTER_USER='legion',
MASTER_PASSWORD='legionpasswd',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=3025154;
START SLAVE;
SHOW SLAVE STATUS\G
# 關鍵指標:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 3025501 # 已追上主庫最新位點

FLUSH TABLES WITH READ LOCK 秒級),InnoDB 數據文件拷貝全程無鎖,依賴 Redo Log 保證一致性。REPLICATION SLAVE/CLIENT,避免高權限洩露。CHANGE MASTER TO MASTER_AUTO_POSITION=1 替代文件/位點。Permission denied → chown -R mysql.mysqlserver-id 衝突 → 雙節點必須不同mixed 或 ROW