Percona xtrabackup backup MySQL (full backup and incremental backup)

Percona xtrabackup backup MySQL (full backup and incremental backup)

Xtrabackup Introduction

Percona Xtrabackup is an open source, free MySQL database hot backup software that can back up non-blocking databases for InnoDB and XTRADB storage engines (for MyISAM

Backup also requires 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 feature is for large data backup recovery use, database size is generally above or above 10G, for the small amount of MySQL library can be fully used

Mysqldump to solve, backup and recovery speed and operation is quick and easy, here the mysqldump command is not introduced.

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. But when it comes to handling MyISAM,

Add a read lock.

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

Add this parameter without 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 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

If you drop it, you 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 (if it is the first incremental backup, the

Directory for the full backup).

Restores:

The restore operation of an incremental backup is a bit different from a full restore, and you must first use the –apply-log–redo-only directory for the full backup and all the incremental backup directories into the

Operations, and then you can restore the full backup as you did.

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 incremental backup when the last read increment record is not used –redo-only parameter, after testing, using this parameter

The restored 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.

Percona xtrabackup backup MySQL (full backup and incremental backup)