Percona xtrabackup backup MySQL full library and specified database (full backup and incremental backup)

Xtrabackup Introduction

Percona Xtrabackup is open source free MySQL database hot backup software, it can be InnoDB and XTRADB storage Engine database non-blocking backup (for MyISAM backup also need to add a table lock). Xtrabackup supports all Percona servers, MySQL, mariadb, and drizzle.

Xtrabackup Advantages:

1, no need to stop the database for InnoDB hot standby

2. Incremental backup MySQL

3. Stream compression to other servers

4, it can be easier to create master-slave synchronization

5. Do not increase server load when backing up MySQL

Xtrabackup Installation

Here only describes the installation method under Ubuntu-12.04, the installation method of other systems, please refer to http://www.percona.com/doc/percona-xtrabackup/2.1/installation.html

Apt-key adv--keyserver keys.gnupg.net--recv-keys 1c4cbdcdcd2efd2a
Join in/etc/apt/sources.list:

Deb Http://repo.percona.com/apt Precise Main
Deb-src http://repo.percona.com/apt Precise Main
Performing Update and installation operations

Apt-get Update
Apt-get Install Percona-xtrabackup
Note: Precise is the Ubuntu-12.04 version code, if it is other system version, need to replace.

Xtrabackup Tools Introduction

After installing Xtrabackup, there will actually be several tools:

Innobackupex: This is actually a Perl script package for the following three tools, which can be backed up MyISAM, InnoDB, xtradb tables. However, you need to add a read lock when handling MyISAM.

Xtrabackup: A binary file compiled from C that can only back up InnoDB and xtradb data.

Xbcrypt: Data used to encrypt or decrypt a backup.

Xbstream: A compressed file used to decompress or compress the Xbstream format.

It is recommended to use Perl-encapsulated Innobackupex as a database backup because it is easier to use. So the following only describes the use of Innobackupex. Other references for use: http://www.percona.com/doc/percona-xtrabackup/2.1/manual.html

Innobackupex How to use

Complete options use execute Innobackupex–help, which only covers full backups and incremental backups and restores using common options.

Innobackupex Options This describes only the common parameters

–defaults-file the configuration file path of the database, feel local backup does not write can also, remote not tested.

–apply-log ready to start the MySQL service on a backup.

–copy-back copies data, indexes, and logs from the backup directory to the initial location specified in the My.cnf file.

–no-timestamp The time directory is not automatically generated when you create a backup, you can customize the backup directory name for example:/backups/mysql/base

–databases is used to specify the database to be backed up and how to use multiple library files: "Database1 Database2″

–incremental incremental backup based on full backup, followed by incremental backup storage directory path

–incremental-basedir=directory the full backup path directory that is required for an incremental backup or the directory path of the last incremental backup

–incremental-dir=directory directory path for incremental backup storage

–redo-only is used to prepare the incremental backup content to merge the data into the full backup directory, in conjunction with the –incremental-dir incremental backup directory.

–force-non-empty-directories if it is a specific library backup restore, do not need to delete the entire MySQL directory, only the specific library and related files can be restored with this parameter will not be error.

Other specific parameters can be consulted: http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_option_reference.html

Full Backup and restore

One, all databases

Backup:

Innobackupex--user=root--password=root--defaults-file=/etc/mysql/my.cnf/data/mysql_backup/full_backup
Restores:

Service MySQL Stop

Rm-rf/var/lib/mysql

Mkdir/var/lib/mysql

Innobackupex--apply-log/data/mysql_backup/full_backup--use-memory=1g--user=root--password=root

Innobackupex--copy-back/data/mysql_backup/full_backup

Chown Mysql:mysql-r/var/lib/mysql

Service MySQL Start
View the recovered database

Ii. Specifying a database

Backup:

If we were to back up the CentOS and AABB databases.

Innobackupex--user=root--password=root--defaults-file=/etc/mysql/my.cnf--databases= "CentOS aabb"/data/mysql_ backup/

This will generate a directory with time in/data/mysql_backup, and if you don't need to take the time, you can use the option –no-timestamp.

If you want to back up a compressed file, you can use the following statement:

Innobackupex--user=root--password=root--defaults-file=/etc/mysql/my.cnf--databases= "CentOS aabb"--no-timestamp- -stream=tar./| Gzip-> centos-aabb.bz.tar.gz
Restores:

If the path of the full backup is/data/mysql_backup/full_backup, if the full backup is a compressed file, it needs to be decompressed and restored first.

Service MySQL Stop

rm-rf/var/lib/mysql/ibdata*

rm-rf/var/lib/mysql/ib_logfile*

Rm-rf/var/lib/mysql/centos

Rm-rf/var/lib/mysql/aabb

Innobackupex--apply-log/data/mysql_backup/centos_aabb_full_backup

Innobackupex--copy-back--defaults-file=/etc/mysql/my.cnf/data/mysql_backup/centos_aabb_full_backup

Chown-r Mysql:mysql/var/lib/mysql

Service MySQL Start
Incremental backup and restore

Here in the whole library of incremental backup method is not much to say, just for a specific database to explain, in fact, is the same as the following parameters in the –databases option to remove it, interested can try it yourself.

Incremental backups are built on top of a full backup, so make sure you've backed up a full backup first.

Full backup:

Innobackupex--user=root--password=root--databases= "CentOS"--no-timestamp/data/mysql_backup/centos_full_backup
Now the full backup directory is/data/mysql_backup/centos_full_backup.

Incremental backup:

Incremental backup for the first time:

Innobackupex--incremental/data/mysql_backup/inc1--no-timestamp--incremental-basedir=/data/mysql_backup/centos_ Full_backup--user=root--password=root--defaults-file=/etc/mysql/my.cnf
Second incremental backup: You need to assign –incremental-basedir to the directory where you made the incremental backup last time, and you should know/DATA/MYSQL_BACKUP/INC1

Innobackupex–incremental/data/mysql_backup/inc2–no-timestamp–incremental-basedir=/data/mysql_backup/inc1–user= Root–password=root–defaults-file=/etc/mysql/my.cnf

Option –incremental is specified as an incremental backup –incremental-basedir option is the directory that specifies the last incremental backup (or the full backup if it is the first incremental backup).

Restores:

The restore operation for an incremental backup is a bit different from a full restore, and you must first use –apply-log–redo-only to operate on the full backup directory and all the incremental backup directories, and then you can restore the operation as if you were restoring the full backup.

Apply-log redo-only operations for each backup directory (including full backups)

Innobackupex--apply-log--redo-only/data/mysql_backup/centos_full_backup--user=root--password=root

Innobackupex--apply-log--redo-only/data/mysql_backup/centos_full_backup--incremental-dir=/data/mysql_backup/ INC1--user=root--password=root

Innobackupex--apply-log--redo-only/data/mysql_backup/centos_full_backup--incremental-dir=/data/mysql_backup/ INC2--user=root--password=root
See an article in the recovery of the incremental backup when the last read of the incremental record does not use the –redo-only parameter, tested, using this parameter after the recovery of the database is also running correctly, I am using 2.17 version xtrabackup, as for the previous version has not been tested. There's something that you can share here.

The following is the same as when restoring a full backup:

Service MySQL Stop

rm-rf/var/lib/mysql/ibdata*

rm-rf/var/lib/mysql/ib_logfile*

Rm-rf/var/lib/mysql/centos

Innobackupex--apply-log/data/mysql_backup/centos_full_backup--use-memory=1g--user=root--password=root

##--use-memory is able to define the amount of memory to be read at the time of backup, so that restore preparation can be done quickly.

Innobackupex--copy-back--defaults-file=/etc/mysql/my.cnf/data/mysql_backup/centos_full_backup

Chown Mysql:mysql-r/var/lib/mysql

Service MySQL Start
Now that all incremental restores are complete, you can log in to the database to see the recovered data.
We will introduce to you later the method of Xtrabackup backup InnoDB.

Percona xtrabackup backup MySQL full library and specified database (full backup and incremental backup)