MySQL Big Data backup and incremental backup and restore

There are currently two major tools available for physical hot provisioning: Ibbackup and Xtrabackup, Ibbackup is 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 implement backup InnoDB, or to back up 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 executing transaction;

(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

Execute script

See if the catalog is successful.

Start a binary file, restart the service

Create a database, table, insert data

Data is backed up and incremental, as described below

Install the BACKUP database package

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

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

An OK at the end means it's okay,

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

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-the binary log file and the current position for binary log files for InnoDB or xtradb tables.

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

Inserting new data into the database

Backing up an incremental binary file

Restoring a Database

For the database, we're moving the data mv out of the database.

Preparing to restore the database

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

You can see the OK.

Restoring a Database

View Database catalog data

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

Restart database systemctl Restart Mysqld

View data in a database

Restore Incremental Data backup

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

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

MySQL Big Data backup and incremental backup and restore