Enable MySQL physical backup recovery with Xtrabackup

Xtrabackup is a MySQL database backup tool provided by Percona, officially described, which is the world's only one by one open source tools that enable hot provisioning of InnoDB and XTRADB databases. Characteristics:

(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;

The xtrabackup consists of two main tools:

Xtrabackup: is a tool for hot backup InnoDB, XTRADB data in tables, cannot back up other types of tables, nor can you back up the data table structure;

Innobackupex: is a Perl script that encapsulates Xtrabackup to back up and restore MyISAM tables and data table structures.

Xtrabackup:

http://www.percona.com/downloads/XtraBackup/XtraBackup-2.1.9/binary/Linux/i686/

[[email protected] mysql]# mkdir/usr/local/xtrabackup[[email protected] MySQL]# TAR-XVF Percona-xtrabackup-2.1.9-744-linux-i686.tar.gz-c/usr/local/xtrabackup[[email protected] ~]# cd/usr/local/ Xtrabackup/percona-xtrabackup-2.1.9-linux-i686[[email protected] percona-xtrabackup-2.1.9-linux-i686]# MV bin/. [[email protected] percona-xtrabackup-2.1.9-linux-i686]# mv Share/. [[email protected] percona-xtrabackup-2.1.9-linux-i686]# cd/usr/local/xtrabackup/[[email protected] xtrabackup]# rmdir percona-xtrabackup-2.1.9-linux-i686/[[email protected] xtrabackup]# lsbin share[[email  protected] xtrabackup]# CD bin/[[email protected] bin]# lltotal 95804-rwxr-xr-x. 1 root root 168821 May 2 17:42 innobackupexlrwxrwxrwx. 1 root root 20:15 innobackupex-1.5.1, innobackupex-rwxr-xr-x. 1 root root 2004765 May 2 17:42 xbcrypt-rwxr-xr-x. 1 root root 2064576 May 2 17:42 xbstream-rwxr-xr-x. 1 root root 10341667 May 2 17:42 XTRABACKUP-RWXr-xr-x. 1 root root 13483602 May 2 17:36 xtrabackup_55-rwxr-xr-x. 1 root root 70026286 May 2 17:40 xtrabackup_56[[email protected] xtrabackup]# echo "Export path= $PATH:/usr/local/xtr Abackup/bin ">>/etc/profile[[email protected] xtrabackup]# source/etc/profile

The installation of this xtrabackup is complete.

1. Use Xtrabackup for full backup

[[email protected] ~]# Innobackupex--user=root--password=asdfgh/backup The error occurs: Xtrabackup:Error:Please set parameter ' DataDir ' Workaround: Add datadir parameter in/etc/my.cnf [[email protected] ~]# VIM/ETC/MY.CNF [mysqld]datadir=/usr/local/mysql/data

[[email protected] ~]# Innobackupex--user=root--password=asdfgh/backup/[[email protected] ~]# cd/backup/ [[email protected] backup]# ls2014-05-15_22-33-56[[email protected] backup]# CD 2014-05-15_22-33-56/[[ Email protected] 2014-05-15_22-33-56]# lltotal 321592-rw-r--r--. 1 root root 260 may 22:33 backup-my.cnfdrwxr-xr-x. 2 root root 4096 may 22:34 employees-rw-r-----. 1 root root 329252864 may 22:34 ibdata1drwxr-xr-x. 2 root root 4096 May 22:34 imkhdrwxr-xr-x. 2 root root 4096 May 22:34 mysqldrwxr-xr-x. 2 root root 4096 May 22:34 performance_schemadrwxr-xr-x. 2 root root 4096 May 22:34 sakiladrwxr-xr-x. 2 root root 4096 May 22:34 testdrwxr-xr-x. 2 root root 4096 May 22:34 test1drwxr-xr-x. 2 root root 4096 May 22:34 worlddrwxr-xr-x. 2 root root 4096 may 22:34 world_innodb-rw-r--r--. 1 root root 22:34 xtrabackup_binary-rw-r--r--. 1 root root 22:34 xtrabackup_binlog_info-rw-r-----. 1 root root 15 22:34 Xtrabackup_checkpoints-rw-r-----. 1 root root 2560 may 22:34 Xtrabackup_logfile

(1) backup-my.cnf--configuration option information used for backup command;

[[email protected] 2014-05-15_22-33-56]# cat backup-my.cnf# This MySQL options file is generated by innobackupex.# the My SQL server[mysqld]innodb_data_file_path=ibdata1:10m:autoextendinnodb_log_files_in_group=2innodb_log_file_size= 5242880innodb_fast_checksum=0innodb_page_size=16384innodb_log_block_size=512

(2) ibdata--backup tablespace file;
(3) xtrabackup executable files used in xtrabackup_binary--backup;

[Email protected] 2014-05-15_22-33-56]# more xtrabackup_binaryxtrabackup_55

(4) Xtrabackup_binlog_info--mysql the binary log file currently in use by the server and the location of the binary log event so far as the backup is made;

[Email protected] 2014-05-15_22-33-56]# more xtrabackup_binlog_infomysql-bin.000017    107

(5) xtrabackup_checkpoints--the backup type (such as full or incremental), the backup state (such as whether it is already in the prepared state), and the LSN (log sequence number) range information;

[Email protected] 2014-05-15_22-33-56]# more xtrabackup_checkpoints backup_type = FULL-BACKUPEDFROM_LSN = 0TO_LSN = 8364 46404LAST_LSN = 836446404compact = 0

(6) Redo log file for xtrabackup_logfile--backup.

When using Innobackupex for backup, you can also use the-NO-TIMESTAMP option to prevent commands from automatically creating a time-named directory, so that the Innobackupex command creates a Backup-dir directory to store the backup data.

[Email protected] ~]# Innobackupex--user=root--password=asdfgh--no-timestamp/backup1    //Don't create/backup1 first

(7) 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 preparation (prepare) is to keep the data files in a consistent state by rolling back uncommitted transactions and synchronizing the committed transactions to the data file.

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

[Email protected] backup]# Innobackupex--apply-log 2014-05-15_22-33-56/

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.

2. Using Xtrabackup to achieve full backup recovery

1) Simulated database corruption

Use the delete data Catalog file directly to simulate corruption:

[Email protected] ~]# cd/usr/local/mysql/data/[[email protected] data]# rm-rf *[[email protected] data]# mysql-uroot- Pmysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema |+--------------------+1 row in Set (0.00 sec)

(2) Restore full backup:

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.

[[email protected] ~]# Innobackupex--copy-back/backup/2014-05-15_ 22-33-56/[[email protected] ~]# cd/usr/local/mysql/data/[[email protected] data]# lltotal 331820drwxr-xr-x. 2 root root 4096 may 23:11 employees-rw-r--r--. 1 root root 329252864 may 23:11 ibdata1-rw-r--r--. 1 root root 5242880 may 23:11 ib_logfile0-rw-r--r--. 1 root root 5242880 may 23:11 ib_logfile1drwxr-xr-x. 2 root root 4096 May 23:11 imkhdrwxr-xr-x. 2 root root 4096 May 23:11 mysqldrwxr-xr-x. 2 root root 4096 May 23:11 performance_schemadrwxr-xr-x. 2 root root 4096 May 23:11 sakiladrwxr-xr-x. 2 root root 4096 May 23:11 testdrwxr-xr-x. 2 root root 4096 May 23:11 test1drwxr-xr-x. 2 root root 4096 May 23:11 worlddrwxr-xr-x. 2 root root 4096 may 23:11 world_innodb-rw-r--r--. 1 root root 23:11 xtrabackup_binlog_pos_innodb 

(3) Modify the restored Data directory permissions:

[Email protected] data]# chown-r mysql:mysql. [Email protected] data]# lltotal 331820drwxr-xr-x. 2 MySQL mysql 4096 may 23:11 employees-rw-r--r--. 1 MySQL MySQL 329252864 may 23:11 ibdata1-rw-r--r--. 1 MySQL MySQL 5242880 may 23:11 ib_logfile0-rw-r--r--. 1 MySQL MySQL 5242880 may 23:11 ib_logfile1drwxr-xr-x. 2 MySQL mysql 4096 may 23:11 imkhdrwxr-xr-x. 2 MySQL mysql 4096 may 23:11 mysqldrwxr-xr-x. 2 MySQL mysql 4096 may 23:11 performance_schemadrwxr-xr-x. 2 MySQL mysql 4096 may 23:11 sakiladrwxr-xr-x. 2 MySQL mysql 4096 may 23:11 testdrwxr-xr-x. 2 MySQL mysql 4096 may 23:11 test1drwxr-xr-x. 2 MySQL mysql 4096 may 23:11 worlddrwxr-xr-x. 2 MySQL mysql 4096 may 23:11 world_innodb-rw-r--r--. 1 MySQL MySQL 23:11 xtrabackup_binlog_pos_innodb