MySQL innodb引擎备份工具XtraBackup之二（数据库全备）

Xtrabackup备份原理：

在InnoDB内部会维护一个redo日志文件，我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据文件和事务日志，并执行两个步骤：它应用(前滚)已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。

xtrabackup在启动时会记住log sequence number(LSN)，并且复制所有的数据文件。复制过程需要一些时间，所以这期间如果数据文件有改动，那么将会使数据库处于一个不同的时间点。这时，xtrabackup会运行一个后台进程，用于监视事务日志，并从事务日志复制最新的修改。xtrabackup必须持续的做这个操作，是因为事务日志是会轮转重复的写入，并且事务日志可以被重用。所以xtrabackup自启动开始，就不停的将事务日志中每个数据文件的修改都记录下来。

上面就是xtrabackup的备份过程。接下来是准备(prepare)过程。在这个过程中，xtrabackup使用之前复制的事务日志，对各个数据文件执行灾难恢复(就像MySQL刚启动时要做的一样)。当这个过程结束后，数据库就可以做恢复还原了。

以上的过程在xtrabackup的编译二进制程序中实现。程序innobackupex可以允许我们备份MyISAM表和frm文件从而增加了便捷和功能。Innobackupex会启动xtrabackup，直到xtrabackup复制数据文件后，然后执行FLUSH TABLES WITH READ LOCK来阻止新的写入进来并把MyISAM表数据刷到硬盘上，之后复制MyISAM数据文件，最后释放锁。

备份MyISAM和InnoDB表最终会处于一致，在准备(prepare)过程结束后，InnoDB表数据已经前滚到整个备份结束的点，而不是回滚到xtrabackup刚开始时的点。这个时间点与执行FLUSH TABLES WITH READ LOCK的时间点相同，所以MyISAM表数据与InnoDB表数据是同步的。类似Oracle的，InnoDB的prepare过程可以称为recover(恢复)，MyISAM的数据复制过程可以称为restore(还原)。

xtrabackup和innobackupex这两个工具都提供了许多前文没有提到的功能特点。手册上有对各个功能都有详细的介绍。简单介绍下，这些工具提供了如流(streaming)备份，增量(incremental)备份等，通过复制数据文件，复制日志文件和提交日志到数据文件(前滚)实现了各种复合备份方式。

如下图所示：

案例分析：

一、数据库全备

1、创建配置文件

[root@rh64 ~]# cat /tmp/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

innodb_data_file_path=ibdata1:12M;ibdata2:10M:autoextend

innodb_log_files_in_group=2

innodb_log_file_size=50331648

2、创建备份目录

[root@rh64 ~]# ls -ld /data/mysql/backup/

drwxrwxrwx. 3 mysql mysql 4096 Oct 15 12:13 /data/mysql/backup/

3、测试

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| prod |

| test |

+--------------------+

5 rows in set (0.06 sec)

mysql> use prod;

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_prod |

+----------------+

| t1 |

| t2 |

| t3 |

+----------------+

3 rows in set (0.00 sec)

mysql> select count(*) from t1;

+----------+

| count(*) |

+----------+

| 49152 |

+----------+

1 row in set (0.13 sec)

插入数据：

mysql> insert into t1 select * from t1;

Query OK, 49152 rows affected (0.69 sec)

Records: 49152 Duplicates: 0 Warnings: 0

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from t1;

+----------+

| count(*) |

+----------+

| 98304 |

+----------+

1 row in set (0.03 sec)

4、创建备份用户并授权

mysql> create user 'bkusr'@'%' identified by 'oracle';

Query OK, 0 rows affected (0.00 sec)

mysql> grant reload,create tablespace,lock tables ,replication client,super on *.* to 'bakusr'@'%';

Query OK, 0 rows affected (0.00 sec

mysql> create user 'bakusr'@localhost identified by 'oracle';

Query OK, 0 rows affected (0.00 sec)

5、进行数据库全备

[root@rh64 ~]# innobackupex --user=bakusr --password='oracle' --socket=/var/lib/mysql/mysql.sock --defaults-file=/tmp/my.cnf /data/mysql/backup/full

xtrabackup: Error: --defaults-file must be specified first on the command line

----提示配置文件参数必须放在第一位

以root用户备份：

[root@rh64 ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password='oracle' --socket=/var/lib/mysql/mysql.sock /data/mysql/backup/full

1. 151028 14:18:16 innobackupex: Starting the backup operation  
2. IMPORTANT: Please  check  that the backup run completes successfully.  
3.             At  the  end   of  a successful backup run innobackupex  
4.            prints  "completed OK!" .  
5. 151028 14:18:16  version_check Connecting  to  MySQL server  with  DSN  'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock'   as   'bakusr'   (using  password : YES).  
6. 151028 14:18:16  version_check Connected  to  MySQL server  
7. 151028 14:18:16  version_check Executing a version  check  against the server...  
8. 151028 14:18:16  version_check Done.  
9. 151028 14:18:16 Connecting  to  MySQL server host: localhost,  user : bakusr,  password :  set , port: 0, socket: /var/lib/mysql/mysql.sock  
10. Using server version 5.6.25-73.1 
11. innobackupex version 2.3.2 based  on  MySQL server 5.6.24 Linux (x86_64) (revision id: 306a2e0)  
12. xtrabackup: uses posix_fadvise().  
13. xtrabackup: cd  to  /var/lib/mysql  
14. xtrabackup:  open  files limit requested 0,  set   to  1024 
15. xtrabackup: using the following InnoDB configuration:  
16. xtrabackup:   innodb_data_home_dir = ./  
17. xtrabackup:   innodb_data_file_path = ibdata1:12M;ibdata2:10M:autoextend  
18. xtrabackup:   innodb_log_group_home_dir = ./  
19. xtrabackup:   innodb_log_files_in_group = 2 
20. xtrabackup:   innodb_log_file_size = 50331648 
21. 151028 14:18:16 >> log scanned up  to  (13254527)  
22. xtrabackup: Generating a list  of  tablespaces  
23. 151028 14:18:16 [01] Copying ./ibdata1  to  /data/mysql/backup/ full /2015-10-28_14-18-16/ibdata1  
24. 151028 14:18:17 [01]        ...done  
25. 151028 14:18:17 [01] Copying ./ibdata2  to  /data/mysql/backup/ full /2015-10-28_14-18-16/ibdata2  
26. 151028 14:18:17 >> log scanned up  to  (13254527)  
27. 151028 14:18:17 [01]        ...done  
28. 151028 14:18:17 [01] Copying ./prod/t2.ibd  to  /data/mysql/backup/ full /2015-10-28_14-18-16/prod/t2.ibd  
29. 151028 14:18:17 [01]        ...done  
30. 151028 14:18:17 [01] Copying ./prod/t1.ibd  to  /data/mysql/backup/ full /2015-10-28_14-18-16/prod/t1.ibd  
31. 151028 14:18:18 [01]        ...done  
32. 151028 14:18:18 >> log scanned up  to  (13254527)  
33. 151028 14:18:18 [01] Copying ./prod/t3.ibd  to  /data/mysql/backup/ full /2015-10-28_14-18-16/prod/t3.ibd  
34. 151028 14:18:18 [01]        ...done  
35. 151028 14:18:18 [01] Copying ./mysql/slave_worker_info.ibd  to  /data/mysql/backup/ full /2015-10-28_14-18-16/mysql/slave_worker_info.ibd  
36. 151028 14:18:18 [01]        ...done  
37. 151028 14:18:18 [01] Copying ./mysql/slave_master_info.ibd  to  /data/mysql/backup/ full /2015-10-28_14-18-16/mysql/slave_master_info.ibd  
38. 151028 14:18:18 [01]        ...done  
39. 151028 14:18:18 [01] Copying ./mysql/slave_relay_log_info.ibd  to  /data/mysql/backup/ full /2015-10-28_14-18-16/mysql/slave_relay_log_info.ibd  
40. 151028 14:18:18 [01]        ...done  
41. 151028 14:18:18 [01] Copying ./mysql/innodb_index_stats.ibd  to  /data/mysql/backup/ full /2015-10-28_14-18-16/mysql/innodb_index_stats.ibd  
42. 151028 14:18:19 [01]        ...done  
43. 151028 14:18:19 [01] Copying ./mysql/innodb_table_stats.ibd  to  /data/mysql/backup/ full /2015-10-28_14-18-16/mysql/innodb_table_stats.ibd  
44. 151028 14:18:19 [01]        ...done  
45. ......  
46. 151028 14:21:39 Executing UNLOCK BINLOG  
47. 151028 14:21:39 Executing UNLOCK TABLES  
48. 151028 14:21:39  All  tables unlocked  
49. 151028 14:21:39 Backup created  in  directory  '/data/mysql/backup/full/2015-10-28_14-21-20'  
50. 151028 14:21:39 [00] Writing backup-my.cnf  
51. 151028 14:21:39 [00]        ...done  
52. 151028 14:21:39 [00] Writing xtrabackup_info  
53. 151028 14:21:39 [00]        ...done  
54. xtrabackup:  Transaction  log  of  lsn (13254537)  to  (13254537) was copied.  
55. 151028 14:21:39 completed OK!  

查看备份：

[root@rh64 backup]# ls

bak.sh full prod t.txt

[root@rh64 backup]# cd full

[root@rh64 full]# ls

2015-10-28_14-23-23

[root@rh64 full]# cd 2015-10-28_14-23-23/

[root@rh64 2015-10-28_14-23-23]# ls -lt

total 22560

-rw-r----- 1 root root 507 Oct 28 14:23 xtrabackup_info

-rw-r----- 1 root root 398 Oct 28 14:23 backup-my.cnf

-rw-r----- 1 root root 115 Oct 28 14:23 xtrabackup_checkpoints

-rw-r----- 1 root root 2560 Oct 28 14:23 xtrabackup_logfile

drwx------ 2 root root 4096 Oct 28 14:23 performance_schema

drwx------ 2 root root 4096 Oct 28 14:23 mysql

drwx------ 2 root root 4096 Oct 28 14:23 test

drwx------ 2 root root 4096 Oct 28 14:23 prod

-rw-r----- 1 root root 10485760 Oct 28 14:23 ibdata2

-rw-r----- 1 root root 12582912 Oct 28 14:23 ibdata1

[root@rh64 2015-10-28_14-23-23]#

使用参数：--no-timestamp

[root@rh64 backup]# innobackupex --defaults-file=/etc/my.cnf --user=root --password='oracle' --socket=/var/lib/mysql/mysql.sock /data/mysql/backup/full --no-timestamp

则不建立时间相关的目录：

[root@rh64 backup]# ls

bak.sh full prod t.txt

[root@rh64 backup]# cd full

[root@rh64 full]# ls

backup-my.cnf ibdata2 performance_schema test xtrabackup_info

ibdata1 mysql prod xtrabackup_checkpoints xtrabackup_logfile

[root@rh64 full]# ls -l

total 22560

-rw-r----- 1 root root 398 Oct 28 14:25 backup-my.cnf

-rw-r----- 1 root root 12582912 Oct 28 14:25 ibdata1

-rw-r----- 1 root root 10485760 Oct 28 14:25 ibdata2

drwx------ 2 root root 4096 Oct 28 14:25 mysql

drwx------ 2 root root 4096 Oct 28 14:25 performance_schema

drwx------ 2 root root 4096 Oct 28 14:25 prod

drwx------ 2 root root 4096 Oct 28 14:25 test

-rw-r----- 1 root root 115 Oct 28 14:25 xtrabackup_checkpoints

-rw-r----- 1 root root 522 Oct 28 14:25 xtrabackup_info

-rw-r----- 1 root root 2560 Oct 28 14:25 xtrabackup_logfile

使用普通用户备份：

[root@rh64 ~]# innobackupex --defaults-file=/etc/my.cnf --user=bakusr --password='oracle' --socket=/var/lib/mysql/mysql.sock /data/mysql/backup/full

Error: failed to execute query LOCK TABLES FOR BACKUP: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation

----提示：缺少reload权限

查看用户权限：

mysql> select user,host,Reload_priv from user where user='bakusr';

+--------+-----------+-------------+

| user | host | Reload_priv |

+--------+-----------+-------------+

| bakusr | % | Y |

| bakusr | localhost | N |

+--------+-----------+-------------+

2 rows in set (0.04 sec)

授权：

mysql> grant reload,create tablespace,lock tables ,replication client,super on *.* to 'bakusr'@localhost;

Query OK, 0 rows affected (0.08 sec)

mysql> select user,host,Reload_priv from user where user='bakusr';

+--------+-----------+-------------+

| user | host | Reload_priv |

+--------+-----------+-------------+

| bakusr | % | Y |

| bakusr | localhost | Y |

+--------+-----------+-------------+

2 rows in set (0.15 sec)

备份：

[root@rh64 ~]# innobackupex --defaults-file=/etc/my.cnf --user=bakusr --password='oracle' --socket=/var/lib/mysql/mysql.sock /data/mysql/backup/full --no-timestamp

1. 151028 14:26:45 innobackupex: Starting the backup operation  
2. IMPORTANT: Please  check  that the backup run completes successfully.  
3.             At  the  end   of  a successful backup run innobackupex  
4.            prints  "completed OK!" .  
5. 151028 14:26:45  version_check Connecting  to  MySQL server  with  DSN  'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock'   as   'usrbak'   (using  password : YES).  
6. Failed  to   connect   to  MySQL server: DBI  connect ( ';mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' , 'usrbak' ,...) failed: Access denied  for   user   'usrbak' @ 'localhost'  (using  password : YES)  at  - line 1314 
7. 151028 14:26:45 Connecting  to  MySQL server host: localhost,  user : usrbak,  password :  set , port: 0, socket: /var/lib/mysql/mysql.sock  
8. Failed  to   connect   to  MySQL server: Access denied  for   user   'usrbak' @ 'localhost'  (using  password : YES).  
9. [root@rh64 backup]# innobackupex   
10. 151028 14:26:59 innobackupex: Starting the backup operation  
11. IMPORTANT: Please  check  that the backup run completes successfully.  
12.             At  the  end   of  a successful backup run innobackupex  
13.            prints  "completed OK!" .  
14. 151028 14:26:59  version_check Connecting  to  MySQL server  with  DSN  'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock'   as   'bakusr'   (using  password : YES).  
15. 151028 14:26:59  version_check Connected  to  MySQL server  
16. 151028 14:26:59  version_check Executing a version  check  against the server...  
17. 151028 14:26:59  version_check Done.  
18. 151028 14:26:59 Connecting  to  MySQL server host: localhost,  user : bakusr,  password :  set , port: 0, socket: /var/lib/mysql/mysql.sock  
19. Using server version 5.6.25-73.1 
20. innobackupex version 2.3.2 based  on  MySQL server 5.6.24 Linux (x86_64) (revision id: 306a2e0)  
21. xtrabackup: uses posix_fadvise().  
22. xtrabackup: cd  to  /var/lib/mysql  
23. xtrabackup:  open  files limit requested 0,  set   to  1024 
24. xtrabackup: using the following InnoDB configuration:  
25. xtrabackup:   innodb_data_home_dir = ./  
26. xtrabackup:   innodb_data_file_path = ibdata1:12M;ibdata2:10M:autoextend  
27. xtrabackup:   innodb_log_group_home_dir = ./  
28. xtrabackup:   innodb_log_files_in_group = 2 
29. xtrabackup:   innodb_log_file_size = 50331648 
30. 151028 14:26:59 >> log scanned up  to  (13254537)  
31. xtrabackup: Generating a list  of  tablespaces  
32. 151028 14:27:00 [01] Copying ./ibdata1  to  /data/mysql/backup/ full /ibdata1  
33. 151028 14:27:00 [01]        ...done  
34. 151028 14:27:00 >> log scanned up  to  (13254537)  
35. 151028 14:27:00 [01] Copying ./ibdata2  to  /data/mysql/backup/ full /ibdata2  
36. 151028 14:27:01 [01]        ...done  
37. 151028 14:27:01 [01] Copying ./prod/t2.ibd  to  /data/mysql/backup/ full /prod/t2.ibd  
38. 151028 14:27:01 [01]        ...done  
39. 151028 14:27:01 [01] Copying ./prod/t1.ibd  to  /data/mysql/backup/ full /prod/t1.ibd  
40. 151028 14:27:01 >> log scanned up  to  (13254537)  
41. 151028 14:27:01 [01]        ...done  
42. 151028 14:27:01 [01] Copying ./prod/t3.ibd  to  /data/mysql/backup/ full /prod/t3.ibd  
43. 151028 14:27:01 [01]        ...done  
44. 151028 14:27:02 [01] Copying ./mysql/slave_worker_info.ibd  to  /data/mysql/backup/ full /mysql/slave_worker_info.ibd  
45. 151028 14:27:02 [01]        ...done  
46. 151028 14:27:02 [01] Copying ./mysql/slave_master_info.ibd  to  /data/mysql/backup/ full /mysql/slave_master_info.ibd  
47. 151028 14:27:02 [01]        ...done  
48. 151028 14:27:02 [01] Copying ./mysql/slave_relay_log_info.ibd  to  /data/mysql/backup/ full /mysql/slave_relay_log_info.ibd  
49. 151028 14:27:02 [01]        ...done  
50. 151028 14:27:02 [01] Copying ./mysql/innodb_index_stats.ibd  to  /data/mysql/backup/ full /mysql/innodb_index_stats.ibd  
51. 151028 14:27:02 [01]        ...done  
52. 151028 14:27:02 [01] Copying ./mysql/innodb_table_stats.ibd  to  /data/mysql/backup/ full /mysql/innodb_table_stats.ibd  
53. 151028 14:27:02 [01]        ...done  
54. 151028 14:27:02 >> log scanned up  to  (13254537)  
55. Error: failed  to   execute  query LOCK TABLES  FOR  BACKUP: Access denied; you need ( at  least one  of ) the RELOAD privilege(s)  for  this operation  
56. [root@rh64 backup]# innobackupex   
57. 151028 14:34:47 innobackupex: Starting the backup operation  
58. IMPORTANT: Please  check  that the backup run completes successfully.  
59.             At  the  end   of  a successful backup run innobackupex  
60.            prints  "completed OK!" .  
61. 151028 14:34:47  version_check Connecting  to  MySQL server  with  DSN  'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock'   as   'bakusr'   (using  password : YES).  
62. 151028 14:34:47  version_check Connected  to  MySQL server  
63. 151028 14:34:47  version_check Executing a version  check  against the server...  
64. 151028 14:34:47  version_check Done.  
65. 151028 14:34:47 Connecting  to  MySQL server host: localhost,  user : bakusr,  password :  set , port: 0, socket: /var/lib/mysql/mysql.sock  
66. Using server version 5.6.25-73.1 
67. innobackupex version 2.3.2 based  on  MySQL server 5.6.24 Linux (x86_64) (revision id: 306a2e0)  
68. xtrabackup: uses posix_fadvise().  
69. xtrabackup: cd  to  /var/lib/mysql  
70. xtrabackup:  open  files limit requested 0,  set   to  1024 
71. xtrabackup: using the following InnoDB configuration:  
72. xtrabackup:   innodb_data_home_dir = ./  
73. xtrabackup:   innodb_data_file_path = ibdata1:12M;ibdata2:10M:autoextend  
74. xtrabackup:   innodb_log_group_home_dir = ./  
75. xtrabackup:   innodb_log_files_in_group = 2 
76. xtrabackup:   innodb_log_file_size = 50331648 
77. innobackupex: Can 't create/write to file ' /data/mysql/backup/ full /xtrabackup_logfile' (Errcode: 17 - File exists)  
78. xtrabackup: error: failed  to   open  the target stream  for   'xtrabackup_logfile' .  
79. [root@rh64 backup]# ls  
80. bak.sh   full   prod  t.txt  
81. [root@rh64 backup]# rm -rf  full   
82. [root@rh64 backup]# innobackupex   
83. 151028 14:35:09 innobackupex: Starting the backup operation  
84. IMPORTANT: Please  check  that the backup run completes successfully.  
85.             At  the  end   of  a successful backup run innobackupex  
86.            prints  "completed OK!" .  
87. 151028 14:35:09  version_check Connecting  to  MySQL server  with  DSN  'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock'   as   'bakusr'   (using  password : YES).  
88. 151028 14:35:09  version_check Connected  to  MySQL server  
89. 151028 14:35:09  version_check Executing a version  check  against the server...  
90. 151028 14:35:09  version_check Done.  
91. 151028 14:35:09 Connecting  to  MySQL server host: localhost,  user : bakusr,  password :  set , port: 0, socket: /var/lib/mysql/mysql.sock  
92. Using server version 5.6.25-73.1 
93. innobackupex version 2.3.2 based  on  MySQL server 5.6.24 Linux (x86_64) (revision id: 306a2e0)  
94. xtrabackup: uses posix_fadvise().  
95. xtrabackup: cd  to  /var/lib/mysql  
96. xtrabackup:  open  files limit requested 0,  set   to  1024 
97. xtrabackup: using the following InnoDB configuration:  
98. xtrabackup:   innodb_data_home_dir = ./  
99. xtrabackup:   innodb_data_file_path = ibdata1:12M;ibdata2:10M:autoextend  
100. xtrabackup:   innodb_log_group_home_dir = ./  
101. xtrabackup:   innodb_log_files_in_group = 2 
102. xtrabackup:   innodb_log_file_size = 50331648 
103. 151028 14:35:09 >> log scanned up  to  (13254537)  
104. xtrabackup: Generating a list  of  tablespaces  
105. 151028 14:35:09 [01] Copying ./ibdata1  to  /data/mysql/backup/ full /ibdata1  
106. 151028 14:35:10 >> log scanned up  to  (13254537)  
107. 151028 14:35:10 [01]        ...done  
108. 151028 14:35:11 [01] Copying ./ibdata2  to  /data/mysql/backup/ full /ibdata2  
109. 151028 14:35:11 [01]        ...done  
110. 151028 14:35:11 >> log scanned up  to  (13254537)  
111. 151028 14:35:11 [01] Copying ./prod/t2.ibd  to  /data/mysql/backup/ full /prod/t2.ibd  
112. 151028 14:35:11 [01]        ...done  
113. 151028 14:35:11 [01] Copying ./prod/t1.ibd  to  /data/mysql/backup/ full /prod/t1.ibd  
114. 151028 14:35:12 [01]        ...done  
115. 151028 14:35:12 [01] Copying ./prod/t3.ibd  to  /data/mysql/backup/ full /prod/t3.ibd  
116. 151028 14:35:12 >> log scanned up  to  (13254537)  
117. 151028 14:35:12 [01]        ...done  
118. 151028 14:35:12 [01] Copying ./mysql/slave_worker_info.ibd  to  /data/mysql/backup/ full /mysql/slave_worker_info.ibd  
119. 151028 14:35:12 [01]        ...done  
120. 151028 14:35:12 [01] Copying ./mysql/slave_master_info.ibd  to  /data/mysql/backup/ full /mysql/slave_master_info.ibd  
121. 151028 14:35:12 [01]        ...done  
122. 151028 14:35:12 [01] Copying ./mysql/slave_relay_log_info.ibd  to  /data/mysql/backup/ full /mysql/slave_relay_log_info.ibd  
123. 151028 14:35:12 [01]        ...done  
124. 151028 14:35:12 [01] Copying ./mysql/innodb_index_stats.ibd  to  /data/mysql/backup/ full /mysql/innodb_index_stats.ibd  
125. 151028 14:35:12 [01]        ...done  
126. 151028 14:35:12 [01] Copying ./mysql/innodb_table_stats.ibd  to  /data/mysql/backup/ full /mysql/innodb_table_stats.ibd  
127. 151028 14:35:12 [01]        ...done  
128. 151028 14:35:12 Starting  to  backup non-InnoDB tables  and  files  
129. 151028 14:35:12 [01] Copying ./prod/t1.frm  to  /data/mysql/backup/ full /prod/t1.frm  
130. 151028 14:35:13 [01]        ...done  
131. 151028 14:35:13 [01] Copying ./prod/t2.frm  to  /data/mysql/backup/ full /prod/t2.frm  
132. 151028 14:35:13 [01]        ...done  
133. 151028 14:35:13 [01] Copying ./prod/t3.frm  to  /data/mysql/backup/ full /prod/t3.frm  
134. 151028 14:35:13 [01]        ...done  
135. 151028 14:35:13 [01] Copying ./prod/db.opt  to  /data/mysql/backup/ full /prod/db.opt  
136. 151028 14:35:13 [01]        ...done  
137. 151028 14:35:13 >> log scanned up  to  (13254537)  
138. 151028 14:35:13 [00] Writing test/db.opt  
139. 151028 14:35:13 [00]        ...done  
140. 151028 14:35:13 [01] Copying ./mysql/time_zone_name.MYD  to  /data/mysql/backup/ full /mysql/time_zone_name.MYD  
141. 151028 14:35:13 [01]        ...done  
142. 151028 14:35:13 [01] Copying ./mysql/help_topic.frm  to  /data/mysql/backup/ full /mysql/help_topic.frm  
143. 151028 14:35:13 [01]        ...done  
144. 151028 14:35:13 [01] Copying ./mysql/event.frm  to  /data/mysql/backup/ full /mysql/event.frm  
145. 151028 14:35:13 [01]        ...done  
146. 151028 14:35:13 [01] Copying ./mysql/time_zone.MYD  to  /data/mysql/backup/ full /mysql/time_zone.MYD  
147. 151028 14:35:13 [01]        ...done  
148. 151028 14:35:13 [01] Copying ./mysql/slow_log.CSV  to  /data/mysql/backup/ full /mysql/slow_log.CSV  
149. 151028 14:35:13 [01]        ...done  
150. 151028 14:35:13 [01] Copying ./mysql/innodb_index_stats.frm  to  /data/mysql/backup/ full /mysql/innodb_index_stats.frm  
151. 151028 14:35:13 [01]        ...done  
152. 151028 14:35:14 [01] Copying ./mysql/columns_priv.MYI  to  /data/mysql/backup/ full /mysql/columns_priv.MYI  
153. 151028 14:35:14 [01]        ...done  
154. 151028 14:35:14 [01] Copying ./mysql/plugin.MYI  to  /data/mysql/backup/ full /mysql/plugin.MYI  
155. 151028 14:35:14 [01]        ...done  
156. 151028 14:35:14 [01] Copying ./mysql/time_zone_leap_second.frm  to  /data/mysql/backup/ full /mysql/time_zone_leap_second.frm  
157. 151028 14:35:14 [01]        ...done  
158. 151028 14:35:14 [01] Copying ./mysql/time_zone_transition_type.MYI  to  /data/mysql/backup/ full /mysql/time_zone_transition_type.MYI  
159. 151028 14:35:14 [01]        ...done  
160. 151028 14:35:14 >> log scanned up  to  (13254537)  
161. 151028 14:35:14 [01] Copying ./mysql/proc.frm  to  /data/mysql/backup/ full /mysql/proc.frm  
162. 151028 14:35:14 [01]        ...done  
163. 151028 14:35:14 [01] Copying ./mysql/procs_priv.MYD  to  /data/mysql/backup/ full /mysql/procs_priv.MYD  
164. 151028 14:35:14 [01]        ...done  
165. 151028 14:35:14 [01] Copying ./mysql/proxies_priv.MYI  to  /data/mysql/backup/ full /mysql/proxies_priv.MYI  
166. 151028 14:35:14 [01]        ...done  
167. 151028 14:35:14 [01] Copying ./mysql/ndb_binlog_index.MYI  to  /data/mysql/backup/ full /mysql/ndb_binlog_index.MYI  
168. 151028 14:35:14 [01]        ...done  
169. 151028 14:35:14 [01] Copying ./mysql/servers.MYI  to  /data/mysql/backup/ full /mysql/servers.MYI  
170. 151028 14:35:14 [01]        ...done  
171. 151028 14:35:15 [01] Copying ./mysql/slow_log.frm  to  /data/mysql/backup/ full /mysql/slow_log.frm  
172. 151028 14:35:15 [01]        ...done  
173. 151028 14:35:15 [01] Copying ./mysql/help_relation.MYD  to  /data/mysql/backup/ full /mysql/help_relation.MYD  
174. 151028 14:35:15 [01]        ...done  
175. 151028 14:35:15 [01] Copying ./mysql/ user .frm  to  /data/mysql/backup/ full /mysql/ user .frm  
176. 151028 14:35:15 [01]        ...done  
177. 151028 14:35:15 >> log scanned up  to  (13254537)  
178. 151028 14:35:15 [01] Copying ./mysql/plugin.frm  to  /data/mysql/backup/ full /mysql/plugin.frm  
179. 151028 14:35:15 [01]        ...done  
180. 151028 14:35:15 [01] Copying ./mysql/time_zone_name.frm  to  /data/mysql/backup/ full /mysql/time_zone_name.frm  
181. 151028 14:35:15 [01]        ...done  
182. 151028 14:35:15 [01] Copying ./mysql/ user .MYI  to  /data/mysql/backup/ full /mysql/ user .MYI  
183. 151028 14:35:15 [01]        ...done  
184. 151028 14:35:15 [01] Copying ./mysql/help_relation.frm  to  /data/mysql/backup/ full /mysql/help_relation.frm  
185. 151028 14:35:15 [01]        ...done  
186. 151028 14:35:15 [01] Copying ./mysql/slow_log.CSM  to  /data/mysql/backup/ full /mysql/slow_log.CSM  
187. 151028 14:35:15 [01]        ...done  
188. 151028 14:35:16 [01] Copying ./mysql/time_zone_transition.MYD  to  /data/mysql/backup/ full /mysql/time_zone_transition.MYD  
189. 151028 14:35:16 [01]        ...done  
190. 151028 14:35:16 [01] Copying ./mysql/help_category.MYI  to  /data/mysql/backup/ full /mysql/help_category.MYI  
191. 151028 14:35:16 [01]        ...done  
192. 151028 14:35:16 [01] Copying ./mysql/proc.MYD  to  /data/mysql/backup/ full /mysql/proc.MYD  
193. 151028 14:35:16 [01]        ...done  
194. 151028 14:35:16 [01] Copying ./mysql/db.MYD  to  /data/mysql/backup/ full /mysql/db.MYD  
195. 151028 14:35:16 [01]        ...done  
196. 151028 14:35:16 >> log scanned up  to  (13254537)  
197. 151028 14:35:16 [01] Copying ./mysql/tables_priv.MYD  to  /data/mysql/backup/ full /mysql/tables_priv.MYD  
198. 151028 14:35:16 [01]        ...done  
199. 151028 14:35:16 [01] Copying ./mysql/time_zone_leap_second.MYI  to  /data/mysql/backup/ full /mysql/time_zone_leap_second.MYI  
200. 151028 14:35:16 [01]        ...done  
201. 151028 14:35:16 [01] Copying ./mysql/help_relation.MYI  to  /data/mysql/backup/ full /mysql/help_relation.MYI  
202. 151028 14:35:16 [01]        ...done  
203. 151028 14:35:16 [01] Copying ./mysql/ndb_binlog_index.MYD  to  /data/mysql/backup/ full /mysql/ndb_binlog_index.MYD  
204. 151028 14:35:16 [01]        ...done  
205. 151028 14:35:16 [01] Copying ./mysql/slave_worker_info.frm  to  /data/mysql/backup/ full /mysql/slave_worker_info.frm  
206. 151028 14:35:16 [01]        ...done  
207. 151028 14:35:17 [01] Copying ./mysql/time_zone.frm  to  /data/mysql/backup/ full /mysql/time_zone.frm  
208. 151028 14:35:17 [01]        ...done  
209. 151028 14:35:17 [01] Copying ./mysql/slave_relay_log_info.frm  to  /data/mysql/backup/ full /mysql/slave_relay_log_info.frm  
210. 151028 14:35:17 [01]        ...done  
211. 151028 14:35:17 [01] Copying ./mysql/columns_priv.MYD  to  /data/mysql/backup/ full /mysql/columns_priv.MYD  
212. 151028 14:35:17 [01]        ...done  
213. 151028 14:35:17 >> log scanned up  to  (13254537)  
214. 151028 14:35:17 [01] Copying ./mysql/time_zone_transition_type.MYD  to  /data/mysql/backup/ full /mysql/time_zone_transition_type.MYD  
215. 151028 14:35:17 [01]        ...done  
216. 151028 14:35:17 [01] Copying ./mysql/ user .MYD  to  /data/mysql/backup/ full /mysql/ user .MYD  
217. 151028 14:35:17 [01]        ...done  
218. 151028 14:35:17 [01] Copying ./mysql/tables_priv.frm  to  /data/mysql/backup/ full /mysql/tables_priv.frm  
219. 151028 14:35:17 [01]        ...done  
220. 151028 14:35:17 [01] Copying ./mysql/help_category.MYD  to  /data/mysql/backup/ full /mysql/help_category.MYD  
221. 151028 14:35:17 [01]        ...done  
222. 151028 14:35:17 [01] Copying ./mysql/help_keyword.MYD  to  /data/mysql/backup/ full /mysql/help_keyword.MYD  
223. 151028 14:35:18 [01]        ...done  
224. 151028 14:35:18 [01] Copying ./mysql/time_zone_transition.frm  to  /data/mysql/backup/ full /mysql/time_zone_transition.frm  
225. 151028 14:35:18 [01]        ...done  
226. 151028 14:35:18 [01] Copying ./mysql/servers.frm  to  /data/mysql/backup/ full /mysql/servers.frm  
227. 151028 14:35:18 [01]        ...done  
228. 151028 14:35:18 [01] Copying ./mysql/general_log.frm  to  /data/mysql/backup/ full /mysql/general_log.frm  
229. 151028 14:35:18 [01]        ...done  
230. 151028 14:35:18 >> log scanned up  to  (13254537)  
231. 151028 14:35:18 [01] Copying ./mysql/help_keyword.frm  to  /data/mysql/backup/ full /mysql/help_keyword.frm  
232. 151028 14:35:18 [01]        ...done  
233. 151028 14:35:18 [01] Copying ./mysql/time_zone_transition_type.frm  to  /data/mysql/backup/ full /mysql/time_zone_transition_type.frm  
234. 151028 14:35:18 [01]        ...done  
235. 151028 14:35:18 [01] Copying ./mysql/columns_priv.frm  to  /data/mysql/backup/ full /mysql/columns_priv.frm  
236. 151028 14:35:18 [01]        ...done  
237. 151028 14:35:18 [01] Copying ./mysql/proxies_priv.frm  to  /data/mysql/backup/ full /mysql/proxies_priv.frm  
238. 151028 14:35:18 [01]        ...done  
239. 151028 14:35:19 [01] Copying ./mysql/help_keyword.MYI  to  /data/mysql/backup/ full /mysql/help_keyword.MYI  
240. 151028 14:35:19 [01]        ...done  
241. 151028 14:35:19 [01] Copying ./mysql/general_log.CSV  to  /data/mysql/backup/ full /mysql/general_log.CSV  
242. 151028 14:35:19 [01]        ...done  
243. 151028 14:35:19 [01] Copying ./mysql/event.MYD  to  /data/mysql/backup/ full /mysql/event.MYD  
244. 151028 14:35:19 [01]        ...done  
245. 151028 14:35:19 [01] Copying ./mysql/plugin.MYD  to  /data/mysql/backup/ full /mysql/plugin.MYD  
246. 151028 14:35:19 [01]        ...done  
247. 151028 14:35:19 [01] Copying ./mysql/proxies_priv.MYD  to  /data/mysql/backup/ full /mysql/proxies_priv.MYD  
248. 151028 14:35:19 [01]        ...done  
249. 151028 14:35:19 >> log scanned up  to  (13254537)  
250. 151028 14:35:19 [01] Copying ./mysql/servers.MYD  to  /data/mysql/backup/ full /mysql/servers.MYD  
251. 151028 14:35:19 [01]        ...done  
252. 151028 14:35:19 [01] Copying ./mysql/event.MYI  to  /data/mysql/backup/ full /mysql/event.MYI  
253. 151028 14:35:19 [01]        ...done  
254. 151028 14:35:19 [01] Copying ./mysql/time_zone_transition.MYI  to  /data/mysql/backup/ full /mysql/time_zone_transition.MYI  
255. 151028 14:35:19 [01]        ...done  
256. 151028 14:35:19 [01] Copying ./mysql/tables_priv.MYI  to  /data/mysql/backup/ full /mysql/tables_priv.MYI  
257. 151028 14:35:19 [01]        ...done  
258. 151028 14:35:19 [01] Copying ./mysql/ndb_binlog_index.frm  to  /data/mysql/backup/ full /mysql/ndb_binlog_index.frm  
259. 151028 14:35:19 [01]        ...done  
260. 151028 14:35:20 [01] Copying ./mysql/db.MYI  to  /data/mysql/backup/ full /mysql/db.MYI  
261. 151028 14:35:20 [01]        ...done  
262. 151028 14:35:20 [01] Copying ./mysql/time_zone_name.MYI  to  /data/mysql/backup/ full /mysql/time_zone_name.MYI  
263. 151028 14:35:20 [01]        ...done  
264. 151028 14:35:20 [01] Copying ./mysql/time_zone_leap_second.MYD  to  /data/mysql/backup/ full /mysql/time_zone_leap_second.MYD  
265. 151028 14:35:20 [01]        ...done  
266. 151028 14:35:20 >> log scanned up  to  (13254537)  
267. 151028 14:35:20 [01] Copying ./mysql/help_topic.MYD  to  /data/mysql/backup/ full /mysql/help_topic.MYD  
268. 151028 14:35:20 [01]        ...done  
269. 151028 14:35:20 [01] Copying ./mysql/help_topic.MYI  to  /data/mysql/backup/ full /mysql/help_topic.MYI  
270. 151028 14:35:20 [01]        ...done  
271. 151028 14:35:20 [01] Copying ./mysql/db.frm  to  /data/mysql/backup/ full /mysql/db.frm  
272. 151028 14:35:20 [01]        ...done  
273. 151028 14:35:20 [01] Copying ./mysql/func.MYI  to  /data/mysql/backup/ full /mysql/func.MYI  
274. 151028 14:35:20 [01]        ...done  
275. 151028 14:35:20 [01] Copying ./mysql/procs_priv.MYI  to  /data/mysql/backup/ full /mysql/procs_priv.MYI  
276. 151028 14:35:21 [01]        ...done  
277. 151028 14:35:21 [01] Copying ./mysql/help_category.frm  to  /data/mysql/backup/ full /mysql/help_category.frm  
278. 151028 14:35:21 [01]        ...done  
279. 151028 14:35:21 [01] Copying ./mysql/procs_priv.frm  to  /data/mysql/backup/ full /mysql/procs_priv.frm  
280. 151028 14:35:21 [01]        ...done  
281. 151028 14:35:21 [01] Copying ./mysql/func.MYD  to  /data/mysql/backup/ full /mysql/func.MYD  
282. 151028 14:35:21 [01]        ...done  
283. 151028 14:35:21 >> log scanned up  to  (13254537)  
284. 151028 14:35:21 [01] Copying ./mysql/func.frm  to  /data/mysql/backup/ full /mysql/func.frm  
285. 151028 14:35:21 [01]        ...done  
286. 151028 14:35:21 [01] Copying ./mysql/proc.MYI  to  /data/mysql/backup/ full /mysql/proc.MYI  
287. 151028 14:35:21 [01]        ...done  
288. 151028 14:35:21 [01] Copying ./mysql/general_log.CSM  to  /data/mysql/backup/ full /mysql/general_log.CSM  
289. 151028 14:35:21 [01]        ...done  
290. 151028 14:35:21 [01] Copying ./mysql/time_zone.MYI  to  /data/mysql/backup/ full /mysql/time_zone.MYI  
291. 151028 14:35:21 [01]        ...done  
292. 151028 14:35:21 [01] Copying ./mysql/slave_master_info.frm  to  /data/mysql/backup/ full /mysql/slave_master_info.frm  
293. 151028 14:35:21 [01]        ...done  
294. 151028 14:35:22 [01] Copying ./mysql/innodb_table_stats.frm  to  /data/mysql/backup/ full /mysql/innodb_table_stats.frm  
295. 151028 14:35:22 [01]        ...done  
296. 151028 14:35:22 [01] Copying ./performance_schema/users.frm  to  /data/mysql/backup/ full /performance_schema/users.frm  
297. 151028 14:35:22 [01]        ...done  
298. 151028 14:35:22 [01] Copying ./performance_schema/events_waits_history_long.frm  to  /data/mysql/backup/ full /performance_schema/events_waits_history_long.frm  
299. 151028 14:35:22 [01]        ...done  
300. 151028 14:35:22 >> log scanned up  to  (13254537)  
301. 151028 14:35:22 [01] Copying ./performance_schema/events_statements_summary_by_host_by_event_name.frm  to  /data/mysql/backup/ full /performance_schema/events_statements_summary_by_host_by_event_name.frm  
302. 151028 14:35:22 [01]        ...done  
303. 151028 14:35:22 [01] Copying ./performance_schema/table_io_waits_summary_by_index_usage.frm  to  /data/mysql/backup/ full /performance_schema/table_io_waits_summary_by_index_usage.frm  
304. 151028 14:35:22 [01]        ...done  
305. 151028 14:35:22 [01] Copying ./performance_schema/events_waits_history.frm  to  /data/mysql/backup/ full /performance_schema/events_waits_history.frm  
306. 151028 14:35:22 [01]        ...done  
307. 151028 14:35:22 [01] Copying ./performance_schema/host_cache.frm  to  /data/mysql/backup/ full /performance_schema/host_cache.frm  
308. 151028 14:35:22 [01]        ...done  
309. 151028 14:35:22 [01] Copying ./performance_schema/events_statements_summary_by_thread_by_event_name.frm  to  /data/mysql/backup/ full /performance_schema/events_statements_summary_by_thread_by_event_name.frm  
310. 151028 14:35:22 [01]        ...done  
311. 151028 14:35:23 [01] Copying ./performance_schema/session_connect_attrs.frm  to  /data/mysql/backup/ full /performance_schema/session_connect_attrs.frm  
312. 151028 14:35:23 [01]        ...done  
313. 151028 14:35:23 [01] Copying ./performance_schema/objects_summary_global_by_type.frm  to  /data/mysql/backup/ full /performance_schema/objects_summary_global_by_type.frm  
314. 151028 14:35:23 [01]        ...done  
315. 151028 14:35:23 [01] Copying ./performance_schema/session_account_connect_attrs.frm  to  /data/mysql/backup/ full /performance_schema/session_account_connect_attrs.frm  
316. 151028 14:35:23 [01]        ...done  
317. 151028 14:35:23 >> log scanned up  to  (13254537)  
318. 151028 14:35:23 [01] Copying ./performance_schema/socket_summary_by_event_name.frm  to  /data/mysql/backup/ full /performance_schema/socket_summary_by_event_name.frm  
319. 151028 14:35:23 [01]        ...done  
320. 151028 14:35:23 [01] Copying ./performance_schema/events_stages_summary_global_by_event_name.frm  to  /data/mysql/backup/ full /performance_schema/events_stages_summary_global_by_event_name.frm  
321. 151028 14:35:23 [01]        ...done  
322. 151028 14:35:23 [01] Copying ./performance_schema/rwlock_instances.frm  to  /data/mysql/backup/ full /performance_schema/rwlock_instances.frm  
323. 151028 14:35:23 [01]        ...done  
324. 151028 14:35:23 [01] Copying ./performance_schema/events_stages_current.frm  to  /data/mysql/backup/ full /performance_schema/events_stages_current.frm  
325. 151028 14:35:23 [01]        ...done  
326. 151028 14:35:23 [01] Copying ./performance_schema/file_summary_by_instance.frm  to  /data/mysql/backup/ full /performance_schema/file_summary_by_instance.frm  
327. 151028 14:35:23 [01]        ...done  
328. 151028 14:35:24 [01] Copying ./performance_schema/events_waits_summary_by_host_by_event_name.frm  to  /data/mysql/backup/ full /performance_schema/events_waits_summary_by_host_by_event_name.frm  
329. 151028 14:35:24 [01]        ...done  
330. 151028 14:35:24 [01] Copying ./performance_schema/performance_timers.frm  to  /data/mysql/backup/ full /performance_schema/performance_timers.frm  
331. 151028 14:35:24 [01]        ...done  
332. 151028 14:35:24 [01] Copying ./performance_schema/events_waits_summary_by_thread_by_event_name.frm  to  /data/mysql/backup/ full /performance_schema/events_waits_summary_by_thread_by_event_name.frm  
333. 151028 14:35:24 [01]        ...done  
334. 151028 14:35:24 >> log scanned up  to  (13254537)  
335. 151028 14:35:24 [01] Copying ./performance_schema/events_waits_summary_by_user_by_event_name.frm  to  /data/mysql/backup/ full /performance_schema/events_waits_summary_by_user_by_event_name.frm  
336. 151028 14:35:24 [01]        ...done  
337. 151028 14:35:24 [01] Copying ./performance_schema/cond_instances.frm  to  /data/mysql/backup/ full /performance_schema/cond_instances.frm  
338. 151028 14:35:24 [01]        ...done  
339. 151028 14:35:24 [01] Copying ./performance_schema/events_statements_summary_global_by_event_name.frm  to  /data/mysql/backup/ full /performance_schema/events_statements_summary_global_by_event_name.frm  
340. 151028 14:35:24 [01]        ...done  
341. 151028 14:35:24 [01] Copying ./performance_schema/setup_timers.frm  to  /data/mysql/backup/ full /performance_schema/setup_timers.frm  
342. 151028 14:35:24 [01]        ...done  
343. 151028 14:35:24 [01] Copying ./performance_schema/hosts.frm  to  /data/mysql/backup/ full /performance_schema/hosts.frm  
344. 151028 14:35:24 [01]        ...done  
345. 151028 14:35:25 [01] Copying ./performance_schema/socket_summary_by_instance.frm  to  /data/mysql/backup/ full /performance_schema/socket_summary_by_instance.frm  
346. 151028 14:35:25 [01]        ...done  
347. 151028 14:35:25 [01] Copying ./performance_schema/file_instances.frm  to  /data/mysql/backup/ full /performance_schema/file_instances.frm  
348. 151028 14:35:25 [01]        ...done  
349. 151028 14:35:25 [01] Copying ./performance_schema/table_lock_waits_summary_by_table.frm  to  /data/mysql/backup/ full /performance_schema/table_lock_waits_summary_by_table.frm  
350. 151028 14:35:25 [01]        ...done  
351. 151028 14:35:25 [01] Copying ./performance_schema/events_statements_summary_by_user_by_event_name.frm  to  /data/mysql/backup/ full /performance_schema/events_statements_summary_by_user_by_event_name.frm  
352. 151028 14:35:25 [01]        ...done  
353. 151028 14:35:25 [01] Copying ./performance_schema/events_waits_current.frm  to  /data/mysql/backup/ full /performance_schema/events_waits_current.frm  
354. 151028 14:35:25 >> log scanned up  to  (13254537)  
355. 151028 14:35:25 [01]        ...done  
356. 151028 14:35:25 [01] Copying ./performance_schema/events_statements_summary_by_digest.frm  to  /data/mysql/backup/ full /performance_schema/events_statements_summary_by_digest.frm  
357. 151028 14:35:25 [01]        ...done  
358. 151028 14:35:25 [01] Copying ./performance_schema/events_stages_history.frm  to  /data/mysql/backup/ full /performance_schema/events_stages_history.frm  
359. 151028 14:35:25 [01]        ...done  
360. 151028 14:35:25 [01] Copying ./performance_schema/file_summary_by_event_name.frm  to  /data/mysql/backup/ full /performance_schema/file_summary_by_event_name.frm  
361. 151028 14:35:25 [01]        ...done  
362. 151028 14:35:25 [01] Copying ./performance_schema/table_io_waits_summary_by_table.frm  to  /data/mysql/backup/ full /performance_schema/table_io_waits_summary_by_table.frm  
363. 151028 14:35:25 [01]        ...done  
364. 151028 14:35:25 [01] Copying ./performance_schema/events_stages_summary_by_thread_by_event_name.frm  to  /data/mysql/backup/ full /performance_schema/events_stages_summary_by_thread_by_event_name.frm  
365. 151028 14:35:25 [01]        ...done  
366. 151028 14:35:26 [01] Copying ./performance_schema/threads.frm  to  /data/mysql/backup/ full /performance_schema/threads.frm  
367. 151028 14:35:26 [01]        ...done  
368. 151028 14:35:26 [01] Copying ./performance_schema/accounts.frm  to  /data/mysql/backup/ full /performance_schema/accounts.frm  
369. 151028 14:35:26 [01]        ...done  
370. 151028 14:35:26 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm  to  /data/mysql/backup/ full /performance_schema/events_waits_summary_global_by_event_name.frm  
371. 151028 14:35:26 [01]        ...done  
372. 151028 14:35:26 >> log scanned up  to  (13254537)  
373. 151028 14:35:26 [01] Copying ./performance_schema/setup_objects.frm  to  /data/mysql/backup/ full /performance_schema/setup_objects.frm  
374. 151028 14:35:26 [01]        ...done  
375. 151028 14:35:26 [01] Copying ./performance_schema/events_statements_current.frm  to  /data/mysql/backup/ full /performance_schema/events_statements_current.frm  
376. 151028 14:35:26 [01]        ...done  
377. 151028 14:35:26 [01] Copying ./performance_schema/socket_instances.frm  to  /data/mysql/backup/ full /performance_schema/socket_instances.frm  
378. 151028 14:35:26 [01]        ...done  
379. 151028 14:35:26 [01] Copying ./performance_schema/setup_actors.frm  to  /data/mysql/backup/ full /performance_schema/setup_actors.frm  
380. 151028 14:35:26 [01]        ...done  
381. 151028 14:35:27 [01] Copying ./performance_schema/events_stages_summary_by_account_by_event_name.frm  to  /data/mysql/backup/ full /performance_schema/events_stages_summary_by_account_by_event_name.frm  
382. 151028 14:35:27 [01]        ...done  
383. 151028 14:35:27 [01] Copying ./performance_schema/events_stages_summary_by_host_by_event_name.frm  to  /data/mysql/backup/ full /performance_schema/events_stages_summary_by_host_by_event_name.frm  
384. 151028 14:35:27 [01]        ...done  
385. 151028 14:35:27 [01] Copying ./performance_schema/db.opt  to  /data/mysql/backup/ full /performance_schema/db.opt  
386. 151028 14:35:27 [01]        ...done  
387. 151028 14:35:27 [01] Copying ./performance_schema/events_stages_summary_by_user_by_event_name.frm  to  /data/mysql/backup/ full /performance_schema/events_stages_summary_by_user_by_event_name.frm  
388. 151028 14:35:27 [01]        ...done  
389. 151028 14:35:27 >> log scanned up  to  (13254537)  
390. 151028 14:35:27 [01] Copying ./performance_schema/events_statements_history.frm  to  /data/mysql/backup/ full /performance_schema/events_statements_history.frm  
391. 151028 14:35:27 [01]        ...done  
392. 151028 14:35:27 [01] Copying ./performance_schema/events_waits_summary_by_account_by_event_name.frm  to  /data/mysql/backup/ full /performance_schema/events_waits_summary_by_account_by_event_name.frm  
393. 151028 14:35:27 [01]        ...done  
394. 151028 14:35:27 [01] Copying ./performance_schema/events_waits_summary_by_instance.frm  to  /data/mysql/backup/ full /performance_schema/events_waits_summary_by_instance.frm  
395. 151028 14:35:27 [01]        ...done  
396. 151028 14:35:27 [01] Copying ./performance_schema/setup_consumers.frm  to  /data/mysql/backup/ full /performance_schema/setup_consumers.frm  
397. 151028 14:35:27 [01]        ...done  
398. 151028 14:35:28 [01] Copying ./performance_schema/setup_instruments.frm  to  /data/mysql/backup/ full /performance_schema/setup_instruments.frm  
399. 151028 14:35:28 [01]        ...done  
400. 151028 14:35:28 [01] Copying ./performance_schema/mutex_instances.frm  to  /data/mysql/backup/ full /performance_schema/mutex_instances.frm  
401. 151028 14:35:28 [01]        ...done  
402. 151028 14:35:28 [01] Copying ./performance_schema/events_statements_history_long.frm  to  /data/mysql/backup/ full /performance_schema/events_statements_history_long.frm  
403. 151028 14:35:28 [01]        ...done  
404. 151028 14:35:28 [01] Copying ./performance_schema/events_statements_summary_by_account_by_event_name.frm  to  /data/mysql/backup/ full /performance_schema/events_statements_summary_by_account_by_event_name.frm  
405. 151028 14:35:28 [01]        ...done  
406. 151028 14:35:28 >> log scanned up  to  (13254537)  
407. 151028 14:35:28 [01] Copying ./performance_schema/events_stages_history_long.frm  to  /data/mysql/backup/ full /performance_schema/events_stages_history_long.frm  
408. 151028 14:35:28 [01]        ...done  
409. 151028 14:35:28 Finished backing up non-InnoDB tables  and  files  
410. 151028 14:35:28 Executing LOCK BINLOG  FOR  BACKUP...  
411. 151028 14:35:28 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...  
412. xtrabackup: The latest  check  point ( for  incremental):  '13254537'  
413. xtrabackup: Stopping log copying thread.  
414. .151028 14:35:28 >> log scanned up  to  (13254537)  
415. 151028 14:35:28 Executing UNLOCK BINLOG  
416. 151028 14:35:28 Executing UNLOCK TABLES  
417. 151028 14:35:28  All  tables unlocked  
418. 151028 14:35:28 Backup created  in  directory  '/data/mysql/backup/full'  
419. 151028 14:35:28 [00] Writing backup-my.cnf  
420. 151028 14:35:28 [00]        ...done  
421. 151028 14:35:29 [00] Writing xtrabackup_info  
422. 151028 14:35:29 [00]        ...done  
423. xtrabackup:  Transaction  log  of  lsn (13254537)  to  (13254537) was copied.  
424. 151028 14:35:29 completed OK! 

[root@rh64 backup]# ls -l full

total 22560

-rw-r----- 1 root root 398 Oct 28 14:35 backup-my.cnf

-rw-r----- 1 root root 12582912 Oct 28 14:35 ibdata1

-rw-r----- 1 root root 10485760 Oct 28 14:35 ibdata2

drwx------ 2 root root 4096 Oct 28 14:35 mysql

drwx------ 2 root root 4096 Oct 28 14:35 performance_schema

drwx------ 2 root root 4096 Oct 28 14:35 prod

drwx------ 2 root root 4096 Oct 28 14:35 test

-rw-r----- 1 root root 115 Oct 28 14:35 xtrabackup_checkpoints

-rw-r----- 1 root root 524 Oct 28 14:35 xtrabackup_info

-rw-r----- 1 root root 2560 Oct 28 14:35 xtrabackup_logfile

二、数据库恢复

1、测试

关闭数据库，更改datadir目录

[mysql@rh64 ~]$ service mysql stop

Shutting down MySQL (Percona Server)...[ OK ]

rm: cannot remove `/var/lock/subsys/mysql': Permission denied

[root@rh64 ~]# mv /var/lib/mysql /var/lib/mysql.bak

[root@rh64 ~]# cd /var/lib/mysql.bak

[root@rh64 mysql.bak]# ls

auto.cnf ibdata2 ib_logfile1 mysql prod RPM_UPGRADE_HISTORY test

ibdata1 ib_logfile0 ib_logfile101 performance_schema rh64.pid RPM_UPGRADE_MARKER-LAST

创建新的datadir:

[root@rh64 mysql.bak]# mkdir /var/lib/mysql

数据库恢复：

[root@rh64 backup]# innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data/mysql/backup/full

......

151028 14:35:22 [01] Copying ./performance_schema/events_waits_history.frm to /data/mysql/backup/full/performance_schema/events_waits_history.frm

151028 14:35:22 [01] ...done

151028 14:35:22 [01] Copying ./performance_schema/host_cache.frm to /data/mysql/backup/full/performance_schema/host_cache.frm

151028 14:35:22 [01] ...done

151028 14:35:22 [01] Copying ./performance

......

151028 14:41:53 [01] ...done

151028 14:41:53 [01] Copying ./performance_schema/events_stages_history_long.frm to /var/lib/mysql/performance_schema/events_stages_history_long.frm

151028 14:41:53 [01] ...done

151028 14:41:53 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info

151028 14:41:53 [01] ...done

151028 14:41:54 completed OK!

重新授权datadir：

[root@rh64 lib]# ls -ld mysql.bak/

drwxr-xr-x. 6 mysql mysql 4096 Oct 28 14:38 mysql.bak/

[root@rh64 lib]# ls -ld mysql

drwxr-xr-x 6 root root 4096 Oct 28 14:45 mysql

[root@rh64 lib]# ls -l mysql.bak/

total 170020

-rw-rw----. 1 mysql mysql 56 Sep 6 18:08 auto.cnf

-rw-rw----. 1 mysql mysql 12582912 Oct 28 14:38 ibdata1

-rw-rw----. 1 mysql mysql 10485760 Oct 28 14:38 ibdata2

-rw-rw----. 1 mysql mysql 50331648 Oct 28 14:38 ib_logfile0

-rw-rw----. 1 mysql mysql 50331648 Sep 6 18:06 ib_logfile1

-rw-rw----. 1 mysql mysql 50331648 Sep 11 11:50 ib_logfile101

drwx------. 2 mysql mysql 4096 Sep 6 18:06 mysql

drwx------. 2 mysql mysql 4096 Sep 6 18:06 performance_schema

drwx------. 2 mysql mysql 4096 Oct 13 16:41 prod

-rw-rw----. 1 mysql mysql 5 Oct 15 11:36 rh64.pid

-rw-r--r--. 1 root root 293 Sep 6 18:07 RPM_UPGRADE_HISTORY

-rw-r--r--. 1 mysql mysql 293 Sep 6 18:07 RPM_UPGRADE_MARKER-LAST

drwx------. 2 mysql mysql 4096 Sep 6 18:06 test

[root@rh64 lib]# chown -R mysql.mysql mysql

验证数据恢复：

[root@rh64 lib]# service mysql start

Starting MySQL (Percona Server)....[ OK ]

[root@rh64 lib]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or /g.

Your MySQL connection id is 1

Server version: 5.6.25-73.1 Percona Server (GPL), Release 73.1, Revision 07b797f

Copyright (c) 2009-2015 Percona LLC and/or its affiliates

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.

mysql>

mysql> use prod;

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> select count(*) from t1;

+----------+

| count(*) |

+----------+

| 98304 |

+----------+

1 row in set (0.04 sec)

---至此，数据恢复成功!!!