Will renaming a MySQL table to another database result in a full table copy operation on disk?

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply