Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
How rename table work ing MySQL 5.1

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 onInnoDB
tables that are supported by theInnoDB
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 TOnew_tbl_name
. When run without other options, MySQL renames files that correspond to the tabletbl_name
without making a copy. (You can also use theRENAME 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.
Best 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,
Answers
-
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?
-
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,