Example of XtraBackup backup and restoration of mysql database

Xtrabackup has two main tools: xtrabackup and innobackupex.

(1) xtrabackup can only back up InnoDB and XtraDB data tables, but cannot back up MyISAM data tables.

(2) innobackupex-1.5.1 encapsulates xtrabackup, which is a script encapsulation, so innodb and myisam can be backed up at the same time, but a read lock is needed when processing myisam

1. Download and install

Http://www.percona.com/downloads/XtraBackup/LATEST/

Percona-xtrabackup-2.2.9-5067.el6.x86_64.rpm

Install centos6.4 _ x64

Yum localinstall percona-xtrabackup-2.2.9-5067.el6.x86_64.rpm

Generate after installation

[Root @ localhost tmp] # rpm-ql percona-xtrabackup
/Usr/bin/innobackupex
/Usr/bin/xbcrypt
/Usr/bin/xbstream
/Usr/bin/xtrabackup
/Usr/share/doc/percona-xtrabackup-2.2.9
/Usr/share/doc/percona-xtrabackup-2.2.9/COPYING
/Usr/share/man/man1/innobackupex.1.gz
/Usr/share/man/man1/xbcrypt.1.gz
/Usr/share/man/man1/xbstream.1.gz
/Usr/share/man/man1/xtrabackup.1.gz

Official help documentation

Http://form.percona.com/rs/percona/images/PerconaXtraBackup-2.2.9.pdf? AliId = 26490527

2. Complete backup

The backup must be connected to mysql. Related options include:
-Host = 127.0.0.1 specify the server ip address
-Port = 3306 specify the port

-Socket =/tmp/mysql. sock
You can use an administrator account or an account with minimum permissions for backup.

Grant user 'bkpuser' @ 'localhost' identified by '2013 ';
Grant reload, lock tables, replication client on *. * to 'bkpuser' @ 'localhost ';
Flush privileges;

For a complete backup, you need to specify the relevant options:
-Defaults-file =/usr/local/mysql/my. cnf: configuration file used
Specifies the Target Directory of the backup, for example,/tmp

Innobackupex -- user = root -- password = root -- socket =/tmp/mysql. sock -- defaults-file =/usr/local/mysql/my. cnf/tmp

3. Perform incremental backup

The first incremental backup must be performed on the basis of a complete backup.
-Incremental/root/one specifies the target directory for storing incremental backup
-Incremental-basedir =/tmp specifies the Directory of the full backup

Innobackupex -- user = root -- password = root -- socket =/tmp/mysql. sock -- defaults-file =/usr/local/mysql/my. cnf -- incremental/root/one -- incremental-basedir =/tmp

For the second incremental backup, you must specify the Directory of the last incremental backup.

-Incremental/root/two specifies the incremental backup directory.
-Incremental-basedir =/root/one knows the last incremental backup directory

Innobackupex -- user = root -- password = root -- socket =/tmp/mysql. sock -- defaults-file =/usr/local/mysql/my. cnf -- incremental/root/two -- incremental-basedir =/root/one

4. Restore an incremental backup

The created full backup cannot be used directly to restore the database. You need to prepare the data, including the replay of some committed transactions and rollback of uncommitted transactions.
-Apply-log options for backup recovery
-Use-memory = 4G: Set the memory used for data restoration, which can increase the preparation time.
Specifies the backup data directory

Innobackupex -- apply-log -- use-memory = 4G/tmp

To restore data, you must specify the configuration file. By default, the configuration file is restored to/var/lib/mysql, and the data directory set in my. cnf does not contain any files.

Innobackupex -- copy-back -- defaults-file =/usr/local/mysql/my. cnf/tmp