Xtrabackup Use guide InnoDB data Backup tool _mysql

Source: Internet
Author: User
Tags mkdir mysql version prepare uncompress ssh percona perl script
First, Xtrabackup Introduction

A, Xtrabackup is what

Xtrabackup is a tool for data backup of InnoDB, which supports online hot backup (without affecting data reading and writing during backup), is a good substitute for the commercial Backup tool InnoDB Hotbackup.

Xtrabackup has two main tools: Xtrabackup, Innobackupex

1, Xtrabackup can only back up InnoDB and xtradb two kinds of data tables, but not backup MyISAM datasheet
2, Innobackupex is referred to the InnoDB hotbackup innoback script modified. Innobackupex is a Perl script encapsulation that encapsulates xtrabackup. The main purpose is to backup the InnoDB and MyISAM engine tables at the same time, but a read lock is required to handle the MyISAM. and added some options to use. If Slave-info can record backup recovery, as a slave need some information, based on this information, can easily use Backup to redo slave.
3. Official documents: Http://www.percona.com/docs/wiki/percona-xtrabackup:start

B, what Xtrabackup can do

Online (hot) back up the entire library of InnoDB, xtradb tables
Incremental backups based on Xtrabackup's last full library backup (InnoDB only)
Generate backups as streams, which can be saved directly to remote machines (useful when local hard disk space is low)

The MySQL database itself provides tools that do not support true incremental backups, and binary log recovery is a point-in-time (point-in-time) recovery rather than an incremental backup. The Xtrabackup tool supports incremental backups of the InnoDB storage Engine, which works as follows:

(1) Complete a full backup first and record the LSN (log Sequence number) of the checkpoint at this point.
(2) in the process incremental backup, compare the LSN of each page in the table space to the LSN at the time of the last backup, and if so, back up the page and record the LSN of the current checkpoint.

First, the final checkpoint ("last checkpoint LSN") is found and recorded in the logfile, and then the logfile to Xtrabackup_logfile from the location of the LSN is started to copy InnoDB; Start copying all the data files. ibd; The copy logfile is not stopped until all data files have been copied.

Because the logfile inside records all the data modification situation, therefore, immediately in the backup process the data file has been modified, restores still can through the resolution Xtrabackup_logfile to maintain the data consistent.

C, Xtrabackup Backup principle

Xtrabackup is based on the InnoDB crash-recovery function. It will copy the InnoDB data file, because the table is not locked, the copy is inconsistent, in the recovery of the use of crash-recovery, so that data recovery consistent.

InnoDB maintains a redo log, also known as the transaction log, which contains all the changes to the InnoDB data. When the InnoDB starts, it checks the data file and transaction log first, and does two steps:

Xtrabackup copies the InnoDB data one page at a time of backup and does not lock the table, while Xtrabackup has another thread monitoring the transactions log and copying the changed log pages once the log changes. Why rush to copy away? Because the transactions log file size is limited, it will be written from the beginning, so the new data may overwrite the old data.

During the prepare process, Xtrabackup uses the copied transactions log to crash recovery the backed-up innodb data file.

D, Implementation Details

Xtrabackup opens the InnoDB data file in Read-write mode and copies it. In fact, it will not modify this file. That is, users running xtrabackup must have read and write access to the InnoDB data file. The Read-write mode is used because Xtrabackup uses its built-in InnoDB library to open the file, and the InnoDB library is RW when it opens the file.

Xtrabackup to copy a large amount of data from the file system, it uses posix_fadvise () as much as possible to tell the OS not to cache read data to improve performance. Because the data will not be reused, the OS is not so smart. If you want to cache, a few g of data, the OS's virtual memory will cause a lot of pressure, other processes, such as mysqld is very likely to be swap out, so the system will be greatly affected.

In the process of backing up InnoDB page, Xtrabackup reads 1MB of data each time, 1mb/16kb=64 a page. This is not configurable. After reading the 1MB data, xtrabackup the 1MB data one page at a time, using the InnoDB buf_page_is_corrupted () function to check if the data on this page is normal, and if the data is not normal, reread the page and reread it 10 times. If it fails, the backup fails and exits. When copying the transactions log, read and write 512KB of data at a time. Also cannot be configured.

Second, xtrabackup installation

This article through the source method installs the Xtrabackup, the newest version is 1.6.

Xtrabackup and the general source code to build a different way, it used in the MySQL source codes on the construction of the patch. The download of the source package is larger because this package is packed with two MySQL source that is needed for compiling, a 5.1.56, a 5.5.10.

To install the dependent packages required for compilation


Yum Install Libaio Libaio-devel

Compile and install Xtrabackup

Copy Code code as follows:

wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-1.6/source/xtrabackup-1.6.tar.gz
Tar xvf xtrabackup-1.6.tar.gz
CD xtrabackup-1.6
Utils/build.sh innodb55

Note: build.sh is the compiled script provided in the source package, MySQL 5.1 available parameters innodb51_builtin.

After the compilation is successful, a xtrabackup program is generated in the corresponding MySQL version directory, and the path I generate here is mysql-5.5.10/storage/innobase/xtrabackup/

LS mysql-5.5.10/storage/innobase/xtrabackup/
Makefile xtrabackup.c xtrabackup_innodb55 XTRABACKUP.O

Copy the corresponding file and build the soft link under/usr/bin, the reason for this is to meet the needs of the Innobackupex script.

Copy Code code as follows:

CP mysql-5.5.10/storage/innobase/xtrabackup/xtrabackup_innodb55/usr/bin/xtrabackup_55
CP Innobackupex/usr/bin/innobackupex
#备份时如果打包就需要这个命令, not required.

   three. Backup and recovery use of xtrabackup

A, xtrabackup commonly used parameter options are as follows:


The path to the default profile, and if not, Xtrabackup will find the profile/etc/my.cnf,/etc/mysql/my.cnf,/USR/LOCAL/ETC/MY.CNF, ~/from the following location in turn. My.cnf and reads the [mysqld] and [xtrabackup] configuration segments in the configuration file. In [mysqld] You only need to specify DataDir, Innodb_data_home_dir, Innodb_data_file_path, Innodb_log_group_home_dir, innodb_log_files_in _group, innodb_log_file_size6 parameters can let xtrabackup normal work.

If this parameter is used, the configuration file specified here is read again after reading the global configuration file

directory path for backup files

Implement Backup to Target-dir

Implement preparation of backup files prior to recovery (generate InnoDB log file)

Print required parameters for backup or restore

This parameter is used when prepare to control the amount of memory used by the InnoDB instance at prepare time

Creates a xtrabackup_suspended file in the Target-dir directory, suspends the xtrabackup process, and synchronizes the changes in the data file to the backup file until the user manually deletes the xtrabackup_suspended file

Io times per second, limiting the amount of I/O operations used in backup, minimizing the impact of backups on the normal business of the database

This parameter is used at backup time to output the Xtrabackup_logfile content to the standard output, and the Suspend-at-end parameter is used automatically when using this parameter, which is used by the Innobackupex script's stream mode.

A copy of the LSN at an incremental backup is more than the specified value for this parameter. New IBD pages, which LSN can look at the previous backup set's Xtrabackup_checkpoints file

This parameter is used at backup time, backing up a backup set that is at a specified location in this parameter. New IDB pages

This parameter is used when prepare, specifying the storage path of the. delta files and log files that are generated when prepare.

Used when backing up file-per-table types of data files, using regular expressions to specify the InnoDB tables that need to be backed up

The data file directory of the MySQL database.

B, normal backup mode

A) normal backup (full volume backup)

Copy Code code as follows:


Note: Xtrabackup only backs up data files and does not back up the data table structure (. frm), so you should back it up manually so that you can use it xtrabackup restore.

Full-volume backup recovery

Implement the preparation of backup files before recovery



Copy the corresponding database table structure from the backup directory to the default data directory



Delete the corresponding data files in the default data directory and copy the backed-up data files to the default data directory



Modify Data Directory Permissions


Chown-r Mysql:mysql/data0/mysql/data

Restart MySQL


/data0/mysql/mysql restart

b) Incremental Backups

Incremental backup Benefits:

1, the database is too large do not have enough space full amount of backup, for incremental backup effectively save space, and high efficiency.
2, support hot backup. The backup process does not lock the table, is not limited by time, and does not affect user use.
3, daily backup produces only a small amount of data, remote backup transmission more convenient. At the same time save space.
4, backup recovery based on file operations, reduce the risk of direct database operations.
5, the backup efficiency is higher, restores the efficiency to be higher.

This I study n long time did not succeed, the reason has not yet found. I test the environment of the MySQL version is 5.5.11, xtrabackup_55 is compiled according to MySQL5.5.9 source code, do not know and this has a relationship? to be verified! Only the implementation steps are sorted out here.

Incremental backup and simulated recovery steps:

Do a full-volume backup first, and incremental backups need to be based on this

Copy Code code as follows:

Ibdata1 xtrabackup_checkpoints Xtrabackup_logfile

Incremental backups based on this full amount of backup

Copy Code code as follows:

xtrabackup_55--defaults-file=/data0/mysql/my.cnf--backup--target-dir=/data0/backup/mysql/delta-- Incremental-basedir=/data0/backup/mysql/base
Ibdata1.delta xtrabackup_checkpoints Xtrabackup_logfile

Note: Under the incremental backup directory, the data files are terminated with the. Delta. An incremental backup backs up only the page that has been modified since the last full backup, so incremental backups take up only a small amount of space. Incremental backups can be incremental on the basis of incremental backups. Incremental backup directories need to be modified each time. For example, the second increment is changed to/DATA0/BACKUP/MYSQL/DELTA2)

Incremental backup Recovery process:

First, you need to do a prepare operation on a full, incremental backup, respectively.


xtrabackup_55--defaults-file=/data0/mysql/my.cnf--prepare--target-dir=/data0/backup/mysql/base-- incremental-dir=/data0/backup/mysql/delta/

Copy the corresponding database table structure from the backup directory to the default data directory



Delete the corresponding data files in the default data directory and copy the backed-up data files to the default data directory


Modify Data Directory Permissions

Chown-r Mysql:mysql/data0/mysql/data

Restart MySQL

/data0/mysql/mysql restart

Iv. Backup and recovery use of Innobackupex

A, data backup

Innobackupex is generally used directly because it can back up the tables of the InnoDB and MyISAM engines at the same time. It is important to note that the DataDir parameter in MY.CNF must be specified, xtrabackup_55 is positioned to locate the InnoDB data file.

Innobackupex syntax


Innobackup [--sleep=ms] [--compress[=level]] [--include=regexp] [--user=name] [--password=word]
[--port=port] [--socket=socket] [--no-timestamp] [--ibbackup=ibbackup-binary] [--slave-info] [--stream=tar]
[--defaults-file=my. CNF] [--databases=list] [--remote-host=hostname] Backup-root-dir

Innobackup--apply-log [--USE-MEMORY=MB] [--uncompress] [--defaults-file=my. CNF] [--ibbackup=ibbackup-binary] Backup-dir

Innobackup--copy-back [--defaults-file=my. CNF] Backup-dir

Description of the parameters:

--defaults-file parameters of the same xtrabackup

Encapsulation of the--prepare parameters for Xtrabackup

DataDir to copy the backup data file to the MySQL server when doing data recovery;

The backup data is stored on the process server via SSH;


Back up the file output format, using TAR4IBD in tar, which can be obtained in xtarbackup binary files. If there is a specified--stream=tar at backup, the directory of TAR4IBD files must be in $path ( Because the TAR4IBD is used to compress, the file can be obtained in the Xtrabackup binary package.

When using the parameter Stream=tar backup, your xtrabackup_logfile may be temporarily placed in the/tmp directory, if you back up the concurrent write larger words xtrabackup_logfile may be very large (5g+), it is likely to fill your TMP directory, which you can use to resolve this problem by specifying a directory with the parameter--tmpdir.


The directory temporarily stored by the transaction log when there is a specified--remote-host or--stream, by default, in the temporary directory specified in the MySQL configuration file tmpdir

--redo-only--apply-log Group,

Force backup log only redo, skip rollback. This is necessary when making incremental backups.


This parameter is used when prepare to control the amount of memory used by the InnoDB instance at prepare time


--throttle parameters of the same xtrabackup

--sleep= is for ibbackup use, specify each backup 1M data, process stop copying how many milliseconds, also in order to minimize the impact on the normal business during backup, you can view the Ibbackup manual;

The backup data in line compression, only support Ibbackup,xtrabackup has not been implemented;

The encapsulation of the Xtrabackup parameter--tables also supports Ibbackup. Backup contains the library table, for example:--include= "test.*", meaning that you want to back up all the tables in the test library. If full backups are required, this argument is omitted, and if you need to back up the 2 tables under test Library: Test1 and Test2, write:--include= "Test.test1|test.test2". You can also use wildcard characters, such as:--include= "test.test*".

Lists the databases that need to be backed up, and if this parameter is not specified, all database containing MyISAM and INNODB tables will be backed up;

Unzip the backup data file, support Ibbackup,xtrabackup has not yet implemented the function;

Backup from the library, plus the--slave-info backup directory will generate more than one Xtrabackup_slave_info file, where the main log file and offset, the contents of the file is similar to: "Change MASTER to Master_log_file=", Master_log_pos=0

Specify where Mysql.sock is located so that the backup process logs on to MySQL.

The first need to establish a backup directory before you backup



1. Normal backup


Innobackupex--database= "Testinnodb test"--user=root--no-lock--defaults-file=/data0/mysql/my.cnf/data0/backup/ MySQL 2>/tmp/mysqlbackup.log

Note: The backup path of the database Testinnodb and test using the MySQL root user is/data0/backup/mysql/. After the backup is successful, a timestamp-name backup directory will be created in the directory. The No-lock parameter is used to specify that the table is not locked during backup. 2>/tmp/mysqlbackup.log saves the output information from the backup process to/tmp/mysqlbackup.log.

2. Package Backup


Innobackupex--database= "Testinnodb test"--user=root--defaults-file=/data0/mysql/my.cnf--no-lock--stream=tar/ Data0/backup/mysql/2>/tmp/innobackup.log 1>/data0/backup/mysql/mike.tar

Note: This backup time will be longer, mainly to perform the compression operation. This way the pack needs to be decompressed using the tar izxvf. The parameter-I must be added.

3, compressed backup


Innobackupex--database= "Testinnodb test"--user=root--defaults-file=/data0/mysql/my.cnf--no-lock--stream=tar/ Data0/backup/mysql/2>/tmp/innobackup.log|gzip>/data0/backup/mysql/mike.tar.gz

   4. Backup to remote server
Back up the data under the current machine to the


Innobackupex--database= "Testinnodb test"--user=root--defaults-file=/data0/mysql/my.cnf--no-lock--stream=tar/ Data0/backup/mysql/|ssh root@ Cat ">"/data0/backup.tar

B, data recovery

In the case of compressed backup data, the recovery method for normal and packaged backups is similar to this one, not for example.

Unpack backup Data


Tar Xvzfi mike.tar.gz

Specifies the directory of the Xtrabackup_logfile files needed to recover the backup



Copy data, indexes, logs from the backup directory to the MY.CNF file to specify the initial data directory location.



Modify Data Directory Permissions


Chown-r Mysql:mysql/data0/mysql/data

Restart MySQL


/data0/mysql/mysql restart

   v. Reference documentation


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.