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

Original address: HTTP://WWW.TUICOOL.COM/ARTICLES/RZRNQ2

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

--recv-1c4cbdcdcd2efd2a

Join in/etc/apt/sources.list:

23	Deb http://repo.percona.com/apt precise maindeb-src http://repo.percona.com/apt Precise Main

Performing Update and installation operations

23	Updateapt-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:

--defaults-file=/etc/mysql//data/mysql_backup/full_backup

Restores:

78910111213141516171819

Service MySQL StopRm-rf/Var/lib/mysqlMkdir/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.

910111213141516171819202122232425

Service MySQL StopRm-rf/Var/lib/mysql/ibdata*Rm-rf/Var/lib/mysql/ib_logfile*Rm-rf/Var/lib/mysql/centosRm-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 /c12>

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:

--user--databases=/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)

34567

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:

910111213141516171819202122232425

Service MySQL StopRm-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 can define the memory size when the backup is read, To quickly complete the restore preparation.  innobackupex --copy-back --defaults-file=/etc/mysql/ MY.CNF /data/ Mysql_backup/centos_full_backup chown mysql:mysql /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 library and specified database (full backup and incremental backup)