RPM version Xtrabackup installation, using

MySQL Innobackupex xtrabackup large amount of data backup restore

Zhang Ying published in 2014-09-19

Category directory: 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

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 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

[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

Workaround: View copy print?      [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-xtrabacku P-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 '

Second, modify the MY.CNF

Check to see if there are any datadir below mysqld, if not plus view copy print? [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, full database backup # 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 # Innobackupex--DEFAULTS-FILE=/ETC/MY.CNF--user=root--database=backup_test/home/tank/backup/

3, database backup and compression view copy print? # 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? #/etc/init.d/mysqld Stop//stop MySQL # Mv/var/lib/mysql/var/lib/mysql_bak//Data Directory Backup # mkdir