MySQL Backup tool

backup Restore scripts and tools

1) X The trabackup implementation is a physical backup, and is a physical hot standby

There are currently two major tools available for physical heat preparation:ibbackup and xtrabackup;ibbackup is commercial software, requires authorization, Very expensive. the xtrabackup function is more powerful than ibbackup , but it is open source.

2)xtrabackup provides two command-line tools:

Xtrabackup: Data dedicated to backing up the InnoDB and XtraDB engines;

Innobackupex: This is a perl script that invokes the xtrabackup command during execution. This allows the backup InnoDB to be implemented , or the objects of the MyISAM enginecan be backed up .

two demonstration steps

First Unpacking the installation package

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M01/9B/77/wKioL1ljag3TCR2zAADpgCOaLAc658.png-wh_500x0-wm_ 3-wmp_4-s_3702938467.png "title=" 00.png "alt=" Wkiol1ljag3tcr2zaadpgcoalac658.png-wh_50 "/>

copy All Programs under Bin to /usr/bin

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M00/9B/78/wKiom1ljaimj7piCAAEPY5NnT_4034.png-wh_500x0-wm_ 3-wmp_4-s_1770430754.png "title=" 01.png "alt=" Wkiom1ljaimj7picaaepy5nnt_4034.png-wh_50 "/>

The xtrabackup consists of two main tools:

Xtrabackup: is a tool for hot backup InnoDB, xtradb data in tables, supports online hot backup, can back up innodb data table without lock, but this tool cannot operate Myisam engine table ;

Innobackupex: A perl script that encapsulates xtrabackup to handle Innodb and Myisam, but you need to add a read lock when handling Myisam.

3) Install the plugin using yum

Yum install perl-dbi perl-dbd-mysql perl-time-hires perl-io-socket-ssl perl-termreadkey.x86_64 perl-Digest-MD5–y

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M01/9B/78/wKiom1ljaknS_CFlAACTk2GxKUo604.png-wh_500x0-wm_ 3-wmp_4-s_1800789837.png "title=" 02.png "alt=" Wkiom1ljakns_cflaactk2gxkuo604.png-wh_50 "/>

Scenario One:xtrabackup full backup +binlog Incremental backup

Create a backup directory

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M02/9B/78/wKiom1ljanKRo9NpAAA9TyGdiKk076.png-wh_500x0-wm_ 3-wmp_4-s_122212852.png "title=" 03.png "alt=" Wkiom1ljankro9npaaa9tygdikk076.png-wh_50 "/>

Full Backup

Innobackupex--user=root--password=123456/opt/mysqlbackup/ full appears OK complete

Note:--defaults-file=/etc/my.cnf  Specify the configuration file for mysql my.cfg , if specified, must be the first parameter.

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M00/9B/78/wKiom1ljaoKR0Td1AAD4exkjArE864.png-wh_500x0-wm_ 3-wmp_4-s_3591546110.png "title=" 04.png "alt=" Wkiom1ljaokr0td1aad4exkjare864.png-wh_50 "/>

Backing up the database will create a directory with the current date name in the backup directory

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M01/9B/78/wKiom1ljaqOCzSkxAAGnRryd4yQ259.png-wh_500x0-wm_ 3-wmp_4-s_2678390836.png "title=" 05.png "alt=" Wkiom1ljaqoczskxaagnrryd4yq259.png-wh_50 "/>

Document description

(1) xtrabackup_checkpoints prepared state) and lsn ( log serial number )

each Innodb page ( usually 16k ) will contain a log sequence number (2) xtrabackup_binlog_info -- The binary log file currently being used by the server and the location of the binary log event up to the moment the backup was made.

(3) Xtrabackup_binlog_pos_innodb -the binary log file and The current binary log file for InnoDB or XtraDB tables position.

(4) Xtrabackup_binary--The xtrabackup executable file used in the backup ;

(5) Backup-my.cnf -the configuration option information used by the backup command;

When you use Innobackupex for backup, you can also use the --no-timestamp option to prevent commands from automatically creating a time-named directory.

Note: The user who backs up the database needs to have the appropriate permissions, and if you want to back up with a user with minimal permissions, you can create such a user based on the following command:

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M01/9B/78/wKiom1lja6Gi7kHEAABd1v7aln0269.png-wh_500x0-wm_ 3-wmp_4-s_1903764790.png "style=" Float:none; "title=" 06.png "alt=" Wkiom1lja6gi7kheaabd1v7aln0269.png-wh_50 "/>

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M01/9B/78/wKioL1lja6HjxlHCAACVdZdM_rk452.png-wh_500x0-wm_ 3-wmp_4-s_1837500055.png "style=" Float:none; "title=" 07.png "alt=" Wkiol1lja6hjxlhcaacvdzdm_rk452.png-wh_50 "/>

This is fully successful, then Insert a few data into a MySQL library, and then make incremental backups to make a binary log incremental backup of the post-database changes to the full backup:

Binlog Log Location When viewing a full backup (position)

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M01/9B/78/wKioL1lja7zDKAEGAABhsKodGkc555.png-wh_500x0-wm_ 3-wmp_4-s_4103724672.png "title=" 08.png "alt=" Wkiol1lja7zdkaegaabhskodgkc555.png-wh_50 "/>

Simulate modifying a database

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/9B/78/wKiom1lja92za93MAADuxaz22bU283.png-wh_500x0-wm_ 3-wmp_4-s_2516967524.png "title=" 09.png "alt=" Wkiom1lja92za93maaduxaz22bu283.png-wh_50 "/>

Incremental backup binary files

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/9B/78/wKiom1ljbBqjKrkcAABXx_pSz10257.png-wh_500x0-wm_ 3-wmp_4-s_840435778.png "title=" 10.png "alt=" Wkiom1ljbbqjkrkcaabxx_psz10257.png-wh_50 "/>

2 Restoring a database binary file

First simulate The data file Delete or transfer, then perform a full backup restore

the --apply-log option of the Innobakupex command can be used to implement the above functions. --apply-log indicates that the log is applied to the data file and the data in the backup file is restored to the database after completion:

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/9B/78/wKiom1ljbCyTWunvAACQI4rApIc770.png-wh_500x0-wm_ 3-wmp_4-s_544066306.png "title=" 11.png "alt=" Wkiom1ljbcytwunvaacqi4rapic770.png-wh_50 "/>

To restore the database syntax:

# Innobackupex--COPY-BACK/OPT/MYSQLBACKUP/FULL/2016-09-12_11-29-55/

Here's -- Copy-back indicates that data recovery is performed. After the data recovery is complete, you need to modify the permissions of the relevant files MySQL database to start normally.

Restore DATABASE syntax

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M01/9B/78/wKiom1ljbELAoJ0KAAEAj624ODE162.png-wh_500x0-wm_ 3-wmp_4-s_3820210386.png "title=" 12.png "alt=" Wkiom1ljbelaoj0kaaeaj624ode162.png-wh_50 "/>

Data has successfully restored restarting MySQL

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M00/9B/78/wKiom1ljbGbiEcPSAAHIV4tC_Bo491.png-wh_500x0-wm_ 3-wmp_4-s_2210115644.png "title=" 13.png "alt=" Wkiom1ljbgbiecpsaahiv4tc_bo491.png-wh_50 "/>

Need to ensure that all data files belong to the owner and the group

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M02/9B/78/wKiom1ljbHaDib28AADILIuuKss636.png-wh_500x0-wm_ 3-wmp_4-s_2749769940.png "title=" 14.png "alt=" Wkiom1ljbhadib28aadiliuukss636.png-wh_50 "/>

Restore succeeded But the incremental backup hasn't been successful yet .

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/9B/78/wKioL1ljbIbD9Ic-AABwrGY3evg095.png-wh_500x0-wm_ 3-wmp_4-s_2680730154.png "title=" 15.png "alt=" Wkiol1ljbibd9ic-aabwrgy3evg095.png-wh_50 "/>

To prevent a large number of binary logs from being generated during a restore, you can temporarily turn off the binary log and then restore it when you restore:

Mysql> set sql_log_bin=0;

Mysql> Source/opt/mysqlbackup/inc/2016-09-12.sql

Restore succeeded Successful Data Recovery

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M01/9B/78/wKiom1ljbJbAQttGAACUSbE4Y10495.png-wh_500x0-wm_ 3-wmp_4-s_3265102602.png "title=" 16.png "alt=" Wkiom1ljbjbaqttgaacusbe4y10495.png-wh_50 "/>

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/9B/78/wKioL1ljbKPAT8w4AACQgy4TbLg812.png-wh_500x0-wm_ 3-wmp_4-s_2912350420.png "title=" 17.png "alt=" Wkiol1ljbkpat8w4aacqgy4tblg812.png-wh_50 "/>

Restart The binary log set sql_log_bin=1;

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M00/9B/78/wKiom1ljbLiyx19JAAA5lQ38Cx4573.png-wh_500x0-wm_ 3-wmp_4-s_1565745056.png "title=" 18.png "alt=" Wkiom1ljbliyx19jaaa5lq38cx4573.png-wh_50 "/>

This article is from the "Chaixinwang" blog, make sure to keep this source http://chaixinwang.blog.51cto.com/13052229/1946081

MySQL Backup tool