MySQL Backup recovery Tool xtrabackup

1. Introduction

Xtrabackup is an open-source, free MySQL database backup tool provided by Percona, which is able to perform hot backups of InnoDB and XTRADB databases, which, according to official introduction, has the following 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 backup inspection;

5. Fast recovery speed.

2, Installation Xtrabackup

the latest version can be Https://www.percona.com/software/percona-xtrabackup obtained.

# yum Install https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.2.10/binary/redhat/6/x86_64/ percona-xtrabackup-2.2.10-1.el6.x86_64.rpm

3. Create the required users

# Create an example of a database user with the minimum permissions required for a full backup:

mysql> CREATE USER ' bkpuser ' @ ' localhost ' identified by ' S3cret ';

Mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT on *. * to ' bkpuser ' @ ' localhost ';

mysql> FLUSH privileges;

4. Full backup

# Innobackupex--user=dbuser--password=dbuserpass/path/to/backup-dir/

# Check the confirmation message for the last line of the output

Innobackupex:backup created in directory '/path/to/backup-dir/2013-03-25_00-00-09 '

Innobackupex:mysql binlog position:filename ' mysql-bin.000003 ', position 1946

111225 00:00:53 innobackupex:completed ok!

5. Prepare (PREPAR) a full backup

# Innobackupex--apply-log/path/to/backup-dir

111225 1:01:57 Innodb:shutdown completed; Log sequence Number 1609228

111225 01:01:57 innobackupex:completed ok!

Note: During the preparation process, Innobackupex can often use the--use-memory option to specify the size of the memory it can use, which defaults to 100M. If there is enough memory available, you can partition some memory into the prepare process to improve the completion speed.

6. Recovering from a full backup

# The--copy-back option of the Innobackupex command is used to perform the recovery operation, and the recovery process is performed by copying all data-related files to the DataDir directory of the MySQL server.

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

# If executed correctly, a few lines will appear

Innobackupex:finished copying back files.

111225 01:08:13 innobackupex:completed ok!

Note: Before deleting the data directory, stop the MYSQLD service and then resume the boot directly with the command without having to reinitialize the database. Once the data has been restored to the DataDir directory, it is also necessary to ensure that all data files are of the correct user and that the owners and groups of the data files need to be modified prior to starting mysqld.

# Chown-r Mysql:mysql/data/mysql

7. Incremental backup

# each InnoDB page will contain an LSN information, and the LSN of the associated page will automatically grow whenever the relevant data changes. This is also 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.

Create an incremental backup (requires a full backup as the basis):

# Innobackupex--user=dbuser--password=dbuserpass--incremental/data/backups--incremental-basedir=basedir

# where Basedir refers to the directory where the full backup is located, the Innobackupex command creates a new time-named directory in the/backup directory to hold all incremental backup data when the command finishes. 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, and for MyISAM tables, performing an incremental backup is actually a full backup.

8. Prepare for Incremental backup

# " ready " incremental backup is a little different from a full backup, and it is important to note that:

1. You need to "replay" the transactions that have been committed on each backup, including full and individual incremental backups. After "Replay", all backup data is merged into a full backup;

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

Operation Steps:

# Innobackupex--apply-log--redo-only Base-dir

120103 22:00:12 Innodb:shutdown completed; Log sequence Number 1291135

120103 22:00:12 innobackupex:completed ok!

# Innobackupex--apply-log--redo-only base-dir--incremental-dir=incremental-dir-1

120103 22:08:43 Innodb:shutdown completed; Log sequence Number 1358967

120103 22:08:43 innobackupex:completed ok!

# Innobackupex--apply-log--redo-only base-dir--incremental-dir=incremental-dir-2

Completed ok!

# where Base-dir refers to the directory where the full backup is located, and Incremental-dir-1 refers to the first incremental backup of the directory, incremental-dir-2 refers to the second incremental backup of the directory, and so on, if there are multiple incremental backups, Each time you perform the action as above.

MySQL Backup recovery Tool xtrabackup