mysql数据库：Xtrabackup安装以及应用

Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写），是商业备份工具InnoDB Hotbackup的一个很好的替代品。

xtrabackup官方网址：https://www.percona.com/downloads/XtraBackup/

MySQL Backup Tool Feature Comparison

Features	Percona XtraBackup	MySQL Enterprise backup
License	GPL	Proprietary
Price	Free	Included in subscription at $5000 per Server
Streaming and encryption formats	Open source	Proprietary
Supported MySQL flavors	MySQL, Percona Server, MariaDB,Percona XtraDB Cluster, MariaDB Galera Cluster	MySQL
Supported operating systems	Linux	Linux, Solaris, Windows, OSX, FreeBSD.
Non-blocking InnoDB backups [1]	Yes	Yes
Blocking MyISAM backups	Yes	Yes
Incremental backups	Yes	Yes
Full compressed backups	Yes	Yes
Incremental compressed backups	Yes
Fast incremental backups [2]	Yes
Incremental backups with archived logs feature in Percona Server	Yes
Incremental backups with REDO log only		Yes
Backup locks [8]	Yes
Encrypted backups	Yes	Yes [3]
Streaming backups	Yes	Yes
Parallel local backups	Yes	Yes
Parallel compression	Yes	Yes
Parallel encryption	Yes	Yes
Parallel apply-log	Yes
Parallel copy-back		Yes
Partial backups	Yes	Yes
Partial backups of individual partitions	Yes
Throttling [4]	Yes	Yes
Backup image validation		Yes
Point-in-time recovery support	Yes	Yes
Safe slave backups	Yes
Compact backups [5]	Yes
Buffer pool state backups	Yes
Individual tables export	Yes	Yes [6]
Individual partitions export	Yes
Restoring tables to a different server [7]	Yes	Yes
Data & index file statistics	Yes
InnoDB secondary indexes defragmentation	Yes
`rsync` support to minimize lock time	Yes
Improved `FTWRL` handling	Yes
Backup history table	Yes	Yes
Backup progress table		Yes
Offline backups		Yes
Backup to tape media managers		Yes
Cloud backups support		Amazon S3
External graphical user interfaces to backup/recovery	Zmanda Recovery Manager for MySQL	MySQL Workbench, MySQL Enterprise Monitor

来源： https://www.percona.com/doc/percona-xtrabackup/2.4/intro.html

xtrabackup安装方法

可以下载源码编译安装，也可以下载适合的RPM包或者yum进行安装。

1、Installing Percona XtraBackup from Percona `yum` repository

安装Percona XtraBackup Percona yum资源库

yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

查看软件包是否存在

[root@db01 ~]# yum list|grep percona
测试是否存在软件包
...
percona-xtrabackup-20.x86_64               2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-20-debuginfo.x86_64     2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-20-test.x86_64          2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-test-22.x86_64          2.2.13-1.el5                percona-release-x86_64
...

安装软件包

yum install percona-xtrabackup-22 -y

2、Installing Percona XtraBackup using downloaded rpm packages

使用rpm包安装

Installing Percona Server using downloaded rpm packages

下载需要的版本：

wget https://www.percona.com/downloads/Percona-Server-5.7/Percona-Server-5.7.10-3/binary/redhat/7/x86_64/Percona-Server-5.7.10-3-r63dafaf-el7-x86_64-bundle.tar

需要解压

tar xvf Percona-Server-5.7.10-3-r63dafaf-el7-x86_64-bundle.tar
ls *.rpm
Percona-Server-57-debuginfo-5.7.10-3.1.el7.x86_64.rpm
Percona-Server-client-57-5.7.10-3.1.el7.x86_64.rpm
Percona-Server-devel-57-5.7.10-3.1.el7.x86_64.rpm
Percona-Server-server-57-5.7.10-3.1.el7.x86_64.rpm
Percona-Server-shared-57-5.7.10-3.1.el7.x86_64.rpm
Percona-Server-shared-compat-57-5.7.10-3.1.el7.x86_64.rpm
Percona-Server-test-57-5.7.10-3.1.el7.x86_64.rpm
Percona-Server-tokudb-57-5.7.10-3.1.el7.x86_64.rpm
运行方式
rpm -ivh Percona-Server-server-57-5.7.10-3.1.el7.x86_64.rpm \
Percona-Server-client-57-5.7.10-3.1.el7.x86_64.rpm \
Percona-Server-shared-57-5.7.10-3.1.el7.x86_64.rpm   ...

Uninstalling Percona XtraBackup

卸载软件包
yum remove percona-xtrabackup

官方文档：https://www.percona.com/doc/percona-xtrabackup/2.2/installation/yum_repo.html#uninstalling-percona-xtrabackup

Download Percona Server 5.7

软件包说明

Download All Packages Together

Percona-Server-5.7.12-5-ra2f663a-el6-x86_64-bundle.tar

Download Packages Separately

Percona-Server-57-debuginfo-5.7.12-5.1.el6.x86_64.rpm

Percona-Server-client-57-5.7.12-5.1.el6.x86_64.rpm

Percona-Server-devel-57-5.7.12-5.1.el6.x86_64.rpm

Percona-Server-server-57-5.7.12-5.1.el6.x86_64.rpm

Percona-Server-shared-57-5.7.12-5.1.el6.x86_64.rpm

Percona-Server-test-57-5.7.12-5.1.el6.x86_64.rpm

Percona-Server-tokudb-57-5.7.12-5.1.el6.x86_64.rpm

官方解释每个软件包的作用：

What’s in each RPM package?¶

每个Percona服务器的RPM包有一个特定的目的。

Percona-Server-server-57包包含了服务器本身(mysqld二进制)。

为服务器Percona-Server-57-debuginfo包包含调试符号。

Percona-Server-client-57包包含命令行客户端。

Percona-Server-devel-57包包含所需的头文件编译软件使用客户端库。

Percona-Server-shared-57包包含客户端共享库。

Percona-Server-shared-compat包包含共享库的编译软件旧版本的客户端库。

库包含在这个包:libmysqlclient.so。

12,libmysqlclient.so。

14日,libmysqlclient.so。

15日,libmysqlclient.so。

16、libmysqlclient.so.18。

Percona-Server-test-57包包括Percona服务器的测试套件

检查安装结果：

[root@db01 ~]# rpm -qa|grep xtrabackup
percona-xtrabackup-22-2.2.13-1.el6.x86_64

Xtrabackup中主要包含两个工具：

xtrabackup：是用于热备innodb，xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；

innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。

使用xtrabackup实现对MySQL的备份

1.完全备份

基本语法：

多实例备份：

[root@db02 3306]# innobackupex --user=root --password=654321 --socket=/data/3306/mysql.sock --defaults-file=/data/3306/my.cnf /tmp/

语法解释：--user=数据库用户

--password=数据库密码

--socket=指定socket

--default-file=指定配置文件

最后面是存放位置

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
160703 20:00:13  innobackupex: Executing a version check against the server...
160703 20:00:13  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/data/3306/my.cnf;mysql_read_default_group=xtrabackup;mysql_socket=/data/3306/mysql.sock' as 'root'  (using password: YES).
160703 20:00:13  innobackupex: Connected to MySQL server
160703 20:00:13  innobackupex: Done.
160703 20:00:13  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/data/3306/my.cnf;mysql_read_default_group=xtrabackup;mysql_socket=/data/3306/mysql.sock' as 'root'  (using password: YES).
160703 20:00:13  innobackupex: Connected to MySQL server
160703 20:00:13  innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".
innobackupex:  Using server version 5.5.49-log
innobackupex: Created backup directory /tmp/2016-07-03_20-00-13
160703 20:00:13  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/data/3306/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/tmp/2016-07-03_20-00-13 --tmpdir=/tmp --extra-lsndir='/tmp'
innobackupex: Waiting for ibbackup (pid=2586) to suspend
innobackupex: Suspend file '/tmp/2016-07-03_20-00-13/xtrabackup_suspended_2'
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 70f4be3)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/3306/data
xtrabackup: open files limit requested 1024, set to 65535
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 4194304
>> log scanned up to (2097320)
xtrabackup: Generating a list of tablespaces
[01] Copying ./ibdata1 to /tmp/2016-07-03_20-00-13/ibdata1
>> log scanned up to (2097320)
>> log scanned up to (2097320)
>> log scanned up to (2097320)
>> log scanned up to (2097320)
>> log scanned up to (2097320)
[01]        ...done
>> log scanned up to (2097320)
xtrabackup: Creating suspend file '/tmp/2016-07-03_20-00-13/xtrabackup_suspended_2' with pid '2586'
160703 20:00:20  innobackupex: Continuing after ibbackup has suspended
160703 20:00:20  innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
160703 20:00:20  innobackupex: Executing FLUSH TABLES WITH READ LOCK...
160703 20:00:20  innobackupex: All tables locked and flushed to disk
160703 20:00:20  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/data/3306/data/'
innobackupex: Backing up file '/data/3306/data//qqqqqqq41233/db.opt'
>> log scanned up to (2097320)
innobackupex: Backing up file '/data/3306/data//dadadadadadad/db.opt'
innobackupex: Backing up file '/data/3306/data//qqqqqqqq3/db.opt'
innobackupex: Backing up file '/data/3306/data//qqqqqqqq/db.opt'
innobackupex: Backing up file '/data/3306/data//qqqqqqq43/db.opt'
innobackupex: Backing up file '/data/3306/data//oldboy_gbk/db.opt'
innobackupex: Backing up file '/data/3306/data//oldboy/student.frm'
innobackupex: Backing up file '/data/3306/data//oldboy/db.opt'
innobackupex: Backing up file '/data/3306/data//ooooooooooooooooo/db.opt'
innobackupex: Backing up files '/data/3306/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
innobackupex: Backing up file '/data/3306/data//qqqqqqqq1/db.opt'
innobackupex: Backing up file '/data/3306/data//wordpress/db.opt'
innobackupex: Backing up file '/data/3306/data//aaa/db.opt'
innobackupex: Backing up files '/data/3306/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)
innobackupex: Backing up file '/data/3306/data//cyh/db.opt'
innobackupex: Backing up file '/data/3306/data//wuyi/db.opt'
160703 20:00:21  innobackupex: Finished backing up non-InnoDB tables and files
160703 20:00:21  innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
160703 20:00:21  innobackupex: Waiting for log copying to finish
>> log scanned up to (2097320)
xtrabackup: The latest check point (for incremental): '2097320'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (2097320)
xtrabackup: Creating suspend file '/tmp/2016-07-03_20-00-13/xtrabackup_log_copied' with pid '2586'
xtrabackup: Transaction log of lsn (2097320) to (2097320) was copied.
160703 20:00:22  innobackupex: All tables unlocked
innobackupex: Backup created in directory '/tmp/2016-07-03_20-00-13'
innobackupex: MySQL binlog position: filename 'mysql-bin.000029', position 107
160703 20:00:22  innobackupex: Connection to database server closed
160703 20:00:22  innobackupex: completed OK!

错误提示：如果执行该命令出现如下错误：

Can't load '/usr/local/lib64/perl5/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient.so.18: 无法打开共享对象 at /usr/bin/innobackupex line 18

需要拷贝libmysqlclient.so.18至/usr/lib64:

[root@localhost ~]# cp /usr/local/mysql/lib/libmysqlclient.so.18 /usr/lib64/

备份后的文件：

在备份的同时，备份数据会在备份目录下创建一个以当前时间为名字的目录存放备份文件：

各文件说明：

（1）xtrabackup_checkpoints ----备份类型（如完全或增量）、备份状态（如是否已经没prepare状态）和LSN（日志序列号）范围信息

每个InnoDB页（通常大小为16K）都会包含一个日志序列号，即LSN，LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表名此页面最近是如何发生改变的。

backup_type = full-backuped
from_lsn = 0
to_lsn = 2097320
last_lsn = 2097320
compact = 0

（2）xtrabackup_binlog_info ----mysql服务器当前正在使用的二进制日志及备份这一刻为二进制日志事件的位置

（3）xtrabackup_pos_innodb ----二进制日志及用于InnoDB或XtraDB表的二进制日志的当前posistion。

（4）xtrabackup_binary ----备份中用到的xtrabackup的可执行文件

（5）backup-my.cnf ---- 备份命令用到的配置选项信息；

在使用innobackupex命令备份时吗还可以使用--no-timestamp选项来阻止命令自动创建一个以时间命名的目录

innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据

还需要注意，备份数据库的用户需要具有相对应的权限，如果要使用一个最小权限的用户进行备份可以使用以下参数：

mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ‘bkpuser’@’localhost’;       
mysql> FLUSH PRIVILEGES;

2. 准备（prepare）一个完整备份

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务，因此，此时数据文件仍处理不一致状态。“准备”的主要作用是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

innobackupex命令的--apply-log选项可用于实现上述功能。

[root@db02 /]# innobackupex --apply-log /tmp/2016-07-03_21-07-38/

如果执行正确，会输出以下内容：不提示错误即可

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
160703 21:43:08  innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".
160703 21:43:08  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/tmp/2016-07-03_21-07-38/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/tmp/2016-07-03_21-07-38
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 70f4be3)
xtrabackup: cd to /tmp/2016-07-03_21-07-38
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(2097320)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 2085708 and 2085708 in ibdata files do not match the log sequence number 2097320 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages 
InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 9706, file name /data/3306/mysql-bin.000025
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 2097320
[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 9706, file name /data/3306/mysql-bin.000025
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2098397
160703 21:43:11  innobackupex: Restarting xtrabackup with command: xtrabackup  --defaults-file="/tmp/2016-07-03_21-07-38/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/tmp/2016-07-03_21-07-38
for creating ib_logfile*
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 70f4be3)
xtrabackup: cd to /tmp/2016-07-03_21-07-38
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 4194304
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 4194304
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101 size to 4 MB
InnoDB: Setting log file ./ib_logfile1 size to 4 MB
InnoDB: Setting log file ./ib_logfile2 size to 4 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=2098397
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 2098700
[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 9706, file name /data/3306/mysql-bin.000025
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2099425
160703 21:43:13  innobackupex: completed OK!

查看完全备份时日志位置；

[root@db02 2016-07-03_21-07-38]# cat xtrabackup_binlog_info 
mysql-bin.000029 107

模式数据库修改

create table student(
id int(4) not null,
name char(20) not null,
age tinyint(2)  NOT NULL default '0',
dept varchar(16)  default NULL
);
insert into student(id,name,age) values(1,'good',15);
insert into student(id,name,age) values(2,'hehe',18);

模拟数据库损坏

进入数据库的data目录删除所有

[root@db02 data]# rm -rf *
mysql>show databases;  现在已经造成了数据库文件被删除，里面的数据消失
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

还原完全备份

innobackupex命令的--copy-back选项用于执行恢复操作，其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程。innobackupex通过backup-my.cnf来获取DATADIR目录的相关信息

还原数据库语法

[root@db02 data]# innobackupex --user=root --password=654321 --socket=/data/3306/mysql.sock --defaults-file=/data/3306/my.cnf --copy-back /tmp/2016-07-03_21-07-38/

只是在恢复的时候加入了--copy-back

如果执行正确，会输出以下内容

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
160704 00:03:33  innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".
innobackupex: Starting to copy files in '/tmp/2016-07-03_21-07-38'
innobackupex: back to original data directory '/data/3306/data'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/xtrabackup_info' to '/data/3306/data/xtrabackup_info'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/xtrabackup_binlog_pos_innodb' to '/data/3306/data/xtrabackup_binlog_pos_innodb'
innobackupex: Creating directory '/data/3306/data/qqqqqqq41233'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/qqqqqqq41233/db.opt' to '/data/3306/data/qqqqqqq41233/db.opt'
innobackupex: Creating directory '/data/3306/data/dadadadadadad'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/dadadadadadad/db.opt' to '/data/3306/data/dadadadadadad/db.opt'
innobackupex: Creating directory '/data/3306/data/qqqqqqqq3'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/qqqqqqqq3/db.opt' to '/data/3306/data/qqqqqqqq3/db.opt'
innobackupex: Creating directory '/data/3306/data/qqqqqqqq'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/qqqqqqqq/db.opt' to '/data/3306/data/qqqqqqqq/db.opt'
innobackupex: Creating directory '/data/3306/data/qqqqqqq43'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/qqqqqqq43/db.opt' to '/data/3306/data/qqqqqqq43/db.opt'
innobackupex: Creating directory '/data/3306/data/oldboy_gbk'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/oldboy_gbk/db.opt' to '/data/3306/data/oldboy_gbk/db.opt'
innobackupex: Creating directory '/data/3306/data/oldboy'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/oldboy/student.frm' to '/data/3306/data/oldboy/student.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/oldboy/db.opt' to '/data/3306/data/oldboy/db.opt'
innobackupex: Creating directory '/data/3306/data/ooooooooooooooooo'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/ooooooooooooooooo/db.opt' to '/data/3306/data/ooooooooooooooooo/db.opt'
innobackupex: Creating directory '/data/3306/data/performance_schema'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/performance_schema/file_instances.frm' to '/data/3306/data/performance_schema/file_instances.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/performance_schema/setup_instruments.frm' to '/data/3306/data/performance_schema/setup_instruments.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/performance_schema/setup_consumers.frm' to '/data/3306/data/performance_schema/setup_consumers.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/performance_schema/file_summary_by_instance.frm' to '/data/3306/data/performance_schema/file_summary_by_instance.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/performance_schema/events_waits_summary_by_instance.frm' to '/data/3306/data/performance_schema/events_waits_summary_by_instance.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/performance_schema/mutex_instances.frm' to '/data/3306/data/performance_schema/mutex_instances.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/performance_schema/db.opt' to '/data/3306/data/performance_schema/db.opt'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/performance_schema/performance_timers.frm' to '/data/3306/data/performance_schema/performance_timers.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/performance_schema/cond_instances.frm' to '/data/3306/data/performance_schema/cond_instances.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/performance_schema/setup_timers.frm' to '/data/3306/data/performance_schema/setup_timers.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/performance_schema/events_waits_current.frm' to '/data/3306/data/performance_schema/events_waits_current.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/performance_schema/file_summary_by_event_name.frm' to '/data/3306/data/performance_schema/file_summary_by_event_name.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/performance_schema/events_waits_history.frm' to '/data/3306/data/performance_schema/events_waits_history.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/performance_schema/events_waits_summary_by_thread_by_event_name.frm' to '/data/3306/data/performance_schema/events_waits_summary_by_thread_by_event_name.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/performance_schema/events_waits_history_long.frm' to '/data/3306/data/performance_schema/events_waits_history_long.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/performance_schema/events_waits_summary_global_by_event_name.frm' to '/data/3306/data/performance_schema/events_waits_summary_global_by_event_name.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/performance_schema/rwlock_instances.frm' to '/data/3306/data/performance_schema/rwlock_instances.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/performance_schema/threads.frm' to '/data/3306/data/performance_schema/threads.frm'
innobackupex: Creating directory '/data/3306/data/qqqqqqqq1'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/qqqqqqqq1/db.opt' to '/data/3306/data/qqqqqqqq1/db.opt'
innobackupex: Creating directory '/data/3306/data/wordpress'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/wordpress/db.opt' to '/data/3306/data/wordpress/db.opt'
innobackupex: Creating directory '/data/3306/data/aaa'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/aaa/db.opt' to '/data/3306/data/aaa/db.opt'
innobackupex: Creating directory '/data/3306/data/mysql'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/help_keyword.MYD' to '/data/3306/data/mysql/help_keyword.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/general_log.CSV' to '/data/3306/data/mysql/general_log.CSV'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/help_keyword.MYI' to '/data/3306/data/mysql/help_keyword.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/help_topic.frm' to '/data/3306/data/mysql/help_topic.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/procs_priv.MYD' to '/data/3306/data/mysql/procs_priv.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/servers.frm' to '/data/3306/data/mysql/servers.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/time_zone_transition_type.MYI' to '/data/3306/data/mysql/time_zone_transition_type.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/time_zone.frm' to '/data/3306/data/mysql/time_zone.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/event.MYI' to '/data/3306/data/mysql/event.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/db.frm' to '/data/3306/data/mysql/db.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/ndb_binlog_index.MYD' to '/data/3306/data/mysql/ndb_binlog_index.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/proc.MYD' to '/data/3306/data/mysql/proc.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/tables_priv.MYD' to '/data/3306/data/mysql/tables_priv.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/user.MYD' to '/data/3306/data/mysql/user.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/time_zone_name.MYI' to '/data/3306/data/mysql/time_zone_name.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/general_log.CSM' to '/data/3306/data/mysql/general_log.CSM'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/event.frm' to '/data/3306/data/mysql/event.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/help_topic.MYD' to '/data/3306/data/mysql/help_topic.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/procs_priv.frm' to '/data/3306/data/mysql/procs_priv.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/time_zone_name.MYD' to '/data/3306/data/mysql/time_zone_name.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/slow_log.CSM' to '/data/3306/data/mysql/slow_log.CSM'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/time_zone_leap_second.MYI' to '/data/3306/data/mysql/time_zone_leap_second.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/help_topic.MYI' to '/data/3306/data/mysql/help_topic.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/proc.MYI' to '/data/3306/data/mysql/proc.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/columns_priv.MYD' to '/data/3306/data/mysql/columns_priv.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/slow_log.CSV' to '/data/3306/data/mysql/slow_log.CSV'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/func.frm' to '/data/3306/data/mysql/func.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/time_zone_leap_second.MYD' to '/data/3306/data/mysql/time_zone_leap_second.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/ndb_binlog_index.frm' to '/data/3306/data/mysql/ndb_binlog_index.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/help_category.frm' to '/data/3306/data/mysql/help_category.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/db.MYI' to '/data/3306/data/mysql/db.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/func.MYI' to '/data/3306/data/mysql/func.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/columns_priv.frm' to '/data/3306/data/mysql/columns_priv.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/user.MYI' to '/data/3306/data/mysql/user.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/general_log.frm' to '/data/3306/data/mysql/general_log.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/help_keyword.frm' to '/data/3306/data/mysql/help_keyword.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/event.MYD' to '/data/3306/data/mysql/event.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/time_zone_transition.frm' to '/data/3306/data/mysql/time_zone_transition.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/help_relation.frm' to '/data/3306/data/mysql/help_relation.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/time_zone_transition_type.MYD' to '/data/3306/data/mysql/time_zone_transition_type.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/host.MYD' to '/data/3306/data/mysql/host.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/help_relation.MYI' to '/data/3306/data/mysql/help_relation.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/servers.MYD' to '/data/3306/data/mysql/servers.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/host.frm' to '/data/3306/data/mysql/host.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/help_relation.MYD' to '/data/3306/data/mysql/help_relation.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/time_zone.MYI' to '/data/3306/data/mysql/time_zone.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/plugin.MYI' to '/data/3306/data/mysql/plugin.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/servers.MYI' to '/data/3306/data/mysql/servers.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/user.frm' to '/data/3306/data/mysql/user.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/ndb_binlog_index.MYI' to '/data/3306/data/mysql/ndb_binlog_index.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/tables_priv.MYI' to '/data/3306/data/mysql/tables_priv.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/plugin.MYD' to '/data/3306/data/mysql/plugin.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/db.MYD' to '/data/3306/data/mysql/db.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/proxies_priv.frm' to '/data/3306/data/mysql/proxies_priv.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/time_zone_transition.MYI' to '/data/3306/data/mysql/time_zone_transition.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/plugin.frm' to '/data/3306/data/mysql/plugin.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/host.MYI' to '/data/3306/data/mysql/host.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/columns_priv.MYI' to '/data/3306/data/mysql/columns_priv.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/time_zone_transition_type.frm' to '/data/3306/data/mysql/time_zone_transition_type.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/help_category.MYD' to '/data/3306/data/mysql/help_category.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/procs_priv.MYI' to '/data/3306/data/mysql/procs_priv.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/time_zone_name.frm' to '/data/3306/data/mysql/time_zone_name.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/time_zone.MYD' to '/data/3306/data/mysql/time_zone.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/proc.frm' to '/data/3306/data/mysql/proc.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/func.MYD' to '/data/3306/data/mysql/func.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/proxies_priv.MYD' to '/data/3306/data/mysql/proxies_priv.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/time_zone_leap_second.frm' to '/data/3306/data/mysql/time_zone_leap_second.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/help_category.MYI' to '/data/3306/data/mysql/help_category.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/proxies_priv.MYI' to '/data/3306/data/mysql/proxies_priv.MYI'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/time_zone_transition.MYD' to '/data/3306/data/mysql/time_zone_transition.MYD'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/tables_priv.frm' to '/data/3306/data/mysql/tables_priv.frm'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/mysql/slow_log.frm' to '/data/3306/data/mysql/slow_log.frm'
innobackupex: Creating directory '/data/3306/data/cyh'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/cyh/db.opt' to '/data/3306/data/cyh/db.opt'
innobackupex: Creating directory '/data/3306/data/wuyi'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/wuyi/db.opt' to '/data/3306/data/wuyi/db.opt'
innobackupex: Starting to copy InnoDB system tablespace
innobackupex: in '/tmp/2016-07-03_21-07-38'
innobackupex: back to original InnoDB data directory '/data/3306/data'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/ibdata1' to '/data/3306/data/ibdata1'
innobackupex: Starting to copy InnoDB undo tablespaces
innobackupex: in '/tmp/2016-07-03_21-07-38'
innobackupex: back to '/data/3306/data'
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/tmp/2016-07-03_21-07-38'
innobackupex: back to original InnoDB log directory '/data/3306/data'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/ib_logfile1' to '/data/3306/data/ib_logfile1'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/ib_logfile0' to '/data/3306/data/ib_logfile0'
innobackupex: Copying '/tmp/2016-07-03_21-07-38/ib_logfile2' to '/data/3306/data/ib_logfile2'
innobackupex: Finished copying back files.
160704 00:03:38  innobackupex: completed OK!

我们可以在查看结果：

mysql>select * from student;  #我们添加的数据也都回来了
+----+--------+-----+------+
| id | name   | age | dept |
+----+--------+-----+------+
|  0 | 1      |   0 | NULL |
|  2 | oldboy |   0 | NULL |
|  3 | good   |   0 | NULL |
|  3 | good   |  15 | NULL |
+----+--------+-----+------+
4 rows in set (0.00 sec)

增量备份二进制文件：

[root@db02 2016-07-03_21-07-38]# mysqlbinlog --start-position=107 /data/3306/mysql-bin.000029 >/tmp/$(date +%F).sql
注：--start-position=107可以不指定，因为107是一个日志的默认起始位置。

还原增量备份

为了防止还原时产生大量的二进制日志，在还原可临时关闭二进制日志后再还原；

mysql> set sql_log_bin=0; 
Query OK, 0 rows affected (0.00 sec)
mysql> SOURCE /tmp/2016-07-03_21-07-38/2016-07-03.sql

使用innobackupex进行增量备份

前面我们进行增量备份时，使用的还是老方法：备份二进制日志。其实xtrabackup还支持进行增量备份。

每个InnoDB的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长。这正是InnoDB表可以进行增量备份的基础，即innobackupex通过备份上次完整备份之后发生改变的页面来实现。

增量备份基本语法：

1.模拟插入数据

mysql>create table test(
     id int(4) not null,
     name char(20) not null,
     age tinyint(2)  NOT NULL default '0',
     dept varchar(16)  default NULL
     );
Query OK, 0 rows affected (0.03 sec)
mysql>insert into test(id,name,age) values(1,'oldboy',19);
mysql>insert into test(id,name,age) values(2,'oldgro;',20);

2.进行增量备份

[root@db02 tmp]# innobackupex --user=root --password=654321 --socket=/data/3306/mysql.sock --defaults-file=/data/3306/my.cnf --incremental /tmp/ --incremental-basedir=/tmp/2016-07-04_00-21-06/

如果没有错误提示就说说明没有问题

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
160704 00:22:03  innobackupex: Executing a version check against the server...
160704 00:22:03  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/data/3306/my.cnf;mysql_read_default_group=xtrabackup;mysql_socket=/data/3306/mysql.sock' as 'root'  (using password: YES).
160704 00:22:03  innobackupex: Connected to MySQL server
160704 00:22:03  innobackupex: Done.
160704 00:22:03  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/data/3306/my.cnf;mysql_read_default_group=xtrabackup;mysql_socket=/data/3306/mysql.sock' as 'root'  (using password: YES).
160704 00:22:03  innobackupex: Connected to MySQL server
160704 00:22:03  innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".
innobackupex:  Using server version 5.5.49-log
innobackupex: Created backup directory /tmp/2016-07-04_00-22-03
160704 00:22:03  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/data/3306/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/tmp/2016-07-04_00-22-03 --tmpdir=/tmp --extra-lsndir='/tmp' --incremental-basedir='/tmp/2016-07-04_00-21-06/'
innobackupex: Waiting for ibbackup (pid=3312) to suspend
innobackupex: Suspend file '/tmp/2016-07-04_00-22-03/xtrabackup_suspended_2'
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 70f4be3)
incremental backup from 2099425 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/3306/data
xtrabackup: open files limit requested 1024, set to 65535
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 4194304
>> log scanned up to (2099425)
xtrabackup: Generating a list of tablespaces
[01] Copying ./ibdata1 to /tmp/2016-07-04_00-22-03/ibdata1.delta
[01]        ...done
>> log scanned up to (2099425)
xtrabackup: Creating suspend file '/tmp/2016-07-04_00-22-03/xtrabackup_suspended_2' with pid '3312'
160704 00:22:05  innobackupex: Continuing after ibbackup has suspended
160704 00:22:05  innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
160704 00:22:05  innobackupex: Executing FLUSH TABLES WITH READ LOCK...
160704 00:22:05  innobackupex: All tables locked and flushed to disk
160704 00:22:05  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/data/3306/data/'
innobackupex: Backing up file '/data/3306/data//qqqqqqq41233/db.opt'
innobackupex: Backing up file '/data/3306/data//dadadadadadad/db.opt'
innobackupex: Backing up file '/data/3306/data//qqqqqqqq3/db.opt'
>> log scanned up to (2099425)
innobackupex: Backing up file '/data/3306/data//qqqqqqqq/db.opt'
innobackupex: Backing up file '/data/3306/data//qqqqqqq43/db.opt'
innobackupex: Backing up file '/data/3306/data//oldboy_gbk/db.opt'
innobackupex: Backing up file '/data/3306/data//oldboy/student.frm'
innobackupex: Backing up file '/data/3306/data//oldboy/db.opt'
innobackupex: Backing up file '/data/3306/data//ooooooooooooooooo/db.opt'
innobackupex: Backing up files '/data/3306/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
innobackupex: Backing up file '/data/3306/data//qqqqqqqq1/db.opt'
innobackupex: Backing up file '/data/3306/data//wordpress/db.opt'
innobackupex: Backing up file '/data/3306/data//aaa/db.opt'
innobackupex: Backing up files '/data/3306/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)
innobackupex: Backing up file '/data/3306/data//cyh/db.opt'
innobackupex: Backing up file '/data/3306/data//wuyi/db.opt'
160704 00:22:06  innobackupex: Finished backing up non-InnoDB tables and files
160704 00:22:06  innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
160704 00:22:06  innobackupex: Waiting for log copying to finish
xtrabackup: The latest check point (for incremental): '2099425'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (2099425)
xtrabackup: Creating suspend file '/tmp/2016-07-04_00-22-03/xtrabackup_log_copied' with pid '3312'
xtrabackup: Transaction log of lsn (2099425) to (2099425) was copied.
160704 00:22:07  innobackupex: All tables unlocked
innobackupex: Backup created in directory '/tmp/2016-07-04_00-22-03'
innobackupex: MySQL binlog position: filename 'mysql-bin.000029', position 1258
160704 00:22:07  innobackupex: Connection to database server closed
160704 00:22:07  innobackupex: completed OK!

查看xtrabackup_checkpoints

backup_type = incremental
from_lsn = 1768597
to_lsn = 1769413
last_lsn = 1769413
compact = 0
recover_binlog_info = 0

准备

准备（prepare）增量备份与整理完全备份有着一些不同，尤其是要注意的是：

（1）需要在每个备份（包括完全和增量备份）上，将已经提交的事务进行“重放”。

“重放”之后，所有的备份数据将合并到完全备份上

（2）基于所有的备份将末提交的事务进行“回滚”。

执行完全备份的redo；

# innobackupex --apply-log --redo-only BASE-DIR

[root@db02 data]# innobackupex --user=root --password=654321 --socket=/data/3306/mysql.sock --defaults-file=/data/3306/my.cnf --apply-log --redo-only /tmp/2016-07-04_00-21-06/

接着执行第一个增量：

例子# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1
[root@db02 data]# innobackupex --user=root --password=654321 --socket=/data/3306/mysql.sock --defaults-file=/data/3306/my.cnf --apply-log --redo-only /tmp/2016-07-04_00-21-06/ --incremental-dir=/tmp/2016-07-04_00-34-48/

而后是第二个增量：因为我们只执行了一次增量，所以只有一个文件夹

例子# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2
[root@db02 data]# innobackupex --user=root --password=654321 --socket=/data/3306/mysql.sock --defaults-file=/data/3306/my.cnf --apply-log --redo-only /tmp/2016-07-04_00-21-06/ --incremental-dir=/tmp/2016-07-04_00-34-48/

提示：

其中BASE-DIR指的是完全备份所在的目录，而INCREMENTAL-DIR-1指定的是第一次增量备份的目录，INCREMENTAL-DIR=2指的是第二次增量备份的目录，其他依次类推，即如果有多次增量备份，每一次都要执行如上操作。

Xtrabackup的“流”及“备份压缩”功能

Xtrabackup对备份的数据文件支持“流”功能，即可以将备份的数据通过STDOUT传输给tar程序进行归档，而不是默认的直接保存至某备份目录中。要使用此功能，仅需要使用--stream选项即可。如：

# innobackupex --stream=tar  /backup | gzip > /backup/`date +%F_%H-%M-%S`.tar.gz

甚至也可以使用类似如下命令将数据备份至其它服务器：

# innobackupex --stream=tar  /backup | ssh user@www.magedu.com  "cat -  > /backups/`date +%F_%H-%M-%S`.tar"

此外，在执行本地备份时，还可以使用--parallel选项对多个文件进行并行复制。此选项用于指定在复制时启动的线程数目。当然，在实际进行备
份时要利用此功能的便利性，也需要启用innodb_file_per_table选项或共享的表空间通过innodb_data_file_path选
项存储在多个ibdata文件中。对某一数据库的多个文件的复制无法利用到此功能。其简单使用方法如下：

# innobackupex --parallel  /path/to/backup

同时，innobackupex备份的数据文件也可以存储至远程主机，这可以使用--remote-host选项来实现：

# innobackupex --remote-host=root@www.magedu.com  /path/IN/REMOTE/HOST/to/backup

导入或导出单张表

默认情况下，InnoDB表不能通过直接复制表文件的方式在mysql服务器之间进行移植，即便使用了innodb_file_per_table 选项。而使用Xtrabackup工具可以实现此种功能，不过，此时需要“导出”表的mysql服务器启用了innodb_file_per_table 选项（严格来说，是要“导出”的表在其创建之前，mysql服务器就启用了innodb_file_per_table选项），并且“导入”表的服务器同时启用了innodb_file_per_table和innodb_expand_import选项。

“导出”表

导出表是在备份的prepare阶段进行的，因此，一旦完全备份完成，就可以在prepare过程中通过--export选项将某表导出了：

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

此命令会为每个innodb表的表空间创建一个以.exp结尾的文件，这些以.exp结尾的文件则可以用于导入至其它服务器。

“导入”表

要在mysql服务器上导入来自于其它服务器的某innodb表，需要先在当前服务器上创建一个跟原表表结构一致的表，而后才能实现将表导入：
mysql> CREATE TABLE mytable (...)  ENGINE=InnoDB;
然后将此表的表空间删除：
mysql> ALTER TABLE mydatabase.mytable  DISCARD TABLESPACE;
接下来，将来自于“导出”表的服务器的mytable表的mytable.ibd和mytable.exp文件复制到当前服务器的数据目录，然后使用如下命令将其“导入”：
mysql> ALTER TABLE mydatabase.mytable  IMPORT TABLESPACE;

使用Xtrabackup对数据库进行部分备份

Xtrabackup也可以实现部分备份，即只备份某个或某些指定的数据库或某数据库中的某个或某些表。但要使用此功能，必须启用 innodb_file_per_table选项，即每张表保存为一个独立的文件。同时，其也不支持--stream选项，即不支持将数据通过管道传输给其它程序进行处理。

此外，还原部分备份跟还原全部数据的备份也有所不同，即你不能通过简单地将prepared的部分备份使用--copy-back选项直接复制回数据目录，而是要通过导入表的方向来实现还原。当然，有些情况下，部分备份也可以直接通过--copy-back进行还原，但这种方式还原而来的数据多数会产生数据不一致的问题，因此，无论如何不推荐使用这种方式。

创建部分备份

创建部分备份的方式有三种：正则表达式(--include), 枚举表文件(--tables-file)和列出要备份的数据库(--databases)。

使用--include

使用--include时，要求为其指定要备份的表的完整名称，即形如databasename.tablename，如：

# innobackupex --include='^mageedu[.]tb1' 
/path/to/backup

使用--tables-file

此选项的参数需要是一个文件名，此文件中每行包含一个要备份的表的完整名称；如：

# echo -e 'mageedu.tb1\nmageedu.tb2' > /tmp/tables.txt
# innobackupex --tables-file=/tmp/tables.txt  /path/to/backup

使用--databases

此选项接受的参数为数据名，如果要指定多个数据库，彼此间需要以空格隔开；同时，在指定某数据库时，也可以只指定其中的某张表。此外，此选项也可以接受一个文件为参数，文件中每一行为一个要备份的对象。如：

# innobackupex --databases="mageedu testdb" 
/path/to/backup

整理(preparing)部分备份

prepare部分备份的过程类似于导出表的过程，要使用--export选项进行：

# innobackupex --apply-log --export 
/pat/to/partial/backup

此命令执行过程中，innobackupex会调用xtrabackup命令从数据字典中移除缺失的表，因此，会显示出许多关于“表不存在”类的警告信息。同时，也会显示出为备份文件中存在的表创建.exp文件的相关信息。

还原部分备份

还原部分备份的过程跟导入表的过程相同。当然，也可以通过直接复制prepared状态的备份直接至数据目录中实现还原，不要此时要求数据目录处于一致状态。

运维人生

Xtrabackup安装以及应用

MySQL Backup Tool Feature Comparison

xtrabackup安装方法

1、Installing Percona XtraBackup from Percona `yum` repository

2、Installing Percona XtraBackup using downloaded rpm packages

Uninstalling Percona XtraBackup

Download Percona Server 5.7

What’s in each RPM package?¶

检查安装结果：

使用xtrabackup实现对MySQL的备份

1.完全备份

备份后的文件：

2. 准备（prepare）一个完整备份

模式数据库修改

模拟数据库损坏

还原完全备份

还原数据库语法

增量备份二进制文件：

还原增量备份

使用innobackupex进行增量备份

增量备份基本语法：

2.进行增量备份

准备

Xtrabackup的“流”及“备份压缩”功能

导入或导出单张表

“导出”表

“导入”表

使用Xtrabackup对数据库进行部分备份

创建部分备份

使用--include

使用--tables-file

使用--databases

整理(preparing)部分备份

还原部分备份

公告

运维人生

mysql数据库：Xtrabackup安装以及应用

Xtrabackup安装以及应用

MySQL Backup Tool Feature Comparison

xtrabackup安装方法

1、Installing Percona XtraBackup from Percona yum repository

2、Installing Percona XtraBackup using downloaded rpm packages

Uninstalling Percona XtraBackup

Download Percona Server 5.7

What’s in each RPM package?¶

检查安装结果：

使用xtrabackup实现对MySQL的备份

1.完全备份

备份后的文件：

2. 准备（prepare）一个完整备份

模式数据库修改

模拟数据库损坏

还原完全备份

还原数据库语法

增量备份二进制文件：

还原增量备份

使用innobackupex进行增量备份

增量备份基本语法：

2.进行增量备份

准备

Xtrabackup的“流”及“备份压缩”功能

导入或导出单张表

“导出”表

“导入”表

使用Xtrabackup对数据库进行部分备份

创建部分备份

使用--include

使用--tables-file

使用--databases

整理(preparing)部分备份

还原部分备份

公告

1、Installing Percona XtraBackup from Percona `yum` repository