innobackupex工具的使用：

介紹

The innobackupex tool is a Perl script that acts as a wrapper for the xtrabackup C program. It is a patched version of the innobackup Perl script that Oracle distributes with the InnoDB Hot Backup tool. It enables more functionality by integrating xtrabackup and other functions such as file copying and streaming, and adds some convenience. It lets you perform point-in-time backups of InnoDB / XtraDB tables together with the schema definitions, MyISAM tables, and other portions of the server.
innodbackupex工具是一個Perl腳本，它對xtrabackup 程序的做了一個封裝滋觉。這是一個修補(bǔ)程序版本的innodbackup perl腳本蹈集，innodb的熱備份工具辩块。它集成了xtrabackup和其他功能（如文件復(fù)制和流媒體），可以增加更多的功能性尚氛，并增加了一些便利性。它允許你將innodb表和MyISAM表和服務(wù)器的其他部分一起執(zhí)行表的實時備份。

下載和安裝

下載地址
https://www.percona.com/downloads/XtraBackup/LATEST/

備份

1戏阅，創(chuàng)建一個全備

The --defaults-file option You can provide other configuration file to innobackupex with this option. Theonly limitation is that it has to be the first option passed:
提供默認(rèn)的配置，需要放在最前面指定
例如：

innobackupex --defaults-file=/tmp/other-my.cnf --user=DBUSER --password=DBUSERPASS /path/to/BACKUP

innobackupex --defaults-file=/data/3306/my.cnf --user=root --password=123456 /tmp/backups/

2啤它，創(chuàng)建增量備份

innobackupex --defaults-file=/data/3306/my.cnf --incremental /tmp/backups/inc/ --incremental-basedir=/tmp/backups/2017-01-04_16-53-06 --user=root --password=123456

innobackupex --defaults-file=/data/3306/my.cnf --incremental /tmp/backups/inc/ --incremental-basedir=/tmp/backups/inc/2017-01-04_17-01-57 --user=root --password=123456

也可以通過LSN號來備份

innobackupex --incremental /data/backups --incremental-lsn=1291135

innobackupex --incremental /data/backups --incremental-lsn=1358967

可以從任意增量上的基礎(chǔ)來進(jìn)行備份
This is a very useful way of doing an incremental backup, since not always the base or the last incremental will beavailable in the system.
這是一種非常有用的增量備份方法奕筐，因為在系統(tǒng)中不一定總是有基礎(chǔ)備份或最后的增量備份。

3变骡，準(zhǔn)備全備備份

**Preparing a Full Backup with innobackupex**
innobackupex --apply-log /path/to/BACKUP-DIR
--user-memory默認(rèn)是100M离赫，提高數(shù)值可以加快速度
innobackupex --apply-log --redo-only /tmp/backups/2017-01-04_16-53-06/ --use-memory=1G --user=USER --password=123456   

4，合并增量和全量備份

--redo-only

--redo-only should be used when merging all incrementals except the last one. That’s why the previous
line doesn’t contain the --redo-only option. Even if the --redo-only was used on the last step, backup wouldstill be consistent but in that case server would perform the rollback phase

在最后一次合并增量塌碌，全量備份的時候渊胸，不需要加上--redo-only，如果加上了台妆，也不會導(dǎo)致數(shù)據(jù)不一致翎猛，但是會讓mysql處于rollback的階段。

innobackupex --apply-log --redo-only /tmp/backups/2017-01-04_16-53-06/ --incremental-dir=/tmp/backups/inc/2017-01-04_17-01-57 --use-memory=1G --user=root --password=123456

innobackupex --apply-log /tmp/backups/2017-01-04_16-53-06/ --incremental-dir=/tmp/backups/inc/2017-01-05_10-19-23 --use-memory=1G --user=root --password=123456

5接剩，測試

關(guān)閉數(shù)據(jù)庫

[root@localhost /]# mysqladmin --defaults-file=/data/3306/my.cnf shutdown -uroot –p

刪除數(shù)據(jù)文件

rm /data/3306/data –rf

恢復(fù)

innobackupex --defaults-file=/data/3306/my.cnf --copy-back /tmp/backups/2017-01-04_16-53-06

授權(quán)

chown -R mysql:mysql /data/3306/data

啟動數(shù)據(jù)庫
OK完成
檢查數(shù)據(jù)

壓縮backups

Compact Backups
創(chuàng)建compact
Creating Compact Backups To make a compact backup innobackupex needs to be started with the --compact option:
$ innobackupex --compact /data/backups
準(zhǔn)備
Preparing Compact Backups Preparing the compact require rebuilding the indexes as well. In order to prepare the backup a new option --rebuild-indexes should be used with --apply-logs:

–apply-log –rebuild-indexes. 可以使用
$ innobackupex --apply-log --rebuild-indexes /data/backups/2013-02-01_10-29-4

恢復(fù)

Restoring Compact Backups innobackupex has a --copy-back option, which performs the restoration of abackup to the server’s datadir

$ innobackupex --copy-back /path/to/BACKUP-DIR

加密備份

Encrypted Backups

Creating Encrypted Backups To make an encrypted backup following options need to be specified (options--encrypt-key and --encrypt-key-file are mutually exclusive, i.e. just one of them needs to be provided):

創(chuàng)建加密備份的時候的時候需要加入選項--encrypt-key --encrypt-key-file其中一個

? --encryption=ALGORITHM - currently supported algorithms are: AES128, AES192 and AES256

? --encrypt-key=ENCRYPTION_KEY - proper length encryption key to use. It is not recommended to use this option where there is uncontrolled access to the machine as the command line and thus the key can be viewed as part of the process info.

? --encrypt-key-file=KEYFILE - the name of a file where the raw key of the appropriate length can be

read from. The file must be a simple binary (or text) file that contains exactly the key to be used.

Both --encrypt-key option and --encrypt-key-file option can be used to specify the encryption key.

Encryption key can be generated with command like:

加密秘鑰可以通過下面的命令來生成

$ openssl rand -base64 24

Example output of that command should look like this:

GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs

使用例子

Using the --encrypt-key option Example of the innobackupex command using the --encrypt-key should look like this

$ innobackupex --encrypt=AES256 --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" /data/backups

Using the --encrypt-key-file option Example of the innobackupex command using the --encrypt-key-file should look like this

$ innobackupex --encrypt=AES256 --encrypt-key-file=/data/backups/keyfile /data/backups

加密備份使用優(yōu)化

Optimizing the encryption process

Two new options have been introduced with the encrypted backups that can be used to speed up the encryption process. These are --encrypt-threads and --encrypt-chunk-size.
使用--encrypt-threads 和--encrypt-chunk-size.這兩個選項加速加密過程
By using the --encrypt-threads option multiple threads can be specified to be used for encryption in parallel.
使用--encrypt-threads可以多線程加密

Option --encrypt-chunk-size can be used to specify the size (in bytes) of the working encryption buffer for each encryption thread (default is 64K).
使用--encrypt-chunk-size可以增加每個線程的buffer切厘。

解密備份
In Percona XtraBackup 2.1.4 new innobackupex --decrypt option has been implemented that can be used to decrypt the backups:
在2.14及其新版中可以使用下面的版本解密

$ innobackupex --decrypt=AES256 --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" /data/backups/2015-03-

準(zhǔn)備備份

Preparing Encrypted Backups After the backups have been decrypted, they can be prepared the same way as the standard full backups with the --apply-logs option:
準(zhǔn)備備份和正常備份準(zhǔn)備備份一樣

$ innobackupex --apply-log /data/backups/2015-03-18_08-31-35/

恢復(fù)備份
Restoring Encrypted Backups innobackupex has a --copy-back option, which performs the restoration of a backup to the server’s datadir

$ innobackupex --copy-back /path/to/BACKUP-DIR

高級特性

流和壓縮備份

Advanced Features

xbstream
使用例子
Examples using xbstream Store the complete backup directly to a single file:

$ innobackupex --stream=xbstream /root/backup/ > /root/backup/backup.xbstream

To stream and compress the backup:

$ innobackupex --stream=xbstream --compress /root/backup/ > /root/backup/backup.xbstream

To unpack the backup to the /root/backup/ directory:

$ xbstream -x < backup.xbstream -C /root/backup/

To send the compressed backup to another host and unpack it:
發(fā)送至另外一臺機(jī)器上進(jìn)行遠(yuǎn)程備份

$ innobackupex --compress --stream=xbstream /root/backup/ | ssh user@otherhost "xbstream -x -C /root

tar備份使用例子
Examples using tar Store the complete backup directly to a tar archive:

$ innobackupex --stream=tar /root/backup/ > /root/backup/out.tar

To send the tar archive to another host:
遠(yuǎn)程備份使用例子

$ innobackupex --stream=tar ./ | ssh user@destination \ "cat - > /data/backups/backup.tar"

Warning: To extract Percona XtraBackup‘s archive you must use tar with -i option:

解壓時候需要加入-i選項

$ tar -xizf backup.tar.gz

Compress with your preferred compression tool:

$ innobackupex --stream=tar ./ | gzip - > backup.tar.gz
$ innobackupex --stream=tar ./ | bzip2 - > backup.tar.bz2

Taking Backups in Replication Environments

加速備份進(jìn)程

Accelerating the backup process

Accelerating with --parallel copy and –compress-threads

$ innobackupex --parallel=4 /path/to/backup

$ innobackupex --stream=xbstream --compress --compress-threads=4 ./ > backup.xbstream

備份局部的數(shù)據(jù)

Partial Backups
Percona XtraBackup features partial backups, which means that you may backup only some specific tables ordatabases. The tables you back up must be in separate tablespaces, as a result of being created or altered after youenabled the innodb_file_per_table option on the server.

table 的物理文件必須是分開的

備份使用選項

Using the --include option The regular expression provided to this will be matched against the fully qualified table name, including the database name, in the form databasename.tablename.

For example,

$ innobackupex --include='^mydatabase[.]mytable' /path/to/backup

可以指定正則表達(dá)式

Using the --tables-file option The text file provided (the path) to this option can contain multiple table

names, one per line, in the databasename.tablename format.

For example,

$ echo "mydatabase.mytable" > /tmp/tables.txt

$ innobackupex --tables-file=/tmp/tables.txt /path/to/backup

使用包含table表格的文件

Using the --databases option This option accepts either a space-separated list of the databases and tables to backup - in the databasename[.tablename] form - or a file containing the list at one element per line.

For example,

$ innobackupex --databases="mydatabase.mytable mysql" /path/to/backup

準(zhǔn)備時候需要加入--export選項

Preparing Partial Backups For preparing partial backups, the procedure is analogous to restoring individual tables

: apply the logs and use the --export option:

$ innobackupex --apply-log --export /path/to/partial/backup

恢復(fù)單獨的表格

Restoring Individual Tables

Exporting tables Exporting is done in the preparation stage, not at the moment of creating the backup. Once a full

backup is created, prepare it with the --export option:

$ innobackupex --apply-log --export /path/to/backup

This will create for each InnoDB with its own tablespace a file with .exp extension. An output of this procedure would contain:

..

xtrabackup: export option is specified.

xtrabackup: export metadata of table 'mydatabase/mytable' to file

./mydatabase/mytable.exp (1 indexes)

Each .exp (or .cfg) file will be used for importing that table.

Note: InnoDB does a slow shutdown (i.e. full purge + change buffer merge) on –export, otherwise the tablespaces wouldn’t be consistent and thus couldn’t be imported. All the usual performance considerations apply: sufficient buffer pool (i.e. –use-memory, 100MB by default) and fast enough

storage, otherwise it can take a prohibitive amount of time for export to complete.

Importing tables To import a table to other server, first create a new table with the same structure as the one that

will be imported at that server:

OTHERSERVER|mysql> CREATE TABLE mytable (...) ENGINE=InnoDB;

then discard its tablespace:

OTHERSERVER|mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

After this, copy mytable.ibd and mytable.exp ( or mytable.cfg if importing to MySQL 5.6) files to

database’s home, and import its tablespace:

cp mytable.cfg mytable.ibd /path/to/data

chown -R mysql:mysql /path/to/data

OTHERSERVER|mysql> ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

Once this is executed, data in the imported table will be available.

定點恢復(fù)

Point-In-Time recovery

首先創(chuàng)建一個完整的備份

For taking the snapshot, we will use innobackupex for a full backup:

$ innobackupex /path/to/backup --no-timestamp

(the --no-timestamp option is for convenience in this example) and we will prepare it to be ready for restoration:

準(zhǔn)備備份

$ innobackupex --apply-log /path/to/backup

查看bin log的記錄點

To find out the position of the snapshot taken, see the xtrabackup_binlog_info at the backup’s directory:

$ cat /path/to/backup/xtrabackup_binlog_info

mysql-bin.000003 57

恢復(fù)完整的備份

This will tell you which file was used at moment of the backup for the binary log and its position. That position will

be the effective one when you restore the backup:

$ innobackupex --copy-back /path/to/backup

找到bin log 使用命令產(chǎn)生sql語句，恢復(fù)至指定位置備份

$ mysqlbinlog /path/to/datadir/mysql-bin.000003 /path/to/datadir/mysql-bin.000004 \
--start-position=57 --stop-datetime="11-12-25 01:00:00" | mysql -u root –p

參考文件：
官方文檔PerconaXtraBackup-2-2-13.pdf