Shulou(Shulou.com)06/01 Report--

Percona Xtrabackup backup mysql (full backup and incremental backup)

Introduction to Xtrabackup

Percona XtraBackup is an open source free MySQL database hot backup software that can back up databases of InnoDB and XtraDB storage engines without blocking (for MyISAM

A table lock is also required for the backup of XtraBackup supports all Percona Server, MySQL, MariaDB, and Drizzle.

XtraBackup advantages:

1. There is no need to stop the database for InnoDB hot backup.

2. Incremental backup MySQL

3. Stream compression to transfer to other servers

4. it is relatively easy to create master-slave synchronization.

5. Backing up MySQL will not increase the server load.

The characteristic of Xtrabackup is for backup and recovery of big data. The database size is generally above G or above 10G, and it can be used for Mysql libraries with small amount of data.

Mysqldump to solve the problem, backup and recovery is fast and easy to operate, here the mysqldump command will not be introduced.

Xtrabackup installation

Only the installation method under Ubuntu-12.04 is introduced here. For installation methods 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

Perform update and installation operations

Apt-get update

Apt-get install percona-xtrabackup

Note: precise is the version code of Ubuntu-12.04, if it is another system version, it needs to be replaced.

Introduction to Xtrabackup tools

After installing XtraBackup, there are actually several tools:

Innobackupex: this is actually a perl script wrapper for the following three tools that can back up MyISAM, InnoDB, and XtraDB tables. But when dealing with Myisam, you need to

Add a read lock.

Xtrabackup: a binary file compiled by C that can only back up InnoDB and XtraDB data.

Xbcrypt: used to encrypt or decrypt backed-up data.

Xbstream: used to extract or compress compressed files in xbstream format.

It is recommended to use perl encapsulated innobackupex for database backup because it is easier to use. So here is only the use of innobackupex. Other references for use:

Http://www.percona.com/doc/percona-xtrabackup/2.1/manual.html

How to use innobackupex

For complete options, please execute innobackupex-help. Here we only introduce full backup and incremental backup and restore using common options.

Innobackupex Options only describes the common parameters here.

-the configuration file path of the defaults-file database. It is OK if the local backup is not written, and it has not been tested remotely.

-apply-log is 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 does not automatically generate a time directory when creating 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. How to use multiple library files: "database1 database2"

-incremental performs incremental backup on the basis of full backup, followed by incremental backup storage directory path

-full backup path directory required for incremental-basedir=DIRECTORY incremental backup or directory path for the last incremental backup

-the directory path of incremental-dir=DIRECTORY incremental backup storage

-redo-only is used to prepare incremental backup content to merge data into a full backup directory, together with-incremental-dir incremental backup directory.

-force-non-empty-directories if it is a backup and restore of a specific library, it is not necessary to delete the entire mysql directory, but only the files of the specific library and related files.

If you add this parameter, the error will not be reported.

For other specific parameters, please see http://www.percona.com/doc/percona-.

Xtrabackup/2.1/innobackupex/innobackupex_option_reference.html

Full backup and restore

I. all databases

Backup:

Innobackupex-user=root-password=root-defaults-file=/etc/mysql/my.cnf / data/mysql_backup/full_backup

Restore:

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 restored database

2. Specify the database

Backup:

If we want to back up 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. If you don't need time, you can use the option-no-timestamp.

If you want to back up as 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

Restore:

If the path to the full backup is / data/mysql_backup/full_backup, if the full backup is a compressed file, you need to unzip it and then restore it.

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, I won't talk about the incremental backup method of the whole database, but will only explain it for a specific database. In fact, the same thing is to put the-databases option in the following parameters.

You can drop it, and you can try it yourself if you are interested.

Incremental backups are based on full backups, so make sure you have a full backup first.

Full backup:

Innobackupex-user=root-password=root-databases= "centos"-no-timestamp

/ data/mysql_backup/centos_full_backup

The directory for the full backup is now / data/mysql_backup/centos_full_backup.

Incremental backup:

First incremental backup:

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 specify-incremental-basedir to the directory where the last incremental backup was done. You should know / data/mysql_backup/inc1 here.

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 for incremental backup-incremental-basedir option is to specify the directory of the last incremental backup (if this is the first incremental backup, then

Is the directory of the full backup).

Restore:

The restore operation of an incremental backup is a little different from a full restore. First, you must use-apply-log-redo-only to enter the full backup directory and all incremental backup directories.

Line, and then you can do what you did when you restored a full backup.

Apply-log redo-only each backup directory (including full backup)

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

I saw an article when restoring an incremental backup that mentioned not using the-redo-only parameter when doing the last read incremental record. After testing, after using this parameter,

The recovered databases are also working correctly. I used version 2.17 of xtrabackup, but I haven't tested the previous version. If you know anything, you can share it 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 can define the amount of memory when reading backups in order to quickly complete the preparation for restore.

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 the incremental recovery is complete, you can log in to the database to view the recovered data.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.