MySQL 8 Cookbook

Moving non-partitioned tables between tablespaces

You can move tables as follows:

1. This is how to move tables from one general tablespace to another. Suppose you want to move the employees table from ts2 to ts1:

mysql> ALTER TABLE employees TABLESPACE ts1;Query OK, 0 rows affected (3.83 sec)Records: 0  Duplicates: 0  Warnings: 0shell> sudo ls -lhtr /var/lib/mysql/ts1.ibd -rw-r-----. 1 mysql mysql 32M Oct  8 17:16 /var/lib/mysql/ts1.ibd

2. This is how to move tables to file-per-table. Suppose you want to move the employees table from ts1 to file per table:

mysql> ALTER TABLE employees TABLESPACE innodb_file_per_table;Query OK, 0 rows affected (4.05 sec)Records: 0  Duplicates: 0  Warnings: 0shell> sudo ls -lhtr /var/lib/mysql/employees/employees.ibd ...