Percona XtraBackup 2.4 innobackupex全量、增量备份恢复流程

妖狐艹你老母 2024-04-18 01:50 3阅读 0赞

**innobackupex是xtrabackup的软连接。**  
**xtrabackup 使用C语言编译的备份工具，可以完整备份MySQL数据库，支持MyISAM、InnoDB和XtraDB存储引擎。**  
  
在备份的时候，备份工具主要执行两个任务来完成备份：  
① 在后台启动一个日志拷贝线程。这个线程会监视InnoDB日志文件，当日志文件发生改变时，这个线程会将发生变化的数据块拷贝到备份目录下一个名为xtrabackup\_logfile的文件中。这个操作是必要的，因为备份可能会持续很长时间，在数据库恢复时，需要所有从备份开始到结束的这些日志文件。  
② 拷贝InnoDB数据文件到指定备份目录下。这不是一个简单的拷贝，备份工具打开并读取文件的方式类似InnoDB，通过读取文件目录并以页（page）为单位进行拷贝。  
  
当数据文件拷贝结束时，xtrabackup会停止日志拷贝线程，并在指定备份目录中创建一个名为xtrabackup\_checkpoints的文件，这个文件包含备份的类型、开始备份的日志序列号和结束备份的日志序列号。  
  
\[root@localhost bin\]\# ls -trl /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin  
 total 224028  
 \-rwxr-xr-x. 1 root root      3020 Mar 29 04:36 xbcloud\_osenv  
 \-rwxr-xr-x. 1 root root   5070569 Mar 29 04:36 xbstream  
 \-rwxr-xr-x. 1 root root   4999056 Mar 29 04:36 xbcrypt  
 \-rwxr-xr-x. 1 root root   5179178 Mar 29 04:36 xbcloud  
 \-rwxr-xr-x. 1 root root 214139498 Mar 29 04:50 xtrabackup  
 lrwxrwxrwx. 1 root root        10 Apr 25 01:11 innobackupex -> xtrabackup  
  
**innobackupex常用参数**  
  
\--compact        创建一个不包含第二索引（除了主键之外的索引）的备份  
  
\--decompress        解压之前所有以–compress参数备份出来的带有.qp格式的备份文件，--parallel参数会允许同时解锁或解压多个文件。需要安装qpress软件。  
  
 \--defaults-file=\[MY.CNF\] 配置文件的路径  
  
 \--incremental-basedir 以上一次全量或增量备份的路径，作为增量备份的基础。指定这个参数的同时，应该同样指定--incremental参数  
  
 \--incremental 创建增量备份，当指定这个参数的时候，应该指定--incremental-lsn或--incremental-basedir参数，否则将会备份到--incremental-basedir路径  
  
 \--apply-log     在备份目录下，通过应用名称为xtrabackup\_logfile的交易日志文件来准备备份。同时，创建新的交易日志。  
  
 \--use-memory=\# 指定数据库恢复时使用的内存大小，需要搭配--apply-log参数。  
  
 \--redo-only 当准备数据库的全备或合并增量备份时，需要指定这个参数。这个参数实际上执行的是 *[xtrabackup --apply-log-only][]，* 会让xtrabackup跳过回滚节点，只做“redo”步骤。当数据库需要应用增量备份时，需要指定这个参数。  
  
\--incremental-dir=DIRECTORY 指定增量备份的目录，需要搭配--incremental参数。  
  
\--no-timestamp          这个参数会让xtrabackup在备份的时候不创建带有时间格式的子文件夹。当指定了这个参数，备份会直接创建在指定的备份目录下。  
  
 \--stream=STREAMNAME 指定流备份的格式。备份将会以指定格式输出到STDOUT。目前支持的格式有tar 和 xbstream。如果指定了这个参数，后面需要接tmpdir目录作为处理流的一个中间目录。  
  
 \--slave-info    当备份一个作为复制环境的服务器时，这个参数会自动将CHANGE MASTER语句写到备份中，在恢复备份后，不必执行CHANGE MASTER语句。  
  
\--tables-file=FILE          这个参数会接受一个字符串，这个字符串指定了一个文件，这个文件包含了要备份的表名，格式如database.table，一行一个。  
  
 \--use-memory=\#        这个参数用于在准备备份时，xtrabackup执行crash recovery所使用的内存大小。这个参数仅和--apply-log搭配时才生效。  
  
\--创建备份账户  
mysql> create user xtrabk@'localhost' identified by 'Myql\#2015';  
 Query OK, 0 rows affected (0.18 sec)  
 mysql> grant reload,lock tables,replication client,super on \*.\* to xtrabk@'localhost';  
 Query OK, 0 rows affected (0.00 sec)  
 mysql> flush privileges;  
 Query OK, 0 rows affected (0.44 sec)  
  
需要注意的是，xtrabackup没有提供日志功能。如果要记录日志的话，需要在备份命令中添加下列命令。

innobackupex --defaults-file=/var/lib/mysql/main\_my.cnf \\

>  \--user=$\{MYSQL\_USER\} --password=$\{MYSQL\_PASS\} \\

>  \--extra-lsndir=/database/backup/2016-08-09/checkpoints/ \\

>  \--compress --compress-threads=8 \\

>  \--stream=xbstream --parallel=4 /tmp \\

>  **2>> /database/backup/2016-08-09/xtra\_full\_bak\_2016-08-09.log** \\

>  > /database/backup/2016-08-09/xtra\_full\_bak\_2016-08-09.xbstream

**--innobackupex全量备份数据库**  
 \[root@localhost 20160719\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/ **innobackupex --defaults-file=/etc/my.cnf --user=root --password='System\#2013' /backup/20160716/**

...

160716 01:48:48 \[00\] Writing xtrabackup\_binlog\_info  
 160716 01:48:48 \[00\]        ...done  
 160716 01:48:48 Executing FLUSH NO\_WRITE\_TO\_BINLOG ENGINE LOGS...  
 xtrabackup: The latest check point (for incremental): '186510528'  
 xtrabackup: Stopping log copying thread.  
 .160716 01:48:49 >> log scanned up to (186510537)  
  
160716 01:48:49 Executing UNLOCK TABLES  
 160716 01:48:49 All tables unlocked  
 160716 01:48:49 \[00\] Copying ib\_buffer\_pool to /backup/20160716/2016-07-16\_01-48-32/ib\_buffer\_pool  
 160716 01:48:49 \[00\]        ...done  
 160716 01:48:49 Backup created in directory '/backup/20160716/2016-07-16\_01-48-32'  
 MySQL binlog position: filename 'production-bin.000006', position '194', GTID of the last change 'cf291e84-2c89-11e6-b6f0-000c29631605:1-44'  
 160716 01:48:49 \[00\] Writing backup-my.cnf  
 160716 01:48:49 \[00\]        ...done  
 160716 01:48:49 \[00\] Writing xtrabackup\_info  
 160716 01:48:49 \[00\]        ...done  
 xtrabackup: Transaction log of lsn (186510528) to (186510537) was copied.  
**160716 01:48:49 completed OK!**

\--查看备份出来的文件  
\[root@localhost 20160716\]\# cd 2016-07-16\_01-48-32/  
 \[root@localhost 2016-07-16\_01-48-32\]\# ls -trl  
 total 77876  
 \-rw-r-----. 1 root root 79691776 Jul 16 01:48 ibdata1  
 drwxr-x---. 2 root root     4096 Jul 16 01:48 fire  
 drwxr-x---. 2 root root    12288 Jul 16 01:48 sys  
 drwxr-x---. 2 root root     4096 Jul 16 01:48 performance\_schema  
 drwxr-x---. 2 root root     4096 Jul 16 01:48 mysql  
 \-rw-r-----. 1 root root       68 Jul 16 01:48 xtrabackup\_binlog\_info  
 \-rw-r-----. 1 root root     2560 Jul 16 01:48 xtrabackup\_logfile  
 \-rw-r-----. 1 root root      117 Jul 16 01:48 xtrabackup\_checkpoints  
 \-rw-r-----. 1 root root     6566 Jul 16 01:48 ib\_buffer\_pool  
 \-rw-r-----. 1 root root      428 Jul 16 01:48 backup-my.cnf  
 \-rw-r-----. 1 root root      577 Jul 16 01:48 xtrabackup\_info

\[root@localhost 2016-07-16\_01-48-32\]\# cat xtrabackup\_info   
 uuid = 1d38a5f2-4b32-11e6-9859-000c29631605  
 name =   
 tool\_name = innobackupex  
 tool\_command = --defaults-file=/etc/my.cnf --user=root --password=... /backup/20160716/  
 tool\_version = 2.4.2  
 ibbackup\_version = 2.4.2  
 server\_version = 5.7.12-log  
 start\_time = 2016-07-16 01:48:32  
 end\_time = 2016-07-16 01:48:49  
 lock\_time = 0  
 binlog\_pos = filename 'production-bin.000006', position '194', GTID of the last change 'cf291e84-2c89-11e6-b6f0-000c29631605:1-44'  
 innodb\_from\_lsn = 0  
 innodb\_to\_lsn = 186510528  
 partial = N  
 incremental = N  
 format = file  
 compact = N  
 compressed = N  
 encrypted = N

\[root@localhost 2016-07-19\_02-07-35\]\# cat xtrabackup\_checkpoints   
 backup\_type = full-backuped  
 from\_lsn = 0  
 to\_lsn = 186511470  
 last\_lsn = 186511479  
 compact = 0  
 recover\_binlog\_info = 0

**--innobackupex全量备份恢复**   
\--在数据库中创建测试表并插入数据  
mysql> use fire  
 Reading table information for completion of table and column names  
 You can turn off this feature to get a quicker startup with -A  
  
 Database changed  
 mysql> show tables;  
 \+----------------+  
 | Tables\_in\_fire |  
 \+----------------+  
 | t1             |  
 | t2             |  
 | test           |  
 | v\_t1           |  
 | v\_t1\_myisam    |  
 \+----------------+  
 5 rows in set (0.00 sec)  
  
 mysql> select \* from t1;  
 \+------+------+  
 | f1   | gc   |  
 \+------+------+  
 |    1 |    2 |  
 |    2 |    3 |  
 |    3 |    4 |  
 |    4 |    5 |  
 |    5 |    6 |  
 \+------+------+  
 5 rows in set (0.00 sec)  
  
 mysql> delete from t1;  
 Query OK, 5 rows affected (0.00 sec)  
 mysql> commit;  
 Query OK, 0 rows affected (0.00 sec)  
 mysql> select \* from t1;  
 Empty set (0.00 sec)  
  
\--关闭数据库  
\[root@localhost fire\]\# service mysqld stop  
  
\--删除数据文件目录下的所有文件  
\[root@localhost ~\]\# cd /var/lib/mysql  
 \[root@localhost mysql\]\# ls  
 auto.cnf         client-key.pem  ib\_logfile0                performance\_schema     production-bin.000003  production-bin.000007  server-cert.pem  
 ca-key.pem       fire            ib\_logfile1                private\_key.pem        production-bin.000004  production-bin.000008  server-key.pem  
 ca.pem           ib\_buffer\_pool  localhost.localdomain.err  production-bin.000001  production-bin.000005  production-bin.index   sys  
 client-cert.pem  ibdata1         mysql                      production-bin.000002  production-bin.000006  public\_key.pem  
 \[root@localhost mysql\]\# rm -rf \*  
  
**--** **备份出来的数据文件由于拷贝时间不同，在拷贝数据的过程中，数据可能会发生变化，直接使用这些数据文件，会导致恢复出来的数据文件可能会有数据损坏。准备备份文件的目的，是让数据文件在某时间点上保持一致性。可以在任意机器上执行准备备份文件这一动作，不需要仅在备份的主机上面执行这一动作。** **准备好备份，使用--apply-log参数并指定带时间的备份子目录。想要加快apply-log的进程，推荐使用--use-memory参数。**  
\[root@localhost 2016-07-16\_01-48-32\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/innobackupex --defaults-file=/etc/my.cnf  --apply-log /backup/20160716/2016-07-16\_01-48-32/  
 .....  
 xtrabackup: using the following InnoDB configuration for recovery:  
 xtrabackup:   innodb\_data\_home\_dir = .  
 xtrabackup:   innodb\_data\_file\_path = ibdata1:12M:autoextend  
 xtrabackup:   innodb\_log\_group\_home\_dir = .  
 xtrabackup:   innodb\_log\_files\_in\_group = 2  
 xtrabackup:   innodb\_log\_file\_size = 50331648  
 InnoDB: PUNCH HOLE support not available  
 InnoDB: Mutexes and rw\_locks use GCC atomic builtins  
 InnoDB: Uses event mutexes  
 InnoDB: GCC builtin \_\_sync\_synchronize() is used for memory barrier  
 InnoDB: Compressed tables use zlib 1.2.3  
 InnoDB: Number of pools: 1  
 InnoDB: Using CPU crc32 instructions  
 InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M  
 InnoDB: Completed initialization of buffer pool  
 InnoDB: page\_cleaner coordinator priority: -20  
 InnoDB: Highest supported file format is Barracuda.  
 InnoDB: Removed temporary tablespace data file: "ibtmp1"  
 InnoDB: Creating shared tablespace for temporary tables  
 InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...  
 InnoDB: File './ibtmp1' size is now 12 MB.  
 InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.  
 InnoDB: 32 non-redo rollback segment(s) are active.  
 InnoDB: Waiting for purge to start  
 InnoDB: 5.7.11 started; log sequence number 186511139  
 xtrabackup: starting shutdown with innodb\_fast\_shutdown = 1  
 InnoDB: FTS optimize thread exiting.  
 InnoDB: Starting shutdown...  
\--当准备备份的操作完成后，会看到下面信息  
**InnoDB: Shutdown completed; log sequence number 186511158**  
 160716 03:07:07 completed OK!  
  
**--** **恢复已经准备好的备份，停止MySQL服务并在innobackupex命令中附带--copy-back参数，这会将已经准备好的数据拷贝回my.cnf参数文件中指定的数据目录下。**  
\[root@localhost 2016-07-16\_01-48-32\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/innobackupex --defaults-file=/etc/my.cnf  --copy-back /backup/20160716/2016-07-16\_01-48-32/  
 .....  
 160716 03:12:30 \[01\] Copying ./mysql/engine\_cost.frm to /var/lib/mysql/mysql/engine\_cost.frm  
 160716 03:12:30 \[01\]        ...done  
 160716 03:12:30 \[01\] Copying ./mysql/user.MYD to /var/lib/mysql/mysql/user.MYD  
 160716 03:12:30 \[01\]        ...done  
 160716 03:12:30 \[01\] Copying ./mysql/server\_cost.frm to /var/lib/mysql/mysql/server\_cost.frm  
 160716 03:12:30 \[01\]        ...done  
 160716 03:12:30 \[01\] Copying ./mysql/db.MYD to /var/lib/mysql/mysql/db.MYD  
 160716 03:12:30 \[01\]        ...done  
 160716 03:12:30 \[01\] Copying ./mysql/general\_log.CSV to /var/lib/mysql/mysql/general\_log.CSV  
 160716 03:12:30 \[01\]        ...done  
 160716 03:12:30 \[01\] Copying ./mysql/time\_zone\_name.ibd to /var/lib/mysql/mysql/time\_zone\_name.ibd  
 160716 03:12:30 \[01\]        ...done  
 160716 03:12:30 \[01\] Copying ./mysql/help\_category.frm to /var/lib/mysql/mysql/help\_category.frm  
 160716 03:12:30 \[01\]        ...done  
 160716 03:12:30 \[01\] Copying ./mysql/innodb\_table\_stats.frm to /var/lib/mysql/mysql/innodb\_table\_stats.frm  
 160716 03:12:30 \[01\]        ...done  
 160716 03:12:30 \[01\] Copying ./mysql/slave\_master\_info.frm to /var/lib/mysql/mysql/slave\_master\_info.frm  
 160716 03:12:30 \[01\]        ...done  
 160716 03:12:30 \[01\] Copying ./mysql/func.MYD to /var/lib/mysql/mysql/func.MYD  
 160716 03:12:30 \[01\]        ...done  
 160716 03:12:30 \[01\] Copying ./mysql/user.frm to /var/lib/mysql/mysql/user.frm  
 160716 03:12:30 \[01\]        ...done  
 160716 03:12:30 \[01\] Copying ./mysql/proxies\_priv.frm to /var/lib/mysql/mysql/proxies\_priv.frm  
 160716 03:12:30 \[01\]        ...done  
 160716 03:12:30 \[01\] Copying ./mysql/proc.MYD to /var/lib/mysql/mysql/proc.MYD  
 160716 03:12:30 \[01\]        ...done  
 160716 03:12:30 \[01\] Copying ./mysql/help\_relation.frm to /var/lib/mysql/mysql/help\_relation.frm  
 160716 03:12:30 \[01\]        ...done  
 160716 03:12:30 \[01\] Copying ./ib\_buffer\_pool to /var/lib/mysql/ib\_buffer\_pool  
 160716 03:12:30 \[01\]        ...done  
 160716 03:12:30 \[01\] Copying ./xtrabackup\_binlog\_pos\_innodb to /var/lib/mysql/xtrabackup\_binlog\_pos\_innodb  
 160716 03:12:30 \[01\]        ...done  
 160716 03:12:30 completed OK!  
  
\--数据恢复完成之后，需要修改相关文件的权限  
\[root@localhost 2016-07-16\_01-48-32\]\# chown -R mysql.mysql /var/lib/mysql  
  
\--启动数据库  
\[root@localhost fire\]\# service mysqld start  
  
\--查看删除的数据，已经恢复  
mysql> select \* from fire.t1;  
 \+------+------+  
 | f1   | gc   |  
 \+------+------+  
 |    1 |    2 |  
 |    2 |    3 |  
 |    3 |    4 |  
 |    4 |    5 |  
 |    5 |    6 |  
 \+------+------+  
 5 rows in set (0.00 sec)  
  
**--使用流的格式压缩备份全库**  
\[root@localhost bin\]\# ./innobackupex  --defaults-file=/etc/my.cnf --stream=tar /tmp --user system --password 'Mysql\#2015' | gzip -> /backup/xtra/xtra\_fullbackup.tar.gz  
  
**--innobackupex增量备份**  
  
 **--进行全** **量备份**  
\[root@localhost backup\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password='System\#2013' /backup/20160719/

.....  
 160719 07:24:33 \[01\] Copying ./mysql/help\_relation.frm to /backup/20160719/2016-07-19\_07-24-18/mysql/help\_relation.frm  
 160719 07:24:33 \[01\]        ...done  
 160719 07:24:33 Finished backing up non-InnoDB tables and files  
 160719 07:24:33 \[00\] Writing xtrabackup\_binlog\_info  
 160719 07:24:33 \[00\]        ...done  
 160719 07:24:33 Executing FLUSH NO\_WRITE\_TO\_BINLOG ENGINE LOGS...  
 xtrabackup: The latest check point (for incremental): '186521799'  
 xtrabackup: Stopping log copying thread.  
 .160719 07:24:33 >> log scanned up to (186521808)  
  
 160719 07:24:33 Executing UNLOCK TABLES  
 160719 07:24:33 All tables unlocked  
 160719 07:24:33 \[00\] Copying ib\_buffer\_pool to /backup/20160719/2016-07-19\_07-24-18/ib\_buffer\_pool  
 160719 07:24:33 \[00\]        ...done  
 160719 07:24:33 Backup created in directory '/backup/20160719/2016-07-19\_07-24-18'  
 MySQL binlog position: filename 'production-bin.000001', position '335', GTID of the last change 'cf291e84-2c89-11e6-b6f0-000c29631605:1-44,  
 e240ea67-4dbb-11e6-a7d5-000c29631605:1'  
 160719 07:24:33 \[00\] Writing backup-my.cnf  
 160719 07:24:33 \[00\]        ...done  
 160719 07:24:34 \[00\] Writing xtrabackup\_info  
 160719 07:24:34 \[00\]        ...done  
 xtrabackup: Transaction log of lsn (186521799) to (186521808) was copied.  
 160719 07:24:34 completed OK!

\[root@localhost 2016-07-19\_07-24-18\]\# cat xtrabackup\_checkpoints   
 backup\_type = full-backuped  
**from\_lsn = 0**  
**to\_lsn = 186521799**  
 last\_lsn = 186521808  
 compact = 0  
 recover\_binlog\_info = 0

\--创建测试表一并插入数据  
mysql> use fire  
 Database changed  
 mysql> create table emp(id int(7), name varchar(15));  
 Query OK, 0 rows affected (0.53 sec)  
  
 mysql> insert into emp values(10, 'Neo');  
 Query OK, 1 row affected (0.15 sec)  
 mysql> insert into emp values(20, 'Trinity');  
 Query OK, 1 row affected (0.03 sec)  
 mysql> commit;  
 Query OK, 0 rows affected (0.04 sec)  
  
 mysql> select \* from emp;  
 \+------+---------+  
 | id   | name    |  
 \+------+---------+  
 |   10 | Neo     |  
 |   20 | Trinity |  
 \+------+---------+  
 2 rows in set (0.06 sec)  
  
**--进行增量备份一，以上面的全量备份目录为基础**  
\[root@localhost 2016-07-19\_07-24-18\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password='System\#2013' --incremental /backup/20160719/inc1 --incremental-basedir=/backup/20160719/2016-07-19\_07-24-18/  
 .....  
 160719 07:29:04 \[01\] Copying ./mysql/proc.MYD to /backup/20160719/inc1/2016-07-19\_07-28-54/mysql/proc.MYD  
 160719 07:29:04 \[01\]        ...done  
 160719 07:29:04 \[01\] Copying ./mysql/help\_relation.frm to /backup/20160719/inc1/2016-07-19\_07-28-54/mysql/help\_relation.frm  
 160719 07:29:04 \[01\]        ...done  
 160719 07:29:04 Finished backing up non-InnoDB tables and files  
 160719 07:29:04 \[00\] Writing xtrabackup\_binlog\_info  
 160719 07:29:04 \[00\]        ...done  
 160719 07:29:04 Executing FLUSH NO\_WRITE\_TO\_BINLOG ENGINE LOGS...  
 xtrabackup: The latest check point (for incremental): '186528276'  
 xtrabackup: Stopping log copying thread.  
 .160719 07:29:04 >> log scanned up to (186528285)  
  
 160719 07:29:04 Executing UNLOCK TABLES  
 160719 07:29:04 All tables unlocked  
 160719 07:29:04 \[00\] Copying ib\_buffer\_pool to /backup/20160719/inc1/2016-07-19\_07-28-54/ib\_buffer\_pool  
 160719 07:29:04 \[00\]        ...done  
 160719 07:29:04 Backup created in directory '/backup/20160719/inc1/2016-07-19\_07-28-54'  
 MySQL binlog position: filename 'production-bin.000001', position '1045', GTID of the last change 'cf291e84-2c89-11e6-b6f0-000c29631605:1-44,  
 e240ea67-4dbb-11e6-a7d5-000c29631605:1-4'  
 160719 07:29:04 \[00\] Writing backup-my.cnf  
 160719 07:29:04 \[00\]        ...done  
 160719 07:29:04 \[00\] Writing xtrabackup\_info  
 160719 07:29:04 \[00\]        ...done  
 xtrabackup: Transaction log of lsn (186528276) to (186528285) was copied.  
 160719 07:29:04 completed OK!

\[root@localhost 2016-07-19\_07-28-54\]\# cat xtrabackup\_checkpoints   
 backup\_type = incremental  
**from\_lsn = 186521799**  
**to\_lsn = 186528276**  
 last\_lsn = 186528285  
 compact = 0  
 recover\_binlog\_info = 0  
  
\--创建测试表二并插入数据  
mysql> create table emp2 like emp;  
 Query OK, 0 rows affected (0.12 sec)  
 mysql> insert into emp2 select \* from emp;  
 Query OK, 2 rows affected (0.01 sec)  
 Records: 2  Duplicates: 0  Warnings: 0  
 mysql> insert into emp2 select \* from emp;  
 Query OK, 2 rows affected (0.00 sec)  
 Records: 2  Duplicates: 0  Warnings: 0  
 mysql> insert into emp2 select \* from emp;  
 Query OK, 2 rows affected (0.01 sec)  
 Records: 2  Duplicates: 0  Warnings: 0  
 mysql> select count(\*) from emp2;  
 \+----------+  
 | count(\*) |  
 \+----------+  
 |        6 |  
 \+----------+  
 1 row in set (0.00 sec)  
 mysql> commit;  
 Query OK, 0 rows affected (0.00 sec)

**--进行增量备份二，以上面的全量备份目录为基础**  
\[root@localhost 2016-07-19\_07-28-54\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password='System\#2013' --incremental /backup/20160719/inc2 --incremental-basedir=/backup/20160719/2016-07-19\_07-24-18/

.....

160719 07:35:19 \[01\] Copying ./mysql/help\_relation.frm to /backup/20160719/inc2/2016-07-19\_07-35-08/mysql/help\_relation.frm  
 160719 07:35:19 \[01\]        ...done  
 160719 07:35:19 Finished backing up non-InnoDB tables and files  
 160719 07:35:19 \[00\] Writing xtrabackup\_binlog\_info  
 160719 07:35:19 \[00\]        ...done  
 160719 07:35:19 Executing FLUSH NO\_WRITE\_TO\_BINLOG ENGINE LOGS...  
 xtrabackup: The latest check point (for incremental): '186537693'  
 xtrabackup: Stopping log copying thread.  
 .160719 07:35:19 >> log scanned up to (186537702)  
  
 160719 07:35:19 Executing UNLOCK TABLES  
 160719 07:35:19 All tables unlocked  
 160719 07:35:19 \[00\] Copying ib\_buffer\_pool to /backup/20160719/inc2/2016-07-19\_07-35-08/ib\_buffer\_pool  
 160719 07:35:19 \[00\]        ...done  
 160719 07:35:19 Backup created in directory '/backup/20160719/inc2/2016-07-19\_07-35-08'  
 MySQL binlog position: filename 'production-bin.000001', position '2028', GTID of the last change 'cf291e84-2c89-11e6-b6f0-000c29631605:1-44,  
 e240ea67-4dbb-11e6-a7d5-000c29631605:1-8'  
 160719 07:35:19 \[00\] Writing backup-my.cnf  
 160719 07:35:19 \[00\]        ...done  
 160719 07:35:19 \[00\] Writing xtrabackup\_info  
 160719 07:35:19 \[00\]        ...done  
 xtrabackup: Transaction log of lsn (186537693) to (186537702) was copied.  
 160719 07:35:19 completed OK!

\[root@localhost 2016-07-19\_07-35-08\]\# cat xtrabackup\_checkpoints   
 backup\_type = incremental  
 from\_lsn =  **186521799**  
 to\_lsn =  **186537693**  
 last\_lsn = 186537702  
 compact = 0  
 recover\_binlog\_info = 0  
  
**--进行增量备份三，以上面的增量备份一为基础**  
\[root@localhost 2016-07-19\_07-35-08\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password='System\#2013' --incremental /backup/20160719/inc3 --incremental-basedir=/backup/20160719/inc1/2016-07-19\_07-28-54/

.....

160719 07:40:27 \[01\] Copying ./mysql/help\_relation.frm to /backup/20160719/inc3/2016-07-19\_07-40-11/mysql/help\_relation.frm  
 160719 07:40:27 \[01\]        ...done  
 160719 07:40:27 Finished backing up non-InnoDB tables and files  
 160719 07:40:27 \[00\] Writing xtrabackup\_binlog\_info  
 160719 07:40:27 \[00\]        ...done  
 160719 07:40:27 Executing FLUSH NO\_WRITE\_TO\_BINLOG ENGINE LOGS...  
 xtrabackup: The latest check point (for incremental): '186537693'  
 xtrabackup: Stopping log copying thread.  
 .160719 07:40:27 >> log scanned up to (186537702)  
  
 160719 07:40:27 Executing UNLOCK TABLES  
 160719 07:40:27 All tables unlocked  
 160719 07:40:27 \[00\] Copying ib\_buffer\_pool to /backup/20160719/inc3/2016-07-19\_07-40-11/ib\_buffer\_pool  
 160719 07:40:27 \[00\]        ...done  
 160719 07:40:27 Backup created in directory '/backup/20160719/inc3/2016-07-19\_07-40-11'  
 MySQL binlog position: filename 'production-bin.000001', position '2028', GTID of the last change 'cf291e84-2c89-11e6-b6f0-000c29631605:1-44,  
 e240ea67-4dbb-11e6-a7d5-000c29631605:1-8'  
 160719 07:40:27 \[00\] Writing backup-my.cnf  
 160719 07:40:27 \[00\]        ...done  
 160719 07:40:27 \[00\] Writing xtrabackup\_info  
 160719 07:40:27 \[00\]        ...done  
 xtrabackup: Transaction log of lsn (186537693) to (186537702) was copied.  
 160719 07:40:27 completed OK!

\[root@localhost 2016-07-19\_07-40-11\]\# cat xtrabackup\_checkpoints   
 backup\_type = incremental  
**from\_lsn = 186528276**  
**to\_lsn = 186537693**  
 last\_lsn = 186537702  
 compact = 0  
 recover\_binlog\_info = 0

**--innobackupex增量恢复**  
**--准备基础全量备份，同时避免回滚阶段。**  
\[root@localhost backup\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/innobackupex --defaults-file=/etc/my.cnf --apply-log --redo-only /backup/20160719/2016-07-19\_07-24-18/

.....  
 InnoDB: Number of pools: 1  
 InnoDB: Using CPU crc32 instructions  
 InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M  
 InnoDB: Completed initialization of buffer pool  
 InnoDB: page\_cleaner coordinator priority: -20  
 InnoDB: Highest supported file format is Barracuda.  
 InnoDB: Log scan progressed past the checkpoint lsn 186521799  
 InnoDB: Doing recovery: scanned up to log sequence number 186521808 (0%)  
 InnoDB: Doing recovery: scanned up to log sequence number 186521808 (0%)  
 InnoDB: Database was not shutdown normally!  
 InnoDB: Starting crash recovery.  
 InnoDB: xtrabackup: Last MySQL binlog file position 864, file name production-bin.000003  
 InnoDB: xtrabackup: Last MySQL binlog file position 864, file name production-bin.000003  
  
 xtrabackup: starting shutdown with innodb\_fast\_shutdown = 1  
 InnoDB: Starting shutdown...  
 InnoDB: Shutdown completed; log sequence number 186521817  
 InnoDB: Number of pools: 1  
 160719 07:47:19 completed OK!

**--** **准备增量备份一，同时避免回滚阶段。**

\[root@localhost backup\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/innobackupex --defaults-file=/etc/my.cnf --apply-log --redo-only /backup/20160719/2016-07-19\_07-24-18/ --incremental-dir=/backup/20160719/inc1/2016-07-19\_07-28-54/ --use-memory=100m

.....

160719 07:50:01 \[01\] Copying /backup/20160719/inc1/2016-07-19\_07-28-54/mysql/user.frm to ./mysql/user.frm  
 160719 07:50:01 \[01\]        ...done  
 160719 07:50:01 \[01\] Copying /backup/20160719/inc1/2016-07-19\_07-28-54/mysql/proxies\_priv.frm to ./mysql/proxies\_priv.frm  
 160719 07:50:01 \[01\]        ...done  
 160719 07:50:01 \[01\] Copying /backup/20160719/inc1/2016-07-19\_07-28-54/mysql/proc.MYD to ./mysql/proc.MYD  
 160719 07:50:01 \[01\]        ...done  
 160719 07:50:01 \[01\] Copying /backup/20160719/inc1/2016-07-19\_07-28-54/mysql/help\_relation.frm to ./mysql/help\_relation.frm  
 160719 07:50:01 \[01\]        ...done  
 160719 07:50:01 \[00\] Copying /backup/20160719/inc1/2016-07-19\_07-28-54//xtrabackup\_binlog\_info to ./xtrabackup\_binlog\_info  
 160719 07:50:01 \[00\]        ...done  
 160719 07:50:01 \[00\] Copying /backup/20160719/inc1/2016-07-19\_07-28-54//xtrabackup\_info to ./xtrabackup\_info  
 160719 07:50:01 \[00\]        ...done  
 160719 07:50:01 completed OK!

**--** **准备增量备份三，同时避免回滚阶段。**  
\[root@localhost backup\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/innobackupex --defaults-file=/etc/my.cnf --apply-log --redo-only /backup/20160719/2016-07-19\_07-24-18/ --incremental-dir=/backup/20160719/inc3/2016-07-19\_07-40-11/ --use-memory=100m

.....

160719 07:55:15 \[01\] Copying /backup/20160719/inc3/2016-07-19\_07-40-11/mysql/func.MYD to ./mysql/func.MYD  
 160719 07:55:15 \[01\]        ...done  
 160719 07:55:15 \[01\] Copying /backup/20160719/inc3/2016-07-19\_07-40-11/mysql/user.frm to ./mysql/user.frm  
 160719 07:55:15 \[01\]        ...done  
 160719 07:55:15 \[01\] Copying /backup/20160719/inc3/2016-07-19\_07-40-11/mysql/proxies\_priv.frm to ./mysql/proxies\_priv.frm  
 160719 07:55:15 \[01\]        ...done  
 160719 07:55:15 \[01\] Copying /backup/20160719/inc3/2016-07-19\_07-40-11/mysql/proc.MYD to ./mysql/proc.MYD  
 160719 07:55:15 \[01\]        ...done  
 160719 07:55:15 \[01\] Copying /backup/20160719/inc3/2016-07-19\_07-40-11/mysql/help\_relation.frm to ./mysql/help\_relation.frm  
 160719 07:55:15 \[01\]        ...done  
 160719 07:55:15 \[00\] Copying /backup/20160719/inc3/2016-07-19\_07-40-11//xtrabackup\_binlog\_info to ./xtrabackup\_binlog\_info  
 160719 07:55:15 \[00\]        ...done  
 160719 07:55:15 \[00\] Copying /backup/20160719/inc3/2016-07-19\_07-40-11//xtrabackup\_info to ./xtrabackup\_info  
 160719 07:55:15 \[00\]        ...done  
 160719 07:55:15 completed OK!

**--** **当把所有全量备份和增量备份放在一起后，再次准备全部备份（基础全量备份 + 增量备份）来回滚暂停的事务**

\[root@localhost backup\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/innobackupex --defaults-file=/etc/my.cnf --apply-log /backup/20160719/2016-07-19\_07-24-18/

.....

InnoDB: PUNCH HOLE support not available  
 InnoDB: Mutexes and rw\_locks use GCC atomic builtins  
 InnoDB: Uses event mutexes  
 InnoDB: GCC builtin \_\_sync\_synchronize() is used for memory barrier  
 InnoDB: Compressed tables use zlib 1.2.3  
 InnoDB: Number of pools: 1  
 InnoDB: Using CPU crc32 instructions  
 InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M  
 InnoDB: Completed initialization of buffer pool  
 InnoDB: page\_cleaner coordinator priority: -20  
 InnoDB: Highest supported file format is Barracuda.  
 InnoDB: Removed temporary tablespace data file: "ibtmp1"  
 InnoDB: Creating shared tablespace for temporary tables  
 InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...  
 InnoDB: File './ibtmp1' size is now 12 MB.  
 InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.  
 InnoDB: 32 non-redo rollback segment(s) are active.  
 InnoDB: Waiting for purge to start  
 InnoDB: 5.7.11 started; log sequence number 186538279  
 xtrabackup: starting shutdown with innodb\_fast\_shutdown = 1  
 InnoDB: FTS optimize thread exiting.  
 InnoDB: Starting shutdown...  
 InnoDB: Shutdown completed; log sequence number 186538298  
 160719 07:59:03 completed OK!

\--停止数据库服务，并删除全部数据文件

\[root@localhost fire\]\# service mysqld stop  
 root@localhost 20160719\]\# cd /var/lib/mysql  
 \[root@localhost mysql\]\# rm -rf \*

**--执行restore操作，将** **已经准备好的数据拷贝回my.cnf参数文件中指定的数据目录下。**  
\[root@localhost backup\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/innobackupex --defaults-file=/etc/my.cnf --copy-back /backup/20160719/2016-07-19\_07-24-18/

.....

160719 08:01:32 \[01\] Copying ./mysql/func.MYD to /var/lib/mysql/mysql/func.MYD  
 160719 08:01:32 \[01\]        ...done  
 160719 08:01:32 \[01\] Copying ./mysql/user.frm to /var/lib/mysql/mysql/user.frm  
 160719 08:01:32 \[01\]        ...done  
 160719 08:01:32 \[01\] Copying ./mysql/proxies\_priv.frm to /var/lib/mysql/mysql/proxies\_priv.frm  
 160719 08:01:32 \[01\]        ...done  
 160719 08:01:32 \[01\] Copying ./mysql/proc.MYD to /var/lib/mysql/mysql/proc.MYD  
 160719 08:01:32 \[01\]        ...done  
 160719 08:01:32 \[01\] Copying ./mysql/help\_relation.frm to /var/lib/mysql/mysql/help\_relation.frm  
 160719 08:01:32 \[01\]        ...done  
 160719 08:01:32 \[01\] Copying ./ib\_buffer\_pool to /var/lib/mysql/ib\_buffer\_pool  
 160719 08:01:32 \[01\]        ...done  
 160719 08:01:32 \[01\] Copying ./xtrabackup\_binlog\_pos\_innodb to /var/lib/mysql/xtrabackup\_binlog\_pos\_innodb  
 160719 08:01:32 \[01\]        ...done  
 160719 08:01:32 completed OK!

\--更改数据目录的权限  
\[root@localhost mysql\]\# chown -R mysql.mysql /var/lib/mysql

\--启动数据库服务，验证数据  
\[root@localhost fire\]\# service mysqld start  
 mysql> select \* from emp;  
 \+------+---------+  
 | id   | name    |  
 \+------+---------+  
 |   10 | Neo     |  
 |   20 | Trinity |  
 \+------+---------+  
 2 rows in set (0.00 sec)  
  
 mysql> select \* from emp2;  
 \+------+---------+  
 | id   | name    |  
 \+------+---------+  
 |   10 | Neo     |  
 |   20 | Trinity |  
 |   10 | Neo     |  
 |   20 | Trinity |  
 |   10 | Neo     |  
 |   20 | Trinity |  
 \+------+---------+  
 6 rows in set (0.00 sec)

#### 创建一个不包含第二索引（除了主键之外的索引）的备份 ####

####  ####

innobackupex --defaults-file=/var/lib/mysql/main\_my.cnf \\

>  --user=$\{MYSQL\_USER\} --password=$\{MYSQL\_PASS\} --compact\\

>  --extra-lsndir=/database/backup/2016-08-09/checkpoints/ \\

>  --compress --compress-threads=8 \\

>  --stream=xbstream --parallel=4 /tmp \\

>  2>> /database/backup/2016-08-09/xtra\_full\_bak\_2016-08-09.log \\

>  > /database/backup/2016-08-09/xtra\_full\_bak\_2016-08-09.xbstream

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2088737/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2088737/

[xtrabackup --apply-log-only]: https://www.percona.com/doc/percona-xtrabackup/2.4/xtrabackup_bin/xbk_option_reference.html#cmdoption-xtrabackup--apply-log-only