MySQL利用xtrabackup进行增量备份详细过程汇总

mysql -uroot --password="" -e"create user 'backup'@'192.168.%' identified by '123456'";

mysql -uroot --password="" -e"grant reload, lock tables, replication

client, create tablespace, superon *.* to 'backup'@'192.168.%'";

备份命令：

xtrabackup --defaults-file=/etc/my.cnf--user=backup

--password="123456" --port=3306

--backup--target-dir=/data/backups/mysql/full_incre_$(date +%y%m%d)

ps: 2>/tmp/fullbackup.log 将备份信息放入文件中。

备份过程信息如下：

xtrabackup --defaults-file=/etc/my.cnf--backup --user=backup

--target-dir=/data/backups/mysql/incre_20150320/--incremental-basedir=/data/backups/mysql/full_incre_201503192>/tmp/incre_backup_1.log

查看备份日志信息：

建立一张表t_incre_2：

--target-dir=/data/backups/mysql/incre_20150321/--incremental-basedir=/data/backups/mysql/incre_20150320

2>/tmp/incre_backup_2.log

备份信息记录如下：

备份命令如下：

xtrabackup --defaults-file=/etc/my.cnf--prepare --user=backup

--password="123456"

--apply-log-only--target-dir=/data/backups/mysql/full_incre_20150319

2>/tmp/full_restore.log

恢复命令：

--apply-log-only--target-dir=/data/backups/mysql/full_incre_20150319--incremental-dir=/data/backups/mysql/incre_20150320

2>/tmp/restore_incre_1.log

恢复过程：

--password="123456" --apply-log-only

--target-dir=/data/backups/mysql/full_incre_20150319--incremental-dir=/data/backups/mysql/incre_201503212>/tmp/restore_incre_2.log

--password="123456"--target-dir=/data/backups/mysql/full_incre_20150319

2>/tmp/restore_end_3.log

停止mysql服务：

service mysqld5612 stop

开始rsync数据文件：

cd /data/backups/mysql/full_incre_20150319

rsync -rvt --exclude'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' ./ /home/data/mysql/data

授予mysql访问权限：

chown -r mysql:mysql /home/data/mysql/data

启动mysql服务：

service mysqld5612 start

登录mysql，看到以前在备份之后删除的t4以及t_incre_2表的数据已经通过2次增量备份恢复过来了，如下所示：

命令如下：

innobackupex --defaults-file=/etc/my.cnf--user=backup

--host=192.168.52.129/data/backups/mysql/innobackupex_full_20150319/

--no-timestamp 2>/tmp/innobackupex_full.log

备份过程信息：

先录入增量数据

再进行增量备份，命令如下：

innobackupex

--incremental/data/backups/mysql/innobackex_incre_1--incremental-basedir=/data/backups/mysql/innobackupex_full_20150319/--user=backup

--password="123456" --host=192.168.52.129

--no-timestamp2>/tmp/innobackex_incre_1.log

先录入增量数据录入

开始进行第二次增量备份，备份命令：

innobackupex --incremental/data/backups/mysql/innobackex_incre_2

--incremental-basedir=/data/backups/mysql/innobackex_incre_1/--user=backup

--no-timestamp2>/tmp/innobackex_incre_2.log

innobackupex --user=backup--password="123456" --host=192.168.52.129

--apply-log/data/backups/mysql/innobackupex_full_20150319/2>/tmp/innobackex_restore_full.log

/data/backups/mysql/innobackupex_full_20150319/--incremental-dir=/data/backups/mysql/innobackex_incre_1

--apply-log2>/tmp/innobackex_restore_incre_1.log

/data/backups/mysql/innobackupex_full_20150319/--incremental-dir=/data/backups/mysql/innobackex_incre_2

--apply-log2>/tmp/innobackex_restore_incre_2.log

停止数据库

清空数据目录下所有文件

mkdir -p /tmp/mysqldatabak/

mv/home/data/mysql/data/* /tmp/mysqldatabak/

将恢复好的数据按照配置文件的需求拷贝到相应目录

--password="123456" --host=192.168.52.129--defaults-file=/etc/my.cnf

--copy-back/data/backups/mysql/innobackupex_full_20150319/

2>/tmp/innobackex_incre_restore_end.log

赋予mysql账号权限

启动mysql服务

ps：--copy-back过程如下：

登录mysql界面，查看表incre1和表incre2，数据已经恢复，如下所示：