mysql full qualified_MySQL备份工具innobackupex

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 程序的做了一个封装。这是一个修补程序版本的innodbackup perl脚本，innodb的热备份工具。它集成了xtrabackup和其他功能(如文件复制和流媒体)，可以增加更多的功能性，并增加了一些便利性。它允许你将innodb表和MyISAM表和服务器的其他部分一起执行表的实时备份。

下载和安装

备份

1，创建一个全备

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:

提供默认的配置，需要放在最前面指定

例如：

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，创建增量备份

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

可以从任意增量上的基础来进行备份

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.

这是一种非常有用的增量备份方法，因为在系统中不一定总是有基础备份或最后的增量备份。

3，准备全备备份

**Preparing a Full Backup with innobackupex**

innobackupex --apply-log /path/to/BACKUP-DIR

--user-memory默认是100M，提高数值可以加快速度

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，如果加上了，也不会导致数据不一致，但是会让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，测试

关闭数据库

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

删除数据文件

rm /data/3306/data –rf

恢复

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

授权

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

启动数据库

OK完成

检查数据

压缩backups

Compact Backups

创建compact

Creating Compact Backups To make a compact backup innobackupex needs to be started with the --compact option:

$ innobackupex --compact /data/backups

准备

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

恢复

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

创建加密备份的时候的时候需要加入选项--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

加密备份使用优化

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-

准备备份

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:

准备备份和正常备份准备备份一样

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

恢复备份

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:

发送至另外一台机器上进行远程备份

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

远程备份使用例子

$ 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

加速备份进程

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

备份局部的数据

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

可以指定正则表达式

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

准备时候需要加入--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

恢复单独的表格

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.

定点恢复

Point-In-Time recovery

首先创建一个完整的备份

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:

准备备份

$ 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

恢复完整的备份

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 使用命令产生sql语句，恢复至指定位置备份

$ 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