環境:CentOs5.4,mysql5.1.48,percona xtrabackup/innobackupex 1.6.5
目前DB中隻有一個DB/test和一張table:對其備份和恢複測試:
實體備份工具之xtrabckup(version:1.6.5)
備份過程:
1.全量備份(All DB)
./xtrabackup --defaults-file=/opt/mysql/my.cnf --backup --target-dir=/opt/xtrabackup_full
2.2次增量備份
./xtrabackup --defaults-file=/opt/mysql/my.cnf --backup --target-dir=/opt/xtrabackup_incr1 --incremental-base=/opt/xtrabackup_full
./xtrabackup --defaults-file=/opt/mysql/my.cnf --backup --target-dir=/opt/xtrabackup_incr2 --incremental-base=/opt/xtrabackup_incr1
3.備份*.frm,ib_logfile*,binlog
cp -rp /path/*.frm ib_logfile* binlog /data/log_files
恢複過程:
備份目錄依次如下:
/opt/xtrabackup_full
/opt/xtrabackup_incr1
/opt/xtrabackup_incr2
1.service mysqld stop
2.rm -f /path/test/t.ibd #删除test庫下t.idb表資料檔案
3.copy t.frm 到 /path/test/下
4.prepare備份集
4.1,./xtrackup --defaults-file=/opt/mysql/my.cnf --apply-log-only --prepare --target-dir=/opt/xtrabackup_full #
加選項:--apply-log-only作用是:隻應用redo log,不對資料的rollback,起到先合并事務日志#
4.2,./xtrbackup --defaults-file=/opt/mysql/my.cnf --apply-log-only --prepare --target-dir=/opt/xtrabckup_full in-cremental-dir=/opt/xtrabackup_incr1 #增量備份incr1檔案事務日志應用且合并到full backup檔案中#
4.3,./xtrabackup --defaults-file=/opt/mysql/my.cnf --prepare --target-dir=/opt/xtrabackup_full incremental-dir=/opt/
xtrabackup_incr2 #最後一個增量備份incr2事務日志應用且合并到full backup檔案,同時rollback操作,保證資料完整和一緻#
5.copy /xtrabackup_full/test/t.ibd 到 資料庫/test/ 目錄下
6.chown -R mysql.mysql /test/t.ibd
7.service mysqld start
8.dblink->use test;驗證恢複資料;
innobackupex備份/恢複過程:
備份:
1.全量備份
./innobackupex --host=192.168.0.66 --port=3306 --user=root --password='123456' /opt/innobackup_full/allDB/ --defaults-file=/opt/mysql/my.cnf [--databases=test] --no-timestamp
其中/opt/innobackup_full是存在目錄,allDB是備份中指令自己建立,可變名稱;
2.增量備份
./innobackupex --host=192.168.0.66 --port=3306 --user=root --password='123456' /opt/innobackup_full/incr1/ --defaults-file=/opt/mysql/my.cnf --incremental --incremental-basedir=/opt/innobackup_full/allDB/ [--databases=test] --no-timestamp
./innobackupex --host=192.168.0.66 --port=3306 --user=root --password='123456' /opt/innobackup_full/incr2/ --defaults-file=/opt/mysql/my.cnf --incremental --incremental-basedir=/opt/innobackup_full/incr1/ [--databases=test] --no-timestamp
恢複:
備份檔案目錄依次如下:
/opt/innobackup_full/allDB/
/opt/innobackup_full/incr1/
/opt/innobackup_full/incr2/
恢複開啟前,
1.The datadir must be empty;
Percona XtraBackup innobackupex --copy-back option will not copy over existing files.
2.Also it’s important to note that MySQL server needs to be shut down before restore is performed.
You can’t restore to a datadir of a running mysqld instance (except when importing a partial backup).
3.開始恢複prepare:
--apply-log,--redo-only 作用:merging all incrementals into full backup files except the last one
./innobackupex --host=192.168.0.66 --port=3306 --user=root --password='123456' --apply-log --redo-only /opt/innobackup_full/allDB/ --defaults-file=/opt/mysql/my.cnf [--databases=test]
./innobackupex --host=192.168.0.66 --port=3306 --user=root --password='123456' --apply-log --redo-only /opt/innobackup_full/allDB/ --defaults-file=/opt/mysql/my.cnf --databases=test --incremental-dir=/opt/innobackup_full/incr1/
./innobackupex --host=192.168.0.66 --port=3306 --user=root --password='123456' --apply-log [--redo-only] /opt/innobackup_full/allDB/ --defaults-file=/opt/mysql/my.cnf --databases=test --incremental-dir=/opt/innobackup_full/incr2/
./innobackupex --host=192.168.0.66 --port=3306 --user=root --password='123456' --apply-log /opt/innobackup_full/allDB/ --defaults-file=/opt/mysql/my.cnf [--databases=test]
4.copy回備份資料庫
Restoring Incremental Backups with innobackupex
./innobackupex --host=192.168.0.66 --port=3306 --user=root --password='123456' --copy-back /opt/innobackup_full/allDB/
這一步,完全可以通過OS級别的cp,rsync替換--copy-back自動copy檔案;
5.grant mysql privileges;
chown -R mysql:mysql /path/data
注意1:
The datadir must be empty;
Percona XtraBackup innobackupex --copy-back option will not copy over existing files.
Also it’s important to note that MySQL server needs to be shut down before restore is performed.
You can’t restore to a datadir of a running mysqld instance (except when importing a partial backup).
注意2:
Note --redo-only should be used when merging all incrementals except the last one
--copy-back可能會出現的問題:
Original data directory is not empty! at /xtrabackup21/xtrabackup-1.6.5/bin/innobackupex line 538.