• 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

MySQL InnoDB表空间传输的用途

MySQL FAQ 彭东稳 7年前 (2017-04-26) 22693次浏览 已收录 0个评论

在 MySQL 中如果要迁移一个表导另外一个服务器/环境中,常规的做法就是使用备份工具备份,比如 mysqldump/mysqlpump/mydumper,然后拷贝备份到目标服务器或者环境导入。如果某一个表数据量很大,导出 dump 文件很大的情况下,使用导出导入工具其实会花费不少的时间。

怎么样提高效率呢,可以有一种想法就是直接拷贝数据文件到目标环境,当然在早期版本中这么做是不可取的,因为会有很多关联数据在 ibdata 中,InnoDB 的数据存在对应的数据字典信息,是存放在共享表空间中,无法直接剥离出来,而在 5.6/5.7 中,就推出了一个很不错的特性,就是迁移表空间,可以把这个配置信息剥离出来,简单来说就是把 ibd 数据文件直接拷贝到目标环境,在目标端挂载即可。

这样一个操作的一个基本前提是使用了独立表空间,开启 innodb_file_per_table 参数(MySQL 5.6 已经默认开启)。

示例:将 InnoDB 表复制到另一个实例

此过程演示如何将正常 InnoDB 表从正在运行的 MySQL 服务器实例复制到另一个正在运行的实例,可以使用与较小调整相同的步骤在同一实例上执行完整表恢复。

1. 在源实例上,创建一个表,如果不存在

2. 在目标实例上,如果不存在,则创建一个表

3. 在源实例上,运行FLUSH TABLES ... FOR EXPORT停止表并创建.cfg元数据文件:

metadata(.cfg)在 InnoDB 数据目录中创建 。

FLUSH TABLES ... FOR EXPORT语句确保对命名表的更改已刷新到磁盘,以便在实例运行时可以进行二进制表副本。当FLUSH TABLES ... FOR EXPORT运行时, InnoDB 会产生一个.cfg在同一个数据库的目录表文件。该.cfg文件包含导入表空间文件时用于模式验证的元数据。

这个命令值得一提的是,保持当前的窗口,不要关闭,如果关闭,.cfg文件就会自动删除,可以看到命令运行后生成了.cfg文件。

在 FLUSH TABLE 之后,这个表就被锁定了,DML 操作是阻塞的,也就意味着迁移的过程中,是无法直接写入数据的。

4. 在目标实例上,丢弃现有的表空间

Discard 的意思就是从数据库 detached,会删除 ibd 文件,保留 frm 文件。也就意味着,你可以对 frm 文件操作,比如:rename table,drop table,但是不能 对ibd 文件操作,比如:dml。

5. 将 .ibd 文件和 .cfg 元数据文件从源实例复制到目标实例

6. 在源实例上,UNLOCK TABLES 用来释放所获得的锁 FLUSH TABLES … FOR EXPORT

7. 在目标实例上,导入表空间

这个时候尤其需要注意 ibd 文件的权限(应该为 600),属主属组都是 mysql 用户。

拷贝完成,我们就可以通过 import tablespace 来进行数据文件挂载。

ALTER TABLE ... IMPORT TABLESPACE功能不会对导入的数据执行外键约束。如果表之间存在外键约束,则所有表应在相同(逻辑)时间点导出。在这种情况下,您将停止更新表,提交所有事务,获取表上的共享锁,然后执行导出操作。

我们检查一下表的情况。

如果没有.cfg文件,还能够 import 成功吗?可以,但是这样就没办法认证 schema 了。导入的时候会报一个 warning。

Level Code Message
Warning 1810 InnoDB: IO Read error: (2, No such file or directory) Error opening ‘./test/t.cfg’, will attempt to import without schema verification

最好能使用.cfg来帮助认证 schema,否则很可能会导致 MySQL 查询此表时卡死,只能通过 kill 查询解决。

另外,我们刚开始就创建了两个相同的表结构,其实也就说是表空间传输时两边的表结构必须一样才行。不然会报错。也就是说如果你有 ibd 文件,还不一定能够恢复,你必须还要知道该表的表结构才行。

同样,分区表也支持表空间传输,具体看官网。

示例:恢复表

有时候由于一些非法操作,或系统断电导致的 InnoDB 表数据字典损坏或 ibdata1 损坏。这时候可能会导致某些表虽然存在 frm 和 ibd 文件,但是数据库启动不了,查看错误日志,如下:

你可以选择开启 innodb_force_recovery 参数,先启动数据库。但由于表数据字典有问题,所以操作表时会报表不存在。

由于存在 frm 和 ibd 文件,我们就可以使用表空间传输来恢复数据了。

第一步就是根据 frm 文件提取出表结构信息,具体可以使用 mysql-utilities 工具包提供的 mysqlfrm 工具来恢复(只能恢复 Oracle MySQL)。

NOTE

MySQL Utilities 是一组基于 Python 语言编写的 Python 库的命令行实用工具集,依赖于 Python 2.6。该工具提供了 MySQL 数据库运维工程中常用的一些工具,诸如克隆、复制、比较、差异、导出、导入、安装、配置、索引、磁盘查看等等。

最好选择源码安装,比较方便:

直接分析

拿到表结构后就可以在当前 MySQL 服务器或者其他服务器上面创建表。之后的操作就跟上面差不多了,先对刚创建的表进行 DISCARD TABLESPACE,然后 IMPORT TABLESPACE 即可。

这里需要注意一点,如果是在当前服务器进行操作,在创建表之前需要先清理掉已经损坏的表(清理之前记得备份 frm 和 ibd 文件哦)。这时候去 drop table 时可能会报表不存在之类的错误。可以多 Google 一下,多做尝试(重启很重要,不要有缓存),一般 frm 文件存在的情况下,drop table 应该问题不大。

<参考>

从Xtrabackup完整备份中恢复单个表

https://dev.mysql.com/doc/refman/8.0/en/innodb-transportable-tablespace-examples.html


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (3)
[资助本站您就扫码 谢谢]
分享 (0)

您必须 登录 才能发表评论!