Introduction to xtrabackup
As mentioned earlier, the backup method of mysqldump adopts logical backup. Its biggest defect is that the speed of backup and recovery is slow. For a database less than 50G, this speed is acceptable, but if the database is very large, it is not suitable to use mysqldump for backup.
At this time, an easy-to-use and efficient tool is needed. xtrabackup is one of them, which is known as the free version of InnoDB HotBackup.
Xtrabackup implements physical backup and physical hot backup
At present, two mainstream tools can realize physical hot backup: ibbackup and xtrabackup; Ibbackup is commercial software that requires authorization and is very expensive. Xtrabackup is more powerful than ibbackup, but it is open source. So let's introduce the use of xtrabackup.
Xtrabackup provides two command-line tools:
xtrabackup: dedicated to backing up the data of InnoDB and XtraDB engines;
innobackupex: This is a perl script. During execution, xtrabackup command will be called. This command can be used to back up InnoDB and MyISAM engine objects.
Xtrabackup is a mysql database backup tool provided by percona. It has the following features:
(1) The backup process is fast and reliable;
(2) The backup process will not interrupt the ongoing transaction;
(3) It can save disk space and traffic based on compression and other functions;
(4) Automatic backup inspection;
(5) Fast restore speed.
Official link address: http://www.percona.com/software/percona-xtrabackup ； You can download the source code, compile and install it, download the appropriate RPM package, install it using yum, or download the binary source code package.
xtrabackup installation
[root@localhost ~]# tar xf percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz [root@localhost ~]# cd percona-xtrabackup-2.4.4-Linux-x86_64 [root@localhost percona-xtrabackup-2.4.4-Linux-x86_64]# cp bin/* /usr/bin/ [root@localhost percona-xtrabackup-2.4.4-Linux-x86_64]# yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64 perl-Digest-MD5 [root@localhost ~]# rpm -ivh percona-toolkit-2.2.19-1.noarch.rpm warning: percona-toolkit-2.2.19-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY Preparing... ########################################### [100%] 1:percona-toolkit ########################################### [100%]
Source code compilation database
[roo[root@localhost ~]# yum -y install ncurses-devel [root@localhost ~]# tar xf cmake-2.8.6.tar.gz [root@localhost ~]# cd cmake-2.8.6 [root@localhost cmake-2.8.6]# ./configure && gmake && gmake install [root@localhost cmake-2.8.6]# cd [root@localhost ~]# groupadd mysql [root@localhost ~]# useradd -M -s /sbin/nologin mysql -g mysql [root@localhost ~]# tar xf mysql-5.5.22.tar.gz -C /usr/src/ [root@localhost ~]# cd /usr/src/mysql-5.5.22/ [root@localhost mysql-5.5.22]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all && make && make instal [root@localhost mysql-5.5.22]# chown -R mysql:mysql /usr/local/mysql [root@localhost mysql-5.5.22]# rm -rf /etc/my.cnf [root@localhost mysql-5.5.22]# cp support-files/my-medium.cnf /etc/my.cnf [root@localhost mysql-5.5.22]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data [root@localhost mysql-5.5.22]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile [root@localhost mysql-5.5.22]# . /etc/profile [root@localhost mysql-5.5.22]# cp /usr/src/mysql-5.5.22/support-files/mysql.server /etc/rc.d/init.d/mysqld [root@localhost mysql-5.5.22]# chmod +x /etc/rc.d/init.d/mysqld [root@localhost mysql-5.5.22]# chkconfig --add mysqld [root@localhost mysql-5.5.22]# vim /etc/my.cnf 27 datadir=/usr/local/mysql/data [root@localhost mysql-5.5.22]# service mysqld start Starting MySQL... [determine] [root@localhost mysql-5.5.22]# mysqladmin -uroot password '123456'
xtrabackup full backup + binlog incremental backup
1. Full backup
Basic syntax: innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP
Note: - defaults file = / etc / my CNF specifies the mysql configuration file my CFG, if specified, must be the first parameter.
/path/to/BACKUP-DIR / specifies the target directory where the backup is stored. During the backup process, a directory named after the backup time will be created to store the backup files.
Create a backup directory. Full is the full backup directory and inc is the incremental backup directory
[root@localhost ~]# mkdir -p /opt/mysqlbackup/{full,inc}
[root@localhost ~]# innobackupex --user=root --password=123456 /opt/mysqlbackup/full/
MySQL binlog position: filename 'mysql-bin.000004', position '107'
161203 21:03:23 [00] Writing backup-my.cnf
161203 21:03:23 [00] ...done
161203 21:03:23 [00] Writing xtrabackup_info
161203 21:03:23 [00] ...done
xtrabackup: Transaction log of lsn (1595675) to (1595675) was copied.
161203 21:03:24 completed OK!
[root@localhost ~]# ls /opt/mysqlbackup/full
2016-12-03_21-03-06
[root@localhost ~]# ls /opt/mysqlbackup/full/2016-12-03_21-03-06/
backup-my.cnf mysql xtrabackup_binlog_info xtrabackup_info
ibdata1 performance_schema xtrabackup_checkpoints xtrabackup_logfile
Description of each document:
(1)xtrabackup_checkpoints - Backup type (e.g. full or incremental), backup status (e.g. whether it is already prepared Status) and LSN(Log serial number)Scope information;
each InnoDB page(Usually 16 k size)Will contain a log serial number, i.e LSN. LSN It is the system version number of the whole database system, and each page is related LSN Can show how this page has changed recently.
(2)xtrabackup_binlog_info - mysql The binary log file currently in use by the server and the location of binary log events up to the moment of backup.
(3)xtrabackup_binlog_pos_innodb - Binary log file and its application InnoDB or XtraDB The current of the binary log file for the table position.
(4)xtrabackup_binary - Used in backup xtrabackup Executable documents;
(5)backup-my.cnf - Configuration option information used in the backup command;
in use innobackupex You can also use--no-timestamp Option to prevent the command from automatically creating a directory named after time; In this way, innobackupex The command will create a BACKUP-DIR Directory to store backup data
Note: description of relevant options:
Among them,--user Specify the user name to connect to the database,--password Specify the password to connect to the database,--defaults-file Specify the configuration file of the database, innobackupex To get from it datadir And other information;--database Specify the database to be backed up. The database specified here is only for MyISAM Table valid for InnoDB The data is all ready (in all databases) InnoDB The data has been backed up, not only the specified database, but also during recovery);/opt/mysqlbackup/full Is the storage location of backup files.
Note: the user who backs up the database needs to have corresponding permissions. If you want to use a user with minimum permissions for backup, you can create such a user based on the following command:
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create user 'bkpuser'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.06 sec)
mysql> revoke all privileges,grant option from 'bkpuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> grant reload,lock tables,replication client, process on *.* to 'bkpuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.05 sec)
2. Incremental backup
Add a benet library to mysql, then insert data for incremental backup, and perform binary log backup for database changes after full backup.
View log location
[root@localhost ~]# cat /opt/mysqlbackup/full/2016-12-03_21-03-06/xtrabackup_binlog_info mysql-bin.000004 107 [root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.22-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database benet; Query OK, 1 row affected (0.00 sec) mysql> use benet Database changed mysql> create table tb1 (id int,name char(16) not null); Query OK, 0 rows affected (0.05 sec) mysql> insert into tb1 values(1,'zhangsan'); Query OK, 1 row affected (0.02 sec) mysql> insert into tb1 values(2,'lisi'); Query OK, 1 row affected (0.02 sec) mysql> select * from tb1; +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | | 2 | lisi | +------+----------+ 2 rows in set (0.00 sec) mysql> quit Bye
Incremental backup binaries
[root@localhost ~]# mysqlbinlog --start-position=107 /usr/local/mysql/data/mysql-bin.000004 > /opt/mysqlbackup/inc/`date +%F`.sql [root@localhost ~]# ls /opt/mysqlbackup/inc/ 2016-12-03.sql
Simulation database corruption
[root@localhost ~]# mv /usr/local/mysql/data/* /tmp/ Generally, after the backup is completed, the data can not be used for recovery operation, because the backed up data may contain uncommitted transactions or transactions that have been committed but have not been synchronized to the data file. Therefore, the data file is still in an inconsistent state at this time. The main function of "preparation" is to roll back uncommitted transactions and synchronize committed transactions to the data file, so as to make the data file in a consistent state. In preparation( prepare)After the process, InnoDB The table data has been rolled forward to the point where the entire backup ends, rather than rolled back to xtrabackup At the beginning. innobakupex Imperative--apply-log Options can be used to achieve the above functions. As follows: [root@localhost ~]# innobackupex --apply-log /opt/mysqlbackup/full/2016-12-03_21-03-06/ ......... InnoDB: File './ibtmp1' size is now 12 MB. InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active. InnoDB: 32 non-redo rollback segment(s) are active. InnoDB: 5.7.13 started; log sequence number 1595925 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1595944 161203 21:33:32 completed OK! [root@localhost ~]# innobackupex --copy-back /opt/mysqlbackup/full/2016-12-03_21-03-06/ ........... 161203 21:36:07 [01] ...done 161203 21:36:07 [01] Copying ./performance_schema/events_waits_current.frm to /usr/local/mysql/data/performance_schema/events_waits_current.frm 161203 21:36:07 [01] ...done 161203 21:36:07 completed OK! [root@localhost ~]# ll /usr/local/mysql/data/ Total consumption 40972 -rw-r-----. 1 root root 18874368 12 March 21:36 ibdata1 -rw-r-----. 1 root root 5242880 12 March 21:36 ib_logfile0 -rw-r-----. 1 root root 5242880 12 March 21:36 ib_logfile1 -rw-r-----. 1 root root 12582912 12 March 21:36 ibtmp1 drwxr-x---. 2 root root 4096 12 March 21:36 mysql drwxr-x---. 2 root root 4096 12 March 21:36 performance_schema -rw-r-----. 1 root root 478 12 March 21:36 xtrabackup_info [root@localhost ~]# chown -R mysql:mysql /usr/local/mysql/data/ [root@localhost ~]# killall mysqld [root@localhost ~]# service mysqld start Starting MySQL.. [determine] [root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.22-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.01 sec) We can see that the data created after the full backup has not been restored, so we need to restore it with incremental backup In order to avoid generating a large number of binary logs, you can turn off the binary logs mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> source /opt/mysqlbackup/inc/2016-12-03.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> set sql_log_bin=1; Query OK, 0 rows affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | benet | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) mysql> use benet Database changed mysql> select * from tb1; +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | | 2 | lisi | +------+----------+ 2 rows in set (0.01 sec)
xtrabackup full backup + xtrabacup incremental backup
In the previous incremental backup, we still use the old method: backing up binary logs. In fact, xtrabackup also supports incremental backup.
First introduce the backup principle of xtrabackup
A redo log file is maintained in InnoDB, which can also be called transaction log. The transaction log will store the modification records of each InnoDB table data. When InnoDB starts, InnoDB will check the data file and transaction log, and perform two steps: it applies the committed transaction log to the data file, and rolls back the modified but uncommitted data.
Xtrabackup will remember log sequence number (LSN) when it starts and copy all data files. The replication process takes some time, so if the data file is changed during this period, the database will be at a different point in time. At this point, xtrabackup runs a background process to monitor the transaction log and copy the latest changes from the transaction log. Xtrabackup must continue to do this because the transaction log will be written repeatedly, and the transaction log can be reused. Therefore, xtrabackup keeps recording the changes of each data file in the transaction log since it was started. This is the backup process of xtrabackup
Therefore, each InnoDB page will contain an LSN information. Whenever the relevant data changes, the LSN of the relevant page will increase automatically. This is the basis for incremental backup of InnoDB tables.
xtraBackup is based on the crash recovery function of InnoDB. It will copy InnoDB's data file. Because the table is not locked, the copied data is inconsistent. Crash recovery is used during recovery to make the data recovery consistent.
When InnoDB starts, it will first check the data file and transaction log, and will do two steps:
1.It applies committed transaction logentries to the data files
2.it performs an undo operation on anytransactions that modified data but did not commit.
Therefore, in the preparation process, XtraBackup uses the copied transactions log to crash recover the backed up innodb data file.
Test environment preparation
mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test; Database changed mysql> create table xx(id int,name varchar(20)); Query OK, 0 rows affected (0.04 sec) mysql> insert into xx values(1,'tom1'); Query OK, 1 row affected (0.02 sec) mysql> insert into xx values(2,'tom2'); Query OK, 1 row affected (0.03 sec) mysql> quit Bye
Perform a full backup
[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --user=root --password='123456' --port=3306 --backup --target-dir=/opt/mysqlbackup/full/full_incre_$(date +%Y%m%d_%H%M%s) .......... 161203 22:01:55 [00] Writing backup-my.cnf 161203 22:01:55 [00] ...done 161203 22:01:55 [00] Writing xtrabackup_info 161203 22:01:55 [00] ...done xtrabackup: Transaction log of lsn (1603986) to (1603986) was copied. 161203 22:01:55 completed OK! [root@localhost ~]# ls -l /opt/mysqlbackup/full/ Total consumption 8 drwxr-x---. 4 root root 4096 12 March 21:33 2016-12-03_21-03-06 drwxr-x---. 6 root root 4096 12 March 22:01 full_incre_20161203_22011480773711
Continue to add data to the database for the first incremental backup
[root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.22-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test Database changed mysql> insert into xx values(3,'tom3'); Query OK, 1 row affected (0.03 sec) mysql> quit Bye [root@localhost ~]# xtrabackup --dafaults-file=/etc/my.cnf --user=root --password="123456" --port=3306 --backup --target-dir=/opt/mysqlbackup/inc/incre_$(date +%Y%m%d_%H%M%S) --incremental-basedir=/opt/mysqlbackup/full/full_incre_20161203_22011480773711/ ........ 161203 22:09:54 [00] Writing xtrabackup_info 161203 22:09:54 [00] ...done xtrabackup: Transaction log of lsn (1605014) to (1605014) was copied. 161203 22:09:54 completed OK! [root@localhost ~]# ls -l /opt/mysqlbackup/inc/ Total consumption 8 -rw-r--r--. 1 root root 3041 12 March 21:24 2016-12-03.sql drwxr-x---. 6 root root 4096 12 March 22:09 incre_20161203_220950 Note: incremental backup here is only for Innodb，and MyISAM Still a full backup Add more data to the table for the second incremental backup [root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.5.22-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test Database changed mysql> insert into xx values(4,'tom4'); Query OK, 1 row affected (0.03 sec) mysql> quit Bye [root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --user=root --password="123456" --port=3306 --backup --target-dir=/opt/mysqlbackup/inc/incre_$(date +%Y%m%d_%H%M%S) --incremental-basedir=/opt/mysqlbackup/inc/incre_20161203_220950/ ......... 161203 22:18:34 [00] ...done 161203 22:18:34 [00] Writing xtrabackup_info 161203 22:18:34 [00] ...done xtrabackup: Transaction log of lsn (1606042) to (1606042) was copied. 161203 22:18:34 completed OK! be careful:Second incremental backup--incremental--basedir Refers to the location of the last incremental backup file [root@localhost ~]# ls -l /opt/mysqlbackup/inc/ Total consumption 12 -rw-r--r--. 1 root root 3041 12 March 21:24 2016-12-03.sql drwxr-x---. 6 root root 4096 12 March 22:09 incre_20161203_220950 drwxr-x---. 6 root root 4096 12 March 22:18 incre_20161203_221830
Xtrabackup for incremental recovery
To verify the comparison, delete the data in the table before two incremental backups
[root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.5.22-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test; Database changed mysql> delete from xx where id=3; Query OK, 1 row affected (0.03 sec) mysql> quit Bye [root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=root --password="123456" --apply-log-only --target-dir=/opt/mysqlbackup/full/full_incre_20161203_22011480773711/ [root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=root --password="123456" --apply-log-only --target-dir=/opt/mysqlbackup/full/full_incre_20161203_22011480773711/ --incremental-dir=/opt/mysqlbackup/inc/incre_20161203_220950/ [root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=root --password="123456" --apply-log-only --target-dir=/opt/mysqlbackup/full/full_incre_20161203_22011480773711/ --incremental-dir=/opt/mysqlbackup/inc/incre_20161203_221830/ [root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=root --password="123456" --target-dir=/opt/mysqlbackup/full/full_incre_20161203_22011480773711/ [root@localhost ~]# service mysqld stop Shutting down MySQL.. [determine] [root@localhost ~]# cd /opt/mysqlbackup/full/full_incre_20161203_22011480773711/ [root@localhost full_incre_20161203_22011480773711]# rsync -rvt --exclude 'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' ./ /usr/local/mysql/data/ [root@localhost ~]# chown -R mysql:mysql /usr/local/mysql/data/ [root@localhost ~]# service mysqld start Starting MySQL... [determine] [root@localhost ~]# mysql -uroot -p123456 -e "select * from test.xx" +------+------+ | id | name | +------+------+ | 1 | tom1 | | 2 | tom2 | | 3 | tom3 | | 4 | tom4 | +------+------+
Innobackupex full database backup + innobackupex incremental backup
Test environment preparation
[root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.22-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test2; Query OK, 1 row affected (0.01 sec) mysql> use test2 Database changed mysql> create table yy(id int,name varchar(20)); Query OK, 0 rows affected (0.05 sec) mysql> insert into yy values(1,'kim1'); Query OK, 1 row affected (0.03 sec) mysql> insert into yy values(2,'kim2'); Query OK, 1 row affected (0.02 sec) mysql> quit Bye
Do a full backup first
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password="123456" /opt/mysqlbackup/full/fulll_incre_$(date +%Y%m%d_%H%M%S) --no-timestamp ........ 161203 22:57:11 [00] Writing xtrabackup_info 161203 22:57:11 [00] ...done xtrabackup: Transaction log of lsn (1609292) to (1609292) was copied. 161203 22:57:12 completed OK!
Next, do the first incremental backup and insert data into the database first
mysql> use test2; Database changed mysql> insert into yy values(3,'kim3'); Query OK, 1 row affected (0.02 sec) [root@localhost ~]# innobackupex --incremental /opt/mysqlbackup/inc/incree_$(date +%Y%m%d_%H%M%S) --incremental-basedir=/opt/mysqlbackup/full/fulll_incre_20161203_225708/ --user=root --password="123456" --no-timestamp
Make the second incremental backup based on the full backup and the first incremental backup
mysql> use test2; Database changed mysql> insert into yy values(4,'kim4'); Query OK, 1 row affected (0.02 sec) [root@localhost ~]# innobackupex --incremental /opt/mysqlbackup/inc/increee_$(date +%Y%m%d_%H%M%S) --incremental-basedir=/opt/mysqlbackup/inc/incree_20161203_230236/ --user=root --password="123456" --no-timestamp
Delete incremental data for incremental recovery
mysql> use test2; Database changed mysql> delete from yy where id=3; Query OK, 1 row affected (0.03 sec) [root@localhost ~]# innobackupex --apply-log --redo-only /opt/mysqlbackup/full/fulll_incre_20161203_225708/ [root@localhost ~]# innobackupex --apply-log --redo-only /opt/mysqlbackup/full/fulll_incre_20161203_225708/ --incremental-dir=/opt/mysqlbackup/inc/incree_20161203_230236/ [root@localhost ~]# innobackupex --apply-log --redo-only /opt/mysqlbackup/full/fulll_incre_20161203_225708/ --incremental-dir=/opt/mysqlbackup/inc/increee_20161203_230810/ [root@localhost ~]# service mysqld stop Shutting down MySQL. [determine] [root@localhost ~]# mkdir /tmp/mysqlbak [root@localhost ~]# mv /usr/local/mysql/data/* /tmp/mysqlbak/ [root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password="123456" --copy-back /opt/mysqlbackup/full/fulll_incre_20161203_225708/
Start the database and check whether the data is recovered