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

谁践踏了优雅 2024-04-18 00:04 58阅读 0赞

**xtrabackup常用参数**  
  
\--backup 创建备份并将备份存放到--target-dir目录  
  
\--defaults-file=\[MY.CNF\] 配置文件的路径  
  
\--target-dir=DIRECTORY 指定备份存放的路径。如果目录不存在，xtrabackup会创建相应的目录  
  
\--user 备份账户的用户名  
  
\--password 备份账户的密码  
  
\--datadir=DIRECTORY 数据目录的路径  
  
\--copy-back 将之前做的备份拷贝回他们的起始目录  
  
**--xtrabackup全量备份**  
  
\[root@localhost ~\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/xtrabackup --defaults-file=/etc/my.cnf --backup --datadir=/var/lib/mysql/ --target-dir=/backup/20160722 --user root   
 \--password 'System\#2013'  
 .....  
 160721 20:30:13 \[01\] Copying ./mysql/user.frm to /backup/20160722/mysql/user.frm  
 160721 20:30:13 \[01\]        ...done  
 160721 20:30:14 \[01\] Copying ./mysql/proxies\_priv.frm to /backup/20160722/mysql/proxies\_priv.frm  
 160721 20:30:14 \[01\]        ...done  
 160721 20:30:14 \[01\] Copying ./mysql/proc.MYD to /backup/20160722/mysql/proc.MYD  
 160721 20:30:14 \[01\]        ...done  
 160721 20:30:14 \[01\] Copying ./mysql/help\_relation.frm to /backup/20160722/mysql/help\_relation.frm  
 160721 20:30:14 \[01\]        ...done  
 160721 20:30:14 Finished backing up non-InnoDB tables and files  
 160721 20:30:14 \[00\] Writing xtrabackup\_binlog\_info  
 160721 20:30:14 \[00\]        ...done  
 160721 20:30:14 Executing FLUSH NO\_WRITE\_TO\_BINLOG ENGINE LOGS...  
 xtrabackup: The latest check point (for incremental): '186651619'  
 xtrabackup: Stopping log copying thread.  
 .160721 20:30:14 >> log scanned up to (186651628)  
  
 160721 20:30:14 Executing UNLOCK TABLES  
 160721 20:30:14 All tables unlocked  
 160721 20:30:14 \[00\] Copying ib\_buffer\_pool to /backup/20160722/ib\_buffer\_pool  
 160721 20:30:14 \[00\]        ...done  
 160721 20:30:14 Backup created in directory '/backup/20160722'  
 MySQL binlog position: filename 'production-bin.000002', position '194', GTID of the last change '026dc034-4dc2-11e6-a9cb-000c29631605:1-17,  
 cf291e84-2c89-11e6-b6f0-000c29631605:1-44'  
 160721 20:30:14 \[00\] Writing backup-my.cnf  
 160721 20:30:14 \[00\]        ...done  
 160721 20:30:14 \[00\] Writing xtrabackup\_info  
 160721 20:30:14 \[00\]        ...done  
 xtrabackup: Transaction log of lsn (186651619) to (186651628) was copied.  
 160721 20:30:14 completed OK!  
  
**--** **xtrabackup全量恢复**  
\-删除myisam表  
mysql> show create table fire.t10\\G  
 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
        Table: t10  
 Create Table: CREATE TABLE \`t10\` (  
   \`f1\` int(11) DEFAULT NULL,  
   \`gc\` int(11) GENERATED ALWAYS AS ((\`f1\` + 1)) STORED,  
   KEY \`gc\` (\`gc\`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1  
 1 row in set (0.00 sec)

mysql> drop table fire.t10;  
 Query OK, 0 rows affected (0.21 sec)  
  
**-** **第1次准备备份，使备份的数据文件一致**

\[root@localhost 20160722\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/backup/20160722/

.....

InnoDB: New log files created, LSN=186651663  
 InnoDB: Highest supported file format is Barracuda.  
 InnoDB: Log scan progressed past the checkpoint lsn 186652172  
 InnoDB: Doing recovery: scanned up to log sequence number 186652181 (0%)  
 InnoDB: Doing recovery: scanned up to log sequence number 186652181 (0%)  
 InnoDB: Database was not shutdown normally!  
 InnoDB: Starting crash recovery.  
 InnoDB: xtrabackup: Last MySQL binlog file position 3212, file name production-bin.000001  
 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: page\_cleaner: 1000ms intended loop took 10267ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)  
 InnoDB: 5.7.11 started; log sequence number 186652181  
 xtrabackup: starting shutdown with innodb\_fast\_shutdown = 1  
 InnoDB: FTS optimize thread exiting.  
 InnoDB: Starting shutdown...  
 InnoDB: Shutdown completed; log sequence number 186652200  
 160721 23:40:39 completed OK!

**-** **第2次准备备份，创建新的日志文件，以使恢复更快；如果不第2次执行--prepare，xtrabackup在restore时会自动创建日志文件。**

\[root@localhost 20160722\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/backup/20160722/  
 .....  
 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 186652219  
 xtrabackup: starting shutdown with innodb\_fast\_shutdown = 1  
 InnoDB: FTS optimize thread exiting.  
 InnoDB: Starting shutdown...  
 InnoDB: Shutdown completed; log sequence number 186652238  
 160721 23:44:05 completed OK!

\[root@localhost 20160722\]\# ls -trl  
 total 196656  
 drwxr-x---. 2 root root     4096 Jul 21 20:30 fire  
 drwxr-x---. 2 root root    12288 Jul 21 20:30 sys  
 drwxr-x---. 2 root root     4096 Jul 21 20:30 performance\_schema  
 drwxr-x---. 2 root root     4096 Jul 21 20:30 mysql  
 \-rw-r-----. 1 root root      111 Jul 21 20:30 xtrabackup\_binlog\_info  
 \-rw-r-----. 1 root root      785 Jul 21 20:30 ib\_buffer\_pool  
 \-rw-r-----. 1 root root      428 Jul 21 20:30 backup-my.cnf  
 \-rw-r-----. 1 root root      677 Jul 21 20:30 xtrabackup\_info  
 \-rw-r-----. 1 root root  8388608 Jul 21 23:40 xtrabackup\_logfile  
 \-rw-r-----. 1 root root 50331648 Jul 21 23:40 ib\_logfile1  
 \-rw-r--r--. 1 root root       27 Jul 21 23:44 xtrabackup\_binlog\_pos\_innodb  
 \-rw-r-----. 1 root root      117 Jul 21 23:44 xtrabackup\_checkpoints  
 \-rw-r-----. 1 root root 12582912 Jul 21 23:44 ibtmp1  
 \-rw-r-----. 1 root root 50331648 Jul 21 23:44 ib\_logfile0  
 \-rw-r-----. 1 root root 79691776 Jul 21 23:44 ibdata1

**-执行restore操作**

\[root@localhost 20160722\]\# rsync -rvt --exclude 'xtrabackup\_checkpoints' --exclude 'xtrabackup\_logfile' \\  
 >  ./ /var/lib/mysql  
 sending incremental file list  
 ./  
 backup-my.cnf  
 ib\_buffer\_pool  
 ib\_logfile0  
 ib\_logfile1  
 ibdata1  
 ibtmp1  
 xtrabackup\_binlog\_info  
 xtrabackup\_binlog\_pos\_innodb  
 xtrabackup\_info  
 fire/  
 fire/db.opt  
 fire/emp.frm  
 fire/emp.ibd  
 fire/emp2.frm  
 fire/emp2.ibd  
 fire/t1.frm  
 fire/t1.ibd  
 fire/t10.MYD  
 fire/t10.MYI  
 fire/t10.frm

.....

sys/x@0024user\_summary\_by\_statement\_latency.frm  
 sys/x@0024user\_summary\_by\_statement\_type.frm  
 sys/x@0024wait\_classes\_global\_by\_avg\_latency.frm  
 sys/x@0024wait\_classes\_global\_by\_latency.frm  
 sys/x@0024waits\_by\_host\_by\_latency.frm  
 sys/x@0024waits\_by\_user\_by\_latency.frm  
 sys/x@0024waits\_global\_by\_latency.frm  
  
 sent 324679831 bytes  received 5769 bytes  22392110.34 bytes/sec  
 total size is 324618115  speedup is 1.00  
  
\-更改数据目录权限  
\[root@localhost 20160722\]\# chown -R mysql:mysql /var/lib/mysql/  
  
\-启动数据库  
\[root@localhost 20160722\]\# service mysqld start  
 Starting mysqld:                                           \[  OK  \]

\-查看恢复的表中数据

mysql> select table\_catalog,table\_schema,table\_name,table\_type,engine,version from information\_schema.tables where table\_name='t10';  
 \+---------------+--------------+------------+------------+--------+---------+  
 | table\_catalog | table\_schema | table\_name | table\_type | engine | version |  
 \+---------------+--------------+------------+------------+--------+---------+  
 | def           | fire         | t10        | BASE TABLE | MyISAM |      10 |  
 \+---------------+--------------+------------+------------+--------+---------+  
 1 row in set (0.00 sec)  
  
 mysql> select \* from fire.t10;  
 \+------+------+  
 | f1   | gc   |  
 \+------+------+  
 |    1 |    2 |  
 |    2 |    3 |  
 |    3 |    4 |  
 |    4 |    5 |  
 |    5 |    6 |  
 \+------+------+  
 5 rows in set (0.00 sec)

**--xtrabackup增量备份**  
  
**-执行基础全备**  
\[root@localhost ~\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/xtrabackup --defaults-file=/etc/my.cnf --backup --datadir=/var/lib/mysql/ --target-dir=/backup/20160722 --user root   
\--password 'System\#2013'  
  
\--创建测试表并 插入数据  
mysql> create table dept(id int(5),d\_name varchar(15),loc varchar(15));  
 Query OK, 0 rows affected (0.42 sec)  
 mysql> insert into dept values(10,'Research','ShangHai');  
 Query OK, 1 row affected (0.06 sec)  
 mysql> insert into dept values(20,'Maintenance','GuangZhou');  
 Query OK, 1 row affected (0.00 sec)  
 mysql> commit;  
 Query OK, 0 rows affected (0.00 sec)

**-执行增量备份一**  
\[root@localhost ~\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/20160722/ --user root --password 'System\#2013'

.....

160722 00:26:18 \[00\] Writing xtrabackup\_binlog\_info  
 160722 00:26:18 \[00\]        ...done  
 160722 00:26:18 Executing FLUSH NO\_WRITE\_TO\_BINLOG ENGINE LOGS...  
 xtrabackup: The latest check point (for incremental): '186658935'  
 xtrabackup: Stopping log copying thread.  
 .160722 00:26:18 >> log scanned up to (186658944)  
  
 160722 00:26:18 Executing UNLOCK TABLES  
 160722 00:26:18 All tables unlocked  
 160722 00:26:18 \[00\] Copying ib\_buffer\_pool to /backup/inc1/ib\_buffer\_pool  
 160722 00:26:18 \[00\]        ...done  
 160722 00:26:18 Backup created in directory '/backup/inc1'  
 MySQL binlog position: filename 'production-bin.000001', position '918', GTID of the last change '026dc034-4dc2-11e6-a9cb-000c29631605:1-17,  
 cf291e84-2c89-11e6-b6f0-000c29631605:1-44,  
 fd17ba52-4fd8-11e6-bd80-000c29631605:1-3'  
 160722 00:26:18 \[00\] Writing backup-my.cnf  
 160722 00:26:18 \[00\]        ...done  
 160722 00:26:18 \[00\] Writing xtrabackup\_info  
 160722 00:26:18 \[00\]        ...done  
 xtrabackup: Transaction log of lsn (186658935) to (186658944) was copied.  
 160722 00:26:18 completed OK!  
  
\--创建测试表并 插入数据  
mysql> create table test2 like test;  
 Query OK, 0 rows affected (0.95 sec)  
 mysql> insert into test2 select \* from test;  
 Query OK, 5 rows affected (0.16 sec)  
 Records: 5  Duplicates: 0  Warnings: 0

**-执行增量备份二**  
\[root@localhost ~\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1 --user root --password 'System\#2013'

.....

160722 00:33:31 \[01\] Copying ./mysql/proxies\_priv.frm to /backup/inc2/mysql/proxies\_priv.frm  
 160722 00:33:31 \[01\]        ...done  
 160722 00:33:31 \[01\] Copying ./mysql/proc.MYD to /backup/inc2/mysql/proc.MYD  
 160722 00:33:31 \[01\]        ...done  
 160722 00:33:31 \[01\] Copying ./mysql/help\_relation.frm to /backup/inc2/mysql/help\_relation.frm  
 160722 00:33:31 \[01\]        ...done  
 160722 00:33:31 Finished backing up non-InnoDB tables and files  
 160722 00:33:31 \[00\] Writing xtrabackup\_binlog\_info  
 160722 00:33:31 \[00\]        ...done  
 160722 00:33:31 Executing FLUSH NO\_WRITE\_TO\_BINLOG ENGINE LOGS...  
 xtrabackup: The latest check point (for incremental): '186672619'  
 xtrabackup: Stopping log copying thread.  
 .160722 00:33:31 >> log scanned up to (186672628)  
  
 160722 00:33:31 Executing UNLOCK TABLES  
 160722 00:33:31 All tables unlocked  
 160722 00:33:31 \[00\] Copying ib\_buffer\_pool to /backup/inc2/ib\_buffer\_pool  
 160722 00:33:31 \[00\]        ...done  
 160722 00:33:31 Backup created in directory '/backup/inc2'  
 MySQL binlog position: filename 'production-bin.000001', position '1421', GTID of the last change '026dc034-4dc2-11e6-a9cb-000c29631605:1-17,  
 cf291e84-2c89-11e6-b6f0-000c29631605:1-44,  
 fd17ba52-4fd8-11e6-bd80-000c29631605:1-5'  
 160722 00:33:31 \[00\] Writing backup-my.cnf  
 160722 00:33:31 \[00\]        ...done  
 160722 00:33:31 \[00\] Writing xtrabackup\_info  
 160722 00:33:31 \[00\]        ...done  
 xtrabackup: Transaction log of lsn (186672619) to (186672628) was copied.  
 160722 00:33:31 completed OK!

**--xtrabackup增量恢复**  
  
 **-准备基础全备**   
\[root@localhost ~\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/backup/20160722/

.....

xtrabackup:   innodb\_log\_files\_in\_group = 2  
 xtrabackup:   innodb\_log\_file\_size = 50331648  
 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  
 xtrabackup: Starting InnoDB instance for recovery.  
 xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)  
 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: xtrabackup: Last MySQL binlog file position 3212, file name production-bin.000001  
  
 xtrabackup: starting shutdown with innodb\_fast\_shutdown = 1  
 InnoDB: Starting shutdown...  
 InnoDB: Shutdown completed; log sequence number 186652247  
 InnoDB: Number of pools: 1  
 160722 01:13:30 completed OK!

**-** **回滚基础全备的数据到第1个增量备份**    
\[root@localhost ~\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/backup/20160722/ --incremental-dir=/backup/inc1/

.....

160722 01:16:56 \[01\] Copying /backup/inc1/mysql/server\_cost.frm to ./mysql/server\_cost.frm  
 160722 01:16:56 \[01\]        ...done  
 160722 01:16:56 \[01\] Copying /backup/inc1/mysql/db.MYD to ./mysql/db.MYD  
 160722 01:16:56 \[01\]        ...done  
 160722 01:16:56 \[01\] Copying /backup/inc1/mysql/general\_log.CSV to ./mysql/general\_log.CSV  
 160722 01:16:56 \[01\]        ...done  
 160722 01:16:56 \[01\] Copying /backup/inc1/mysql/help\_category.frm to ./mysql/help\_category.frm  
 160722 01:16:56 \[01\]        ...done  
 160722 01:16:56 \[01\] Copying /backup/inc1/mysql/innodb\_table\_stats.frm to ./mysql/innodb\_table\_stats.frm  
 160722 01:16:56 \[01\]        ...done  
 160722 01:16:56 \[01\] Copying /backup/inc1/mysql/slave\_master\_info.frm to ./mysql/slave\_master\_info.frm  
 160722 01:16:56 \[01\]        ...done  
 160722 01:16:56 \[01\] Copying /backup/inc1/mysql/func.MYD to ./mysql/func.MYD  
 160722 01:16:56 \[01\]        ...done  
 160722 01:16:56 \[01\] Copying /backup/inc1/mysql/user.frm to ./mysql/user.frm  
 160722 01:16:56 \[01\]        ...done  
 160722 01:16:56 \[01\] Copying /backup/inc1/mysql/proxies\_priv.frm to ./mysql/proxies\_priv.frm  
 160722 01:16:56 \[01\]        ...done  
 160722 01:16:56 \[01\] Copying /backup/inc1/mysql/proc.MYD to ./mysql/proc.MYD  
 160722 01:16:56 \[01\]        ...done  
 160722 01:16:56 \[01\] Copying /backup/inc1/mysql/help\_relation.frm to ./mysql/help\_relation.frm  
 160722 01:16:56 \[01\]        ...done  
 160722 01:16:56 \[00\] Copying /backup/inc1//xtrabackup\_binlog\_info to ./xtrabackup\_binlog\_info  
 160722 01:16:56 \[00\]        ...done  
 160722 01:16:56 \[00\] Copying /backup/inc1//xtrabackup\_info to ./xtrabackup\_info  
 160722 01:16:56 \[00\]        ...done  
 160722 01:16:56 completed OK!  
  
**-** **回滚基础全备的数据到第2个增量备份**    
\[root@localhost ~\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/backup/20160722/ --incremental-dir=/backup/inc2/

.....

160722 01:19:13 \[01\] Copying /backup/inc2/mysql/user.MYD to ./mysql/user.MYD  
 160722 01:19:13 \[01\]        ...done  
 160722 01:19:13 \[01\] Copying /backup/inc2/mysql/server\_cost.frm to ./mysql/server\_cost.frm  
 160722 01:19:13 \[01\]        ...done  
 160722 01:19:13 \[01\] Copying /backup/inc2/mysql/db.MYD to ./mysql/db.MYD  
 160722 01:19:13 \[01\]        ...done  
 160722 01:19:13 \[01\] Copying /backup/inc2/mysql/general\_log.CSV to ./mysql/general\_log.CSV  
 160722 01:19:13 \[01\]        ...done  
 160722 01:19:13 \[01\] Copying /backup/inc2/mysql/help\_category.frm to ./mysql/help\_category.frm  
 160722 01:19:13 \[01\]        ...done  
 160722 01:19:13 \[01\] Copying /backup/inc2/mysql/innodb\_table\_stats.frm to ./mysql/innodb\_table\_stats.frm  
 160722 01:19:13 \[01\]        ...done  
 160722 01:19:13 \[01\] Copying /backup/inc2/mysql/slave\_master\_info.frm to ./mysql/slave\_master\_info.frm  
 160722 01:19:13 \[01\]        ...done  
 160722 01:19:13 \[01\] Copying /backup/inc2/mysql/func.MYD to ./mysql/func.MYD  
 160722 01:19:13 \[01\]        ...done  
 160722 01:19:13 \[01\] Copying /backup/inc2/mysql/user.frm to ./mysql/user.frm  
 160722 01:19:13 \[01\]        ...done  
 160722 01:19:13 \[01\] Copying /backup/inc2/mysql/proxies\_priv.frm to ./mysql/proxies\_priv.frm  
 160722 01:19:13 \[01\]        ...done  
 160722 01:19:13 \[01\] Copying /backup/inc2/mysql/proc.MYD to ./mysql/proc.MYD  
 160722 01:19:13 \[01\]        ...done  
 160722 01:19:13 \[01\] Copying /backup/inc2/mysql/help\_relation.frm to ./mysql/help\_relation.frm  
 160722 01:19:13 \[01\]        ...done  
 160722 01:19:13 \[00\] Copying /backup/inc2//xtrabackup\_binlog\_info to ./xtrabackup\_binlog\_info  
 160722 01:19:13 \[00\]        ...done  
 160722 01:19:13 \[00\] Copying /backup/inc2//xtrabackup\_info to ./xtrabackup\_info  
 160722 01:19:13 \[00\]        ...done  
 160722 01:19:13 completed OK!

**-** **准备全部的备份，创建新的日志文件**   
\[root@localhost ~\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/backup/20160722/

.....

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: Page \[page id: space=0, page number=9\] log sequence number 186659641 is in the future! Current system log sequence number 186652266.  
 InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.  
 InnoDB: Page \[page id: space=0, page number=10\] log sequence number 186659944 is in the future! Current system log sequence number 186652266.  
 InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.  
 InnoDB: Page \[page id: space=0, page number=12\] log sequence number 186662606 is in the future! Current system log sequence number 186652266.  
 InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.  
 InnoDB: Page \[page id: space=13, page number=3\] log sequence number 186669950 is in the future! Current system log sequence number 186652266.  
 InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.  
 InnoDB: 5.7.11 started; log sequence number 186652247  
 InnoDB: Page \[page id: space=82, page number=0\] log sequence number 186655532 is in the future! Current system log sequence number 186652266.  
 InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.  
 InnoDB: Page \[page id: space=83, page number=0\] log sequence number 186663700 is in the future! Current system log sequence number 186652266.  
 InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.  
 InnoDB: xtrabackup: Last MySQL binlog file position 1421, file name production-bin.000001  
  
 xtrabackup: error: The transaction log file is corrupted.  
 xtrabackup: error: The log was not applied to the intended LSN!  
 xtrabackup: Log applied to lsn 186652247  
 xtrabackup: The intended lsn is 186672619  
  
\-停止数据库，并删除数据文件下面的所有文件  
\[root@localhost 20160722\]\# cd /var/lib/mysql  
 \[root@localhost mysql\]\# rm -rf \*

**-执行restore操作**

\[root@localhost backup\]\# cd /backup/20160722  
 \[root@localhost 20160722\]\# rsync -rvt --exclude 'xtrabackup\_checkpoints' --exclude 'xtrabackup\_logfile' \\  
 > ./ /var/lib/mysql  
 sending incremental file list  
 ./  
 backup-my.cnf  
 ib\_buffer\_pool  
 ib\_logfile0  
 ib\_logfile1  
 ibdata1

.....

sys/x@0024schema\_table\_statistics\_with\_buffer.frm  
 sys/x@0024schema\_tables\_with\_full\_table\_scans.frm  
 sys/x@0024session.frm  
 sys/x@0024statement\_analysis.frm  
 sys/x@0024statements\_with\_errors\_or\_warnings.frm  
 sys/x@0024statements\_with\_full\_table\_scans.frm  
 sys/x@0024statements\_with\_runtimes\_in\_95th\_percentile.frm  
 sys/x@0024statements\_with\_sorting.frm  
 sys/x@0024statements\_with\_temp\_tables.frm  
 sys/x@0024user\_summary.frm  
 sys/x@0024user\_summary\_by\_file\_io.frm  
 sys/x@0024user\_summary\_by\_file\_io\_type.frm  
 sys/x@0024user\_summary\_by\_stages.frm  
 sys/x@0024user\_summary\_by\_statement\_latency.frm  
 sys/x@0024user\_summary\_by\_statement\_type.frm  
 sys/x@0024wait\_classes\_global\_by\_avg\_latency.frm  
 sys/x@0024wait\_classes\_global\_by\_latency.frm  
 sys/x@0024waits\_by\_host\_by\_latency.frm  
 sys/x@0024waits\_by\_user\_by\_latency.frm  
 sys/x@0024waits\_global\_by\_latency.frm  
  
 sent 324910462 bytes  received 5845 bytes  24067874.59 bytes/sec  
 total size is 324848462  speedup is 1.00

\-更改数据目录的权限

\[root@localhost 20160722\]\# chown -R mysql.mysql /var/lib/mysql

\--启动数据库  
\[root@localhost mysql\]\# service mysqld start  
 Starting mysqld:                                           \[  OK  \]

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

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