使用MySQL的可传输表空间特性恢复表或迁移表数据
文章目录
【注意】最后更新于 March 5, 2017,文中内容可能已过时,请谨慎使用。
在MySQL运维中可能会遇到以下两个需求的场景,一个是从MySQL的备份中恢复单个表;另一个是将一套环境数据库某个表中的数据导入到另一套环境的数据中。 对于前者肯定需要用到数据库的备份,而对于后者如果使用普通的mysqldump导出表数据再导入的方式将十分缓慢。 MySQL从5.6开始支持了可传输表空间(Transportable Tablespaces)特性,该特性允许表空间(table.ibd)从一个实例拷贝到另一个实例上,使用这个特性可以实现前面提到的两个运维场景。
在使用可传输表空间(Transportable Tablespaces)特性要注意以下几点:
- MySQL必须开启
innodb_file_per_table
为on,因为只有这样InnoDB表才会有自己的表空间 - 当表静默时,受影响的表只允许只读事务
- 可传输表空间特性不支持分区表,因为分区表不支持
DISCARD TABLESPACE
foreign_key_checks=1
时,DISCARD TABLESPACE
不支持主外键约束关系,在丢弃表空间之前需要先设置foreign_key_checks=0
ALTER TABLE ... IMPORT TABLESPACE
不会对导入的数据强制执行外键约束检查。如果表之间存在外键约束,则应在相同时间点导出相关的表IMPORT TABLESPACE
对主从复制支持的不是很友好,可能会导致从库卡住。如果执行表空间DISCARD和IMPORT的数据库存在从库,需要同时提前在从库上做好准备工作
下面从MySQL单表备份还原的场景对可传输表空间特性做一个演练,利用可传输表空间特性实现对MySQL的在线局部(单独的库或表)备份和还原。
innobackupex对数据库部分备份
首先使用innobackupex对数据库进行部分备份,备份指定的表:
|
|
其中tables_to_backup.txt
的内容为多行的dbname.tablename
,用于指定部分备份数据库的哪些表:
|
|
备份完成后,检查确认backup.log中没有错误,并且看到了completed OK!
的信息。接下来需要对备份进行一下apply log
,因为可能有未提交的事务需要回滚,或者日志中的事务需要重放到备份中。
|
|
apply log完成后,检查apply.log中没有错误,并且看到了completed OK!
的信息。
基于Transportable Tablespaces特性恢复表
在需要恢复表的目标数据库上创建相同的表结构,这里以将mydb.table1和mydb.table2这两张表恢复到mydb2库中为例:
|
|
上面的命令对新创建的表加上了写锁,以确保安全。接下来在mydb2中丢弃新创建的table1和table2的表空间:
|
|
执行成功后,可以去mysql数据目录里mydb2
的目录里查看表空间文件table1.ibd
和table2.ibd
已经被删除了,如果数据库存在从库的话,从库上的表空间文件也会被删除。
接下来将前面备份目录中的table1.ibd
和table1.ibd
拷贝到mysql数据目录里mydb2
的目录里,如果数据库存在从库的话,确认也要拷贝到从库对应的数据目录里,并修改文件权限和所有者为mysql用户。
|
|
接下来对table1
和table2
分别导入表空间(如果是主从复制是在主库上执行):
|
|
表空间导入后,确认mydb2中的table1和table2的数据已经恢复,最后对这两个表进行一下解锁:
|
|
参考
文章作者 青蛙小白
上次更新 2017-03-05