How rename table work ing MySQL 5.1

3410558

Does renaming a table in MySQL 5.1 create a temporay table or does it just rename the file .

I want to rename a huge table in Prod , the behaviour of the rename defines how long it will take .

ALTER TABLE operations that are performed in place do not require creating a temporary copy of the original table. These operations include:

• ALTER TABLE operations on InnoDB tables that are supported by the InnoDB online DDL feature. For an overview of supported operations, see Section 14.13.1, “Online DDL Overview”. For information about performance and concurrency of online DDL operations, see Section 14.13.2, “Online DDL Performance, Concurrency, and Space Requirements”.
• ALTER TABLE <em class="replaceable" style="font-size: 13.5432px; background: transparent;"><code style="padding: 1px; font-size: 12.866px; background-position: initial; background-color: inherit; color: inherit; font-weight: bold; font-family: 'Courier New', Courier, fixed, monospace;">tbl_name RENAME TO new_tbl_name. When run without other options, MySQL renames files that correspond to the table tbl_name without making a copy. (You can also use the RENAME TABLE statement to rename tables. See Section 13.1.33, “RENAME TABLE Syntax”.) Privileges granted specifically for the renamed table are not migrated to the new name. They must be changed manually.

Alexandre Almeida

Hi there,

Actually, it depends on the table's storage engine. Renaming a MyISAM table is quite simple, the files (.frm, .MYI and .MYD) are simple renamed. Now, if your tables are innodb, it's a little bit more complex because data dictionary must be changed also. Starting MySQL 5.1 we got the variable innodb_file_per_table. If this variable is set as 0 your data resides inside shared table space (aka ibdata) and renaming table will cause no pages move. If innodb_file_per_table is set to 1, the your_file_table.ibd and your_file_table.frm will be renamed.

Anyways, renaming table won't copy table and won't cause any panic I/O.

Cheers,

Gaz in Oz

Why are you using such an old version of mysql?

Why are you reading 5.7 documentation that may not be relevant to your old 5.1 db?

Are you using

. MyISAM

. InnoDB

Why don't you try it for yourself on a small table first and see how long it takes, what actions are carried out to rename it?

Alexandre Almeida

Hi there,

Actually, it depends on the table's storage engine. Renaming a MyISAM table is quite simple, the files (.frm, .MYI and .MYD) are simple renamed. Now, if your tables are innodb, it's a little bit more complex because data dictionary must be changed also. Starting MySQL 5.1 we got the variable innodb_file_per_table. If this variable is set as 0 your data resides inside shared table space (aka ibdata) and renaming table will cause no pages move. If innodb_file_per_table is set to 1, the your_file_table.ibd and your_file_table.frm will be renamed.

Anyways, renaming table won't copy table and won't cause any panic I/O.

Cheers,