How to split MySQL/MariaDB datadir to multiple mount points

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.