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

Source: Internet
Author: User
Tags percona perl script

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.