When I clone a mysql database, with data_directory set on individual tables, where do the cloned tables get stored as files?

So I have a MySQL database with innodb_file_per_table true and the majority of the database in one SSD, and one huge table on a separate drive.

The majority of the files are stored in /var/lib/mysql/database1/ but by setting DATA_DIRECTORY on a table (set to /home/that_table), such that that table’s files are stored instead in /home/that_table/database1

If I clone database1 to database2 with something like:

mysqldump database1 | mysql database2

where would the “that_table” in the cloned database be stored?

My hunch is that it would match the data_directory directive and store it to /home/that_table/database2…

But I’m not sure.

Answer

Yes, it automatically creates a new subdirectory for each schema under your custom data directory.

Here’s a demo:

mysql> create table test.ddtest (i int) data directory='/tmp/tests';
mysql> create database test2;

$ mysqldump test | mysql test2

$ sudo ls /tmp/tests
test    test2