Install a tutorial using Percona xtrabackup to back up Mysql _mysql

1. Install Xtrabackup

Installation method of Yum:

Automatic

$ RPM-UHV http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

Then you'll see:

Retrieving http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

preparing ...        ########################################### [100%]

  1:percona-release    ############################# ############## [100%]

Manual

[Percona]

Name = CentOS $releasever-percona

baseurl=http://repo.percona.com/centos/$releasever/os/$basearch/

Enabled = 1

gpgkey = File:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona

Gpgcheck = 1

Test Setup Library

Use Yum List | grep Percona to ensure installation

Yum List | grep Percona

The following message appears:

percona-release.x86_64           0.0-1            installed ...

percona-server-client-51.x86_64      5.1.47-rel11.1.51.rhel5   percona

percona-server-devel-51.x86_64       5.1.47-rel11.1.51.rhel5   percona

percona-server-server-51.x86_64      5.1.47-rel11.1.51.rhel5   Percona

percona-server-shared-51.x86_64      5.1.47-rel11.1.51.rhel5   percona

percona-server-test-51.x86_64       5.1.47-rel11.1.51.rhel5

percona

... xtrabackup.x86_64             1.2-22.rhel5        Percona

2. Make a full backup
backup must be connected to MySQL, the relevant options are

• –host=127.0.0.1 Specify server IP
• –port=3306 specified port
• –socket=/tmp/mysql.sock If this is a native you can specify only the path to the socket file

Backup can use the Administrator account, but also open a least-privileged account

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

For a full backup, the relevant options you need to specify are

• –DEFAULTS-FILE=/USR/LOCAL/MYSQL/MY.CNF Specifies the configuration file to use

Specify the target directory where the backup is stored, such as/tmp

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

3. Make incremental backups
for the first incremental backup, it needs to be done on the basis of a full backup

• –incremental/root/one Specify the target directory for incremental backups
• –incremental-basedir=/tmp Specify a full backup directory

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

For a second incremental backup, you need to specify the directory for the last incremental backup

• –incremental/root/two Specify this incremental backup directory
• –incremental-basedir=/root/one know 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 creation of a full backup cannot be used directly to restore the database, and some preparation is required for the data, including the replay of some committed transactions and the rollback of uncommitted transactions.

• –apply-log the option to restore a backup
• –USE-MEMORY=4G sets the amount of memory to use when preparing to restore data, which can increase the time it takes to prepare

Specify the data directory for the backup

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

Restore the data, you need to specify the configuration file, the default will revert to/var/lib/mysql, and ensure that there are no files in the data directory set in MY.CNF

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