MySQL 备份恢复单个innodb表

在实际环境中,时不时需要备份恢复单个或多个表(注意:这里除非明确指定,所说的表一律指InnoDB表),而对于innodb引擎恢复单个表需要整体的恢复,xtrabackup也可以单个表恢复,只不过是用的正则过滤的,不知最新版本是否支持表空间传输特性。本文将要说说怎么移动或复制部分或全部的表到另一台服务器上,而所要用到的技术点就是transportable tablespace特性,这就意味着MySQL5.6.6以及以上版本才支持。

表空间传输特性允许表空间从一个实例移动到另一个实例上。这在以前版本上,这对InnoDB表空间是不可能的,因为所有的表数据都是系统表空间的一部分。

在MySQL5.6.6以及更改版本,FLUSH TABLES ... FOR EXPORT 语法准备将InnoDB表复制到另一台服务器,然后在另一台服务器上执行ALTER TABLE ... DISCARD TABLESPACE 和 ALTER TABLE ... IMPORT TABLESPACE 将数据导入。将.cfg 和 .ibd 文件复制过去,用于在导入时更新表元数据,如空间ID。

使用限制和说明

  • innodb_file_per_table必须设置为on,在 MySQL5.6.6版本默认是开启的。居留在共享系统表空间的表不能静默。
  • 当表静默时,只有只读事务被允许。
  • 当导入表空间时,页面大小必须与导入实例的页面大小相符合。
  • DISCARD TABLESPACE 不支持分区表,也就意味着transportable tablespaces 也不支持分区表。如果在分区表上执行ALTER TABLE ... DISCARD TABLESPACE 将会返回下面的错误信息:ERROR 1031 (HY000): Table storage engine for 'part' doesn't have this option.
  • 当foreign_key_checks=1时,DISCARD TABLESPACE 不支持主键外键约束关系。操作这些表时需要设置为foreign_key_checks。
  • ALTER TABLE ... IMPORT TABLESPACE 不强制外键约束。如果表之间有外键约束,所有的表应该在同一个时间点被导出。
  • ALTER TABLE ... IMPORT TABLESPACE 导入表空间不要求.cfg元数据文件。然而在导入时缺少了.cfg文件元数据检查就无法完成,或返回下面的信息:InnoDB: IO Read error: (2, No such file or directory) Error opening '.\test\t.cfg', will attempt to import without schema verification 1 row in set (0.00 sec) 。
    当没有不匹配的表结构时,导入没有.cfg文件可能会更方便。此外,在元数据不能从.ibd文件中收集的故障恢复时,导入没有.cfg可能更有用的。
  • 导出导入的MySQL版本需要相同。否则,文件必须要在导入的服务器上创建。
  • 在复制架构中,主和从必须设置innodb_file_per_table=1。
  • windows中,文件是不区分大小写的,而Linux和unix是区分大小写的,在跨平台导入导出时,需要设置lower_case_table_names=1。

将表空间复制到另一台上

此过程将演示如何从一个运行的MySQL服务器实例上将表空间复制到另一台上。假设源实例为server_A,目的实例为server_B。

  1. 在server_A上
  2. 在server_B上
  3. 在server_B上
    放弃现有的表空间。在表空间导入前,InnoDB必须丢弃已连接到接受表的表空间。
  4. 在server_A上
    执行FLUSH TABLES ... FOR EXPORT语句静默表并生成.cfg元数据文件。FLUSH TABLES ... FOR EXPORT 这个执行之后,会话不能退出,否则cfg自动消失。

    文件.cfg创建在InnoDB数据目录。
  5. 在server_A上
    复制.ibd和.cfg文件到server_B上

    文件.ibd和.cfg必须在释放共享锁之前复制。
  6. 在server_A上
    释放FLUSH TABLES ... FOR EXPORT语句锁
  7. 在server_B上
    导入表空间

Transportable Tablespace 内幕

以下说明在表空间传输过程中的内部和错误日志信息。

  1. 当在server_B上执行ALTER TABLE ... DISCARD TABLESPACE
    该表锁定在X模式下
    表空间从该表分离
  2. 当在server_A上执行FLUSH TABLES ... FOR EXPORT
    表锁定在共享模式下
    purge coordinator 线程停止
    脏页被同步到磁盘上
    表元数据写入到二进制.cfg文件中
    日志信息如下:
  3. 当在server_A上执行UNLOCK TABLES
    二进制.cfg文件将删除
    共享锁将释放,purge coordinator 线程将重启
    日志信息如下:
  4. 当在server_B上执行ALTER TABLE ... IMPORT TABLESPACE
    每个表空间页面将检查损坏
    每个空间ID和日志序号(LSN)将更新
    标志有效的和LSN更新头页
    Btree页将更新
    页面状态被设置为脏将被写入到磁盘
    日志信息如下:

下文实际操作。理论弄清楚了,实际操作就知道是咋么一回事了。还是那句话,死磕手册。

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

目前评论:3   其中:访客  3   博主  0

  1. longdechuanren517 4

    那么有什么参数能够快速的导入数据而且还能够保证数据

  2. longdechuanren517 4

    我想问下我们线上的服务也是用的是mysql 。引擎是InnoDB。我如果想要在半个小时内恢复数据的话。我应该使用什么参数能够快速恢复而且所有的信息都存在