MySQL Innobackupex xtrabackup Big Data Backup restore

Original: http://blog.51yip.com/mysql/1650.html

Sea Eagle

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

I. Introduction of Xtrabackup

What is 1,xtrabackup?

Xtrabackup is a innodb to do data backup tools, Support Online hot backup (backup without affecting the data read and write), is a commercial backup tool InnoDB hotbackup a good alternative.

Xtrabackup has two main tools: Xtrabackup, Innobackupex

1, Xtrabackup can only back up InnoDB and xtradb two kinds of data tables, but cannot back up MyISAM data table

2, Innobackupex is a reference to the InnoDB hotbackup innoback script modified. Innobackupex is a Perl script package that encapsulates xtrabackup. The main purpose is to back up 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 slave need some information, according to this information, can easily use Backup to redo slave.

What 2,xtrabackup can do:

Online (Hot) backup InnoDB, xtradb tables for the entire library
Incremental backup based on Xtrabackup's last backup of the whole library (InnoDB only)
Generate backups as streams that can be saved directly to a remote machine (useful when the hard disk space is low)

The MySQL database itself provides tools that do not support true incremental backups, and binary log recovery is point-in-time (Point-in-time) recovery instead of incremental backups.
The Xtrabackup tool supports incremental backups of the InnoDB storage engine and works as follows:

(1) Complete a full backup first and record the LSN (log Sequence number) of the checkpoint at this point.

(2) When a process incremental backup, compares the LSN of each page in the tablespace to the LSN at the time of the last backup, and if so, backs up the page and logs the LSN of the current checkpoint.
First, the final checkpoint ("last checkpoint LSN") is found and recorded in logfile, and the logfile to Xtrabackup_logfile of InnoDB is started from the position of the LSN; Start copying all data files. ibd; Copy logfile is not stopped until the end of the copy of all data files.
Because the logfile inside records all the data modification situation, so, in the backup process, the data file has been modified, still can be restored by parsing xtrabackup_logfile to keep the data consistent.

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

Second, install Xtrabackup

1,

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

2, installation

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

[Email protected] 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

Workaround:

View copy print?

1. [Email protected] xtrabackup]# yum-y install perl perl-devel libaio libaio-devel perl-time-hires perl-dbd-mysql //Installation Dependency Packages
3. [[email protected] xtrabackup]# RPM-IVH percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm //Reinstall
4. Warning:percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm:header V4 dsa/sha1 Signature, key ID Cd2efd2a:nokey
5. Preparing ... ########################################### [100%]
6. 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, you need to use a high-version MySQL database, or you will be reported the following error:

innobackupex:Error:Unsupported Server version: ' 5.1.73 '

Second, modify the MY.CNF

Check mysqld below there is no datadir, if not added

View copy print?

1. [Mysqld]
2. datadir=/Var/lib/mysql

If you do not add, you may have the following problems 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 Group ' mysqld ' in MySQL options

Three, all database backup and restore

1, all database backup

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

If the following error is reported:

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 had died At/usr/bin/innobackupex line 2679.

Workaround: Add the following to the MY.CNF mysqld

1. [Mysqld]
2. Innodb_log_file_size = 5M

2, single database backup

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

3, database backup and compression

View copy print?

1. # 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

View copy print?

2. #/etc/init.d/mysqld Stop //stop MySQL
4. # MV/var/lib/mysql/var/lib/mysql_bak //Data Catalog backup
6. # mkdir-p/var/lib/mysql //Rebuild Data directory
10. The command for the--apply-log option is to start the MySQL service on a backup
12. # Innobackupex--DEFAULTS-FILE=/ETC/MY.CNF--user=root--apply-log/home/tank/backup/2014-09-18_16-35-12
16. The--copy-back command copies data from the backup directory, index, and log to the initial location specified in the My.cnf file.
18. # Innobackupex--DEFAULTS-FILE=/ETC/MY.CNF--user=root--copy-back/home/tank/backup/2014-09-18_16-35-12
22. # Chown-r mysql.mysql/var/lib/mysql //change file belongs to
24. #/etc/init.d/mysqld Stop //start MySQL

Four, incremental backup and restore

1. Create a test database and table

View copy print?

1. Create Database backup_test; //Create a library
3. CREATE TABLE ' backup ' ( //Creating Tables
4. ' id ' int (one) not NULL auto_increment,
5. ' Name ' varchar (a) not NULL DEFAULT ' ',
6. ' Create_time ' timestamp not NULL DEFAULT Current_timestamp,
7. ' del ' tinyint (1) not NULL DEFAULT ' 0 ',
8. PRIMARY KEY (' id ')
9. ) Engine=myisam DEFAULT Charset=utf8 auto_increment=1;

2, incremental backup

View copy print?

1. Mysql> INSERT into Backup (name) VALUES ('tank '), (' tank1 '); //Insert data
3. # 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 an incremental basis

View copy print?

1. Mysql> INSERT into Backup (name) VALUES (' Tank2 '), (' Tank3 '), ('tank4 '),(' Tank5 '), (' Tank6 '); //When inserting data
3. 2014-09-18_18-05-20 This directory, the directory generated by the last incremental backup
4. # 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 the incremental backup record file:

View copy print?

2. [[email protected] 2014-09-18_16-35-12]# cat xtrabackup_checkpoints //Full directory file
4. Backup_type = full-prepared
6. FROM_LSN = 0 //fully prepared starting at 0
8. TO_LSN = 23853959
10. LAST_LSN = 23853959
12. Compact = 0
16. [[email protected] 2014-09-18_18-05-20]# cat xtrabackup_checkpoints //file under the first incremental backup directory
18. Backup_type = Incremental
20. FROM_LSN = 23853959
22. TO_LSN = 23854112
24. LAST_LSN = 23854112
26. Compact = 0
30. [[email protected] 2014-09-18_18-11-43]# cat xtrabackup_checkpoints //Second Incremental backup directory file
32. Backup_type = Incremental
34. FROM_LSN = 23854112
36. TO_LSN = 23854712
38. LAST_LSN = 23854712
40. Compact = 0

After the incremental backup is done, delete the Backup_test database and drop the DB backup_test, so you can compare the restored

4, incremental restore

1. # Innobackupex--defaults-file=/etc/my.cnf--user=root--apply-log--redo-only/home/tank/backup/2014-09-18_16-35-12
3. # 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
5. # 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, the directory generated by the second incremental backup

There is no end to this incremental restore, and one of the most important steps is to do a full-scale restore. Stop the database, delete the/var/lib/mysql, and restore it.

The principle of incremental backup is that the data in the incremental directory is integrated into the full variable directory, and then the total data volume is restored.

Overall, Innobackupex speed, support Innodb,myisam, use is not very convenient, single-database restore, or incremental backup restore, all of the database restore, this unreasonable.

Five, innobackup common parameter description

--defaults-file
--defaults-file parameters of the same xtrabackup

--apply-log
Encapsulation of the--prepare parameters of the Xtrabackup

--copy-back
Copy the backup data file to the MySQL server's datadir when doing the 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 is available in the Xtarbackup binary file. If you have specified--stream=tar at the time of backup, the directory where the Tar4ibd file is located must be in $path ( Because TAR4IBD is used to compress, the file can be obtained in binary packages of xtrabackup.
When using the parameter Stream=tar backup, your xtrabackup_logfile may be temporarily placed in the/tmp directory, if you back up when the concurrent write large words xtrabackup_logfile may be large (5g+), it will probably fill up your/ TMP directory, you can solve this problem by specifying the directory with the parameter--tmpdir.

--tmpdir=directory
When a--remote-host or--stream is specified, the transaction log is temporarily stored in a directory that defaults to 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 doing an incremental backup.

--use-memory=#
This parameter is used when prepare, controlling the amount of memory used by InnoDB instances when prepare

--throttle=ios
--throttle parameters of the same xtrabackup

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

--compress[=level]
Import row compression for backup data, only support Ibbackup,xtrabackup has not been implemented;

--include=regexp
The encapsulation of the Xtrabackup parameter--tables also supports Ibbackup. Back up the included library table, for example:--include= "test.*", which means that you want to back up all the tables in the test library. Omit this parameter if you need a full backup, or 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 the MyISAM and InnoDB tables will be backed up;

--uncompress
Unzip the backed up data file, support Ibbackup,xtrabackup has not implemented this function;

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

--socket=socket
Specify the location of the Mysql.sock so that the backup process logs on to MySQL.

MySQL Innobackupex xtrabackup Large data backup restore (RPM)