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
Bash
Yum Install Libaio Libaio-devel
Compile and install Xtrabackup
Bash
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/
Bash
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.
Bash
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.
CP LIBTAR-1.2.11/LIBTAR/TAR4IBD/USR/BIN/TAR4IBD
Ln-sf/usr/local/webserver/mysql/bin/mysql/usr/bin/mysql
three. Backup and recovery use of xtrabackup
A, xtrabackup commonly used parameter options are as follows:
Bash
--defaults-file=#
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.
--defaults-extra-file=#
If this parameter is used, the configuration file specified here is read again after reading the global configuration file
--target-dir=name
directory path for backup files
--backup
Implement Backup to Target-dir
--prepare
Implement preparation of backup files prior to recovery (generate InnoDB log file)
--print-param
Print required parameters for backup or restore
--use-memory=#
This parameter is used when prepare to control the amount of memory used by the InnoDB instance at prepare time
--suspend-at-end
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
--throttle=#
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
--log-stream
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.
--incremental-lsn=name
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
--incremental-basedir=name
This parameter is used at backup time, backing up a backup set that is at a specified location in this parameter. New IDB pages
--incremental-dir=name
This parameter is used when prepare, specifying the storage path of the. delta files and log files that are generated when prepare.
--tables=name
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
--datadir=name
The data file directory of the MySQL database.
B, normal backup mode
A) normal backup (full volume backup)
Bash
Copy Code code as follows:
Mkdir-p/data0/backup/mysql
xtrabackup_55--defaults-file=/data0/mysql/my.cnf--backup--target-dir=/data0/backup/mysql/
Cp-r/data0/mysql/data/testinnodb//data0/backup/mysql/
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
Bash
xtrabackup_55--defaults-file=/data0/mysql/my.cnf--prepare--target-dir=/data0/backup/mysql/
Copy the corresponding database table structure from the backup directory to the default data directory
Bash
Cp-r/data0/backup/mysql/testinnodb//data0/mysql/data/
Delete the corresponding data files in the default data directory and copy the backed-up data files to the default data directory
Bash
rm/data0/backup/mysql/ib*
cp/data0/backup/mysql/ib*/data0/mysql/data/
Modify Data Directory Permissions
Bash
Chown-r Mysql:mysql/data0/mysql/data
Restart MySQL
Bash
/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
Bash
Copy Code code as follows:
#建立备份目录
Mkdir-p/data0/backup/mysql/base
#建立一个全量备份
xtrabackup_55--defaults-file=/data0/mysql/my.cnf--backup--target-dir=/data0/backup/mysql/base
#生成的备份数据文件
ls/data0/backup/mysql/base/
Ibdata1 xtrabackup_checkpoints Xtrabackup_logfile
#备份数据库表结构
Cp-r/data0/mysql/data/testinnodb//data0/backup/mysql/
Incremental backups based on this full amount of backup
Bash
Copy Code code as follows:
#建立备份目录
Mkdir-p/data0/backup/mysql/delta
#建立一个增量备份
xtrabackup_55--defaults-file=/data0/mysql/my.cnf--backup--target-dir=/data0/backup/mysql/delta-- Incremental-basedir=/data0/backup/mysql/base
#生成的备份数据文件
ls/data0/backup/mysql/delta/
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.
Bash
xtrabackup_55--defaults-file=/data0/mysql/my.cnf--prepare--target-dir=/data0/backup/mysql/base
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
Bash
Cp-r/data0/backup/mysql/testinnodb//data0/mysql/data/
Delete the corresponding data files in the default data directory and copy the backed-up data files to the default data directory
Bash
rm/data0/backup/mysql/ib*
cp/data0/backup/mysql/ib*/data0/mysql/data/
Modify Data Directory Permissions
Bash
Chown-r Mysql:mysql/data0/mysql/data
Restart MySQL
Bash
/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
Bash
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
--defaults-file parameters of the same xtrabackup
--apply-log
Encapsulation of the--prepare parameters for Xtrabackup
--copy-back
DataDir to copy the backup data file to the MySQL server when doing data recovery;
--remote-host=hostname
The backup data is stored on the process server via SSH;
--stream=[tar]
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.
--tmpdir=directory
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.
--use-memory=#
This parameter is used when prepare to control the amount of memory used by the InnoDB instance at prepare time
--throttle=ios
--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;
--compress[=level]
The backup data in line compression, only support Ibbackup,xtrabackup has not been implemented;
--include=regexp
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*".
--databases=list
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;
--uncompress
Unzip the backup data file, support Ibbackup,xtrabackup has not yet implemented the function;
--slave-info,
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
--socket=socket
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
Bash
Mkdir-p/data0/backup/mysql
1. Normal backup
Bash
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
Bash
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
Bash
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 192.168.1.101/data0/directory.
Bash
Innobackupex--database= "Testinnodb test"--user=root--defaults-file=/data0/mysql/my.cnf--no-lock--stream=tar/ Data0/backup/mysql/|ssh root@192.168.1.101 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
Bash
Tar Xvzfi mike.tar.gz
Specifies the directory of the Xtrabackup_logfile files needed to recover the backup
Bash
Innobackupex--apply-log--user=root--defaults-file=/data0/mysql/my.cnf--no-lock/data0/backup/mysql
Copy data, indexes, logs from the backup directory to the MY.CNF file to specify the initial data directory location.
Bash
Innobackupex--copy-back--user=root--defaults-file=/data0/mysql/my.cnf--no-lock/data0/backup/mysql
Modify Data Directory Permissions
Bash
Chown-r Mysql:mysql/data0/mysql/data
Restart MySQL
Bash
/data0/mysql/mysql restart
v. Reference documentation
http://www.google.com
Http://www.linuxidc.com/Linux/2011-05/35410.htm
Http://blogold.chinaunix.net/u4/122567/showart_2537465.html
