mysql5.6 using Xtrabackup full standby and incremental backup

1.xtrabackup Installation

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/B3/wKioL1Wl8x7DJM-GAAE5jG_ZhOQ428.jpg "title=" 1.png " alt= "Wkiol1wl8x7djm-gaae5jg_zhoq428.jpg"/>

2. Full Standby and recovery

(1) Fully prepared

View Database

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6F/B3/wKioL1Wl84mx9d74AAIB-jOdUq0536.jpg "title=" 111111.png "alt=" Wkiol1wl84mx9d74aaib-joduq0536.jpg "/>

[Email protected] opt]# Innobackupex--defaults-file=/etc/my.cnf--user=root--password=123456/bak/mysql/

The statement copies the data file (specified by the variable DataDir in the MY.CNF) to the backup directory (/bak/mysql/), note: If you do not specify--defaults-file, the default value is/ETC/MY.CNF.

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

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/B3/wKioL1Wl8y7SvLYqAACmcgZ_ULQ942.jpg "title=" 2.png " alt= "Wkiol1wl8y7svlyqaacmcgz_ulq942.jpg"/>

(2) Recovery

Delete Database test123, try to recover

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/B6/wKiom1Wl8V6SmyAsAADORLkMdjY006.jpg "title=" 3.png " alt= "Wkiom1wl8v6smyasaadorlkmdjy006.jpg"/>

Before recovering, close the database and delete the data files and log files.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/B3/wKioL1Wl80OQJVL6AAB-SB40w8o427.jpg "style=" float: none; "title=" 4.png "alt=" Wkiol1wl80oqjvl6aab-sb40w8o427.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/B6/wKiom1Wl8WqA3Xc_AACUUkYv2TM009.jpg "style=" float: none; "title=" 5.png "alt=" Wkiom1wl8wqa3xc_aacuukyv2tm009.jpg "/>

[Email protected] opt]# Innobackupex--defaults-file=/etc/my.cnf--user=root--password=123456--apply-log/bak/mysql /2015-07-08_18-52-43/

[Email protected] opt]# Innobackupex--defaults-file=/etc/my.cnf--user=root--password=123456--copy-back/bak/mysql /2015-07-08_18-52-43/

Recovery is divided into two steps:

The 1th step is apply-log, in order to speed up, it is generally recommended to set--use-memory, after this step is completed, the backup file under directory/bak/mysql/2015-07-08_18-52-43/is ready.

The 2nd step is Copy-back, which is to copy the backup files to the original data directory. When the recovery is complete, be sure to check the data Catalog for the correct owner and permissions.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/B6/wKiom1Wl8XyjgkXjAADnTEmKT0k497.jpg "style=" float: none; "title=" 6.png "alt=" Wkiom1wl8xyjgkxjaadntemkt0k497.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/B3/wKioL1Wl81axInYgAACruTPVzNU191.jpg "style=" float: none; "title=" 7.png "alt=" Wkiol1wl81axinygaacrutpvznu191.jpg "/>

3. Incremental backup and Recovery

Note: Innobackupex incremental backups are only for innodb such a support transaction engine, and for engines such as MyISAM, they are still fully-prepared.

(1) Incremental backup

Incremental backups need to be based on full provisioning, we already have a full standby (/bak/mysql/2015-07-08_18-52-43/), and we need to do incremental backups on this fully-prepared basis.

Test the new library, import new data, and make incremental backups.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/B6/wKiom1Wl8evC_m9FAACvnqlXuqg068.jpg "style=" float: none; "title=" 9.png "alt=" Wkiom1wl8evc_m9faacvnqlxuqg068.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/B6/wKiom1Wl8evS8eSKAAC0qv0B26A606.jpg "style=" float: none; "title=" 10.png "alt=" Wkiom1wl8evs8eskaac0qv0b26a606.jpg "/>

[Email protected] 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://s3.51cto.com/wyfs02/M02/6F/B3/wKioL1Wl89uxZjleAABYqxRRGi8411.jpg "title=" 11.png "alt=" Wkiol1wl89uxzjleaabyqxrrgi8411.jpg "/>

Where--incremental-basedir points to the fully-prepared directory,--incremental the directory that points to the incremental backup.

After the execution of the above statement succeeds, a timestamp subdirectory (in this case:/bak/mysql/2015-07-08_19-46-30) is created under the directory executed by--incremental, where all the files for the incremental backup are stored.

In the backup directory, there is a file xtrabackup_checkpoints records the backup information, the full information is as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/B6/wKiom1Wl8g_SIMqCAACseLV8Ox0437.jpg "title=" 12.png "alt=" Wkiom1wl8g_simqcaacselv8ox0437.jpg "/>

Information based on this fully-prepared incremental backup is as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/B3/wKioL1Wl8_KjAj1vAADQ_W92QGU297.jpg "title=" 13.png "alt=" Wkiol1wl8_kjaj1vaadq_w92qgu297.jpg "/>

As can be seen from the above, the FROM_LSN of an incremental backup is exactly equal to the fully prepared TO_LSN.

So, can we do incremental backups on the basis of incremental backups? The answer is yes, just take the--incremental-basedir to perform the last incremental backup of the directory, as follows:

[Email protected] 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://s3.51cto.com/wyfs02/M00/6F/B6/wKiom1Wl8iLAaZSNAABxDrxZu6o385.jpg "title=" 14.png "alt=" Wkiom1wl8ilaazsnaabxdrxzu6o385.jpg "/>

Its xtrabackup_checkpoints records the backup information as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/B3/wKioL1Wl9ATDhqlBAAFdcnuwZWA232.jpg "title=" 15.png "alt=" Wkiol1wl9atdhqlbaafdcnuwzwa232.jpg "/>

As you can see, the FROM_LSN of this incremental backup starts with the TO_LSN of the last incremental backup.

(2) Recovery

Delete Database test456, try to recover

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/B3/wKioL1Wl9A2hf6-_AAGld3rtD3c819.jpg "title=" 16.png "alt=" Wkiol1wl9a2hf6-_aagld3rtd3c819.jpg "/>

Before recovering, close the database and delete the data files and log files.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/B6/wKiom1Wl8kSylz_xAAB-SB40w8o654.jpg "style=" float: none; "title=" 4.png "alt=" Wkiom1wl8ksylz_xaab-sb40w8o654.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/B3/wKioL1Wl9B2jcg_9AACUUkYv2TM881.jpg "style=" float: none; "title=" 5.png "alt=" Wkiol1wl9b2jcg_9aacuukyv2tm881.jpg "/>

The recovery of an incremental backup is much more complex than full provisioning, and the first step is to redo the committed log under all backup directories, such as:

[Email protected] mysql]# Innobackupex--defaults-file=/etc/my.cnf--user=root--password=123456--apply-log-- redo-only/bak/mysql/2015-07-08_18-52-43/

[Email protected] 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/

[Email protected] 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/

Where/bak/mysql/2015-07-08_18-52-43/refers to a fully-prepared directory,/bak/mysql/2015-07-08_19-46-30/is the first incremental backup,/bak/mysql/2015-07-08_ 21-01-19/refers to the second incremental backup, and so on.

After the execution of the above statement succeeds, the final data is in the fully-prepared directory.

When the first step is complete, we begin the second step: Roll back the unfinished log:

[Email protected] mysql]# Innobackupex--defaults-file=/etc/my.cnf--user=root--password=123456--apply-log/bak/mys ql/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 final step is to copy:

[Email protected] mysql]# Innobackupex--defaults-file=/etc/my.cnf--user=root--password=123456--copy-back/bak/ mysql/2015-07-08_18-52-43/

Modify directory permissions to start MySQL.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/B3/wKioL1Wl9C7xF2LPAAEM0FD8_eo215.jpg "title=" 17.png "alt=" Wkiol1wl9c7xf2lpaaem0fd8_eo215.jpg "/>

Modify directory permissions to start MySQL.

Recovery complete, such as

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/B6/wKiom1Wl8l7Bgh7iAADWtnSS1fk302.jpg "title=" 18.png "alt=" Wkiom1wl8l7bgh7iaadwtnss1fk302.jpg "/>

This article is from the "Linux art (q Group: 1991706)" blog, please be sure to keep this source http://304076020.blog.51cto.com/7503470/1674913

mysql5.6 using Xtrabackup full standby and incremental backup