使用MySQL的可传输表空间特性恢复表或迁移表数据
📅 2017-03-05 | 🖱️
在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=0ALTER TABLE ... IMPORT TABLESPACE不会对导入的数据强制执行外键约束检查。如果表之间存在外键约束，则应在相同时间点导出相关的表IMPORT TABLESPACE对主从复制支持的不是很友好，可能会导致从库卡住。如果执行表空间DISCARD和IMPORT的数据库存在从库，需要同时提前在从库上做好准备工作
下面从MySQL单表备份还原的场景对可传输表空间特性做一个演练，利用可传输表空间特性实现对MySQL的在线局部(单独的库或表)备份和还原。
innobackupex对数据库部分备份 #
首先使用innobackupex对数据库进行部分备份，备份指定的表:
1innobackupex --no-timestamp \
2 --user=root \
3 --password='therootpwd' \
4 --tables-file=/home/mysql/backups/partial-backup/tables_to_backup.txt \
5 /home/mysql/backups/partial-backup > /home/mysql/backups/partial-backup/backup.log 2>&1 &
其中tables_to_backup.txt 的内容为多行的dbname.tablename，用于指定部分备份数据库的哪些表:
1mydb.table1
2mydb.table2
备份完成后，检查确认backup.log中没有错误，并且看到了completed OK!的信息。接下来需要对备份进行一下apply log，因为可能有未提交的事务需要回滚，或者日志中的事务需要重放到备份中。
1innobackupex --apply-log --export /home/mysql/backups/partial-backup > /home/mysql/backups/partial-backup/apply.log 2>&1 &
apply log完成后，检查apply.log中没有错误，并且看到了completed OK!的信息。
基于Transportable Tablespaces特性恢复表 #
在需要恢复表的目标数据库上创建相同的表结构，这里以将mydb.table1和mydb.table2这两张表恢复到mydb2库中为例:
1use mydb2;
2create table table1 ......;
3create table table2 ......;
4lock tables table1 write;
5lock tables table2 write;
上面的命令对新创建的表加上了写锁，以确保安全。接下来在mydb2中丢弃新创建的table1和table2的表空间:
1alter table table1 discard tablespace;
2alter table table2 discard tablespace;
执行成功后，可以去mysql数据目录里mydb2的目录里查看表空间文件table1.ibd和table2.ibd已经被删除了，如果数据库存在从库的话，从库上的表空间文件也会被删除。
接下来将前面备份目录中的table1.ibd和table1.ibd拷贝到mysql数据目录里mydb2的目录里，如果数据库存在从库的话，确认也要拷贝到从库对应的数据目录里，并修改文件权限和所有者为mysql用户。
1cd /home/mysql/backups/partial-backup/mydb
2cp table1.ibd /home/mysql/data/mydb2
3cp table2.ibd /home/mysql/data/mydb2
4cp table1.cfg /home/mysql/data/mydb2
5cp table2.cfg /home/mysql/data/mydb2
6cd /home/mysql/data/mydb2
7chown mysql:mysql table1.ibd table2.ibd table1.cfg table2.cfg
8
9# 如果有从库的话，scp table1.ibd table2.ibd table1.cfg table2.cfg到从库对应的数据目录，并chown文件所有者为mysql
接下来对table1和table2分别导入表空间(如果是主从复制是在主库上执行):
1use mydb2;
2ALTER TABLE table1 IMPORT TABLESPACE;
3ALTER TABLE table2 IMPORT TABLESPACE;
表空间导入后，确认mydb2中的table1和table2的数据已经恢复，最后对这两个表进行一下解锁:
1unlock tables;