Mysql5.6 uses xtrabackup for full backup and Incremental Backup

Bytes

1. xtrabackup install 650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z5504108-0.jpg "title =" 1.png" alt = "wKioL1Wl8x7DJM-GAAE5jG_ZhOQ428.jpg"/> 2. full backup and recovery (1) Full backup to view database 650) this. width = 650; "src =" http: // www.6

1. Install xtrabackup

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z5504108-0.jpg "title =" 1.png" alt = "wKioL1Wl8x7DJM-GAAE5jG_ZhOQ428.jpg"/>

2. Full backup and recovery

(1) Full backup

View Database

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z5501522-1.jpg "title =" 111111.png" alt = "wKioL1Wl84mx9d74AAIB-jOdUq0536.jpg"/>

[Root @ local-db opt] # innobackupex -- defaults-file =/etc/my. cnf -- user = root -- password = 123456/bak/mysql/

This statement will copy the data file (by my. specify the variable datadir in cnf) to the backup directory (/bak/mysql/). Note: If -- defaults-file is not specified, the default value is/etc/my. cnf.

After the backup is successful, a timestamp directory will be created under the Backup Directory (the directory created in this example is/bak/mysql/2015-07-08_18-52-43), where the backup files will be stored.

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z55052B-2.jpg "title =" 2.png" alt = "wKioL1Wl8y7SvLYqAACmcgZ_ULQ942.jpg"/>

(2) Recovery

Delete database test123 and try to restore

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z5503R5-3.jpg "title =" 3.png" alt = "wKiom1Wl8V6SmyAsAADORLkMdjY006.jpg"/>

Before restoration, you must close the database and delete the data files and log files.

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z5506414-4.jpg "style =" float: none; "title =" 4.png" alt = "wKioL1Wl80OQJVL6AAB-SB40w8o427.jpg"/>

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z5502V9-5.jpg "style =" float: none; "title =" 5.png" alt = "wKiom1Wl8WqA3Xc_AACUUkYv2TM009.jpg"/>

[Root @ local-db opt] # innobackupex -- defaults-file =/etc/my. cnf -- user = root -- password = 123456 -- apply-log/bak/mysql/2015-07-08_18-52-43/

[Root @ local-db opt] # innobackupex -- defaults-file =/etc/my. cnf -- user = root -- password = 123456 -- copy-back/bak/mysql/2015-07-08_18-52-43/

There are two steps for recovery:

The first step is apply-log. to speed up, we recommend that you set -- use-memory. After this step is completed, the backup files under the/bak/mysql/2015-07-08_18-52-43/directory are ready.

Step 2 is copy-back, that is, copy the backup file to the original data directory. After the data is restored, check whether the owner and permissions of the Data Directory are correct.

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z5505924-6.jpg "style =" float: none; "title =" 6.png" alt = "wKiom1Wl8XyjgkXjAADnTEmKT0k497.jpg"/>

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z5505G4-7.jpg "style =" float: none; "title =" 7.png" alt = "wKioL1Wl81axInYgAACruTPVzNU191.jpg"/>

3. Incremental backup and recovery

Note: innobackupex Incremental backup is only for engines that support transactions such as InnoDB. For engines such as MyISAM, it is still full backup.

(1) Incremental Backup

Incremental Backup must be based on full backup. We already have a full backup (/bak/mysql/2015-07-08_18-52-43/). We need to perform Incremental Backup Based on this full backup.

Test creating a database, importing new data, and performing Incremental backup.

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z5503I3-8.jpg "style =" float: none; "title =" 9.png" alt = "wkiom1wl8evc_m9faacvnqlxuq1_68.jpg"/>

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z550A49-9.jpg "style =" float: none; "title =" 10.png" alt = "wKiom1Wl8evS8eSKAAC0qv0B26A606.jpg"/>

[Root @ local-db mysql] # innobackupex -- defaults-file =/etc/my. cnf -- user = root -- password = 123456 -- incremental-basedir =/bak/mysql/2015-07-08_18-52-43/-- incremental/bak/mysql/

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z5501G3-10.jpg "title =" 11.png" alt = "wKioL1Wl89uxZjleAABYqxRRGi8411.jpg"/>

Here, -- incremental-basedir points to the full backup directory, and -- incremental points to the incremental Backup Directory.

After the preceding statement is successfully executed, a time stamp sub-directory (/bak/mysql/2015-07-08_19-46-30 in this example) will be created under the directory where -- incremental is executed ), all Incremental backup files are stored in this directory.

In the backup directory, the xtrabackup_checkpoints file records the backup information. The full backup information is as follows:

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z550OI-11.jpg "title =" 12.png" alt = "wKiom1Wl8g_SIMqCAACseLV8Ox0437.jpg"/>

The Incremental backup information based on the full backup is as follows:

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z5502B9-12.jpg "title =" 13.png" alt = "wKioL1Wl8_KjAj1vAADQ_W92QGU297.jpg"/>

As shown above, the Incremental Backup from_lsn is exactly the same as the full backup to_lsn.

So can we perform Incremental backup on the basis of Incremental backup? The answer is yes. Just execute the directory of -- incremental-basedir For The Last incremental backup, as shown below:

[Root @ local-db mysql] # innobackupex -- defaults-file =/etc/my. cnf -- user = root -- password = 123456 -- incremental-basedir =/bak/mysql/2015-07-08_19-46-30/-- incremental/bak/mysql/

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z5504916-13.jpg "title =" 14.png" alt = "wkiom1wl8ilaazsnabxdrxzu6o385.jpg"/>

Its xtrabackup_checkpoints records the following backup information:

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z5503158-14.jpg "title =" 15.png" alt = "wKioL1Wl9ATDhqlBAAFdcnuwZWA232.jpg"/>

We can see that the from_lsn of the Incremental Backup starts from the to_lsn of the previous Incremental backup.

(2) Recovery

Delete database test456 and try to restore

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z5506446-15.jpg "title =" 16.png" alt = "wKioL1Wl9A2hf6-_AAGld3rtD3c819.jpg"/>

Before restoration, you must close the database and delete the data files and log files.

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z55060V-16.jpg "style =" float: none; "title =" 4.png" alt = "wKiom1Wl8kSylz_xAAB-SB40w8o654.jpg"/>

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z550N16-17.jpg "style =" float: none; "title =" 5.png" alt = "wkiol1wl9b2jcg_9aacukyv2tm881.jpg"/>

Incremental Backup recovery is much more complex than full backup. The first step is to redo the submitted logs in all backup directories, such:

[Root @ local-db mysql] # innobackupex -- defaults-file =/etc/my. cnf -- user = root -- password = 123456 -- apply-log -- redo-only/bak/mysql/2015-07-08_18-52-43/

[Root @ local-db mysql] # innobackupex -- defaults-file =/etc/my. cnf -- user = root -- password = 123456 -- apply-log -- redo-only/bak/mysql/2015-07-08_18-52-43/-- incremental-dir =/bak/mysql/2015-07-08_19-46-30/

[Root @ local-db mysql] # innobackupex -- defaults-file =/etc/my. cnf -- user = root -- password = 123456 -- apply-log -- redo-only/bak/mysql/2015-07-08_18-52-43/-- incremental-dir =/bak/mysql/2015-07-08_21-01-19/

/Bak/mysql/2015-07-08_18-52-43/indicates the full backup directory,/bak/mysql/2015-07-08_19-46-30/indicates the first Incremental backup, /bak/mysql/2015-07-08_21-01-19/indicates the second Incremental backup, and so on.

After the preceding statement is successfully executed, the final data is in the full backup directory.

After step 1 is completed, we start Step 2: Roll Back unfinished logs:

[Root @ local-db mysql] # innobackupex -- defaults-file =/etc/my. cnf -- user = root -- password = 123456 -- apply-log/bak/mysql/2015-07-08_18-52-43/

After the above execution, the backup file in/bak/mysql/2015-07-08_18-52-43/is fully ready, and the last step is to copy:

[Root @ local-db mysql] # innobackupex -- defaults-file =/etc/my. cnf -- user = root -- password = 123456 -- copy-back/bak/mysql/2015-07-08_18-52-43/

Modify directory permissions and start mysql.

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z550L21-18.jpg "title =" 17.png" alt = "wKioL1Wl9C7xF2LPAAEM0FD8_eo215.jpg"/>

Modify directory permissions and start mysql.

Recovery completed, as shown in figure

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151119/0Z5504015-19.jpg "title =" 18.png" alt = "wKiom1Wl8l7Bgh7iAADWtnSS1fk302.jpg"/>