一、安装xtrabackup
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/debian/trusty/x86_64/percona-xtrabackup-24_2.4.12-1.trusty_amd64.deb
dpkg -i percona-xtrabackup-24_2.4.12-1.trusty_amd64.deb
二、全量备份
innobackupex --user=root --password=vagrant --no-timestamp `date +%Y%m%d/%H%M%S`
三、全备恢复
1、关闭数据库
service mysql stop
2、准备备份数据(--use-memory 使用该参数加快速度)
innobackupex --apply-log --user=root --password=vagrant /vagrant/packages/back/20181127/000000
3、 将备份还原到 my.cnf 指定的datadir中,可用--defaults-file指定my.cnf文件位置
innobackupex --defaults-file=/etc/my.cnf --user=root --password=vagrant --copy-back /vagrant/packages/back/20181127/000000
4、修改数据目录权限
chown -R mysql:mysql ./
5、启动数据库
service mysql start
四、增量备份
1、首先进行一次全量备份
innobackupex --user=root --password=vagrant --no-timestamp `date +%Y%m%d/%H%M%S`
2、进行增量备份,需指定全量备份目录
innobackupex --user=root --password=vagrant --no-timestamp --incremental-basedir=/vagrant/packages/back/20181127/000000 --incremental /vagrant/packages/back/20181127/000000.01
# --incremental-basedir=/data/backup_db/test_incre 第一次增备指定全备路径 # --incremental 参数说明这是一次增备,并指定增备路径
3、再次进行增量备份
innobackupex --user=root --password=vagrant --no-timestamp --incremental-basedir=/vagrant/packages/back/20181127/000000.01 --incremental /vagrant/packages/back/20181127/000000.02
4、第二次增备是在第一次增备的基础上,所以 --incremental-basedir 指定的是第一次增备目录,最后指定的第二次增备目录
cat /vagrant/packages/back/20181127/000000/xtrabackup_checkpoints
backup_type = full-prepared from_lsn = 0 to_lsn = 144300637 last_lsn = 144300646 compact = 0 recover_binlog_info = 0
cat /vagrant/packages/back/20181127/000000.01/xtrabackup_checkpoints
backup_type = incremental from_lsn = 144300637 to_lsn = 144301531 last_lsn = 144301540 compact = 0 recover_binlog_info = 0
cat /vagrant/packages/back/20181127/000000.02/xtrabackup_checkpoints
backup_type = incremental from_lsn = 144301531 to_lsn = 144302712 last_lsn = 144302721 compact = 0 recover_binlog_info = 0
五、增备恢复
1、关闭数据库
service mysql stop
2、准备备份数据,--redo-only 对已提交的事务进行前滚,仅最后一次增备恢复不需要添加该参数
innobackupex --apply-log --redo-only /vagrant/packages/back/20181127/000000
3、指定增量备份目录
innobackupex --apply-log --redo-only /vagrant/packages/back/20181127/000000 --incremental-dir=/vagrant/packages/back/20181127/000000.01
4、指定最后一次增量备份目录
innobackupex --apply-log /vagrant/packages/back/20181127/000000 --incremental-dir=/vagrant/packages/back/20181127/000000.02
5、复制整合后的全备文件
innobackupex --copy-back /vagrant/packages/back/20181127/000000
6、修改数据目录权限
chown -R mysql.mysql ./
7、启动数据库
service mysql start