MySQL 8 Cookbook

Adding tables to a general tablespace

You can add a table to a tablespace while creating it, or you can run the ALTER command to move a table from one tablespace to another:

mysql> CREATE TABLE employees.table_gen_ts1 (id INT PRIMARY KEY) TABLESPACE ts1;Query OK, 0 rows affected (0.01 sec)

Suppose you want to move the employees table to TABLESPACE ts2:

mysql> USE employees;Database changedmysql> ALTER TABLE employees TABLESPACE ts2;Query OK, 0 rows affected (3.93 sec)Records: 0  Duplicates: 0  Warnings: 0

You can notice the increase in the ts2.ibd file:

shell> sudo ls -lhtr /var/lib/mysql_general_ts/ts2.ibd-rw-r-----. 1 mysql mysql 32M Oct  8 17:07 /var/lib/mysql_general_ts/ts2.ibd