Xtrabackup&Innobackupex备份和恢复

环境: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.