Innobackupex xtrabackup backup/restore in MySQL database

Large amount of data backup and restore, is always a difficult point. When MySQL is super 10G, it is slower to export with mysqldump. Recommend Xtrabackup here, this tool is much faster than mysqldump.

1, Xtrabackup Introduction

What's 1,xtrabackup?
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.
What 2,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.
Because Innobackupex supports Innodb,myisam, so this article says, how to use Innobackupex

Second, install Xtrabackup

1, download address
http://www.percona.com/downloads/XtraBackup/
2, install
According to the requirements, select a different version, I choose the RPM installation package, if the following error
[ Root@localhost xtrabackup]# RPM-IVH percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm
Warning: Percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm:header V4 dsa/sha1 Signature, key ID cd2efd2a:nokey
error:failed Depe Ndencies:
Perl (time::hires) is needed by percona-xtrabackup-2.2.4-5004.el6.x86_64
Workaround:

code is as follows

copy code

[root@localhost xtrabackup]# yum-y Install Perl perl-devel libaio libaio-devel perl-time-hires//Installation dependencies     [root@localhost xtrabackup]# RPM-IVH percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm  //Reinstall   Warning:percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm:header V4 dsa/sha1 Signature, key ID cd2efd2a:nokey  Pr Eparing ... ########################################### [100%]  1:percona-xtrabackup ##################### ###################### [100%]

Attention:
Xtrabackup 2.2.4 does not support MySQL 5.1.73, so if you want to use a new xtrabackup, use a high version of the MySQL database, or you will report the following error:
innobackupex:Error:Unsupported Server version: ' 5.1.73 '
Two, modify MY.CNF
Check to see if there are any datadir below mysqld, if not added

The code is as follows	Copy Code
[Mysqld] Datadir=/var/lib/mysql If you do not add, the following problems may occur when you restore the data, Xtrabackup:Error:Please set parameter ' DataDir ' Innobackupex:fatal error:no ' mysqld ' Group in MySQL options Innobackupex:fatal error:or No ' datadir ' option in the group ' mysqld ' in MySQL options

three, all database backup and restore
1, all database backups

The code is as follows

Copy Code

# Innobackupex--DEFAULTS-FILE=/ETC/MY.CNF--user=root/home/tank/backup/ If you report the following error: InnoDB:Error:log file./ib_logfile0 is of different size 5242880 bytes Innodb:than specified in the. cnf file 50331648 bytes! Innobackupex:Error:The xtrabackup child process has died At/usr/bin/innobackupex line 2679. Workaround: Add the following in the MY.CNF mysqld [Mysqld] Innodb_log_file_size = 5M

2, single database backup

The code is as follows	Copy Code
# Innobackupex--DEFAULTS-FILE=/ETC/MY.CNF--user=root--database=backup_test/home/tank/backup/

3, database backup and compression

The code is as follows	Copy Code
# Innobackupex--defaults-file=/etc/my.cnf--user=root--stream=tar/home/tank/backup | gzip >/home/tank/backup/' Date +%f_%h-%m-%s '. tar.gz

4, full data restore

code is as follows

copy code

#/etc/init.d/mysqld stop     //Stop mysql  # MV /var/lib/mysql/var/lib/mysql_bak  //Data Directory backup   # mkdir-p/var/lib/mysql //Rebuild Data directory   & nbsp The //--apply-log option command is to start the MySQL service on a backup   # Innobackupex--defaults-file=/etc/my.cnf--user=root- The commands for the apply-log/home/tank/backup/2014-09-18_16-35-12    //--copy-back option copy data from the backup directory, index, Log to my.cnf the initial position specified in the file   # Innobackupex--defaults-file=/etc/my.cnf--user=root--copy-back/home/tank/backup/ 2014-09-18_16-35-12    # chown-r mysql.mysql/var/lib/mysql //Change file Affiliation   #/etc/init.d/m Ysqld stop //start mysql

IV, incremental backup and restore
1, create the test database and table

The code is as follows	Copy Code
Create Database backup_test; Create a library CREATE TABLE ' backup ' (//creating tables ' id ' int (one) not NULL auto_increment, ' Name ' varchar ' not NULL DEFAULT ', ' Create_time ' timestamp not NULL DEFAULT Current_timestamp, ' del ' tinyint (1) Not NULL DEFAULT ' 0 ', PRIMARY KEY (' id ') ) Engine=myisam DEFAULT Charset=utf8 auto_increment=1;

2, incremental backup

The code is as follows	Copy Code
Mysql> INSERT into Backup (name) VALUES (' Tank '), (' Tank1 '); Inserting data # Innobackupex--DEFAULTS-FILE=/ETC/MY.CNF--user=root--incremental-basedir=/home/tank/backup/2014-09-18_16-35-

--incremental/home/tank/backup/
3, incremental backups on an incremental basis

The code is as follows	Copy Code
Mysql> INSERT into Backup (name) VALUES (' Tank2 '), (' Tank3 '), (' Tank4 '), (' Tank5 '), (' tank6 '); In the Insert data 2014-09-18_18-05-20 This directory, the directory that was generated by the last incremental backup # Innobackupex--DEFAULTS-FILE=/ETC/MY.CNF--user=root--incremental-basedir=/home/tank/backup/2014-09-18_18-05-20 --incremental/home/tank/backup/

To view an incremental backup record file:

The code is as follows

Copy Code

[root@localhost 2014-09-18_16-35-12]# Cat xtrabackup_checkpoints  //Full directory files   Backup_type = full-prepared  br> FROM_LSN = 0            //All-ready starting as 0  To_lsn = 23853959  Last_lsn = 23853959  Compact = 0    [root@localhost 2014-09-18_18-05-20] # cat xtrabackup_checkpoints //files in the first incremental backup directory   Backup_type = incremental  From_lsn = 23853959&nb Sp To_lsn = 23854112  Last_lsn = 23854112  Compact = 0    [root@localhost 2014-0 9-18_18-11-43]# Cat xtrabackup_checkpoints //second incremental backup directory files   Backup_type = incremental  From_ LSN = 23854112  To_lsn = 23854712  Last_lsn = 23854712  Compact = 0

After the incremental backup is done, delete the backup_test this database, drop DB backup_test, so that you can compare the restore after
4, the incremental restore

The code is as follows

Copy Code

# Innobackupex--defaults-file=/etc/my.cnf--user=root--apply-log--redo-only/home/tank/backup/2014-09-18_16-35-12 # Innobackupex--defaults-file=/etc/my.cnf--user=root--apply-log--redo-only/home/tank/backup/2014-09-18_16-35-12 --incremental-dir=/home/tank/backup/2014-09-18_18-05-20 # Innobackupex--defaults-file=/etc/my.cnf--user=root--apply-log--redo-only/home/tank/backup/2014-09-18_16-35-12 --incremental-dir=/home/tank/backup/2014-09-18_18-11-43

There are three directories here,
1),/home/tank/backup/2014-09-18_16-35-12, full backup directory
2),/home/tank/backup/2014-09-18_18-05-20, the directory generated by the first incremental backup
3),/home/tank/backup/2014-09-18_18-11-43, second incremental backup generated by the directory
The incremental restore here is not over yet, and the most important step is to do a full restore. Stop the database, delete the/var/lib/mysql, and in the restore.
The principle of incremental backup is to integrate the data under the incremental directory into the full variable directory, and then to restore the full amount of data.
Overall, Innobackupex speed, support Innodb,myisam, use is not very convenient, single-Library restore, or incremental backup restore, all the database restore, this unreasonable.
Five, innobackup commonly used parameter description
--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]
For backup data row 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.