MySQL Big Data backup and restore (ii)

MySQL Big Data backup and incremental backup and restore

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 are expensive to authorize and xtrabackup is more powerful and open-source than ibbackup .

Xtrabackup provides two command-line tools:

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

Innobackupex: This is a perl script that calls the xtrabackup command during execution to enable backup InnoDB, you can also back up The objects of the MyISAM engine

Xtrabackup is a mysql database backup tool provided by Percona and Features:

(1) The backup process is fast and reliable;

(2) The backup process does not interrupt the transaction being performed;

(3) can save disk space and traffic based on functions such as compression;

(4) Automatic implementation of backup inspection;

(5) fast restore speed.

Using scripts to fully back up the database

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/9A/E9/wKiom1lcMXGjUDgIAAB8XTPWDLo884.png "title=" Picture 1.png "alt=" Wkiom1lcmxgjudgiaab8xtpwdlo884.png "/>

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/9A/E9/wKiom1lcMXzxMNkBAAAQ5oI1nkc498.png "title=" Picture 2.png "alt=" Wkiom1lcmxzxmnkbaaaq5oi1nkc498.png "/>

Execute script

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/9A/E9/wKioL1lcMYyyLWzgAAAJarRDkz4740.png "title=" Picture 3.png "alt=" Wkiol1lcmyyylwzgaaajarrdkz4740.png "/>

See if the catalog is successful.

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/9A/E9/wKioL1lcMZmTEY1gAAAPEDxUi04924.png "title=" Picture 4.png "alt=" Wkiol1lcmzmtey1gaaapedxui04924.png "/>

Start a binary file, restart the service

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/9A/E9/wKioL1lcMaOzABNCAAALgzqMlec517.png "title=" Picture 5.png "alt=" Wkiol1lcmaozabncaaalgzqmlec517.png "/>

Create a database, table, insert data

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/9A/E9/wKioL1lcMbOA2Y5pAAAY-LYYXfw554.png "title=" Picture 6.png "alt=" Wkiol1lcmboa2y5paaay-lyyxfw554.png "/>

Data is backed up and incremental, as described below

Install the BACKUP database package

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/9A/E9/wKiom1lcMb2SCSikAAAlv8lTC0w457.png "title=" Picture 7.png "alt=" Wkiom1lcmb2scsikaaalv8ltc0w457.png "/>

Tar zxf percona-xtrabackup-2.4.4-linux-x86_64.tar.gz

CD percona-xtrabackup-2.4.4-linux-x86_64/

CP bin/*/usr/bin/

Installing a Dependency package with Yum

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/9A/E9/wKiom1lcMcviHN0OAABA_629Aug752.png "title=" Picture 8.png "alt=" Wkiom1lcmcvihn0oaaba_629aug752.png "/>

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/9A/E9/wKioL1lcMdayOedHAAAqMftOsZs734.png "title=" Picture 9.png "alt=" Wkiol1lcmdayoedhaaaqmftoszs734.png "/>

Create backup directory (full: Fully stocked directory,inc: directory for incremental backups)

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/9A/E9/wKioL1lcMeOQoIFDAAAe_5fNJw8370.png "title=" Picture 10.png "alt=" Wkiol1lcmeoqoifdaaae_5fnjw8370.png "/>

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/9A/E9/wKiom1lcMe2zLX1tAAAMKEicGGM343.png "title=" Picture 11.png "alt=" Wkiom1lcme2zlx1taaamkeicggm343.png "/>

At the end there is a Ok , that means it's ready.

See what's backed up in the fully-prepared directory

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/9A/E9/wKioL1lcMfajNDkoAAAoVX_ddnE132.png "title=" Picture 12.png "alt=" Wkiol1lcmfajndkoaaaovx_ddne132.png "/>

xtrabackup_checkpoints-- Backup type, backup status,LSN range Information

Xtrabackup_binlog_info--mysql the binary log file currently in use by the server and the location of the binary log event up to the moment the backup was made.

Xtrabackup_binlog_pos_innodb-- binary log files and the current of binary log files for InnoDB or XtraDB tables position .

Xtrabackup_binary--The xtrabackup executable file used in the backup ;

BACKUP-MY.CNF- The configuration option information used by the backup command;

View The number of the log file in info

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/9A/E9/wKiom1lcMhehSzwNAAAKszKPcOE088.png "title=" Picture 13.png "alt=" Wkiom1lcmhehszwnaaakszkpcoe088.png "/>

Inserting new data into the database

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/9A/E9/wKioL1lcMiKDFeqGAAATa89ut2Y845.png "title=" Picture 14.png "alt=" Wkiol1lcmikdfeqgaaata89ut2y845.png "/>

Backing up an incremental binary file

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/9A/E9/wKioL1lcMi2QZzsnAAAOg5mGo7A443.png "title=" Picture 15.png "alt=" Wkiol1lcmi2qzzsnaaaog5mgo7a443.png "/>

Restoring a Database

for the database to be guaranteed We're moving the data mv from the database.

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/9A/E9/wKiom1lcMjfRDU99AAAYiNljBLs558.png "title=" Picture 16.png "alt=" Wkiom1lcmjfrdu99aaayinljbls558.png "/>

Preparing to restore the database

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/9A/E9/wKiom1lcMkySKqzuAAAYBjdFOQ4721.png "title=" Picture 17.png "alt=" Wkiom1lcmkyskqzuaaaybjdfoq4721.png "/>

Note: The above is only a part, there are more output content not cut

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/9A/E9/wKiom1lcMmTyQmrYAAAEOZ0_XCs460.png "title=" Picture 18.png "alt=" Wkiom1lcmmtyqmryaaaeoz0_xcs460.png "/>

You can see the OK.

Restoring a Database

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M00/9A/E9/wKioL1lcMnPzGU5OAAALgLJy3eU320.png "title=" Picture 19.png "alt=" Wkiol1lcmnpzgu5oaaalgljy3eu320.png "/>

View Database catalog data

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/9A/E9/wKioL1lcMn7Q4gZiAAAegpZtwCc054.png "title=" Picture 20.png "alt=" Wkiol1lcmn7q4gziaaaegpztwcc054.png "/>

It can be seen that the data has been restored, but the main group is root, because it is restored by the root User, modify the genus Master Group

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/9A/E9/wKiom1lcMobzoqp4AAAfnhCZ6Q8102.png "title=" Picture 21.png "alt=" Wkiom1lcmobzoqp4aaafnhcz6q8102.png "/>

Restart database systemctl Restart mysqld

View data in a database

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/9A/E9/wKioL1lcMo_yZNTPAAAVoBRDV9Y811.png "title=" Picture 22.png "alt=" Wkiol1lcmo_yzntpaaavobrdv9y811.png "/>

Restore Incremental Data backup

Note: When restoring an incremental backup, many binaries are generated and the binaries are closed

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/9A/E9/wKiom1lcMpnRA80uAAAzk4XH7aY507.png "title=" Picture 23.png "alt=" Wkiom1lcmpnra80uaaazk4xh7ay507.png "/>

Then open the binary file to see if the data is restored

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/9A/E9/wKiom1lcMqSRWeeUAAALUZYWCWo522.png "title=" Picture 24.png "alt=" Wkiom1lcmqsrweeuaaaluzywcwo522.png "/>

This article from "Centos7" blog, reproduced please contact the author!

MySQL Big Data backup and restore (ii)