Make a little progress every day--mysql--percona xtrabackup (Innobackupex)

First, Introduction

Percona Xtrabackup is open source free MySQL database hot backup software, it can be InnoDB and XTRADB storage Engine database non-blocking backup (for MyISAM backup also need to add a table lock). Xtrabackup supports all Percona servers, MySQL, mariadb, and drizzle.

Xtrabackup Advantages:
1, no need to stop the database for InnoDB hot standby
2. Incremental backup MySQL
3. Stream compression to other servers
4, it can be easier to create master-slave synchronization
5. Do not increase server load when backing up MySQL

installationXtrabackupafter that, there will actually be several tools:
Innobackupex:
This is actually one of the following three toolsPerlscript encapsulation, can be backed upMyISAM, InnoDB, XtraDBtable.
xtrabackup:
One byCcompiled binaries, can only be backed upInnoDBand theXtraDBdata.
xbcrypt:
the data used to encrypt or decrypt the backup.
xbstream:
used to decompress or compressXbstreamcompressed files in the format.
Recommended UsePerlEncapsulation ofInnobackupexto make a database backup, because it is relatively easy to use. So here are just a fewInnobackupexthe use of. Other references for use:http://www.percona.com/doc/percona-xtrabackup/2.1/manual.html

This article mainly introduces Innobackupex Tools

Note: Do a full backup again after each recovery is complete

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

The first step is to ensure that the installation depends on package

[Email protected] packages]# yum-y install perl perl-devel libaiolibaio-devel perl-time-hires Perl-dbd-mysql

Then install Percona-xtrabackup

[Email protected] ~]# rpm-ivhpercona-xtrabackup-2.2.11-1.el6.x86_64.rpm

Warning:percona-xtrabackup-2.2.11-1.el6.x86_64.rpm:header V4 dsa/sha1 Signature, KeyID Cd2efd2a:nokey

Preparing ... ###########################################[100%]

1:percona-xtrabackup ########################################### [100%]

Second, the command format

innobackupex parameter directory .

Three, common parameters

--defaults-file Specifies the my.cnf file for the MySQL instance that you want to back up, the only limitation was that it have to be the first option passed; CNF must have datadir and other parameters to indicate the path of the data file!

--host Specifying host name/IP

--port Specifies the port for the connection you want, default 3306 ignores

--socket The location of the socket byte, which defaults to/var/lib/mysql/mysql.sock

--use-memory=4g This parameter is used to control the amount of memory used by the backup, which defaults to 100m! Generally used in conjunction with--apply-log, the backup was specified but it seems useless!!

--apply-log After the database has been backed up, these backed-up data are not immediately available for recovery because the data that you just backed up contains uncommitted data and needs to be rolled back, Oracle says undo data! Also includes the completed transactions in the Redo log file that are not written to the data file, which need to be re-made!  Oracle in the name of redo! This parameter is used to do these things to ensure consistency of data files! This parameter must be applied to the backed up data file before the database is restored! (Innobackup will reproduce the transaction entries in the Redo log file (redo log files), redo the committed transaction, and rollback the uncommitted transaction! ）

--copy-back Copies the backup data back to the server's DataDir, which is determined by the DataDir parameter in My.cnf, and the DataDir directory must be empty when recovering, and the MySQL database must be Shudown!

--no-timestamp innobackupex--defaults-file=/etc/my.cnf--user=root--no-timestamp/tmp/backup/full (using--no -timestamp, the back of this full directory must be followed and can not be established in advance, it is automatically established by Innobackupex, otherwise it will be reported innobackupex:Error:Failed to create backup directory/ tmp/backup/full/: File exists At/usr/bin/innobackupex line 3899.)

--redo-only When doing an incremental restore, the fully-prepared and incremental backup data files must be re-committed in the Redo log file before they are restored! This parameter merges the data files for both full and incremental backups, but does not include the data file for the last incremental backup! (--redo-only should be used if merging all incrementals except Thelast one)

--compress compression options, this option is incompatible with--stream=tar, is compatible with--stream=xbstream only, and encryption entries encrypted are not compatible--stream=tar!

--decompress         If encryption or compression is used when backing up, After unpacking with Xbstream-x, you must also use innodbackupex      --decompress/ Data/backup   compressed file is Ibdata1.qp , QP ends the file! In addition to use decompress also need yum install qpress.x86_64 loaded qpress this bag!

--slave-info This option is useful whenbacking up a replication slave server. It also writes this information tothe "Xtrabackup_slave_info" the file as a "change MASTER" command. A new slave for the master can be set to starting a slaveserver on this backup and issuing a "change" xtrabackup_slave_i  NFO "file. Use this parameter to record the log and offset of master when backing up from the server! Used to make a new slave server! In general, full backups,master binary log files, and offset points are recorded in the xtrabackup_binlog_info file!

Iv. examples of common use 1. Full backup

[Email protected]]# innobackupex--defaults-file=/etc/my.cnf--host 10.22.19.44--port=3306--user=root--password= 123456--socket=/tmp/mysql.sock/back

2. Incremental backup based on 2015-08-27_09-54-02 for incremental backup

[Email protected]]# innobackupex--user=root--password=123456--defaults-file=/etc/my.cnf--socket=/tmp/mysql.sock --no-timestamp--incremental--incremental-basedir=/back/2015-08-27_09-54-02//back/001/

Incremental backup based on/back/001/

[Email protected]]# innobackupex--user=root--password=123456--defaults-file=/etc/my.cnf--socket=/tmp/mysql.sock --no-timestamp--incremental--incremental-basedir=/back/001//back/002/

You can view backup information from the following files:

[Email protected]]# cat 2015-08-27_09-54-02/xtrabackup_checkpoints

Backup_type = full-backuped

FROM_LSN = 0

TO_LSN = 919852583

LAST_LSN = 919852583

Compact = 0

[Email protected]]# cat 001/xtrabackup_checkpoints

Backup_type = Incremental

FROM_LSN = 919852583

TO_LSN = 919854256

LAST_LSN = 919854256

Compact = 0

[Email protected]]# cat 002/xtrabackup_checkpoints

Backup_type = Incremental

FROM_LSN = 919854256

TO_LSN = 919858085

LAST_LSN = 919858085

These 3 backup set relationships can be seen through the LSN

You can also use this command to make a backup of the specified LSN

[Email protected]]# innobackupex--user=root--password=123456--defaults-file=/etc/my.cnf--socket=/tmp/mysql.sock --no-timestamp--incremental--incremental-lsn=919854256/back/002/

3. Back up and package the Tar method:

[Email protected] back]# Innobackupex--defaults-file=/etc/my.cnf--host 10.22.19.44--port=3306--user=root-- Password=123456--socket=/tmp/mysql.sock--stream=tar/back>/back/fullbackup.tar

Xbstream Way

[Email protected] back]# innobackupex--defaults-file=/etc/my.cnf--host 10.22.19.44--port=3306--user= root--password=123456--socket=/tmp/mysql.sock--stream=xbstream/back>/back/fullbackup.xbstream

4. Back up and package and compress the package in tar mode

Gzip Mode compression:

[Email protected]]# innobackupex--defaults-file=/etc/my.cnf--host 10.22.19.44--port=3306--user=root--password= 123456--socket=/tmp/mysql.sock--stream=tar/back/|gzip,/back/fullbackup2.tar.gz

bzip2 Mode compression:

[Email protected]]# innobackupex--defaults-file=/etc/my.cnf--host 10.22.19.44--port=3306--user=root--password= 123456--socket=/tmp/mysql.sock--stream=tar/back/|bzip,/back/fullbackup2.tar.bz2

Xbstream way to package and compress

[Email protected]]# innobackupex--defaults-file=/etc/my.cnf--host 10.22.19.44--port=3306--user=root--password= 123456--socket=/tmp/mysql.sock--stream=xbstream--compress/back>/back/fullbackup2.xbstream

Backup packaging and delivery to other servers compressed storage

[Email protected] back]# innobackupex--defaults-file=/etc/my.cnf--host 10.22.19.44--port=3306--user= root--password=123456--socket=/tmp/mysql.sock--stream=tar/back/2>/back/log2.log|ssh 10.22.19.90 "bzip2->/ ROOT/MYSQL.TAR.BZ2 "

[email protected] ' s password:

5. Full backup RESTORE Database: Application log

[Email protected]]# Innobackupex--apply-log/back/2015-08-27_09-51-14/

Restoring a fully prepared library

[Email protected]]# innobackupex--user=root--password=123456--copy-back--use-memory=512m/back/2015-08-27_ 09-51-14/

View Data Directory Permissions

[Email protected]]# ll/usr/local/mysql|grep Data

Drwxr-xr-x root root 4096 August 12:50 data

drwx------mysql mysql 4096 July 13:13 data2

Modify Directory Permissions

[Email protected]]# chown-r Mysql:mysql/usr/local/mysql/data

[Email protected]]# ll/usr/local/mysql|grep Data

drwxr-xr-x mysql mysql 4096 August 12:50 data

drwx------mysql mysql 4096 July 13:13 data2

Start the database

[[Email protected]~]# service mysqld Start

Starting MySQL. Determine

Restore binary logs

[Email protected]]# mysqlbinlog/root/mysql.000018 >/tmp/abc.sql

[[email protected]] #mysql-uroot-p123456

Mysql>set sql_log_bin=0;

Query OK, 0 rows Affected (0.00 sec)

mysql>source/tmp/abc.sql;

Restore succeeded

6. Incremental backup RESTORE Database

Perform redo redo of the most recent full-time database file

[Email protected]]# innobackupex--apply-log--redo-only/back/2015-08-27_09-54-02/

Performs redo redo and merges into a fully prepared library in addition to the last incremental backup library

[Email protected]]# innobackupex--apply-log--redo-only/back/2015-08-27_09-54-02/--incremental-dir=/back/001/

Apply the last incremental backup to the log (without--redo-only) and merge into the fully-prepared library

[Email protected]]# Innobackupex--apply-log/back/2015-08-27_09-54-02/--incremental-dir=/back/002/

This is because, in addition to the last incremental backup, the preceding things should not be rolled back because it is possible that a previously uncommitted thing was committed in the next backup, and if a rollback could result in inconsistent data and could not be restored

Perform a full library restore

[Email protected]]# Innobackupex--copy-back/back/2015-08-27_09-54-02/

Restore binary logs

[Email protected]]# mysqlbinlog/root/mysql.000018 >/tmp/abc.sql

[[email protected]] #mysql-uroot-p123456

Mysql>set sql_log_bin=0;

Query OK, 0 rows Affected (0.00 sec)

mysql>source/tmp/abc.sql;

Restore succeeded

No partial restore content was added due to time reasons, added before the weekend

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Make a little progress every day--mysql--percona xtrabackup (Innobackupex)