社区编辑申请
注册/登录
MySQL innodb引擎备份工具XtraBackup之二(数据库全备)
数据库 MySQL
Xtrabackup备份原理:在InnoDB内部会维护一个redo日志文件,我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时,InnoDB会检查数据文件和事务日志,并执行两个步骤:它应用(前滚)已经提交的事务日志到数据文件,并将修改过但没有提交的数据进行回滚操作。

  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, passwordset, 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, passwordset, 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 --defaults-file=/etc/my.cnf --user=bakusr --password='oracle'  --socket=/var/lib/mysql/mysql.sock  /data/mysql/backup/full --no-timestamp  
  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, passwordset, 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 --defaults-file=/etc/my.cnf --user=bakusr --password='oracle'  --socket=/var/lib/mysql/mysql.sock  /data/mysql/backup/full --no-timestamp  
  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, passwordset, 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 --defaults-file=/etc/my.cnf --user=bakusr --password='oracle'  --socket=/var/lib/mysql/mysql.sock  /data/mysql/backup/full --no-timestamp  
  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, passwordset, 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)

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

责任编辑:honglu 来源: 51CTO博客
相关推荐

2022-05-18 23:42:08

网络安全安全分析工具

2015-10-29 13:44:06

2022-04-01 10:08:21

SQL 优化MySQL数据库

2022-05-16 13:37:12

Sysrv僵尸网络微软

2022-05-18 10:58:36

LinuxKali Linux

2022-05-11 14:48:33

腾讯云寿险民生保险

2022-05-16 10:36:08

GitHub开源项目

2022-05-17 11:06:44

数据库MySQL系统

2022-04-19 14:41:29

Oracle数据库SQL

2022-05-25 08:01:37

2022-05-09 11:19:12

CSS函数开源

2022-05-24 14:26:11

云原生数据库云架构

2022-04-26 08:10:33

MySQL存储InnoDB

2022-05-19 19:26:33

区块链大数据数据分析

2022-05-26 10:13:22

C/C++GCC插件单元测试

2022-04-28 08:05:05

2022-04-02 10:23:12

MySQL数据库

2022-04-13 18:40:59

Python开发

2022-05-17 16:56:33

开发工具前端

2022-04-14 13:48:33

MySQL数据库并发量

同话题下的热门内容

关于MySQL数据库性能优化方法,看这一篇文章就够了分库分表会带来读扩散问题?怎么解决?面试官问我 InnoDB 的物理存储结构!浅析MySQL日志体系聊聊索引失效的经典场景InnoDB B-TREE 索引怎么定位一条记录?MySQL常用查询Databases和Tables爷青回,Canal 1.1.6来了,几个重要特性和bug修复

编辑推荐

MySQL集群搭建详解如果对MySQL还停留在这个印象,就out了防止服务器宕机时MySQL数据丢失的几种方案MySQL innodb引擎备份工具XtraBackup之二(数据库全备)MySQL优化: Slave延迟很大的优化方法总结
我收藏的内容
点赞
收藏

51CTO技术栈公众号