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

I wrote posts on why not to do this:

• Aug 07, 2011 : MySQL InnoDB data file directly on a disk slice (fixed-size)?
• Mar 12, 2012 : How to set a specific directory location for individual MySQL database?

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

CAVEAT

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

CREATE DATABASE helloworld;
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

END OF TEST

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.

OMG !!! Look at the output of df -h

Size: 20G Used: 19G Available 0 Use Percent: 100%

YOU HAVE NO DISK SPACE !!!

Get more diskspace freed up or have linode increase diskspace.

According to MySQL 5.0 Certification Study Guide

Page 408,409 Section 29.2 Bulletpoint 11 says:

If you run out of disk space while adding rows to a MyISAM table, no error occurs. The server suspends the operation until space becomes available, and then completes the operation.

In reality, mysqld is not dead. It is waiting for free diskspace to continue whatever it normally does. Even during startup, if mysqld makes a temp table, it is a MyISAM table. No space left on the disk? No problem. MyISAM just patiently waits for you to make free space.

If you have an error file like /var/log/mysql/log, clear its contents like this:

echo -n > /var/log/mysqld.log

If the error file in /var/lib/mysql/my-machine.err then do this:

echo -n > /var/lib/mysql/my-machine.err

Give it a Try !!!