Percona xtrabackup for full MySQL backup and incremental backup

########################################################################
# # # Install MySQL or mariadb local also installed MySQL or mariadb related packages # #
########################################################################
Slightly

#######################################
# # # # of Xtrabackup installation # # #
#######################################
[email protected] ~]# Yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/ PERCONA-RELEASE-0.1-3.NOARCH.RPM # Installing the specified Yum source
[[email protected] ~]# yum-y install percona-xtrabackup.x86_64 # installation software

#######################################
# # MySQL data for full backup server # # #
#######################################
1) Create a path to save backup data on the same day
[Email protected] ~]# mkdir-p/backup/mysql/full/2015-05-07
2) Full amount of Backup server data (not compressed)
[Email protected] full]#/usr/bin/innobackupex--user=root--password=123456789--host=localhost--port=3306-- defaults-file=/etc/my.cnf/backup/mysql/full/2015-05-07/
3) Modify config file, add [mysqld] option to prevent error in recovering data times
[Email protected] full]# VI/ETC/MY.CNF
[Mysqld]
Datadir= "/var/lib/mysql"
########################################
# # # Full backup Data Recovery # # #
########################################
1) Stop MySQL
[[Email protected] full]# service MySQL stop

2) Analog data loss
[Email protected] ~]# Mv/var/lib/mysql/var/lib/mysql_bak
Bye

3) restore with data from the above backup
To apply a log to a data file by--apply-log
[Email protected] full]#/usr/bin/innobackupex--user=root--password=123456789--defaults-file=/etc/my.cnf-- apply-log/backup/mysql/full/2015-05-07/2015-05-07_18-14-25/

3.1 If the database directory is empty, you can use the--copy-back option to perform a recovery operation. The restored directory must be empty because of the use of--copy-back recovery
[Email protected] full]# Mv/var/lib/mysql/var/lib/mysql_old
[Email protected] full]# Mkdir/var/lib/mysql
[Email protected] full]#/usr/bin/innobackupex--user=root--password=123456789--copy-back--defaults-file=/etc/ my.cnf/backup/mysql/full/2015-05-07/2015-05-07_18-14-25/
[[email protected] full]# chown-r Mysql:mysql/var/lib/mysql # Modify owner, owning group
[[Email protected] full]# service MySQL start # start MySQL
[[email protected] full]# mysql-u root-p # Login to see if data is restored
Enter Password:
MariaDB [(None)]> show databases; # Discovery data has been restored
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
| test_db |
+--------------------+
3.2 If the database directory is not empty, use the direct copy method to recover data (infrequently used)
Rm-f/var/lib/mysql/ibdata/ib* # If the InnoDB data file is placed in a separate directory, it needs to be deleted separately. I'm not putting it alone.
[[email protected] full]# rm-f/var/lib/mysql/ib* # Delete innodb data file # It's not necessary to perform this step if you are recovering the data sheet for the MyISAM engine
[[email protected] full]# rm-rf/var/lib/mysql/test_db # Delete the specified database test_db
# recovery
[Email protected] full]# cp-r/backup/mysql/full/2015-05-07/2015-05-07_18-14-25/test_db/var/lib/mysql/
[Email protected] full]# cp-r/backup/mysql/full/2015-05-07/2015-05-07_18-14-25/ib*/var/lib/mysql/# If the InnoDB data file is placed in a separate directory, it needs to be copied separately
[[email protected] full]# chown-r Mysql:mysql/var/lib/mysql # Modify owner, owning group
[[Email protected] full]# service MySQL start
[[email protected] full]# mysql-u root-p123456789 # Login Database View data has been restored
MariaDB [(None)]> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
| test_db |
+--------------------+
4 rows in Set (0.00 sec)

MariaDB [(None)]> use test_db;
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a

Database changed
MariaDB [test_db]> SELECT * from Test_table1;
+------+----------+
| ID | name |
+------+----------+
| 1 | Zhangsan |
| 2 | Lisi |
| 3 | Wangwu |
| 4 | Zhaoliu |
| 5 | Guqi |
+------+----------+
5 rows in Set (0.00 sec) # Data has been restored


##############################################
# # # Incremental Backup # #
##############################################
1) Open Binary log
[Email protected] ~]# VI/ETC/MY.CNF
[Mysqld]
Datadir= "/var/lib/mysql"
Log-bin=/data/mysql/log/mysql-bin
[[Email protected] ~]# service MySQL restart
2) Incremental backups are built on a full-scale backup, so make sure you have a full backup, a full-scale backup that has been introduced before

3) We add data to the database for the first incremental backup
MariaDB [test_db]> INSERT INTO test_table1 values (6, ' xiaoming ');
Query OK, 1 row Affected (0.00 sec)

MariaDB [test_db]> SELECT * from Test_table1;
+------+----------+
| ID | name |
+------+----------+
| 1 | Zhangsan |
| 2 | Lisi |
| 3 | Wangwu |
| 4 | Zhaoliu |
| 5 | Guqi |
| 6 | Xiaoming |
+------+----------+
6 rows in Set (0.00 sec)
[[email protected] ~]# mkdir-p/backup/mysql/increment/2015-05-07 # Create an incremental backup saved path

4) Make the first incremental backup
[Email protected] ~]#/usr/bin/innobackupex--user=root--password=123456789--defaults-file=/etc/my.cnf-- Incremental-force-scan--incremental/backup/mysql/increment/2015-05-07--incremental-basedir=/backup/mysql/full/ 2015-05-07/2015-05-07_18-14-25/# The first incremental backup (if MARIADB is not added--incremental-force-scan parameter may be because flush Changed_page_ is not supported Bitmaps and error result in incremental backup failure)
The first incremental backup generates a folder of/backup/mysql/increment/2015-05-07/2015-05-07_18-41-41/

5) Insert the contents into the data table again
MariaDB [test_db]> INSERT INTO test_table1 values (7, ' Xiaohong ');
Query OK, 1 row affected (0.05 sec)

6) Make a second incremental backup
[Email protected] ~]#/usr/bin/innobackupex--user=root--password=123456789--defaults-file=/etc/my.cnf-- Incremental-force-scan--incremental/backup/mysql/increment/2015-05-07--incremental-basedir=/backup/mysql/ increment/2015-05-07/2015-05-07_18-41-41/
The second incremental backup generates a folder of/backup/mysql/increment/2015-05-07/2015-05-07_18-48-32/

7) View the binary log and location for the last incremental backup
[Email protected] ~]# Cat/backup/mysql/increment/2015-05-07/2015-05-07_18-48-32/xtrabackup_binlog_info
mysql-bin.000001 833 0-1-3
MariaDB [(None)]> Show Master Status
;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000001 |              833 |                  | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec) # You can see that the database is not being incremented after an incremental backup. The information derived from the incremental backup is the same as seen from the binary log.


###################################################################
# # # Incremental backup Data restore # # #
###################################################################
Recovering incremental Backups
The restore operation for an incremental backup is a bit different from a full restore, and you must first use--apply-log--redo-only to operate on the full backup directory and all the incremental backup directories, and then you can restore the operation as if you were restoring the full backup. (Apply increment log to full data)
1 Analog data loss
[[Email protected] ~]# service MySQL stop
[Email protected] ~]# Rm-rf/var/lib/mysql
[Email protected] ~]# Mkdir/var/lib/mysql

2 using--apply-log--redo-only to indicate that the log is applied to the data file
[Roo[email protected] ~]#/usr/bin/innobackupex--user=root--password=123456789--defaults-file=/etc/my.cnf-- Apply-log--redo-only/backup/mysql/full/2015-05-07/2015-05-07_18-14-25/

3 Apply the data of the first incremental backup to a full-scale backup
[Email protected] ~]#/usr/bin/innobackupex--user=root--password=123456789--defaults-file=/etc/my.cnf--apply-log --redo-only/backup/mysql/full/2015-05-07/2015-05-07_18-14-25/--incremental-dir=/backup/mysql/increment/ 2015-05-07/2015-05-07_18-41-41/

4 Apply the data of the second incremental backup to the full-scale backup
[Email protected] ~]#/usr/bin/innobackupex--user=root--password=123456789--defaults-file=/etc/my.cnf--apply-log --redo-only/backup/mysql/full/2015-05-07/2015-05-07_18-14-25/--incremental-dir=/backup/mysql/increment/ 2015-05-07/2015-05-07_18-48-32/

5 Recover data (this is the same as the recovery of full-scale backups)
[Email protected] ~]#/usr/bin/innobackupex--user=root--password=123456789--defaults-file=/etc/my.cnf--copy-back /backup/mysql/full/2015-05-07/2015-05-07_18-14-25/

6 Change permissions, start the service, log in to MySQL to see if the data is restored
[Email protected] ~]# chown-r mysql:mysql/var/lib/mysql
[[Email protected] ~]# service MySQL start
MariaDB [test_db]> SELECT * from Test_table1;
+------+----------+
| ID | name |
+------+----------+
| 1 | Zhangsan |
| 2 | Lisi |
| 3 | Wangwu |
| 4 | Zhaoliu |
| 5 | Guqi |
| 6 | Xiaoming |
| 7 | Xiaohong |
+------+----------+
7 Rows in Set (0.00 sec) # Data has been restored

Percona xtrabackup for full MySQL backup and incremental backup