2

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?

2
  • use show create table <table_name>; to copy the structure of the table first and then you can use select * from <table_name> into outfile 'file_name'; to unload all the data from one server/disk and then can use load data local infile 'file_name' into table <table_name> to load the data in table or you can take mysqldump of the table only which include structure and data to do the same..hope it helps Nov 23, 2015 at 14:16
  • To move InnoDB tablespace without rebuild the entire table: dev.mysql.com/doc/refman/8.0/en/…
    – n0099
    Oct 30 at 18:17

2 Answers 2

2
  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.)

3
  • for large tables (one of mine has 1bio rows), INSERT INTO ... SELECT FROM would take forever.
    – mcmillab
    Aug 10, 2020 at 22:09
  • @mcmillab - It takes a long time to read 100GB from a disk and to write a similar amount of data to another disk. The read and write may be done in parallel, but I would need more details to say. My point is that the I/O is possibly the reason it takes "forever", not the choice of INSERT..SELECT. For further discussion, start a new Question; include CREATE TABLE for each.
    – Rick James
    Aug 10, 2020 at 22:16
  • you are right, of course, but I think there's significant extra overhead in an INSERT... SELECT statement - eg the db is preparing a Rollback in case of failure, which can be far more intensive than a similar 100gb read/write by mysqldump.
    – mcmillab
    Aug 11, 2020 at 2:52
1

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.

2
  • I tried that procedure and got "Error Code: 1808. Schema mismatch (Table flags don't match)". I took the ibd and cfg files, put them in the original folder and tried the procedure and it worked (so it is like the table flags contained the original folder and it doesn't let me change it). Any ideas?
    – rufo
    Mar 6, 2019 at 21:53
  • 1
    See the update at the top of the blogpost. I originally performed these steps with MariaDB. They should also work with MySQL, but they don't due to an open bug (bugs.mysql.com/bug.php?id=76142). That bug report also describes a possible workaround. Mar 7, 2019 at 8:37

Your Answer

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

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