Innobackupex command to make full and incremental backups of MySQL database

Test environment:

Os:rhel5

Mysql:mysql-5.6.25-linux-glibc2.5-i686.tar.gz

xtrabackup:percona-xtrabackup-2.2.10-1.el5.i386.rpm

Here MySQL generic binary package installation steps omitted ~

The MySQL data directory is built on a logical volume/mydata/data

MySQL Data backup directory/backup

1. Installing Xtrabackup

#rpm-IVH percona-xtrabackup-2.2.10-1.el5.i386.rpm

2. Create the original data

#mysql

mysql> CREATE database student;

mysql> use student;

Mysql> CREATE TABLE User (ID tinyint primary key auto_increment not null,name char (+) NOT null default ');

mysql> desc User;

mysql> INSERT into user (name) values (' Luochen '), (' Redhat ');

Mysql> select * from user; # # #验证数据是否插入成功

3. Full backup

#innobackupex--user=root/backup # #这里没有设定root密码

#cd/backup/# # #查看是否有一个已当前时间生成的目录

PS: If you want to restore a full backup without incremental backup use the following command

#innobackupex--apply-log/backup/2015-09-24_10-27-04

#innobackupex--copy-back/backup/2015-09-24_10-27-04

4. Modify the data in the database table (modulo in real case)

#mysql

mysql> INSERT into user (name) values (' Zuowei '), (' Zhangsan ');

Mysql> select * from user;

5. Make the first incremental backup (on a full backup basis)

#innobackupex--incremental/backup--increment-basedir=/backup/2015-09-24_10-27-04

Ps:2015-09-24_10-27-04 The directory that is generated for a full backup

6. Modify the data in the table again

#mysql

mysql> use student;

Mysql> Delete from the user where ID in (2,3);

mysql> INSERT into user (name) values (' Endtime ');

Mysql> select * from user;

7. Make a second incremental backup

#innobackupex--incremental/backup--incremental-basedir=/backup/2015-09-24_10-28-43

Ps:2015-09-24_10-28-43 the directory generated for the first incremental backup

8. Die with data being destroyed

#service mysqld Stop

#rm-rf/mydata/data/*

9. Prepare to recover data from full and incremental backups

#innobackupex--apply-log--redo-only/backup/2015-09-24_10-27-04

#innobackupex--apply-log--redo-only/backup/2015-09-24_10-27-04--incremental-dir=/backup/2015-09-24_10-28-43

#innobackupex--apply-log--redo-only/backup/2015-09-24_10-27-04--incremental-dir=/backup/2015-09-24_10-30-37

10. Recover All data

#innobackupex--copy-back/backup/2015-09-24_10-27-04

11. Change the Data catalog file to the owning master group

#cd/mydata/data

#chown-R mysql.mysql./*

12. Verify that the data is restored successfully

#mysql

mysql> use student;

Mysql> select * from user;

Ps:

2015-09-24_10-27-04 # # #完全备份

2015-09-24_10-28-43 # # #第一次增量备份

2015-09-24_10-30-37 # # #第二次增量备份

This article is from "Luo Chen's blog" blog, please be sure to keep this source http://luochen2015.blog.51cto.com/9772274/1697750

Innobackupex command to make full and incremental backups of MySQL database