If you are going to be using InnoDB tables and if you plan to have innodb_file_per_table enabled, then your best option would probably be to use the CREATE TABLE statement’s “DATA DIRECTORY” option, so that you can place a table outside the data directory.
From the MySQL documentation:
DATA DIRECTORY, INDEX DIRECTORY
For InnoDB, the DATA DIRECTORY=’directory’ option allows you to create InnoDB file-per-table tablespaces outside the MySQL data directory. Within the directory that you specify, MySQL creates a subdirectory corresponding to the database name, and within that a .ibd file for the table. The innodb_file_per_table configuration option must be enabled to use the DATA DIRECTORY option with InnoDB. The full directory path must be specified. See Section 14.7.5, “Creating File-Per-Table Tablespaces Outside the Data Directory” for more information.
https://dev.mysql.com/doc/refman/5.7/en/create-table.html
There’s additional information about that here:
https://dev.mysql.com/doc/refman/5.7/en/tablespace-placing.html
If you expect that any single table will exceed 1 TB, then you may need to use partitioning for that table, and you may need to use different “DATA DIRECTORY” clauses for different partitions.
https://dev.mysql.com/doc/refman/5.7/en/partitioning.html
Sometime we are curious to know that how would be the backup and restoration performed on these databases while using CREATE TABLE statements with “DATA DIRECTORY” option?
In case of mysqldump or Percona xtrabackup or MariaDB Backup,
mysqldump backups would include the DATA DIRECTORY option. However, a mysqldump backup would probably be impractical for a database that is several TB large.
Percona XtraBackup seems to do something a little interesting. It places the tablespace file in the same directory as the rest of the backup during the actual backup step, but during the restore step, it places the tablespace file back in the correct directory as specified by the DATA DIRECTORY option. See the output below for an example:
[ec2-user@ip-172-30-0-249 ~]$ mysql -u root Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 11 Server version: 10.1.31-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use db1; 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 MariaDB [db1]> CREATE TABLE tab ( -> str varchar(50) -> ) DATA DIRECTORY='/mariadb_data/'; Query OK, 0 rows affected (0.01 sec) MariaDB [db1]> \q Bye [ec2-user@ip ~]$ sudo ls -l /mariadb_data/ total 0 drwxrwx--- 2 mysql mysql 20 Mar 23 15:04 db1 [ec2-user@ip ~]$ sudo ls -l /mariadb_data/db1/ total 192 -rw-rw---- 1 mysql mysql 98304 Mar 23 15:04 tab.ibd [ec2-user@ip ~]$ cd backups/ [ec2-user@ip backups]$ sudo innobackupex /home/ec2-user/backups/ 180323 15:06:05 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". ... 180323 15:06:14 completed OK! [ec2-user@ip backups]$ sudo ls -l total 4 drwx------ 8 root root 4096 Mar 23 15:06 2018-03-23_15-06-05 [ec2-user@ip backups]$ sudo ls -l 2018-03-23_15-06-05/db1/ | grep "tab\..*" -rw-r----- 1 root root 481 Mar 23 15:06 tab.frm -rw-r----- 1 root root 98304 Mar 23 15:06 tab.ibd -rw-r----- 1 root root 25 Mar 23 15:06 tab.isl [ec2-user@ip backups]$ sudo systemctl stop mariadb [ec2-user@ip backups]$sudo innobackupex --applylog 2018-03-23_15-06-05\ 180323 15:14:17 innobackupex: Starting the apply-log operation IMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!". ... 180323 15:14:21 completed OK! [ec2-user@ip backups]$ sudo rm /mariadb_data/db1/tab.ibd [ec2-user@ip backups]$ sudo rm -fr /var/lib/mysql/* [ec2-user@ip backups]$ sudo innobackupex --copy-back /home/ec2-user/backups/2018-03-23_15-06-05/ 180323 15:16:17 innobackupex: Starting the copy-back operation IMPORTANT: Please check that the copy-back run completes successfully. At the end of a successful copy-back run innobackupex prints "completed OK!". ... 180323 15:16:25 completed OK! [ec2-user@ip backups]$ sudo ls -l /mariadb_data/db1/ total 96 -rw-r----- 1 root root 98304 Mar 23 15:16 tab.ibd
MariaDB Backup based on Percona Xtrabackup so almost same behavior will be seen while using it.
So xtrabackup restores the tablespace in the same place as all the others, and then moves it in proper folder during copy back? Moving back a large tablespace to a different filesystem during a restore will take ages… this issue alone would be a show stopper for me.