Percona xtrabackup backup MySQL large database (full backup and incremental backup)

Percona xtrabackup backup MySQL large database (full and incremental backup) Article directory[Hide]

• Xtrabackup Introduction
• Xtrabackup Installation
• Xtrabackup Tools Introduction
• Innobackupex How to use
  • Full Backup and restore
  • Incremental backup and restore

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

1. Apt-key adv--keyserver keys.gnupg.net--recv-keys 1c4cbdcdcd2efd2a

Join in/etc/apt/sources.list:

1. Deb Http://repo.percona.com/apt Precise Main
2. Deb-src http://repo.percona.com/apt Precise Main

Performing Update and installation operations

1. Apt-get Update
2. 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.
Xtrabackup:
A binary file compiled from C that can only back up InnoDB and xtradb data.
Xbcrypt:
The data used to encrypt or decrypt the 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 perform Innobackupex--help, which only covers full backups and incremental backups and restores using common options.

Full Backup and restore

If we were to back up the CentOS and MySQL databases.
Backup:

1. Innobackupex--user=root--password=root--defaults-file=/etc/mysql/my.cnf--include= "centos.*|mysql.*"/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:

1. Innobackupex--user=root--password=root--defaults-file=/etc/mysql/my.cnf--include= "centos.*|mysql.*"-- No-timestamp--stream=tar./| Gzip-> www.centos.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.

1. Innobackupex--apply-log/data/mysql_backup/centos_full_backup
2. Innobackupex--copy-back--defaults-file=/etc/mysql/my.cnf/data/mysql_backup/centos_full_backup
3. Chown-r Mysql:mysql/var/lib/mysql

Incremental backup and restore

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

1. Innobackupex--user=root--password=root--include= "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:

1. Innobackupex--incremental/data/mysql_backup/inc/20130906--no-timestamp--incremental-basedir=/data/mysql_backup /centos_full_backup--user=root--password=root--defaults-file=/etc/mysql/my.cnf

Second Incremental backup:

1. Innobackupex--incremental/data/mysql_backup/inc/20130907--no-timestamp--incremental-basedir=/data/mysql_backup /inc/20130906--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)

1. Innobackupex--apply-log--redo-only/data/mysql_backup/centos_full_backup--user=root--password=root
2. Innobackupex--apply-log--redo-only/data/mysql_backup/centos_full_backup--incremental-dir=/data/mysql_backup/ inc/20130906--user=root--password=root
3. Innobackupex--apply-log--redo-only/data/mysql_backup/centos_full_backup--incremental-dir=/data/mysql_backup/ inc/20130907--user=root--password=root

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

1. innobackupex-1.5.1--apply-log/data/mysql_backup/centos_full_backup--use-memory=1g--user=root--password=root
2. Innobackupex--copy-back--defaults-file=/etc/mysql/my.cnf/data/mysql_backup/centos_full_backup

Reprint please indicate the article source: "https://www.centos.bz/2013/09/percona-xtrabackup-mysql-backup/"

Percona xtrabackup backup MySQL large database (full backup and incremental backup)