mysql> ALTER TABLE t DISCARD TABLESPACE;

mysql> ALTER TABLE t IMPORT TABLESPACE

mysql> create table t1(id int,hiredate datetime);
Query OK, 0 rows affected (0.14 sec)

mysql> create table t2(id int,hiredate datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1,now());
Query OK, 1 row affected (0.06 sec)

mysql> insert into t1 values(2,now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values(1,now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values(2,now());
Query OK, 1 row affected (0.00 sec)

[root@localhost data]# cd /data/
[root@localhost data]# ls
auto.cnf  ib_logfile0  localhost.localdomain.err  mysql_upgrade_info  test
ibdata1   ib_logfile1  mysql                      performance_schema
[root@localhost data]# rm -rf ibdata1 
[root@localhost data]# rm -rf ib_logfile*[root@localhost data]# ls
auto.cnf  localhost.localdomain.err  mysql  mysql_upgrade_info  performance_schema  test

# 2016-08-18 11:13:18 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-08-18 11:13:18 0 [Note] /usr/test/mysql-5.6.32-linux-glibc2.5-x86_64/bin/mysqld (mysqld 5.6.32) starting as process 3948 ...
2016-08-18 11:13:18 3948 [Note] Plugin 'FEDERATED' is disabled.
2016-08-18 11:13:18 3948 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-08-18 11:13:18 3948 [Note] InnoDB: The InnoDB memory heap is disabled
2016-08-18 11:13:18 3948 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-08-18 11:13:18 3948 [Note] InnoDB: Memory barrier is not used
2016-08-18 11:13:18 3948 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-08-18 11:13:18 3948 [Note] InnoDB: Using Linux native AIO
2016-08-18 11:13:18 3948 [Note] InnoDB: Using CPU crc32 instructions
2016-08-18 11:13:18 3948 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-08-18 11:13:19 3948 [Note] InnoDB: Completed initialization of buffer pool
2016-08-18 11:13:19 3948 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2016-08-18 11:13:19 3948 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2016-08-18 11:13:19 3948 [Note] InnoDB: Database physically writes the file full: wait...
2016-08-18 11:13:19 3948 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2016-08-18 11:13:21 3948 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2016-08-18 11:13:22 3948 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2016-08-18 11:13:22 3948 [Warning] InnoDB: New log files created, LSN=45781
2016-08-18 11:13:22 3948 [Note] InnoDB: Doublewrite buffer not found: creating new
2016-08-18 11:13:22 3948 [Note] InnoDB: Doublewrite buffer created
2016-08-18 11:13:22 3948 [Note] InnoDB: 128 rollback segment(s) are active.
2016-08-18 11:13:22 3948 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-08-18 11:13:22 3948 [Note] InnoDB: Foreign key constraint system tables created
2016-08-18 11:13:22 3948 [Note] InnoDB: Creating tablespace and datafile system tables.
2016-08-18 11:13:22 3948 [Note] InnoDB: Tablespace and datafile system tables created.
2016-08-18 11:13:22 3948 [Note] InnoDB: Waiting for purge to start
2016-08-18 11:13:22 3948 [Note] InnoDB: 5.6.32 started; log sequence number 0
2016-08-18 11:13:22 3948 [Note] Server hostname (bind-address): '*'; port: 3310
2016-08-18 11:13:23 3948 [Note] IPv6 is available.
2016-08-18 11:13:23 3948 [Note]   - '::' resolves to '::';
2016-08-18 11:13:23 3948 [Note] Server socket created on IP: '::'.
2016-08-18 11:13:23 3948 [Note] Event Scheduler: Loaded 0 events
2016-08-18 11:13:23 3948 [Note] /usr/test/mysql-5.6.32-linux-glibc2.5-x86_64/bin/mysqld: ready for connections.
Version: '5.6.32'  socket: '/data/mysql.sock'  port: 3310  MySQL Community Server (GPL)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist

2016-08-18 11:15:13 3948 [Warning] InnoDB: Cannot open table test/t1 from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

[root@localhost test]# cd /data/test/
[root@localhost test]# ll
total 216
-rw-rw---- 1 mysql mysql  8594 Aug 18 11:06 t1.frm
-rw-rw---- 1 mysql mysql 98304 Aug 18 11:07 t1.ibd
-rw-rw---- 1 mysql mysql  8594 Aug 18 11:06 t2.frm
-rw-rw---- 1 mysql mysql 98304 Aug 18 11:07 t2.ibd
[root@localhost test]# mv * /backup/
[root@localhost test]# ls
[root@localhost test]# ll /backup/
total 216
-rw-rw---- 1 mysql mysql  8594 Aug 18 11:06 t1.frm
-rw-rw---- 1 mysql mysql 98304 Aug 18 11:07 t1.ibd
-rw-rw---- 1 mysql mysql  8594 Aug 18 11:06 t2.frm
-rw-rw---- 1 mysql mysql 98304 Aug 18 11:07 t2.ibd

mysql> show tables;
Empty set (0.00 sec)

mysql> create table t1(id_1 int,hiredate_1 datetime);
ERROR 1146 (42S02): Table 'test.t1' doesn't exist

mysql> drop table t1;
ERROR 1051 (42S02): Unknown table 'test.t1'
mysql> create table t1(id_1 int,hiredate_1 datetime);
Query OK, 0 rows affected (0.07 sec)

mysql> drop table t2;
ERROR 1051 (42S02): Unknown table 'test.t2'
mysql> create table t2(id_1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE t1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE t2 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)

[root@localhost test]# ls
t1.frm  t2.frm

[root@localhost test]# cp /backup/t1.ibd .
[root@localhost test]# chown mysql.mysql t1.ibd 

mysql> ALTER TABLE t1 IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.21 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                 |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t2.cfg', will attempt to import without schema verification |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+---------------------+
| id_1 | hiredate_1          |
+------+---------------------+
|    1 | 2016-08-18 17:45:02 |
|    2 | 2016-08-18 17:45:02 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> flush table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+---------------------+
| id_1 | hiredate_1          |
+------+---------------------+
|    1 | 2016-08-18 17:45:02 |
|    2 | 2016-08-18 17:45:02 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE t1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)

[root@localhost test]# cp /backup/t1.frm .
cp: overwrite `./t1.frm'? y
[root@localhost test]# cp /backup/t1.ibd .
[root@localhost test]# chown mysql.mysql t1.frm 
[root@localhost test]# chown mysql.mysql t1.ibd 

mysql> ALTER TABLE t1 import TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> select * from t1;
+------+---------------------+
| id_1 | hiredate_1          |
+------+---------------------+
|    1 | 2016-08-18 17:45:02 |
|    2 | 2016-08-18 17:45:02 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> flush table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+---------------------+
| id   | hiredate            |
+------+---------------------+
|    1 | 2016-08-18 17:45:02 |
|    2 | 2016-08-18 17:45:02 |
+------+---------------------+
2 rows in set (0.00 sec)

[root@localhost test]# cp /backup/t2.ibd .
[root@localhost test]# ll
total 216
-rw-rw---- 1 mysql mysql  8594 Aug 18 17:55 t1.frm
-rw-r----- 1 mysql mysql 98304 Aug 18 18:00 t1.ibd
-rw-rw---- 1 mysql mysql  8556 Aug 18 17:52 t2.frm
-rw-r----- 1 root  root  98304 Aug 18 18:10 t2.ibd
[root@localhost test]# chown mysql.mysql t2.ibd 

mysql> ALTER TABLE t2 import TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> select * from t2;
+------+
| id_1 |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> flush table t2;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
+------+
| id_1 |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE t2 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.06 sec)

[root@localhost test]# rm -rf t2.frm 
[root@localhost test]# cp /backup/t2.frm .
[root@localhost test]# cp /backup/t2.ibd .
[root@localhost test]# chown mysql.mysql t2.frm 
[root@localhost test]# chown mysql.mysql t2.ibd 

mysql> ALTER TABLE t2 import TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> select * from t2;
+------+
| id_1 |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> flush table t2;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist

mysql> create table t1(id int,hiredate varchar(10));
Query OK, 0 rows affected (0.05 sec)

mysql> ALTER TABLE t1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE t1 import TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> select * from t1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> select table_name from information_schema.tables  where table_schema='mysql' and engine='innodb';
+----------------------+
| table_name           |
+----------------------+
| innodb_index_stats   |
| innodb_table_stats   |
| slave_master_info    |
| slave_relay_log_info |
| slave_worker_info    |