3

I understand that renaming a table within the same database will not generally result in a table copy... It's not clear to me if this holds true for renaming across databases.

For reference, I want to move/rename a ~1TB table to a different database, and I don't want to initiate a huge copy operation.

Will renaming a MySQL table to another database result in a full table copy operation on disk? Please provide an explanation or references if possible.

Dan
  • 235
  • 2
  • 11

1 Answers1

3

This depends entirely on the disk volume of the source database and the target database.

Given the mysqld instance uses /var/lib/mysql as datadir and you run

ALTER TABLE mydb1.mytb RENAME mydb2.mytb;

OR

RENAME TABLE mydb1.mytb TO mydb2.mytb;

Here is what happens under the hood

mv /var/lib/mysql/mydb1/mytb.frm /var/lib/mysql/mydb2 (If not using MySQL 8.0)
mv /var/lib/mysql/mydb1/mytb.ibd /var/lib/mysql/mydb2

If mydb1 and mydb2 are on the same disk volume, it would change the Linux i-nodes. Of course, the information_schema database gets updated in RAM. That's it. Metadata locking would occur during the move.

If the table files are symlinked, the symlinks are moved, nothing more.

Now if you symlinked the database mydb2 to be on a different disk volume, LOOK OUT !!! It has to initiate a table copy at the OS level. This will not be the case for MySQL 8.0 since metadata is better managed than previous MySQL versions.

See MySQL Docs on RENAME TABLE for more info.

SUGGESTION

Test renaming a 10G table in a Dev Server and see what occurs.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536