Using Xtrabackup for MySQL database backup

Xtrabackup is a physical backup tool that supports the INNODB and XTRADB storage engines, and it has the following 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.

Packages that need to be installed:

Percona-xtrabackup can obtain the corresponding version from the official website Https://www.percona.com/software/mysql-database/percona-xtrabackup

Xtrabackup Backup Policy:

A. Full backup

Xtrabackupex--user=dbuser--passwd=dbuserpasswd/path/to/backup_dir/

When using a Innobakupex Backup, it invokes xtrabackup to back up all the InnoDB Tables and copies all relevant files about the table structure definition (. frm), and MyISAM,MERGE,CSV , and ARCHIVE The file associated with the table, and also backs up files related to the trigger and database configuration information. These files are saved to a time-named directory.

At the same time as the backup,Innobackupex also creates the following files in the backup directory:

(1) Xtrabackup_checkpoints --backup type (such as full or incremental), backup status (such as whether it is already prepared State), and LSN ( Log serial number ) range information;

Each InnoDB page ( typically 16k size ) contains a log sequence number, which is LSN. LSN is the system version number of the entire database system, and each page-related LSN can indicate how the page has changed recently.

(2) Xtrabackup_binlog_info - the binary log file currently being used by the MySQL server and the location of the binary log event so far as 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;

2. Prepare (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 the committed transactions to the data file.

Implementation method plus parameter--appy-log

# Innobackupex--apply-log/path/to/backup_dir

In the process of "preparing,"Innobackupex can typically use The--use-memory option to specify the size of the 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.

3. Recovering data from a full backup ( Note: Recovery does not start MySQL)

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

# Innobackupex--copy-back/path/to/backup-dir

#还原以后默认权限是root用户, need to be modified to MySQL

# Chown-r mysql:mysql/mydata/data/

4. Use Innobackupex for incremental backup

Each InnoDB page contains an LSN information, and the LSN of the associated page automatically grows whenever the relevant data changes . This is the basis for an incremental backup of the InnoDB table, that is, Innobackupex is implemented by backing up pages that have changed since the last full backup.

To make an incremental backup on a full-scale basis, you can use the following command:

# Innobackupex--incremental/backup--incremental-basedir=/backup/2016-05-10_17-18-54

#2016 -05-10_17-18-54 A directory generated for full-scale backups, based on incremental backups

after the execution of this command is complete, Innobackupex command will be in /backup a new time-named directory is created in the directory to hold all the incremental backup data. Also, when incremental backups are performed after an incremental backup, their --incremental-basedir should point to the same directory as the last incremental backup.

Note: Incremental backups can only be applied to InnoDB or XtraDB tables, for MyISAM tables, A full backup is actually performed when performing an incremental backup .

Create the Environment:

#/etc/init.d/mysqld stop

#cp-A/var/lib/mysql/var/lib/mysql.bak

# Rm-fr/var/lib/mysql

Theprepare Incremental backup is a bit different from organizing a full backup, especially to note that:

(1) need to be in each backup ( including full and individual incremental backups ) , a transaction that has already been committed is "replayed". After replay, all backup data is merged into a full backup.

(2) "Roll Back" uncommitted transactions based on all backups.

# Innobackupex--user=db_user--password=db_password--apply-log--redo-only/backup/2016-05-10_17-18-54

# Innobackupex--user=db_user--password=db_password--apply-log--redo-only/backup/2016-05-10_17-18-54-- Incremental-dir=/backup/incremental-1

If you have multiple incremental backups, you need to do this multiple times to add an incremental backup to a full-scale backup

# Innobackupex--user=db_user--password=db_password--apply-log/backup/2016-05-10_17-18-54 rollback log

# Innobackupex--user=db_user--password=db_password--copy-back/backup/2016-05-10_17-18-54

Restore complete, restart MySQL to

#/etc/init.d/mysqld start

Using Xtrabackup for MySQL database backup