I have a busy InnoDB table that expands and contracts regularly in size on a daily basis based on it's load. Typically, we'd see the table grow to 80GB in size (50/50 data and indexes) and then shrink down to around 30GB. Unfortunately, InnoDB doesn't like freeing up space it's not using anymore which doesn't bode well for our 120GB SSD.

Typically, an optimize (or recreate for InnoDB tables) would give us a bit more breathing space. But sometimes there isn't enough room for the temporary table to be created.

Despite setting a different location for the temporary table variable in the my.cnf, the InnoDB database still insists in creating temporary tables in it's data directory, within the database folder. Is there any way I can point MySQL to a different drive when creating temporary InnoDB tables?

I'm sure it would do more damage than good, but it was suggested here that we could potentially create a symlink for the database on a larger drive, then symlink the tables to the SSD. That way, all standard operations will run on the much faster SSD and routine maintenance tasks will run on the larger, slower drive.

EDIT: I should clarify. innodb_file_per_table is set and so is tmpdir (to the larger drive), but tmpdir seems to do nothing for me.

  • Are you using tmpdir and innodb_file_per_table ??? Commented Jan 29, 2013 at 22:39
  • Apologies, I didn't make that clear. tmpdir and innodb_file_per_table are set. tmpdir seemed to do nothing for me.
    – MrNorm
    Commented Jan 29, 2013 at 22:41

1 Answer 1


Symlinking InnoDB is definitely not a good idea for regular maintenance of InnoDB.

I wrote posts on why not to do this:

However, if this strictly for the purpose of shrinking a large table, I guess you can symlink the whole data directory where the temp table is to go, but you will have to personally script the shrinkage.

For example

  • Suppose the table to shrink is mydata.mytable
  • datsdir is /var/lib/mysql

Your procedure would be

STEP 01: You should create a database called mydatatemp. Do not put any data in it just yet.

STEP 02: Move the /var/lib/mysql/mydatatemp over to another disk.

STEP 03: Create a Symlink /var/lib/mysql/mydatatemp over to the folder on the other disk

STEP 04: chown -R mysql:mysql against the external folder and against the symlink.

STEP 05: Now perform the data shrinkage as follows:

CREATE TABLE mydatatemp.mytable LIKE mydata.mytable;
INSERT INTO mydatatemp.mytable SELECT * FROM mydata.mytable;
DROP TABLE mydata.mytable;
ALTER TABLE mydatatemp.mytable RENAME mydata.mytable;

To recap, do the following (assuming /tmpdata is the external disk)

STEPS 01-04

chown mysql:mysql /tmpdata
ln -s /tmpdata /var/lib/mysql/mydatatemp
chown -R mysql:mysql /var/lib/mysql/mydatatemp

then run STEP 05


This would be rather high risk, but it should work. Please test this with an empty table first like this:

USE mydatatemp
CREATE TABLE mrnorm (id int not null, name varchar(20) primary key (id));
INSERT INTO mrnorm (name) VALUES ('one'),('two'),('three');
SELECT * FROM mrnorm;

If these SQL command worked, go to OS and do this:

cd /tmpdata
ls -l

You should see two files:

  • mrnorm.frm
  • mrnorm.ibd

Also, test if mrnorm can be moved

ALTER TABLE mydatatemp.mrnorm RENAME helloworld.mrnorm;

Go to the OS and run

cd /tmpdata
ls -l

The table should be gone. Now, do this:

cd /var/lib/mysql/helloworld
ls -l

The table should be there


Keep in mind that the rename of the table would essentially be controlled copy of the table from one disk to another disk with a proper adjusting of the data dictionary section of the system tablespace in ibdata1. This could take some time.

  • Oh dear! The last step of those instructions generates a -1 code in and the following in the logs: Error number 18 means 'Invalid cross-device link' .. Any ideas?
    – MrNorm
    Commented Feb 1, 2013 at 19:19
  • ERROR 1025 (HY000): Error on rename of './mydatatemp/table' to './mydata/table' (errno: 168 - Unknown (generic) error from engine)
    – OrangeDog
    Commented Dec 13, 2018 at 16:24

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.