Percona xtrabackup backup MySQL

Original: http://www.drupal001.com/2014/02/percona-xtrabackup-mysql/

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 is the characteristics of large data for backup recovery use, the database size is generally above or above the 10G, for a small amount of data MySQL library can be used to solve the mysqldump, backup and recovery speed and easy to operate quickly, The mysqldump command is not introduced here.

Xtrabackup Installation

Here only describes the installation method under Ubuntu-14.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

Add the following two sentences to/etc/apt/sources.list:

Deb http://repo.percona.com/apt trusty maindeb-src http://repo.percona.com/apt Trusty main

To perform the update and installation, before installing, use Apt-get to check the version of the Xtrabackup that will be installed to see if it supports your database version.

I ate this loss, the installation is percona-xtrabackup2.2.6, it is based on MySQL5.6.21, and I installed is MySQL5.6.22, although this

The two versions differ very little, but they are still concerned about errors when using Percona-xtrabackup. Percona-xtrabackup relies on Libdbd-mysql-perl, so

If there is an error installing percona-xtrabackup, you can try sudo apt-get-f install percona-xtrabackup.

sudo apt-get updatesudo aptinstall percona-xtrabackup

Note: Trusty is the Ubuntu-14.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.2/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

The configuration file path for the –defaults-file database.

–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.2/innobackupex/innobackupex_option_reference.html

Full Backup and restore

One, all databases

Backup:

Innobackupex--user=repl--password=123456--defaults-file=/etc/mysql/my.cnf  /data/mysql_backup/full _backup

Restores:

rm -rf/var/lib/mkdir /var/lib/--apply-log/data/mysql_backup/full_backup-- use-memory=1g--user=root--password=--copy-back/data/mysql_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=repl--password=123456--defaults-file=/etc/mysql/my.cnf--databases="MyDB " /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=repl--password=123456--defaults-file=/etc/mysql/my.cnf--databases="  CentOS aabb" --no-timestamp--stream=targzip -> Centos-aabb.bz. tar. gz

Restores:

RM RM rm -rf/var/lib/mysql/rm -rf/var/lib/mysql/--apply-log/data/mysql_backup/  --copy-back--defaults-file=/etc/mysql/my.cnf/data/mysql_backup/chown -R MySQL: mysql/var/lib/mysql service mysql start

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.

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=repl--password=123456--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=repl  --password=123456--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=-- Apply-log--redo-only/data/mysql_backup/centos_full_backup--incremental-dir=/data/mysql_backup/inc1-- User=root--password=--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:

RM RM rm -rf/var/lib/mysql/--apply-log/data/mysql_backup/centos_full_backup--use-memory=1g--user= Root--password=Root #--use---copy-back--defaults-file=/etc/mysql/my.cnf/data/mysql_ 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 (RPM)