mysql alter table 使用MySQL的可传输表空间特性恢复表或迁移表数据

在MySQL运维中可能会遇到以下两个需求的场景，一个是从MySQL的备份中恢复单个表；另一个是将一套环境数据库某个表中的数据导入到另一套环境的数据中。 对于前者肯定需要用到数据库的备份，而对于后者如果使用普通的mysqldump导出表数据再导入的方式将十分缓慢。 MySQL从5.6开始支持了可传输表空间(Transportable Tablespaces)特性，该特性允许表空间(table.ibd)从一个实例拷贝到另一个实例上，使用这个特性可以实现前面提到的两个运维场景。

在使用可传输表空间(Transportable Tablespaces)特性要注意以下几点:

下面从MySQL单表备份还原的场景对可传输表空间特性做一个演练，利用可传输表空间特性实现对MySQL的在线局部(单独的库或表)备份和还原。

innobackupex对数据库部分备份

首先使用innobackupex对数据库进行部分备份，备份指定的表:

innobackupex --no-timestamp   --user=root   --password='therootpwd'   --tables-file=/home/mysql/backups/partial-backup/tables_to_backup.txt   /home/mysql/backups/partial-backup > /home/mysql/backups/partial-backup/backup.log 2>&1 &

其中tables_to_backup.txt 的内容为多行的dbname.tablename，用于指定部分备份数据库的哪些表:

mydb.table1mydb.table2

备份完成后，检查确认backup.log中没有错误，并且看到了completed OK!的信息。接下来需要对备份进行一下apply log，因为可能有未提交的事务需要回滚，或者日志中的事务需要重放到备份中。

innobackupex --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库中为例:

use mydb2;create table table1 ......;create table table2 ......;lock tables table1 write;lock tables table2 write;

上面的命令对新创建的表加上了写锁，以确保安全。接下来在mydb2中丢弃新创建的table1和table2的表空间:

alter table table1 discard tablespace; alter table table2 discard tablespace; 

执行成功后，可以去mysql数据目录里mydb2的目录里查看表空间文件table1.ibd和table2.ibd已经被删除了，如果数据库存在从库的话，从库上的表空间文件也会被删除。

接下来将前面备份目录中的table1.ibd和table1.ibd拷贝到mysql数据目录里mydb2的目录里，如果数据库存在从库的话，确认也要拷贝到从库对应的数据目录里，并修改文件权限和所有者为mysql用户。

cd /home/mysql/backups/partial-backup/mydbcp table1.ibd /home/mysql/data/mydb2cp table2.ibd /home/mysql/data/mydb2cp table1.cfg /home/mysql/data/mydb2cp table2.cfg /home/mysql/data/mydb2cd /home/mysql/data/mydb2chown mysql:mysql table1.ibd table2.ibd table1.cfg table2.cfg# 如果有从库的话，scp table1.ibd table2.ibd table1.cfg table2.cfg到从库对应的数据目录，并chown文件所有者为mysql

接下来对table1和table2分别导入表空间(如果是主从复制是在主库上执行):

use mydb2;ALTER TABLE table1 IMPORT TABLESPACE;ALTER TABLE table2 IMPORT TABLESPACE;

表空间导入后，确认mydb2中的table1和table2的数据已经恢复，最后对这两个表进行一下解锁:

unlock tables;

参考