在 MySQL 中如果要迁移一个表导另外一个服务器/环境中,常规的做法就是使用备份工具备份,比如 mysqldump/mysqlpump/mydumper,然后拷贝备份到目标服务器或者环境导入。如果某一个表数据量很大,导出 dump 文件很大的情况下,使用导出导入工具其实会花费不少的时间。
怎么样提高效率呢,可以有一种想法就是直接拷贝数据文件到目标环境,当然在早期版本中这么做是不可取的,因为会有很多关联数据在 ibdata 中,InnoDB 的数据存在对应的数据字典信息,是存放在共享表空间中,无法直接剥离出来,而在 5.6/5.7 中,就推出了一个很不错的特性,就是迁移表空间,可以把这个配置信息剥离出来,简单来说就是把 ibd 数据文件直接拷贝到目标环境,在目标端挂载即可。
这样一个操作的一个基本前提是使用了独立表空间,开启 innodb_file_per_table 参数(MySQL 5.6 已经默认开启)。
1 2 3 4 5 6 7 |
mysql> show variables like '%per_table%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.04 sec) |
示例:将 InnoDB 表复制到另一个实例
此过程演示如何将正常 InnoDB 表从正在运行的 MySQL 服务器实例复制到另一个正在运行的实例,可以使用与较小调整相同的步骤在同一实例上执行完整表恢复。
1. 在源实例上,创建一个表,如果不存在
1 2 |
mysql> use test; mysql> CREATE TABLE `t` (`DocumentId` int(11) NOT NULL AUTO_INCREMENT,`Text` text,PRIMARY KEY (`DocumentId`)) ENGINE=InnoDB; |
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> select * from t; +------------+----------------------------------------+ | DocumentId | Text | +------------+----------------------------------------+ | 1 | Pease porridge bot,pease porridge cold | | 2 | Pease porridge in the pot | | 3 | Nine days old | | 4 | Some like it bot,some like it cold | | 5 | Some like it in the pot | | 6 | Nine days old | +------------+----------------------------------------+ 6 rows in set (0.00 sec) |
2. 在目标实例上,如果不存在,则创建一个表
1 2 |
mysql> use test; mysql> CREATE TABLE `t` (`DocumentId` int(11) NOT NULL AUTO_INCREMENT,`Text` text,PRIMARY KEY (`DocumentId`)) ENGINE=InnoDB; |
3. 在源实例上,运行FLUSH TABLES ... FOR EXPORT
停止表并创建.cfg
元数据文件:
1 2 |
mysql> use test; mysql> FLUSH TABLES t FOR EXPORT; |
metadata(.cfg
)在 InnoDB 数据目录中创建 。
该FLUSH TABLES ... FOR EXPORT
语句确保对命名表的更改已刷新到磁盘,以便在实例运行时可以进行二进制表副本。当FLUSH TABLES ... FOR EXPORT
运行时, InnoDB 会产生一个.cfg
在同一个数据库的目录表文件。该.cfg
文件包含导入表空间文件时用于模式验证的元数据。
这个命令值得一提的是,保持当前的窗口,不要关闭,如果关闭,.cfg
文件就会自动删除,可以看到命令运行后生成了.cfg
文件。
1 2 3 4 |
$ ls -ls /usr/local/mysql/data/test/ 8 -rw-r----- 1 _mysql _mysql 404 4 26 14:50 t.cfg 24 -rw-r----- 1 _mysql _mysql 8602 4 26 14:33 t.frm 192 -rw-r----- 1 _mysql _mysql 98304 4 26 14:33 t.ibd |
在 FLUSH TABLE 之后,这个表就被锁定了,DML 操作是阻塞的,也就意味着迁移的过程中,是无法直接写入数据的。
4. 在目标实例上,丢弃现有的表空间
1 |
mysql> ALTER TABLE t DISCARD TABLESPACE; |
Discard 的意思就是从数据库 detached,会删除 ibd 文件,保留 frm 文件。也就意味着,你可以对 frm 文件操作,比如:rename table,drop table,但是不能 对ibd 文件操作,比如:dml。
5. 将 .ibd 文件和 .cfg 元数据文件从源实例复制到目标实例
1 |
$ scp /path/to/datadir/test/t.{ibd,cfg} destination-server:/path/to/datadir/test |
6. 在源实例上,UNLOCK TABLES 用来释放所获得的锁 FLUSH TABLES … FOR EXPORT
1 2 |
mysql> use test; mysql> UNLOCK TABLES; |
7. 在目标实例上,导入表空间
这个时候尤其需要注意 ibd 文件的权限(应该为 600),属主属组都是 mysql 用户。
1 |
ERROR 1812 (HY000): Tablespace is missing for table |
拷贝完成,我们就可以通过 import tablespace 来进行数据文件挂载。
1 2 |
mysql> use test; mysql> ALTER TABLE t IMPORT TABLESPACE; |
该ALTER TABLE ... IMPORT TABLESPACE
功能不会对导入的数据执行外键约束。如果表之间存在外键约束,则所有表应在相同(逻辑)时间点导出。在这种情况下,您将停止更新表,提交所有事务,获取表上的共享锁,然后执行导出操作。
我们检查一下表的情况。
1 |
mysql> check table users; |
如果没有.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 文件,但是数据库启动不了,查看错误日志,如下:
1 |
InnoDB: Cannot open table sbtest/t1 from the internal data dictionary of InnoDB though the .frm file for the table exists. |
你可以选择开启 innodb_force_recovery 参数,先启动数据库。但由于表数据字典有问题,所以操作表时会报表不存在。
由于存在 frm 和 ibd 文件,我们就可以使用表空间传输来恢复数据了。
第一步就是根据 frm 文件提取出表结构信息,具体可以使用 mysql-utilities 工具包提供的 mysqlfrm 工具来恢复(只能恢复 Oracle MySQL)。
NOTE
MySQL Utilities 是一组基于 Python 语言编写的 Python 库的命令行实用工具集,依赖于 Python 2.6。该工具提供了 MySQL 数据库运维工程中常用的一些工具,诸如克隆、复制、比较、差异、导出、导入、安装、配置、索引、磁盘查看等等。
最好选择源码安装,比较方便:
1 2 3 4 |
tar -xvzf mysql-utilities-1.6.5.tar.gz cd mysql-utilities-1.6.5 python2.6 setup.py build python2.6 setup.py install |
直接分析
1 |
$ mysqlfrm --diagnostic t1.frm |
拿到表结构后就可以在当前 MySQL 服务器或者其他服务器上面创建表。之后的操作就跟上面差不多了,先对刚创建的表进行 DISCARD TABLESPACE,然后 IMPORT TABLESPACE 即可。
这里需要注意一点,如果是在当前服务器进行操作,在创建表之前需要先清理掉已经损坏的表(清理之前记得备份 frm 和 ibd 文件哦)。这时候去 drop table 时可能会报表不存在之类的错误。可以多 Google 一下,多做尝试(重启很重要,不要有缓存),一般 frm 文件存在的情况下,drop table 应该问题不大。
<参考>
https://dev.mysql.com/doc/refman/8.0/en/innodb-transportable-tablespace-examples.html