A course of backup and restore of large data using Innobackupex, xtrabackup in Mysql _mysql

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.

First, Xtrabackup Introduction

1. What is 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.

2, 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.

Because Innobackupex support Innodb,myisam, so this article says, how to use Innobackupex.

Second, install Xtrabackup

1, download the address

http://www.percona.com/downloads/XtraBackup/

2, installation

Depending on the requirements, choose a different version, I choose the RPM installation package, if the following error

Copy Code code as follows:

[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 dependencies:
Perl (Time::hires) is needed by percona-xtrabackup-2.2.4-5004.el6.x86_64

Solution:

Copy Code code as follows:

[root@localhost xtrabackup]# yum-y Install perl perl-devel libaio libaio-devel perl-time-hires//Installation Dependency pack
[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
Preparing ... ########################################### [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 '

Ii. Modification of MY.CNF

Check to see if there are any datadir below mysqld, if not added

Copy Code code as follows:

[Mysqld]
Datadir=/var/lib/mysql

If you do not add, the following problems may occur when you restore the data:

Copy Code code as follows:

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

Third, all database backup and restore

1, all database backup

Copy Code code as follows:

# Innobackupex--DEFAULTS-FILE=/ETC/MY.CNF--user=root/home/tank/backup/

If you report the following error:

Copy Code code as follows:

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:

Copy Code code as follows:

[Mysqld]
Innodb_log_file_size = 5M

2. Single database backup

Copy Code code as follows:

# Innobackupex--DEFAULTS-FILE=/ETC/MY.CNF--user=root--database=backup_test/home/tank/backup/

3. Database backup and compression

Copy Code code as follows:

# 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

Copy Code code as follows:

#/ETC/INIT.D/MYSQLD Stop//Shut down MySQL
# Mv/var/lib/mysql/var/lib/mysql_bak//Data Directory Backup
# mkdir-p/var/lib/mysql//Rebuild Data Directory

The command for the--apply-log option is to start the MySQL service on a backup
# Innobackupex--DEFAULTS-FILE=/ETC/MY.CNF--user=root--apply-log/home/tank/backup/2014-09-18_16-35-12

--copy-back option commands copy data from the backup directory, index, log to the initial location specified in the My.cnf 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/mysqld stop/start MySQL

IV. Incremental backup and restore

1. Create Test database and table

Copy Code code as follows:

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

Copy Code code as follows:

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-12 --incremental/home/tank/backup/

3, incremental backup on the basis of incremental

Copy Code code as follows:

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:

Copy code code as follows:

[Root@localhost 2014-09-18_16-35-12]# Cat xtrabackup_checkpoints//All files in the directory
Backup_type = full-prepared
FROM_LSN = 0/Full preparation starting at 0
TO_LSN = 23853959
LAST_LSN = 23853959
Compact = 0

[Root@localhost 2014-09-18_18-05-20]# Cat xtrabackup_checkpoints//files under the first incremental backup directory
Backup_type = Incremental
FROM_LSN = 23853959
TO_LSN = 23854112
LAST_LSN = 23854112
Compact = 0

[Root@localhost 2014-09-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 the DB backup_test, so that you can compare the restore.

4. Incremental restore

Copy Code code as follows:

# 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 first incremental backup generated by the directory
3),/home/tank/backup/2014-09-18_18-11-43, the 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 explanation

--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: the 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.