MySQL Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How rename table work ing MySQL 5.1

3410558Jun 16 2017 — edited Oct 27 2017

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

This post has been answered by Alexandre Almeida on Oct 23 2017

Jump to Answer

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?

1 person found this helpful

Alexandre Almeida

Answer

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,

Marked as Answer by 3410558 · Sep 27 2020

1 - 2

Added on Jun 16 2017

#advocate-mysql, #mysql-faqs, #mysql-user-groups

2 comments

441 views

MySQL Database

How rename table work ing MySQL 5.1

Comments

Post Details