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

朴灿烈づ我的快乐病毒、 2022-08-09 14:48 218阅读 0赞

**MySQL Study之--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）备份等，通过复制数据文件，复制日志文件和提交日志到数据文件（前滚）实现了各种复合备份方式。

如下图所示：

![Center][]

**案例分析：**

**一、数据库全备**

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

151028 14:18:16 innobackupex: Starting the backup operation
    
    IMPORTANT: Please check that the backup run completes successfully.
               At the end of a successful backup run innobackupex
               prints "completed OK!".
    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).
    151028 14:18:16  version_check Connected to MySQL server
    151028 14:18:16  version_check Executing a version check against the server...
    151028 14:18:16  version_check Done.
    151028 14:18:16 Connecting to MySQL server host: localhost, user: bakusr, password: set, port: 0, socket: /var/lib/mysql/mysql.sock
    Using server version 5.6.25-73.1
    innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 306a2e0)
    xtrabackup: uses posix_fadvise().
    xtrabackup: cd to /var/lib/mysql
    xtrabackup: open files limit requested 0, set to 1024
    xtrabackup: using the following InnoDB configuration:
    xtrabackup:   innodb_data_home_dir = ./
    xtrabackup:   innodb_data_file_path = ibdata1:12M;ibdata2:10M:autoextend
    xtrabackup:   innodb_log_group_home_dir = ./
    xtrabackup:   innodb_log_files_in_group = 2
    xtrabackup:   innodb_log_file_size = 50331648
    151028 14:18:16 >> log scanned up to (13254527)
    xtrabackup: Generating a list of tablespaces
    151028 14:18:16 [01] Copying ./ibdata1 to /data/mysql/backup/full/2015-10-28_14-18-16/ibdata1
    151028 14:18:17 [01]        ...done
    151028 14:18:17 [01] Copying ./ibdata2 to /data/mysql/backup/full/2015-10-28_14-18-16/ibdata2
    151028 14:18:17 >> log scanned up to (13254527)
    151028 14:18:17 [01]        ...done
    151028 14:18:17 [01] Copying ./prod/t2.ibd to /data/mysql/backup/full/2015-10-28_14-18-16/prod/t2.ibd
    151028 14:18:17 [01]        ...done
    151028 14:18:17 [01] Copying ./prod/t1.ibd to /data/mysql/backup/full/2015-10-28_14-18-16/prod/t1.ibd
    151028 14:18:18 [01]        ...done
    151028 14:18:18 >> log scanned up to (13254527)
    151028 14:18:18 [01] Copying ./prod/t3.ibd to /data/mysql/backup/full/2015-10-28_14-18-16/prod/t3.ibd
    151028 14:18:18 [01]        ...done
    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
    151028 14:18:18 [01]        ...done
    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
    151028 14:18:18 [01]        ...done
    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
    151028 14:18:18 [01]        ...done
    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
    151028 14:18:19 [01]        ...done
    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
    151028 14:18:19 [01]        ...done
    ......
    151028 14:21:39 Executing UNLOCK BINLOG
    151028 14:21:39 Executing UNLOCK TABLES
    151028 14:21:39 All tables unlocked
    151028 14:21:39 Backup created in directory '/data/mysql/backup/full/2015-10-28_14-21-20'
    151028 14:21:39 [00] Writing backup-my.cnf
    151028 14:21:39 [00]        ...done
    151028 14:21:39 [00] Writing xtrabackup_info
    151028 14:21:39 [00]        ...done
    xtrabackup: Transaction log of lsn (13254537) to (13254537) was copied.
    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**

151028 14:26:45 innobackupex: Starting the backup operation
    IMPORTANT: Please check that the backup run completes successfully.
               At the end of a successful backup run innobackupex
               prints "completed OK!".
    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).
    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
    151028 14:26:45 Connecting to MySQL server host: localhost, user: usrbak, password: set, port: 0, socket: /var/lib/mysql/mysql.sock
    Failed to connect to MySQL server: Access denied for user 'usrbak'@'localhost' (using password: YES).
    [root@rh64 backup]# innobackupex --defaults-file=/etc/my.cnf --user=bakusr --password='oracle'  --socket=/var/lib/mysql/mysql.sock  /data/mysql/backup/full --no-timestamp
    151028 14:26:59 innobackupex: Starting the backup operation
    
    IMPORTANT: Please check that the backup run completes successfully.
               At the end of a successful backup run innobackupex
               prints "completed OK!".
    
    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).
    151028 14:26:59  version_check Connected to MySQL server
    151028 14:26:59  version_check Executing a version check against the server...
    151028 14:26:59  version_check Done.
    151028 14:26:59 Connecting to MySQL server host: localhost, user: bakusr, password: set, port: 0, socket: /var/lib/mysql/mysql.sock
    Using server version 5.6.25-73.1
    innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 306a2e0)
    xtrabackup: uses posix_fadvise().
    xtrabackup: cd to /var/lib/mysql
    xtrabackup: open files limit requested 0, set to 1024
    xtrabackup: using the following InnoDB configuration:
    xtrabackup:   innodb_data_home_dir = ./
    xtrabackup:   innodb_data_file_path = ibdata1:12M;ibdata2:10M:autoextend
    xtrabackup:   innodb_log_group_home_dir = ./
    xtrabackup:   innodb_log_files_in_group = 2
    xtrabackup:   innodb_log_file_size = 50331648
    151028 14:26:59 >> log scanned up to (13254537)
    xtrabackup: Generating a list of tablespaces
    151028 14:27:00 [01] Copying ./ibdata1 to /data/mysql/backup/full/ibdata1
    151028 14:27:00 [01]        ...done
    151028 14:27:00 >> log scanned up to (13254537)
    151028 14:27:00 [01] Copying ./ibdata2 to /data/mysql/backup/full/ibdata2
    151028 14:27:01 [01]        ...done
    151028 14:27:01 [01] Copying ./prod/t2.ibd to /data/mysql/backup/full/prod/t2.ibd
    151028 14:27:01 [01]        ...done
    151028 14:27:01 [01] Copying ./prod/t1.ibd to /data/mysql/backup/full/prod/t1.ibd
    151028 14:27:01 >> log scanned up to (13254537)
    151028 14:27:01 [01]        ...done
    151028 14:27:01 [01] Copying ./prod/t3.ibd to /data/mysql/backup/full/prod/t3.ibd
    151028 14:27:01 [01]        ...done
    151028 14:27:02 [01] Copying ./mysql/slave_worker_info.ibd to /data/mysql/backup/full/mysql/slave_worker_info.ibd
    151028 14:27:02 [01]        ...done
    151028 14:27:02 [01] Copying ./mysql/slave_master_info.ibd to /data/mysql/backup/full/mysql/slave_master_info.ibd
    151028 14:27:02 [01]        ...done
    151028 14:27:02 [01] Copying ./mysql/slave_relay_log_info.ibd to /data/mysql/backup/full/mysql/slave_relay_log_info.ibd
    151028 14:27:02 [01]        ...done
    151028 14:27:02 [01] Copying ./mysql/innodb_index_stats.ibd to /data/mysql/backup/full/mysql/innodb_index_stats.ibd
    151028 14:27:02 [01]        ...done
    151028 14:27:02 [01] Copying ./mysql/innodb_table_stats.ibd to /data/mysql/backup/full/mysql/innodb_table_stats.ibd
    151028 14:27:02 [01]        ...done
    151028 14:27:02 >> log scanned up to (13254537)
    Error: failed to execute query LOCK TABLES FOR BACKUP: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
    [root@rh64 backup]# innobackupex --defaults-file=/etc/my.cnf --user=bakusr --password='oracle'  --socket=/var/lib/mysql/mysql.sock  /data/mysql/backup/full --no-timestamp
    151028 14:34:47 innobackupex: Starting the backup operation
    
    IMPORTANT: Please check that the backup run completes successfully.
               At the end of a successful backup run innobackupex
               prints "completed OK!".
    
    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).
    151028 14:34:47  version_check Connected to MySQL server
    151028 14:34:47  version_check Executing a version check against the server...
    151028 14:34:47  version_check Done.
    151028 14:34:47 Connecting to MySQL server host: localhost, user: bakusr, password: set, port: 0, socket: /var/lib/mysql/mysql.sock
    Using server version 5.6.25-73.1
    innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 306a2e0)
    xtrabackup: uses posix_fadvise().
    xtrabackup: cd to /var/lib/mysql
    xtrabackup: open files limit requested 0, set to 1024
    xtrabackup: using the following InnoDB configuration:
    xtrabackup:   innodb_data_home_dir = ./
    xtrabackup:   innodb_data_file_path = ibdata1:12M;ibdata2:10M:autoextend
    xtrabackup:   innodb_log_group_home_dir = ./
    xtrabackup:   innodb_log_files_in_group = 2
    xtrabackup:   innodb_log_file_size = 50331648
    innobackupex: Can't create/write to file '/data/mysql/backup/full/xtrabackup_logfile' (Errcode: 17 - File exists)
    xtrabackup: error: failed to open the target stream for 'xtrabackup_logfile'.
    [root@rh64 backup]# ls
    bak.sh  full  prod  t.txt
    [root@rh64 backup]# rm -rf full
    [root@rh64 backup]# innobackupex --defaults-file=/etc/my.cnf --user=bakusr --password='oracle'  --socket=/var/lib/mysql/mysql.sock  /data/mysql/backup/full --no-timestamp
    151028 14:35:09 innobackupex: Starting the backup operation
    
    IMPORTANT: Please check that the backup run completes successfully.
               At the end of a successful backup run innobackupex
               prints "completed OK!".
    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).
    151028 14:35:09  version_check Connected to MySQL server
    151028 14:35:09  version_check Executing a version check against the server...
    151028 14:35:09  version_check Done.
    151028 14:35:09 Connecting to MySQL server host: localhost, user: bakusr, password: set, port: 0, socket: /var/lib/mysql/mysql.sock
    Using server version 5.6.25-73.1
    innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 306a2e0)
    xtrabackup: uses posix_fadvise().
    xtrabackup: cd to /var/lib/mysql
    xtrabackup: open files limit requested 0, set to 1024
    xtrabackup: using the following InnoDB configuration:
    xtrabackup:   innodb_data_home_dir = ./
    xtrabackup:   innodb_data_file_path = ibdata1:12M;ibdata2:10M:autoextend
    xtrabackup:   innodb_log_group_home_dir = ./
    xtrabackup:   innodb_log_files_in_group = 2
    xtrabackup:   innodb_log_file_size = 50331648
    151028 14:35:09 >> log scanned up to (13254537)
    xtrabackup: Generating a list of tablespaces
    151028 14:35:09 [01] Copying ./ibdata1 to /data/mysql/backup/full/ibdata1
    151028 14:35:10 >> log scanned up to (13254537)
    151028 14:35:10 [01]        ...done
    151028 14:35:11 [01] Copying ./ibdata2 to /data/mysql/backup/full/ibdata2
    151028 14:35:11 [01]        ...done
    151028 14:35:11 >> log scanned up to (13254537)
    151028 14:35:11 [01] Copying ./prod/t2.ibd to /data/mysql/backup/full/prod/t2.ibd
    151028 14:35:11 [01]        ...done
    151028 14:35:11 [01] Copying ./prod/t1.ibd to /data/mysql/backup/full/prod/t1.ibd
    151028 14:35:12 [01]        ...done
    151028 14:35:12 [01] Copying ./prod/t3.ibd to /data/mysql/backup/full/prod/t3.ibd
    151028 14:35:12 >> log scanned up to (13254537)
    151028 14:35:12 [01]        ...done
    151028 14:35:12 [01] Copying ./mysql/slave_worker_info.ibd to /data/mysql/backup/full/mysql/slave_worker_info.ibd
    151028 14:35:12 [01]        ...done
    151028 14:35:12 [01] Copying ./mysql/slave_master_info.ibd to /data/mysql/backup/full/mysql/slave_master_info.ibd
    151028 14:35:12 [01]        ...done
    151028 14:35:12 [01] Copying ./mysql/slave_relay_log_info.ibd to /data/mysql/backup/full/mysql/slave_relay_log_info.ibd
    151028 14:35:12 [01]        ...done
    151028 14:35:12 [01] Copying ./mysql/innodb_index_stats.ibd to /data/mysql/backup/full/mysql/innodb_index_stats.ibd
    151028 14:35:12 [01]        ...done
    151028 14:35:12 [01] Copying ./mysql/innodb_table_stats.ibd to /data/mysql/backup/full/mysql/innodb_table_stats.ibd
    151028 14:35:12 [01]        ...done
    151028 14:35:12 Starting to backup non-InnoDB tables and files
    151028 14:35:12 [01] Copying ./prod/t1.frm to /data/mysql/backup/full/prod/t1.frm
    151028 14:35:13 [01]        ...done
    151028 14:35:13 [01] Copying ./prod/t2.frm to /data/mysql/backup/full/prod/t2.frm
    151028 14:35:13 [01]        ...done
    151028 14:35:13 [01] Copying ./prod/t3.frm to /data/mysql/backup/full/prod/t3.frm
    151028 14:35:13 [01]        ...done
    151028 14:35:13 [01] Copying ./prod/db.opt to /data/mysql/backup/full/prod/db.opt
    151028 14:35:13 [01]        ...done
    151028 14:35:13 >> log scanned up to (13254537)
    151028 14:35:13 [00] Writing test/db.opt
    151028 14:35:13 [00]        ...done
    151028 14:35:13 [01] Copying ./mysql/time_zone_name.MYD to /data/mysql/backup/full/mysql/time_zone_name.MYD
    151028 14:35:13 [01]        ...done
    151028 14:35:13 [01] Copying ./mysql/help_topic.frm to /data/mysql/backup/full/mysql/help_topic.frm
    151028 14:35:13 [01]        ...done
    151028 14:35:13 [01] Copying ./mysql/event.frm to /data/mysql/backup/full/mysql/event.frm
    151028 14:35:13 [01]        ...done
    151028 14:35:13 [01] Copying ./mysql/time_zone.MYD to /data/mysql/backup/full/mysql/time_zone.MYD
    151028 14:35:13 [01]        ...done
    151028 14:35:13 [01] Copying ./mysql/slow_log.CSV to /data/mysql/backup/full/mysql/slow_log.CSV
    151028 14:35:13 [01]        ...done
    151028 14:35:13 [01] Copying ./mysql/innodb_index_stats.frm to /data/mysql/backup/full/mysql/innodb_index_stats.frm
    151028 14:35:13 [01]        ...done
    151028 14:35:14 [01] Copying ./mysql/columns_priv.MYI to /data/mysql/backup/full/mysql/columns_priv.MYI
    151028 14:35:14 [01]        ...done
    151028 14:35:14 [01] Copying ./mysql/plugin.MYI to /data/mysql/backup/full/mysql/plugin.MYI
    151028 14:35:14 [01]        ...done
    151028 14:35:14 [01] Copying ./mysql/time_zone_leap_second.frm to /data/mysql/backup/full/mysql/time_zone_leap_second.frm
    151028 14:35:14 [01]        ...done
    151028 14:35:14 [01] Copying ./mysql/time_zone_transition_type.MYI to /data/mysql/backup/full/mysql/time_zone_transition_type.MYI
    151028 14:35:14 [01]        ...done
    151028 14:35:14 >> log scanned up to (13254537)
    151028 14:35:14 [01] Copying ./mysql/proc.frm to /data/mysql/backup/full/mysql/proc.frm
    151028 14:35:14 [01]        ...done
    151028 14:35:14 [01] Copying ./mysql/procs_priv.MYD to /data/mysql/backup/full/mysql/procs_priv.MYD
    151028 14:35:14 [01]        ...done
    151028 14:35:14 [01] Copying ./mysql/proxies_priv.MYI to /data/mysql/backup/full/mysql/proxies_priv.MYI
    151028 14:35:14 [01]        ...done
    151028 14:35:14 [01] Copying ./mysql/ndb_binlog_index.MYI to /data/mysql/backup/full/mysql/ndb_binlog_index.MYI
    151028 14:35:14 [01]        ...done
    151028 14:35:14 [01] Copying ./mysql/servers.MYI to /data/mysql/backup/full/mysql/servers.MYI
    151028 14:35:14 [01]        ...done
    151028 14:35:15 [01] Copying ./mysql/slow_log.frm to /data/mysql/backup/full/mysql/slow_log.frm
    151028 14:35:15 [01]        ...done
    151028 14:35:15 [01] Copying ./mysql/help_relation.MYD to /data/mysql/backup/full/mysql/help_relation.MYD
    151028 14:35:15 [01]        ...done
    151028 14:35:15 [01] Copying ./mysql/user.frm to /data/mysql/backup/full/mysql/user.frm
    151028 14:35:15 [01]        ...done
    151028 14:35:15 >> log scanned up to (13254537)
    151028 14:35:15 [01] Copying ./mysql/plugin.frm to /data/mysql/backup/full/mysql/plugin.frm
    151028 14:35:15 [01]        ...done
    151028 14:35:15 [01] Copying ./mysql/time_zone_name.frm to /data/mysql/backup/full/mysql/time_zone_name.frm
    151028 14:35:15 [01]        ...done
    151028 14:35:15 [01] Copying ./mysql/user.MYI to /data/mysql/backup/full/mysql/user.MYI
    151028 14:35:15 [01]        ...done
    151028 14:35:15 [01] Copying ./mysql/help_relation.frm to /data/mysql/backup/full/mysql/help_relation.frm
    151028 14:35:15 [01]        ...done
    151028 14:35:15 [01] Copying ./mysql/slow_log.CSM to /data/mysql/backup/full/mysql/slow_log.CSM
    151028 14:35:15 [01]        ...done
    151028 14:35:16 [01] Copying ./mysql/time_zone_transition.MYD to /data/mysql/backup/full/mysql/time_zone_transition.MYD
    151028 14:35:16 [01]        ...done
    151028 14:35:16 [01] Copying ./mysql/help_category.MYI to /data/mysql/backup/full/mysql/help_category.MYI
    151028 14:35:16 [01]        ...done
    151028 14:35:16 [01] Copying ./mysql/proc.MYD to /data/mysql/backup/full/mysql/proc.MYD
    151028 14:35:16 [01]        ...done
    151028 14:35:16 [01] Copying ./mysql/db.MYD to /data/mysql/backup/full/mysql/db.MYD
    151028 14:35:16 [01]        ...done
    151028 14:35:16 >> log scanned up to (13254537)
    151028 14:35:16 [01] Copying ./mysql/tables_priv.MYD to /data/mysql/backup/full/mysql/tables_priv.MYD
    151028 14:35:16 [01]        ...done
    151028 14:35:16 [01] Copying ./mysql/time_zone_leap_second.MYI to /data/mysql/backup/full/mysql/time_zone_leap_second.MYI
    151028 14:35:16 [01]        ...done
    151028 14:35:16 [01] Copying ./mysql/help_relation.MYI to /data/mysql/backup/full/mysql/help_relation.MYI
    151028 14:35:16 [01]        ...done
    151028 14:35:16 [01] Copying ./mysql/ndb_binlog_index.MYD to /data/mysql/backup/full/mysql/ndb_binlog_index.MYD
    151028 14:35:16 [01]        ...done
    151028 14:35:16 [01] Copying ./mysql/slave_worker_info.frm to /data/mysql/backup/full/mysql/slave_worker_info.frm
    151028 14:35:16 [01]        ...done
    151028 14:35:17 [01] Copying ./mysql/time_zone.frm to /data/mysql/backup/full/mysql/time_zone.frm
    151028 14:35:17 [01]        ...done
    151028 14:35:17 [01] Copying ./mysql/slave_relay_log_info.frm to /data/mysql/backup/full/mysql/slave_relay_log_info.frm
    151028 14:35:17 [01]        ...done
    151028 14:35:17 [01] Copying ./mysql/columns_priv.MYD to /data/mysql/backup/full/mysql/columns_priv.MYD
    151028 14:35:17 [01]        ...done
    151028 14:35:17 >> log scanned up to (13254537)
    151028 14:35:17 [01] Copying ./mysql/time_zone_transition_type.MYD to /data/mysql/backup/full/mysql/time_zone_transition_type.MYD
    151028 14:35:17 [01]        ...done
    151028 14:35:17 [01] Copying ./mysql/user.MYD to /data/mysql/backup/full/mysql/user.MYD
    151028 14:35:17 [01]        ...done
    151028 14:35:17 [01] Copying ./mysql/tables_priv.frm to /data/mysql/backup/full/mysql/tables_priv.frm
    151028 14:35:17 [01]        ...done
    151028 14:35:17 [01] Copying ./mysql/help_category.MYD to /data/mysql/backup/full/mysql/help_category.MYD
    151028 14:35:17 [01]        ...done
    151028 14:35:17 [01] Copying ./mysql/help_keyword.MYD to /data/mysql/backup/full/mysql/help_keyword.MYD
    151028 14:35:18 [01]        ...done
    151028 14:35:18 [01] Copying ./mysql/time_zone_transition.frm to /data/mysql/backup/full/mysql/time_zone_transition.frm
    151028 14:35:18 [01]        ...done
    151028 14:35:18 [01] Copying ./mysql/servers.frm to /data/mysql/backup/full/mysql/servers.frm
    151028 14:35:18 [01]        ...done
    151028 14:35:18 [01] Copying ./mysql/general_log.frm to /data/mysql/backup/full/mysql/general_log.frm
    151028 14:35:18 [01]        ...done
    151028 14:35:18 >> log scanned up to (13254537)
    151028 14:35:18 [01] Copying ./mysql/help_keyword.frm to /data/mysql/backup/full/mysql/help_keyword.frm
    151028 14:35:18 [01]        ...done
    151028 14:35:18 [01] Copying ./mysql/time_zone_transition_type.frm to /data/mysql/backup/full/mysql/time_zone_transition_type.frm
    151028 14:35:18 [01]        ...done
    151028 14:35:18 [01] Copying ./mysql/columns_priv.frm to /data/mysql/backup/full/mysql/columns_priv.frm
    151028 14:35:18 [01]        ...done
    151028 14:35:18 [01] Copying ./mysql/proxies_priv.frm to /data/mysql/backup/full/mysql/proxies_priv.frm
    151028 14:35:18 [01]        ...done
    151028 14:35:19 [01] Copying ./mysql/help_keyword.MYI to /data/mysql/backup/full/mysql/help_keyword.MYI
    151028 14:35:19 [01]        ...done
    151028 14:35:19 [01] Copying ./mysql/general_log.CSV to /data/mysql/backup/full/mysql/general_log.CSV
    151028 14:35:19 [01]        ...done
    151028 14:35:19 [01] Copying ./mysql/event.MYD to /data/mysql/backup/full/mysql/event.MYD
    151028 14:35:19 [01]        ...done
    151028 14:35:19 [01] Copying ./mysql/plugin.MYD to /data/mysql/backup/full/mysql/plugin.MYD
    151028 14:35:19 [01]        ...done
    151028 14:35:19 [01] Copying ./mysql/proxies_priv.MYD to /data/mysql/backup/full/mysql/proxies_priv.MYD
    151028 14:35:19 [01]        ...done
    151028 14:35:19 >> log scanned up to (13254537)
    151028 14:35:19 [01] Copying ./mysql/servers.MYD to /data/mysql/backup/full/mysql/servers.MYD
    151028 14:35:19 [01]        ...done
    151028 14:35:19 [01] Copying ./mysql/event.MYI to /data/mysql/backup/full/mysql/event.MYI
    151028 14:35:19 [01]        ...done
    151028 14:35:19 [01] Copying ./mysql/time_zone_transition.MYI to /data/mysql/backup/full/mysql/time_zone_transition.MYI
    151028 14:35:19 [01]        ...done
    151028 14:35:19 [01] Copying ./mysql/tables_priv.MYI to /data/mysql/backup/full/mysql/tables_priv.MYI
    151028 14:35:19 [01]        ...done
    151028 14:35:19 [01] Copying ./mysql/ndb_binlog_index.frm to /data/mysql/backup/full/mysql/ndb_binlog_index.frm
    151028 14:35:19 [01]        ...done
    151028 14:35:20 [01] Copying ./mysql/db.MYI to /data/mysql/backup/full/mysql/db.MYI
    151028 14:35:20 [01]        ...done
    151028 14:35:20 [01] Copying ./mysql/time_zone_name.MYI to /data/mysql/backup/full/mysql/time_zone_name.MYI
    151028 14:35:20 [01]        ...done
    151028 14:35:20 [01] Copying ./mysql/time_zone_leap_second.MYD to /data/mysql/backup/full/mysql/time_zone_leap_second.MYD
    151028 14:35:20 [01]        ...done
    151028 14:35:20 >> log scanned up to (13254537)
    151028 14:35:20 [01] Copying ./mysql/help_topic.MYD to /data/mysql/backup/full/mysql/help_topic.MYD
    151028 14:35:20 [01]        ...done
    151028 14:35:20 [01] Copying ./mysql/help_topic.MYI to /data/mysql/backup/full/mysql/help_topic.MYI
    151028 14:35:20 [01]        ...done
    151028 14:35:20 [01] Copying ./mysql/db.frm to /data/mysql/backup/full/mysql/db.frm
    151028 14:35:20 [01]        ...done
    151028 14:35:20 [01] Copying ./mysql/func.MYI to /data/mysql/backup/full/mysql/func.MYI
    151028 14:35:20 [01]        ...done
    151028 14:35:20 [01] Copying ./mysql/procs_priv.MYI to /data/mysql/backup/full/mysql/procs_priv.MYI
    151028 14:35:21 [01]        ...done
    151028 14:35:21 [01] Copying ./mysql/help_category.frm to /data/mysql/backup/full/mysql/help_category.frm
    151028 14:35:21 [01]        ...done
    151028 14:35:21 [01] Copying ./mysql/procs_priv.frm to /data/mysql/backup/full/mysql/procs_priv.frm
    151028 14:35:21 [01]        ...done
    151028 14:35:21 [01] Copying ./mysql/func.MYD to /data/mysql/backup/full/mysql/func.MYD
    151028 14:35:21 [01]        ...done
    151028 14:35:21 >> log scanned up to (13254537)
    151028 14:35:21 [01] Copying ./mysql/func.frm to /data/mysql/backup/full/mysql/func.frm
    151028 14:35:21 [01]        ...done
    151028 14:35:21 [01] Copying ./mysql/proc.MYI to /data/mysql/backup/full/mysql/proc.MYI
    151028 14:35:21 [01]        ...done
    151028 14:35:21 [01] Copying ./mysql/general_log.CSM to /data/mysql/backup/full/mysql/general_log.CSM
    151028 14:35:21 [01]        ...done
    151028 14:35:21 [01] Copying ./mysql/time_zone.MYI to /data/mysql/backup/full/mysql/time_zone.MYI
    151028 14:35:21 [01]        ...done
    151028 14:35:21 [01] Copying ./mysql/slave_master_info.frm to /data/mysql/backup/full/mysql/slave_master_info.frm
    151028 14:35:21 [01]        ...done
    151028 14:35:22 [01] Copying ./mysql/innodb_table_stats.frm to /data/mysql/backup/full/mysql/innodb_table_stats.frm
    151028 14:35:22 [01]        ...done
    151028 14:35:22 [01] Copying ./performance_schema/users.frm to /data/mysql/backup/full/performance_schema/users.frm
    151028 14:35:22 [01]        ...done
    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
    151028 14:35:22 [01]        ...done
    151028 14:35:22 >> log scanned up to (13254537)
    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
    151028 14:35:22 [01]        ...done
    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
    151028 14:35:22 [01]        ...done
    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_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
    151028 14:35:22 [01]        ...done
    151028 14:35:23 [01] Copying ./performance_schema/session_connect_attrs.frm to /data/mysql/backup/full/performance_schema/session_connect_attrs.frm
    151028 14:35:23 [01]        ...done
    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
    151028 14:35:23 [01]        ...done
    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
    151028 14:35:23 [01]        ...done
    151028 14:35:23 >> log scanned up to (13254537)
    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
    151028 14:35:23 [01]        ...done
    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
    151028 14:35:23 [01]        ...done
    151028 14:35:23 [01] Copying ./performance_schema/rwlock_instances.frm to /data/mysql/backup/full/performance_schema/rwlock_instances.frm
    151028 14:35:23 [01]        ...done
    151028 14:35:23 [01] Copying ./performance_schema/events_stages_current.frm to /data/mysql/backup/full/performance_schema/events_stages_current.frm
    151028 14:35:23 [01]        ...done
    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
    151028 14:35:23 [01]        ...done
    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
    151028 14:35:24 [01]        ...done
    151028 14:35:24 [01] Copying ./performance_schema/performance_timers.frm to /data/mysql/backup/full/performance_schema/performance_timers.frm
    151028 14:35:24 [01]        ...done
    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
    151028 14:35:24 [01]        ...done
    151028 14:35:24 >> log scanned up to (13254537)
    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
    151028 14:35:24 [01]        ...done
    151028 14:35:24 [01] Copying ./performance_schema/cond_instances.frm to /data/mysql/backup/full/performance_schema/cond_instances.frm
    151028 14:35:24 [01]        ...done
    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
    151028 14:35:24 [01]        ...done
    151028 14:35:24 [01] Copying ./performance_schema/setup_timers.frm to /data/mysql/backup/full/performance_schema/setup_timers.frm
    151028 14:35:24 [01]        ...done
    151028 14:35:24 [01] Copying ./performance_schema/hosts.frm to /data/mysql/backup/full/performance_schema/hosts.frm
    151028 14:35:24 [01]        ...done
    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
    151028 14:35:25 [01]        ...done
    151028 14:35:25 [01] Copying ./performance_schema/file_instances.frm to /data/mysql/backup/full/performance_schema/file_instances.frm
    151028 14:35:25 [01]        ...done
    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
    151028 14:35:25 [01]        ...done
    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
    151028 14:35:25 [01]        ...done
    151028 14:35:25 [01] Copying ./performance_schema/events_waits_current.frm to /data/mysql/backup/full/performance_schema/events_waits_current.frm
    151028 14:35:25 >> log scanned up to (13254537)
    151028 14:35:25 [01]        ...done
    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
    151028 14:35:25 [01]        ...done
    151028 14:35:25 [01] Copying ./performance_schema/events_stages_history.frm to /data/mysql/backup/full/performance_schema/events_stages_history.frm
    151028 14:35:25 [01]        ...done
    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
    151028 14:35:25 [01]        ...done
    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
    151028 14:35:25 [01]        ...done
    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
    151028 14:35:25 [01]        ...done
    151028 14:35:26 [01] Copying ./performance_schema/threads.frm to /data/mysql/backup/full/performance_schema/threads.frm
    151028 14:35:26 [01]        ...done
    151028 14:35:26 [01] Copying ./performance_schema/accounts.frm to /data/mysql/backup/full/performance_schema/accounts.frm
    151028 14:35:26 [01]        ...done
    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
    151028 14:35:26 [01]        ...done
    151028 14:35:26 >> log scanned up to (13254537)
    151028 14:35:26 [01] Copying ./performance_schema/setup_objects.frm to /data/mysql/backup/full/performance_schema/setup_objects.frm
    151028 14:35:26 [01]        ...done
    151028 14:35:26 [01] Copying ./performance_schema/events_statements_current.frm to /data/mysql/backup/full/performance_schema/events_statements_current.frm
    151028 14:35:26 [01]        ...done
    151028 14:35:26 [01] Copying ./performance_schema/socket_instances.frm to /data/mysql/backup/full/performance_schema/socket_instances.frm
    151028 14:35:26 [01]        ...done
    151028 14:35:26 [01] Copying ./performance_schema/setup_actors.frm to /data/mysql/backup/full/performance_schema/setup_actors.frm
    151028 14:35:26 [01]        ...done
    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
    151028 14:35:27 [01]        ...done
    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
    151028 14:35:27 [01]        ...done
    151028 14:35:27 [01] Copying ./performance_schema/db.opt to /data/mysql/backup/full/performance_schema/db.opt
    151028 14:35:27 [01]        ...done
    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
    151028 14:35:27 [01]        ...done
    151028 14:35:27 >> log scanned up to (13254537)
    151028 14:35:27 [01] Copying ./performance_schema/events_statements_history.frm to /data/mysql/backup/full/performance_schema/events_statements_history.frm
    151028 14:35:27 [01]        ...done
    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
    151028 14:35:27 [01]        ...done
    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
    151028 14:35:27 [01]        ...done
    151028 14:35:27 [01] Copying ./performance_schema/setup_consumers.frm to /data/mysql/backup/full/performance_schema/setup_consumers.frm
    151028 14:35:27 [01]        ...done
    151028 14:35:28 [01] Copying ./performance_schema/setup_instruments.frm to /data/mysql/backup/full/performance_schema/setup_instruments.frm
    151028 14:35:28 [01]        ...done
    151028 14:35:28 [01] Copying ./performance_schema/mutex_instances.frm to /data/mysql/backup/full/performance_schema/mutex_instances.frm
    151028 14:35:28 [01]        ...done
    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
    151028 14:35:28 [01]        ...done
    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
    151028 14:35:28 [01]        ...done
    151028 14:35:28 >> log scanned up to (13254537)
    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
    151028 14:35:28 [01]        ...done
    151028 14:35:28 Finished backing up non-InnoDB tables and files
    151028 14:35:28 Executing LOCK BINLOG FOR BACKUP...
    151028 14:35:28 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
    xtrabackup: The latest check point (for incremental): '13254537'
    xtrabackup: Stopping log copying thread.
    .151028 14:35:28 >> log scanned up to (13254537)
    
    
    151028 14:35:28 Executing UNLOCK BINLOG
    151028 14:35:28 Executing UNLOCK TABLES
    151028 14:35:28 All tables unlocked
    151028 14:35:28 Backup created in directory '/data/mysql/backup/full'
    151028 14:35:28 [00] Writing backup-my.cnf
    151028 14:35:28 [00]        ...done
    151028 14:35:29 [00] Writing xtrabackup_info
    151028 14:35:29 [00]        ...done
    xtrabackup: Transaction log of lsn (13254537) to (13254537) was copied.
    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)

**---至此，数据恢复成功！！！**

[Center]: /images/20220731/ca9e552d87024b7c9bd620889ea2d048.png