A pure MySQL solution is not possible. In fact, it can be downright dangerous.
Even when you create a table that uses DATA DIRECTORY, mysqld uses symlinks.
You will have to create a symlink to another folder
First thing you need to do is to prep the database with the symlink
For this example
- datadir is
/var/lib/mysql
- outside folder is mounted on
/data/newfolder
Step 01 : Create the symlink to the other disk location from datadir
ln -s /var/lib/mysql/outerdb /data/newfolder
Step 02 : Make sure the symlink and the new folder is owned by mysql
chown -R mysql:mysql /var/lib/mysql/outerdb /data/newfolder
chown -R mysql:mysql /data/newfolder
Step 03 : Login to mysql and make sure outerdb appears as a database
mysql> SHOW DATABASES LIKE 'outerdb';
NOTE: Steps 01-03 should work because mysqld (MySQL 5.7 and prior versions) will trigger information_schema to be updated with the latest databases and files that appear in the datadir. This is not supported in MySQL 8.0.
Step 04 : Create table in that database and see if it appears
USE outerdb
CREATE TABLE testtable
(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(10),
PRIMARY KEY (id)
) ENGINE=InnoDB;
INSERT INTO testtable (name)
VALUES ('MySQL'),('Oracle'),('PostgreSQL'),('MSSQL'),('MongoDB');
SELECT * FROM testtable;
Step 05 : Go to OS and see if the table appears
cd /var/lib/mysql/outerdb
ls -l testtable.*
If everything works, you can then copy the data will SQL but do not do the following
ALTER TABLE ... RENAME ...
RENAME TABLE ... TO ...
These can fail at times.
A safer approach would be to copy the data row by row.
For this example, say the table (call it mytable) is in database mydb and you wish to move it to outerdb (please choose a small table to test this)
Step 06 : Create the table in outerdb with the same structure from mydb
CREATE TABLE outerdb.mytable LIKE mydb.mytable;
Step 07 : Copy the table data in outerdb over to mydb
INSERT INTO outerdb.mytable SELECT * FROM mydb.mytable;
Step 08 : Make sure all the data is there
SELECT COUNT(1) FROM outerdb.mytable;
SELECT COUNT(1) FROM mydb.mytable;
If all the table data makes it successfully, you can now use mysqldump to push all the tables over from mydb to outerdb.
Step 09 : mysqldump from mydb to outerdb
MYSQLDUMP_OPTIONS="--add-drop-table --single-transaction"
mysqldump -uroot -p${pw} ${MYSQLDUMP_OPTIONS} mydb | mysql -uroot -p${pw} -Douterdb
Why use mysqldump at this point ??? It will handle making triggers, stored procedures, foreign keys constraints, and all other database related objects.
You can then go back and run Step 05 to verify all the table data appears
cd /var/lib/mysql/outerdb
ls -l testtable.*
