How move MySql table to another disk?

Question

According to this it is impossible to use alter table for moving tables. So if I understand correctlly - to move table it is neccessary to do:

1. Create new table
2. Copy all row from old one
3. drop old table
4. Rename new table created.

First of all - it these steps are correct? Second - is there more beautiful way to this operation?

Answer 1

1. SHOW CREATE TABLE real; -- to avoid typos
2. edit it change name to new and to add DATA_DIRECTORY= ... or whatever
3. perform the CREATE TABLE new ...
4. INSERT INTO new SELECT * FROM real; -- single, fast, step. But not instantaneous
5. RENAME TABLE real TO old, new TO real;
6. DROP TABLE old;

Step 5 is the main added "elegance". It is atomic and instantaneous; it won't interrupt the live system. Also, it give you a chance to test the new real before DROP TABLE old.

If this is InnoDB, you must have innodb_file_per_table ON before step 3. Was it ON when the old real was created? If not, ibdata1 will not free up any space on the old disk. Resolving this takes several more steps.

(My steps should work fine for a MyISAM table.)

Answer 2

I have dealt with this problem myself, and eventually found a more elegant solution than 'create new - copy - switch': detaching, moving and re-importing the underlying tablespace files. This is much more efficient on large and/or heavily indexed tables as MySQL does not have to redo work it has already done.

---

In short, it comes down to the following steps:

FLUSH TABLES `table_name` FOR EXPORT;

While keeping the connection open, move the tablespace files in a shell:

$ mv /var/lib/mysql/database_name/table_name.{ibd,cfg} ~

Now back in MySQL release the lock, drop the table, re-create it with the correct DATA DIRECTORY and discard its tablespace:

UNLOCK TABLES;
SHOW CREATE TABLE `table_name`;
DROP TABLE `table_name`;
CREATE TABLE `table_name` /* ... */ DATA DIRECTORY='/path/to/desired/location';
ALTER TABLE `table_name` DISCARD TABLESPACE;

Now copy the moved tablespace files to the desired location:

$ cp -a ~/table_name.{ibd,cfg} /path/to/desired/location

And import them:

ALTER TABLE `table_name` IMPORT TABLESPACE;

---

More background and motivation for why 'create new - copy - switch' is inefficient can be found in a blogpost I wrote on this topic: https://www.moxio.com/blog/28/moving-individual-mysql-tables-on-disk.