MySQL xtrabackup Backup

Remark: Install xtrabackup First

: https://www.percona.com/downloads/XtraBackup/LATEST/

My version here is: percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm

Local installation: Yum Localinstall percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm

View 650) this.width=650; "Src=" Https://s5.51cto.com/wyfs02/M00/91/72/wKioL1j2CWLz3Zg-AAAN6BD8dDo967.png-wh_ 500x0-wm_3-wmp_4-s_3560748276.png "title=" 1.png "alt=" Wkiol1j2cwlz3zg-aaan6bd8ddo967.png-wh_50 "/>

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M00/91/73/wKiom1j2CZyxingGAAAyfyAvuM4785.png-wh_500x0-wm_ 3-wmp_4-s_2299128796.png "title=" 1.png "alt=" Wkiom1j2czyxinggaaayfyavum4785.png-wh_50 "/>

OK, Installation complete

I.xtrabackup full preparation and recovery

1. Create a backup user, and authorize

Create user ' bkuser ' @ ' localhost ' identified by ' 123456 ';

Grant Reload,lock tables,process,replication Client on *. * to ' bkuser ' @ ' localhost ';

Flush privileges;

2. Start Backup

Innobackupex--user=bkuser--password=123456/data/backup/

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/91/74/wKiom1j2CjqQIUnPAABTGRg1Bz8875.png-wh_500x0-wm_ 3-wmp_4-s_687460348.png "title=" 1.png "alt=" Wkiom1j2cjqqiunpaabtgrg1bz8875.png-wh_50 "/>

The backup was in addition to the error because the socket file was not found.

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M01/91/72/wKioL1j2CrKxP2tAAADLB-B6w7g698.png-wh_500x0-wm_ 3-wmp_4-s_3334046994.png "title=" 1.png "alt=" Wkiol1j2crkxp2taaadlb-b6w7g698.png-wh_50 "/>

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M01/91/72/wKioL1j2CunibE3zAADRlopa2Do385.png-wh_500x0-wm_ 3-wmp_4-s_2524802151.png "title=" 1.png "alt=" Wkiol1j2cunibe3zaadrlopa2do385.png-wh_50 "/>

Backup complete, Success!

Files backed up: 650) this.width=650; "Src=" Https://s1.51cto.com/wyfs02/M01/91/72/wKioL1j2C2uxc1liAAAPpASHvXs691.png-wh_ 500x0-wm_3-wmp_4-s_1575959324.png "title=" 1.png "alt=" Wkiol1j2c2uxc1liaaappashvxs691.png-wh_50 "/>

3. Backup Recovery

Suppose we delete the previous data file and restore it directly (stop the MySQL service first)

First Innobackupex--apply-log/data/backup/2017-04-18_20-35-51/

and Innobackupex--copy-back/data/backup/2017-04-18_20-35-51/.

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M02/91/74/wKiom1j2DDOTLqMvAADrlf-pH4U461.png-wh_500x0-wm_ 3-wmp_4-s_2257091141.png "title=" 1.png "alt=" Wkiom1j2ddotlqmvaadrlf-ph4u461.png-wh_50 "/>

Restore succeeded. Restart MySQL

Two. Incremental backup and Recovery

1. Make a full backup, but don't apply-log

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M02/91/75/wKioL1j2FTOhaDPHAAA_e9gpWvg038.png-wh_500x0-wm_ 3-wmp_4-s_737908622.png "title=" 1.png "alt=" Wkiol1j2ftohadphaaa_e9gpwvg038.png-wh_50 "/>

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M02/91/75/wKioL1j2FV3giVfhAAA3P7xg-Tk882.png-wh_500x0-wm_ 3-wmp_4-s_2147475329.png "title=" 1.png "alt=" Wkiol1j2fv3givfhaaa3p7xg-tk882.png-wh_50 "/>

2, incremental backup on a full-scale backup basis

Add a record first

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M02/91/75/wKioL1j2FbfC4NFRAAA1q0UVQMU378.png-wh_500x0-wm_ 3-wmp_4-s_2492186655.png "title=" 1.png "alt=" Wkiol1j2fbfc4nfraaa1q0uvqmu378.png-wh_50 "/>

Start the first incremental backup Innobackupex--user=bkuser--password=123456--socket=/tmp/mysql.sock--incremental/data/backup/ Db-incre-20170418-1--incremental-basedir=/data/backup/20170418full

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M01/91/78/wKiom1j2GRTxwa15AAB-a-_vie4338.png-wh_500x0-wm_ 3-wmp_4-s_156846135.png "title=" 1.png "alt=" Wkiom1j2grtxwa15aab-a-_vie4338.png-wh_50 "/>

Backup successful

Insert one more data and make a second incremental backup

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M00/91/77/wKioL1j2GVngRMIBAAAhtYal0Xk556.png-wh_500x0-wm_ 3-wmp_4-s_3646519775.png "title=" 1.png "alt=" Wkiol1j2gvngrmibaaahtyal0xk556.png-wh_50 "/>

Innobackupex--user=bkuser--password=123456--socket=/tmp/mysql.sock--no-timestamp--incremental/data/backup/ Db-incre-20170418-2--incremental-basedir=/data/backup/db-incre-20170418-1

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/91/78/wKiom1j2GwrhqWb6AAB7q7lQRmk383.png-wh_500x0-wm_ 3-wmp_4-s_2149185219.png "title=" 1.png "alt=" Wkiom1j2gwrhqwb6aab7q7lqrmk383.png-wh_50 "/>

A second incremental backup is complete

3. Restore Backup Data

1) First time Full recovery + first incremental recovery (full backup and first incremental backup are backed up first)

Innobackupex--apply-log--redo-only/data/backup/20170418full

Innobackupex--apply-log--redo-only/data/backup/20170418full--incremental-dir=/data/backup/db-incre-20170418-1

Innobackupex--apply-log/data/backup/20170418full

The data recovered in theory is 8.

Innobackupex--copy-back/data/backup/20170418full

Similarly, first stop MySQL and delete the previous data, create the Data folder and authorize

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M02/91/78/wKioL1j2HrODFhExAABsv2-UPpY067.png-wh_500x0-wm_ 3-wmp_4-s_240213256.png "title=" 1.png "alt=" Wkiol1j2hrodfhexaabsv2-uppy067.png-wh_50 "/>

2) restore full and two incremental backups

Innobackupex--apply-log--redo-only/data/backup/20170418full

Innobackupex--apply-log--redo-only/data/backup/20170418full--incremental-dir=/data/backup/db-incre-20170418-1

Innobackupex--apply-log--redo-only/data/backup/20170418full--incremental-dir=/data/backup/db-incre-20170418-2

Innobackupex--apply-log/data/backup/20170418full

Shut down MySQL, delete data.

Recovering data Innobackupex--copy-back/data/backup/20170418full

Restart MySQL look, view data

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M01/91/7B/wKiom1j2JDrz6LAqAABqk_Hd63Q561.png-wh_500x0-wm_ 3-wmp_4-s_3396917503.png "title=" 1.png "alt=" Wkiom1j2jdrz6laqaabqk_hd63q561.png-wh_50 "/>

Incremental backup Recovery complete!

MySQL xtrabackup Backup