测试:
1、创建一个表结构相同的表
(root@localhost) [test]> create table b like a;
Query OK, 0 rows affected (0.01 sec)
2、查看数据文件是否有
[root@rabbitmq_1 mysql]# cd test/
[root@rabbitmq_1 test]# ll
total 1152
-rw-r-----. 1 mysql mysql 114688 Nov 20 13:58 a.ibd
-rw-r-----. 1 mysql mysql 114688 Nov 21 10:33 b.ibd
3、删除表空间文件
(root@localhost) [test]> alter table b discard tablespace;
Query OK, 0 rows affected (0.00 sec)
4、查看
[root@rabbitmq_1 test]# ll
total 1072
-rw-r-----. 1 mysql mysql 114688 Nov 20 13:58 a.ibd
5、查询这张表会报错 表空间被删除
(root@localhost) [test]> select * from b;
ERROR 1814 (HY000): Tablespace has been discarded for table 'b'
6、把 a 表锁住(就是锁成只读),把a的数据给b
(root@localhost) [test]> flush table a for export;
Query OK, 0 rows affected (0.00 sec)
7、可以在开一个窗口 看一下
(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test]> delete from a where a='1'; #删除不了,是因为锁住了
可以看一下 session
(root@localhost) [test]> show processlist;
+----+----------+------------------+------+-------------+------+-----------------------+--------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+------------------+------+-------------+------+----------------- -----+--------------+
| 10 | root | localhost | test | Query | 146 | Waiting for table metadata lock | delete from a where a='1' |
:表示现在正在等待表元数据锁定
8、把a的表空间文件 拷贝到b下边
[root@rabbitmq_1 test]# cp a.ibd b.idb
9、然后抓紧做一件事 因为刚才 for export 把表锁住了,需要解锁
(root@localhost) [test]> unlock tables;
Query OK, 0 rows affected (0.00 sec)
10、然后在查看 刚才删除的数据,已经删除 (7步)
(root@localhost) [test]> delete from a where a='1';
Query OK, 1 row affected (7 min 14.36 sec)
11、回滚
(root@localhost) [test]> rollback;
Query OK, 0 rows affected (0.00 sec)
12、修改刚才拷贝文件的权限
[root@rabbitmq_1 test]# chown mysql:mysql b.idb
13、然后 导入
(root@localhost) [test]> alter table b import tablespace;
Query OK, 0 rows affected, 1 warning (0.05 sec)