Xtrabackup a MySQL database backup

The previous introduction of Mysqldump backup method is the use of logical backup, the biggest drawback is that the backup and recovery is slow, for a database less than 50G, this speed is acceptable, but if the database is very large, then use mysqldump backup is not suitable. At this time need a useful and efficient tool, xtrabackup is one of them, known as the free version of the InnoDB hotbackup. The Xtrabackup implementation is a physical backup, and is a physical hot standby current mainstream two tools that can be physically hot prepared: Ibbackup and Xtrabackup;ibbackup are commercial software that need to be licensed and very expensive. The Xtrabackup function is more powerful than ibbackup, but it is open source. So we're here to introduce the use of xtrabackup.

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 engine can be backed up.

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.

Official link address: http://www.percona.com/software/percona-xtrabackup; You can download the source code to compile the installation, or you can download the appropriate RPM package or use Yum to install or download the binary source package.

Installing Xtrabackup

1) Download Xtrabackup

wget Https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/bin ary/tarball/ Percona-xtrabackup-2.4.4-linux-x86_64.tar.gz

2) Unzip # tar zxf percona-xtrabackup-2.4.4-linux-x86_64.tar.gz

3) go to unzip directory # CD percona-xtrabackup-2.4.4-linux-x86_64/

4) Copy all Programs under Bin to/usr/bin [[email protected] percona-xtrabackup-2.4.4-linux-x86_64]# CP bin/*/usr/bin/

The xtrabackup consists of two main tools:

Xtrabackup: is a tool for hot backup InnoDB, XTRADB data in tables, Support online hot backup,

The INNODB data table can be backed up without locking, but the tool cannot operate the Myisam engine table;

Innobackupex: A Perl script that encapsulates Xtrabackup to handle Innodb and Myisam at the same time,

However, you need to add a read lock when handling Myisam. Because a read lock is required to operate the Myisam, this can block write operations on the online service, and Innodb does not have such limitations, so the larger the proportion of Innodb table types in the database, the more advantageous.

4) Install related plugins

#yum Install perl-dbi perl-dbd-mysql perl-time-hires perl-io-socket-ssl perl-termreadkey.x86_64 perl-digest-md5–y

5) Download Percona-toolkit and install

#wget https://www.percona.com/downloads/percona-toolkit/2.2.19/RPM/percona-toolkit-2.2.19-1. noarch.rpm

# RPM-VIH percona-toolkit-2.2.19-1.noarch.rpm

You can start the backup below

1. Backup Create backup directory

# Mkdir-p/opt/mysqlbackup/{full,inc} full:

Fully stocked directory; Inc: the directory where incremental backups reside

1) Full backup

Perform a full backup of the following command:

# Innobackupex--user=root--password=123456/opt/mysqlbackup/full

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

/path/to/backup-dir/Specifies the destination directory where the backup is stored, and the backup process creates a directory that is named after the backup time to hold the backup file. The following prompt appears. Indicates success

Files after backup:

At the same time as the backup, the backup data will create a directory in the backup directory with the name of the current datetime to hold the backup file:

This is fully successful, then inserts a few data into a MySQL library, and then makes an incremental backup of the binary log incremental backup for the post-database changes to the full backup: View the Binlog log location (position) when a full backup is in progress:

Simulating database modifications:

2. Restore the database:

Impersonate a database corruption: I use the delete data Catalog file here directly to simulate corruption.

# rm-fr/usr/local/mysql/data/*

To restore a full backup:

(1) Preparation (prepare) a full backup

In general, data cannot be used for recovery operations after the backup is complete, because the data that is backed up may contain transactions that have not yet been committed or that have been committed but have not been synchronized to the data file.

Therefore, the data file still handles the inconsistent state at this time.

The primary role of Prep is to keep the data file in a consistent state by rolling back uncommitted transactions and synchronizing committed transactions to data files.

At the end of the prepare (prepare) process, the InnoDB table data has been rolled forward to the point where the entire backup ended, rather than rolling back to the point at which the Xtrabackup was initially started.

The--apply-log option of the Innobakupex command can be used to implement the above functions.

As the following command:--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:

# Innobackupex--APPLY-LOG/OPT/MYSQLBACKUP/FULL/2016-09-12_11-29-55/

Note: The directory name where the/opt/mysqlbackup/full/2016-09-12_11-29-55/backup file is located if executed correctly, the last few lines of information for the output are usually as follows:

In the process of "preparing," Innobackupex can typically use the--use-memory option to specify the amount of memory it can use, which is typically 100M by default.

If there is enough memory available, you can partition some memory into the prepare process to improve its completion speed.

The--copy-back option of the Innobackupex command is used to perform recovery operations, which perform the recovery process by copying all data-related files to the MySQL server DataDir directory. Innobackupex backup-my.cnf to get information about the DataDir directory.

(2) Restore DATABASE syntax:

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

The--copy-back here 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. If executed correctly, the last lines of its output information are usually as follows:

Make sure that "completed ok!" appears on the top line of the information above.

To modify the restored data directory permissions:

Once the data has been restored to the DataDir directory, it is also necessary to ensure that all data files belong to the owner and the group are the correct users.

such as MySQL, otherwise, before starting the mysqld, you need to modify the data file's owner and host group beforehand.

such as: # Chown-r mysql:mysql/usr/local/mysql/data/

MySQL must be restarted:

# systemctl Restart mysqld (if there is an error adding/ETC/MY.CNF pidfile=/usr/local/mysql/mysqld.pid pkill mysqld Then restart repeatedly using this command)

Verify the restored data:

Mysql> select * from TB1;

Incremental backup:

Note: The relevant option description:

Where--user specifies the user name to connect to the database,--password specifies the password to connect to the database,--defaults-file the configuration file of the specified database, Innobackupex to obtain datadir information from it;--database Specifies to Backed up database, the database specified here is only valid for the MyISAM table, is fully prepared for InnoDB data (all InnoDB data in all databases are backed up, not only the specified database, but also when recovering);/opt/mysqlbackup/full is the location where the backup files are stored.

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:

mysql> create user ' bkpuser ' @ ' localhost ' identified by ' 123456 ';

mysql> revoke all privileges,grant option from ' bkpuser ' @ ' localhost ';

Mysql> Grant Reload,lock tables,replication client, Process on *. * to ' bkpuser ' @ ' localhost ';

mysql> flush Privileges;

Simulating database modifications:

2) Incremental backup binary files:

#mysqlbinlog--start-position=2378/usr/local/mysql/data/mysql-bin.000023 >/opt/mysqlbackup/inc/' Date +%F '. sql

(3) Restore an incremental backup: To prevent a large number of binary logs from being generated during a restore, you can temporarily turn off the binary log before restoring it:

Mysql> set sql_log_bin=0;

(The following three kinds are possible)

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

or on the command line: Mysql–uroot–p </opt/mysqlbackup/inc/2016-09-12.sql

Mysqlbinlog/opt/mysqlbackup/inc/2016-09-12.sql | Mysql–uroot-p

Restart the binary log and verify the Restore data:

Mysql> set sql_log_bin=1;

Verify that the data is back

Xtrabackup a MySQL database backup