1

I was wondering if this is possible with mysql 5.6.

I have a Database with several tables separated by Month (eg TonsOfRows_201411). I don't need access over the whole data so I decided to separate tables instead of partitioning.

The current month is always used for importing/queries (we are talking some 100m rows per month) so I'd like to put the current table on a SSD and then archive it to a slower HDD afterwards (I still need to run queries on it, r/o only)

In postgresql I create a tablespace with different physical location and can change the tablespace later on, so I can move around the table.

I havent found a 'datadir' option in the mysql tablespaces, so not sure if I can solve it via mySQL tablespaces.

I have found a 'datadir' for InnoDB, the problem is alter table doesn't support a change of the datadir (datadir is ignored for alter table), so not sure how to change it.

So I was wondering if its possible to change the datadir of a mysql table after you have created it (with mySQL commands)?

One solution would be symbolic links, but according to the Docu this is not supported for innoDB (nevertheless it is working, but i really prefer a SQL only solution here)

Your help is appreciated, I am sure I am missing something here.

0

1 Answer 1

2
+50

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.*

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.