Posted by Apenas no MySQL 5.7 o Engine InnoDB passou a permitir a criação de diversas TABLESPACEs.
Mas, conforme lembrado por meu amigo Reinaldo Cantídio, antes da 5.7 era possível criar tabelas em diretório distintos sem o uso de links simbólicos, com a opção DATA DIRECTORY do comando CREATE TABLE.
[root@nerv09 ~]# mkdir /mysql [root@nerv09 ~]# chown -R mysql:mysql /mysql/ [root@nerv09 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.36 MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.02 sec) mysql> use test; Database changed mysql> create table t1 (c1 int) DATA DIRECTORY='/mysql/'; Query OK, 0 rows affected (0.45 sec) mysql> create table t2 (c1 int) DATA DIRECTORY='/mysql/'; Query OK, 0 rows affected (0.29 sec) mysql> exit Bye [root@nerv09 ~]# ls -lh /mysql/ total 4.0K drwxrwx---. 2 mysql mysql 4.0K Jul 17 14:45 test [root@nerv09 ~]# ls -lh /mysql/test/ total 192K -rw-rw----. 1 mysql mysql 96K Jul 17 14:45 t1.ibd -rw-rw----. 1 mysql mysql 96K Jul 17 14:45 t2.ibd [root@nerv09 ~]#
Entretanto, esta opção das tabelas não permite alteração, como é no caso das TABLESPACEs. Veja o teste a seguir.
[root@nerv09 ~]# mkdir /data [root@nerv09 ~]# chown -R mysql:mysql /data/ [root@nerv09 ~]# mysql -vvv Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.6.36 MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Reading history-file /root/.mysql_history Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 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> alter table t1 DATA DIRECTORY='/data/'; -------------- alter table t1 DATA DIRECTORY='/data/' -------------- Query OK, 0 rows affected, 1 warning (2.87 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> show warnings; -------------- show warnings -------------- +---------+------+---------------------------------+ | Level | Code | Message | +---------+------+---------------------------------+ | Warning | 1618 | DATA DIRECTORY option ignored | +---------+------+---------------------------------+ 1 row in set (0.07 sec) mysql> show create table t1; -------------- show create table t1 -------------- +-------+-----------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='/mysql/' | +-------+-----------------------------------------------------------------------------------------------------------------+ 1 row in set (0.17 sec) mysql>
Abaixo está a parte da documentação que explicita esta característica.
However, ALTER TABLE ignores DATA DIRECTORY and INDEX DIRECTORY when given as table options. ALTER TABLE permits them only as partitioning options, and, as of MySQL 5.7.17, requires that you have the FILE privilege.
Fonte: https://dev.mysql.com/doc/refman/5.7/en/alter-table.html
However, ALTER TABLE ignores DATA DIRECTORY and INDEX DIRECTORY when given as table options. ALTER TABLE permits them only as partitioning options, and, as of MySQL 5.6.35, requires that you have the FILE privilege.
Fonte: https://dev.mysql.com/doc/refman/5.6/en/alter-table.html