MySQL: Tabelas em diretórios diferentes

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

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.