There is one new feature in MySQL 5.6 that didn’t get the attention it deserved (at least from me ) : “DATA DIRECTORY” for InnoDB tables.
This is implemented since MySQL 5.6.6 and can be used only at the creation of the table. It’s not possible to change the DATA DIRECTORY with an ALTER for a normal table (but it’s in some case with partitioned ones as you will see below). If you do so, the option will be just ignored:
mysql> CREATE TABLE `sales_figures` (
-> `region_id` int(11) DEFAULT NULL,
-> `sales_date` date DEFAULT NULL,
-> `amount` int(11) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
-> DATA DIRECTORY = '/tb1/';
Query OK, 0 rows affected (0.11 sec)
mysql> alter table sales_figures engine=innodb data directory='/tb2/';
Query OK, 0 rows affected, 1 warning (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> show warnings; …[Read more]