MySQL is larger than 50G database backup Xtarbackup

Xtarbackup
Brief introduction:
Mysqldump Backup method is the use of logical backup, the biggest drawback is that the backup and recovery is slow, for less than 50G of data can be accepted, if the data is very large, you can use open source Xtarbackup
Installation:
1, using RPM installation:
wget http://www.percona.com/downloads/XtraBackup/LATEST/RPM/rhel6/x86_64/percona-xtrabackup-2.1.5-680.rhel6.x86_64.rpm
Yum install-y perl-dbd-mysql perl-dbi perl-time-hires libaio*
RPM-IVH percona-xtrabackup-2.1.5-680.rhel6.x86_64.rpm
2. Install with Yum:
RPM-UHV http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
Yum-y Install Percona-xtrabackup
3. View the installation results:
Rpm-qa |grep Xtrabackup
RPM-QL percona-xtrabackup-2.1.5-680.rhel6.x86_64

? The xtrabackup consists of two main tools:
Xtrabackup: is a tool for hot backup InnoDB, XTRADB data in tables, cannot back up other types of tables, nor can you back up the data table structure;
Innobackupex: is a Perl script that encapsulates Xtrabackup, providing the ability to back up MyISAM tables.

using:
[[email protected] 3306]# innobackupex--user=root--password=123456--socket=/usr/local/mysql/sock/ Mysql.sock--defaults-file=/etc/my.cnf/tmp/
Note: Backup will create a folder at the current time in the backup directory to hold the backup directory
File Description:
(1) xtrabackup_ Checkpoints----backup type (such as full or incremental), backup status (such as whether it is not prepare), and LSN (log sequence number) range information
Each InnoDB page (typically 16K in size) contains a log sequence number, the LSN, LSN is the system version number of the entire database system, and each page-related LSN is able to table the name of how the page has changed recently.
Backup_type = full-backuped
From_lsn = 0
To_lsn = 2097320
Last_lsn = 2097320
Compact = 0
(2) xtrabackup_binlog_info----The binary log and backup currently being used by the MySQL server This moment is the location of the binary log event
(3) Xtrabackup_pos_innodb---- Binary logs and the current posistion of binary logs for InnoDB or xtradb tables.
(4) xtrabackup_binary the Xtrabackup executable file used in the----backup
(5) backup-my.cnf the configuration option information----the backup command;
When using the Innobackupex command backup, you can also use the--NO-TIMESTAMP option to prevent the command from automatically creating a time-named directory
Innobackupex command will create a backup-dir directory to store the backup data

It is also important to note that the user who backs up the database needs to have the corresponding permissions, and the following parameters are used if you want to back up a user with a minimum permission:
Mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT on . To ' bkpuser ' @ ' localhost ';
mysql> FLUSH privileges;

准备（prepare）一个完整备份

Innobackupex--apply-log/tmp/2016-07-03_21-07-38/
--apply-log data incompleteness to avoid uncommitted transactions

View the log location when a full backup is taken;
[email protected] 2016-07-03_21-07-38]# cat Xtrabackup_binlog_info
mysql-bin.000029 107

Data restore:
Innobackupex--user=root--password=123456--socket=/usr/local/sock/mysql.sock--defaults-file=/etc/my.cnf-- copy-back/tmp/2016-07-03_21-07-38/
#只是在恢复的时候加入了--copy-back
Incremental backup:
[Email protected] tmp]# Innobackupex--user=root--password=123456--socket=/usr/local/sock/mysql.sock-- DEFAULTS-FILE=/ETC/MY.CNF--incremental/tmp/--incremental-basedir=/tmp/2016-07-04_00-21-06/
Xtrabackup "streaming" and "backup compression" functions

Xtrabackup supports the "stream" feature for backed-up data files, which means that the backed up data can be transferred to the TAR program through stdout instead of being saved directly to a backup directory by default. To use this feature, you only need to use the--stream option. Such as:
#innobackupex--stream=tar/backup | gzip >/backup/ date +%F_%H-%M-%S . tar.gz
You can even use a command similar to the following to back up data to other servers:
#innobackupex--stream=tar/backup | SSH [email protected] "cat->/backups/ date +%F_%H-%M-%S . Tar"
Error Resolution:
Error: Can ' t load '/usr/local/lib64/perl5/auto/dbd/mysql/mysql.so ' for module dbd::mysql:libmysqlclient.so.18: Cannot open shared object At/usr/bin/innobackupex Line 18
Solution: cp/usr/local/mysql/lib/libmysqlclient.so.18/usr/lib64/

MySQL is larger than 50G database backup Xtarbackup