How to rename a table in MySQL or MariaDB

Renaming a table keeps schemas readable, supports clearer naming conventions, and enables safe cutovers when swapping in a rebuilt table or archiving old data.

In MySQL and MariaDB, table renames are performed with RENAME TABLE, which changes the table name in the database metadata without copying table data. The same statement can rename multiple tables at once, which is useful for atomic swaps during deployments.

Renames still require metadata locks, so active transactions can delay completion and cause application-side stalls when the rename waits. Case sensitivity and filesystem behavior can also affect renames that only change letter case, and any stored SQL text (views, routines, app queries) may need updates if it references the old name.

1. Open a MySQL or MariaDB client session connected to the target server.

   $ mysql -u dbadmin -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 94
   Server version: 8.0.36 MySQL Community Server - GPL
   ##### snipped #####
   mysql>

2. Select the database that owns the table.

   mysql> USE appdb;
   Database changed

3. Confirm the current table name exactly as stored.

   mysql> SHOW FULL TABLES LIKE 'orders';
   +----------------+------------+
   | Tables_in_appdb | Table_type |
   +----------------+------------+
   | orders          | BASE TABLE |
   +----------------+------------+
   1 row in set (0.00 sec)

4. Confirm the new table name is not already in use.

   mysql> SHOW FULL TABLES LIKE 'orders_archive';
   Empty set (0.00 sec)

5. Rename the table with RENAME TABLE.

   mysql> RENAME TABLE orders TO orders_archive;
   Query OK, 0 rows affected (0.01 sec)

   The statement waits for a metadata lock; long-running queries or transactions can make the rename block and hold up DDL in busy schemas.

   Atomic swap for cutovers: RENAME TABLE orders TO orders_old, orders_new TO orders; changes both names in one statement.

   Case-only renames can be tricky on case-insensitive filesystems or when lower_case_table_names is enabled; use an intermediate name (tmp) if a case-only rename does not take effect.

6. Verify the renamed table appears in the schema.

   mysql> SHOW TABLES LIKE 'orders_archive';
   +----------------------------------+
   | Tables_in_appdb (orders_archive) |
   +----------------------------------+
   | orders_archive                   |
   +----------------------------------+
   1 row in set (0.00 sec)

7. Confirm the old table name no longer resolves.

   mysql> DESCRIBE orders;
   ERROR 1146 (42S02): Table 'appdb.orders' doesn't exist

8. Update any stored SQL that still references the old name.

   Views, procedures, functions, and application queries are text-based and are not automatically rewritten by a table rename.

9. Quote identifiers when a name contains special characters or collides with reserved words.

   Example: RENAME TABLE `order` TO `order_archive`;

10. Move a table to another database by renaming with fully qualified names.

    mysql> RENAME TABLE appdb.orders_archive TO archive_db.orders_archive;
    Query OK, 0 rows affected (0.01 sec)

    Cross-database renames remain metadata operations, but privileges and table-level grants may need review when access is scoped to a specific database or table name.