MySQL Table Rename Runbook: Dependency Inventory, Safe Cutover, and Rollback

A table rename looks like one line of SQL, but it instantly changes the object name shared by applications, views, jobs, permissions, and monitoring. Production safety does not mean merely making the statement succeed. It means using evidence to show that every consumer can switch in the same change window and that an explicit recovery path remains available if anything goes wrong.

This guide targets Oracle MySQL 8.4 LTS and supported MySQL 8.x deployments. MariaDB, Aurora MySQL, Cloud SQL, Azure Database for MySQL, HeatWave, NDB Cluster, and other managed or compatible products can restrict DDL, backups, replication, cross-schema moves, or privileges. Consult the exact product and version documentation first. Every name and SQL statement below is hypothetical and must not be run unchanged against a real database.

Table of Contents

Scope and non-negotiable gates

Proceed to change design only when all of these are true:

the asset owner and DBA have authorized the exact server, schema, old name, new name, and maintenance window in writing;
the distribution, exact version, storage engine, replication topology, managed-service restrictions, and lower_case_table_names value are known;
the dependency inventory covers applications, configuration, ORM, migrations, ETL, reports, backups, monitoring, and database objects;
a fresh consistent backup or service snapshot, a verified restore path, a recovery-point identifier, and a named recovery owner exist;
application cutover and rollback releases are ready, writes can be paused or drained, and an observable maintenance window is available;
the DDL session uses an audited DBA channel, with no password, token, or private DSN in command-line arguments, history, logs, or these examples.

Stop if any condition is missing. A rename is not a temporary fix and should never be attempted on a “change first, inspect later” basis.

`RENAME TABLE` versus `ALTER TABLE ... RENAME`

Statement	Appropriate scope	Critical boundary
`RENAME TABLE`	One or more normal tables; clearer when several names must change together	Does not apply to `TEMPORARY` tables; multi-object operations proceed left to right; any error fails the whole statement, but atomic DDL still depends on engine and version
`ALTER TABLE ... RENAME TO`	One normal table; also the documented route for renaming a session temporary table	Handles one table per statement; remains DDL with an implicit commit and metadata lock; do not execute it in addition to the first statement

Neither is an ordinary transaction operation that ROLLBACK can undo. MySQL documents that DDL implicitly ends an active transaction in the current session before execution and usually commits after execution as well. MySQL 8 atomic DDL combines data-dictionary changes, operations by a supporting storage engine, and the binary-log write into one atomic operation, but it is not transactional DDL and cannot roll back application configuration, external jobs, or a mistaken business decision.

In MySQL 8.4, InnoDB supports atomic DDL. Do not extend that guarantee to non-InnoDB engines, older versions, forks, or managed-service implementations. Even though a rename normally does not copy table contents, waiting for an exclusive metadata lock can still cause a long blockage.

Record version, engine, objects, and case mode first

Have a read-only audit account run these queries on the target connection and attach the results to the change record. In the examples, app_live.customer_order_legacy is the old name and app_live.customer_order is the new name.

SELECT VERSION() AS server_version,
       @@version_comment AS distribution,
       @@lower_case_table_names AS lower_case_table_names;

SELECT table_schema, table_name, table_type, engine
FROM information_schema.tables
WHERE table_schema IN ('app_live', 'app_archive')
  AND table_name IN ('customer_order_legacy', 'customer_order')
ORDER BY table_schema, table_name;

The evidence must show the old object present, the new object absent, and the planned object type and engine. Stop rather than infer if both names exist, the target is a view, the object is temporary, or query privileges are insufficient.

MySQL schema and table-name case behavior depends on both the operating-system filesystem and the initialization-time lower_case_table_names value. Names that differ only by case can be separate objects on one server and conflicts on another. A different mode on the source, replica, restore environment, or migration target can change the result of a case-only rename. A DBA must design and validate such a change in an isolated environment with matching configuration.

Privileges, commits, and metadata locks

The official RENAME TABLE documentation requires ALTER and DROP on the old object, plus CREATE and INSERT on the new object. The ALTER TABLE documentation also lists ALTER on the new object for a rename. Check effective privileges using the exact change account and enabled roles. If privileges are missing, the privilege owner must address that gap; do not grant broad global privileges temporarily or use database root to bypass the workflow.

MySQL holds metadata locks on objects used by active transactions. A rename needs an exclusive metadata lock, so an idle but uncommitted long transaction can make DDL wait. Once an exclusive request queues, later access can queue behind it and expand the incident. A small table does not imply a short lock wait.

An approved bounded lock_wait_timeout for the change session can make the operation fail instead of waiting indefinitely. After a timeout, diagnose and reschedule. Do not auto-retry, and do not terminate an unknown session without its owner or the DBA.

Build the complete dependency inventory

Dependency class	Evidence to preserve	Cutover owner
Application code, configuration, ORM mappings, and migrations	Exact-string and generated-SQL search results, release version, and connection-pool refresh plan	Application owner
Views, triggers, foreign keys, procedures, functions, and events	`SHOW CREATE`, `INFORMATION_SCHEMA` results, and object owner	DBA and database-object owner
Dynamic SQL, ETL, queue consumers, scheduled jobs, BI, and exports	Runtime catalog, scheduler definitions, lineage, and pause or switch plan	Data and operations owner
Replication, CDC, backups, restore, archive, and audit	Topology, filters, replication lag, recovery point, restore rehearsal, and name dependencies	DBA and platform owner
Table- or column-specific grants	Pre-change grant inventory and least-privilege plan for the new name	Security and privilege owner
Monitoring, alerts, capacity, SLOs, and runbooks	Query templates, dashboards, alert rules, and old/new observation window	SRE or on-call owner

String search is only a starting point. Concatenated dynamic SQL, ORM-generated names, case-sensitive references, external SaaS connectors, and old backup scripts may evade static search. Every class needs an accountable sign-off; “not found” does not prove “no dependency.”

Read-only database dependency inventory

Capture the canonical definition and indexes first, not just a GUI column list. Output can contain definers, comments, or business identifiers. Sanitize it before wider sharing, while retaining an original controlled copy in the change evidence.

SHOW CREATE TABLE app_live.customer_order_legacy;
SHOW INDEX FROM customer_order_legacy FROM app_live;

Inventory both foreign keys defined on the old table and foreign keys that reference it. During a rename, MySQL updates foreign-key references to the table. Internally generated constraint names, and user-defined names beginning with the old table name plus _ibfk_, can also change. A name conflict makes the statement fail, so preserve and review constraint names before cutover.

SELECT constraint_schema, constraint_name,
       table_schema, table_name,
       referenced_table_schema, referenced_table_name,
       ordinal_position
FROM information_schema.key_column_usage
WHERE (table_schema = 'app_live'
       AND table_name = 'customer_order_legacy')
   OR (referenced_table_schema = 'app_live'
       AND referenced_table_name = 'customer_order_legacy')
ORDER BY constraint_schema, constraint_name, ordinal_position;

Triggers remain associated with a table during a same-schema rename; a table with triggers cannot be moved to another schema by this method. A view reference does not become a safe application-compatibility layer merely because the underlying table was renamed. MySQL allows some table changes to invalidate a view without an advance warning, so list views and inspect their definitions before cutover.

SELECT trigger_schema, trigger_name,
       event_manipulation, action_timing
FROM information_schema.triggers
WHERE event_object_schema = 'app_live'
  AND event_object_table = 'customer_order_legacy'
ORDER BY trigger_schema, trigger_name;

SELECT view_schema, view_name
FROM information_schema.view_table_usage
WHERE table_schema = 'app_live'
  AND table_name = 'customer_order_legacy'
ORDER BY view_schema, view_name;

The following is only a candidate screen for stored programs and events. Definition visibility is privilege-dependent, dynamic SQL can concatenate a name, and false positives are possible. Continue with controlled SHOW CREATE PROCEDURE, SHOW CREATE FUNCTION, or SHOW CREATE EVENT and manual review for every candidate.

SELECT routine_schema, routine_name, routine_type
FROM information_schema.routines
WHERE LOCATE('customer_order_legacy',
             COALESCE(routine_definition, '')) > 0
ORDER BY routine_schema, routine_name;

SELECT event_schema, event_name, status
FROM information_schema.events
WHERE LOCATE('customer_order_legacy',
             COALESCE(event_definition, '')) > 0
ORDER BY event_schema, event_name;

Grants specific to the table name do not automatically migrate to the new name. Preserve table and column grants, role mappings, and application identities. Re-create only the approved least privileges for the new object; do not copy obsolete historical grants.

SELECT CURRENT_USER() AS authenticated_account,
       CURRENT_ROLE() AS active_roles;

SHOW GRANTS FOR CURRENT_USER;

SELECT grantee, privilege_type, is_grantable
FROM information_schema.table_privileges
WHERE table_schema = 'app_live'
  AND table_name = 'customer_order_legacy'
ORDER BY grantee, privilege_type;

SELECT grantee, column_name, privilege_type, is_grantable
FROM information_schema.column_privileges
WHERE table_schema = 'app_live'
  AND table_name = 'customer_order_legacy'
ORDER BY grantee, column_name, privilege_type;

These results are still not complete proof of effective privilege. Schema-level or global grants, active or mandatory roles, role inheritance, and partial revokes can change the outcome. The DBA must calculate execution-time privileges for both source and destination names. Never replay SHOW GRANTS output without review.

Metadata-lock and long-transaction preflight

Check once before the maintenance window and again immediately before DDL. performance_schema.metadata_locks is read-only, but managed privileges or instrumentation settings can hide it. If visibility is unavailable, use a provider-approved equivalent or escalate to the DBA. An empty result without adequate visibility is not proof that no lock exists.

SELECT object_schema, object_name,
       lock_type, lock_duration, lock_status,
       owner_thread_id
FROM performance_schema.metadata_locks
WHERE object_type = 'TABLE'
  AND object_schema = 'app_live'
  AND object_name = 'customer_order_legacy'
ORDER BY lock_status, owner_thread_id;

SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds,
       trx_tables_locked, trx_rows_modified,
       trx_mysql_thread_id
FROM information_schema.innodb_trx
ORDER BY trx_started;

Put only the minimum sanitized thread IDs, durations, and lock states in a ticket. Raw process lists and SQL text can contain personal data or secrets and should not be pasted into public logs. If a long transaction, unknown lock owner, abnormal replication-applier thread, or queued DDL appears, stop. The session owner or DBA decides whether to commit, roll back, cancel, or reschedule.

Cross-schema, trigger, view, engine, and filesystem boundaries

MySQL syntax permits renaming a normal table into another schema, but this is not a universal migration procedure. The following only illustrates syntax and is not part of the default cutover:

RENAME TABLE app_live.customer_order_legacy
TO app_archive.customer_order_legacy;

A cross-schema rename fails if the table has triggers, and RENAME TABLE cannot move a view across schemas. If source and destination databases reside on different filesystems, success depends on the platform’s underlying move calls. Encryption defaults, tablespaces, storage engines, managed-service permissions, and backup policy can also block or change the operation. Use a version- and provider-specific migration runbook for these cases instead of transplanting the same-schema steps.

RENAME TABLE does not work on TEMPORARY tables. If a temporary table genuinely must be renamed in the same session that created it, the documented route is:

ALTER TABLE app_work.customer_order_session
RENAME TO app_work.customer_order_session_v2;

Temporary-table visibility, lifetime, and implicit-commit boundaries differ from persistent tables. Confirm that it really is the current session’s temporary table, and do not use this example on a persistent production object.

A case-only rename is not an ordinary rename

Before changing case alone, inspect @@lower_case_table_names, the underlying filesystem, every replica, and every restore target. Do not change a server initialization variable to “fix” a table name; MySQL 8 prohibits changing this setting after initialization.

Once the exact environment has been validated and the destination is absent, an unambiguous intermediate name may be required. This is only candidate syntax for an isolated test environment and is not guaranteed for any production topology:

RENAME TABLE app_live.CustomerOrder
TO app_live.customer_order_case_stage,
app_live.customer_order_case_stage
TO app_live.customerorder;

Stop immediately if case comparison makes the source, destination, or intermediate name the same object, or if a replica uses a different setting. Never manipulate data-directory files directly or use an operating-system mv to bypass the MySQL data dictionary.

Maintenance-window and cutover choreography

Freeze the plan: Lock the old name, new name, change ID, owners, start time, maximum lock wait, acceptance criteria, and rollback deadline. Prohibit other schema changes in the window.
Backup gate: Record the consistent backup or managed snapshot, binary-log or service recovery point, and restore-rehearsal evidence. Replication is not a backup, and atomic DDL is not recovery from a logical mistake.
Consumer gate: Deploy a release capable of using the new name without switching traffic yet. Pause or drain writes, ETL, CDC downstreams, reports, backups, and maintenance jobs. Have the connection-pool refresh plan ready.
Health gate: Replication and backup services are healthy, with no unknown long transaction, metadata-lock wait, or platform alert. The old object exists, the new object does not, and the SHOW CREATE, index, foreign-key, trigger, and grant evidence is saved.
Single executor: One authorized operator runs one reviewed DDL statement in an audited session. Do not wrap it in START TRANSACTION, and do not permit unbounded automation retries.
Immediate validation: Check names, canonical definition, indexes, constraints, triggers, least privileges, replication application, and a read-only application canary. Keep writes frozen on any discrepancy.
Controlled traffic switch: Switch applications, ORM, jobs, and monitoring, then refresh pools. Restore reads and writes in stages while observing error rate, latency, lock waits, and replication lag.
Close the window: End the window only after all consumers and replicas pass acceptance and the rollback owner agrees. Do not immediately delete backups, old configuration, or rollback artifacts.

Hypothetical same-schema DDL

This example changes app_live.customer_order_legacy to app_live.customer_order. It is eligible only after every gate above passes, writes are handled according to the plan, and the new name is confirmed free. The 15 seconds is illustrative; the real value must come from the change plan.

SET SESSION lock_wait_timeout = 15;

RENAME TABLE app_live.customer_order_legacy
TO app_live.customer_order;

Do not execute the equivalent ALTER TABLE against the same target afterward. If the runbook explicitly chooses single-table ALTER TABLE, the candidate for review is:

SET SESSION lock_wait_timeout = 15;

ALTER TABLE app_live.customer_order_legacy
RENAME TO app_live.customer_order;

Choose exactly one of the two blocks. After success, failure, or timeout, record the precise error and object state before doing anything else; do not retry immediately. A multi-table swap must put every name in one comprehensively reviewed RENAME TABLE and re-check all engines, locks, dependencies, and destination names. Separate single-table DDL statements are not an atomic transaction.

Validation must not rely on `COUNT(*)`

First prove that the new name exists and the old name does not, then capture the definition again. Do not alert merely because information_schema.tables.table_rows differs from an exact count; it is an estimate for some engines. Do not make an unconditional COUNT(*) the default validation either: it can be expensive and does not prove that indexes, constraints, privileges, consumers, or replication are correct.

SELECT table_schema, table_name, table_type, engine
FROM information_schema.tables
WHERE table_schema = 'app_live'
  AND table_name IN ('customer_order_legacy', 'customer_order')
ORDER BY table_name;

SHOW CREATE TABLE app_live.customer_order;
SHOW INDEX FROM customer_order FROM app_live;

Compare the pre- and post-change canonical definitions, indexes, foreign keys, and trigger evidence. Validate approved read-only application paths, views, jobs, monitoring, backup discovery, and every replica. If the table has a known, non-sensitive indexed key, use a bounded probe. Replace the example columns with confirmed columns for that table and do not scan an unknown large table:

SELECT order_id, created_at
FROM app_live.customer_order
ORDER BY order_id
LIMIT 5;

Run a write canary only when approved test data, idempotency, and cleanup are all defined; this guide deliberately supplies no generic write statement. Sanitize validation output and combine order volume, error rate, latency, replication lag, and established business checks rather than trusting one successful query.

Rollback is another controlled DDL operation

Before rollback, freeze or drain consumers again. Confirm that app_live.customer_order remains the exact object just renamed, the old name is free, no other schema change or parallel restore occurred, and the application configuration can switch back. Only then review this reverse candidate:

SET SESSION lock_wait_timeout = 15;

RENAME TABLE app_live.customer_order
TO app_live.customer_order_legacy;

Rollback also implicitly commits, requires privileges and a metadata lock, and can time out. Repeat the complete validation afterward and restore the old consumers. If the old name was recreated, a structural change occurred after cutover, the table moved across schemas, replication diverged, or object identity is uncertain, do not overwrite or chain more renames. Keep writes frozen and use a DBA-approved recovery point or dedicated recovery plan.

Stop and escalate conditions

Stop DDL and escalate to the DBA, platform owner, or application owner when any of these applies:

the exact distribution, version, engine, managed-service limits, schema, or object type is unknown;
the destination already exists, or old/new case has different meaning on the target or a replica;
a cross-schema move is planned for a table with triggers, view or stored-program dependencies are unresolved, or no owner exists for dynamic SQL;
table or column grants, definers, application identities, or the least-privilege recreation plan are incomplete;
the backup is stale, restore is unverified, or the recovery point or owner is unclear;
an unknown long transaction, metadata-lock wait, replication filter or lag, CDC backlog, backup conflict, or platform alert exists;
writes cannot be paused or coordinated, applications and jobs cannot switch in one window, or no tested rollback release exists;
DDL times out, errors, loses the connection, or returns an uncertain status; query object state first and never retry blindly;
validation finds any mismatch in definitions, indexes, foreign keys, triggers, grants, application canaries, replication, or monitoring.

Official sources

These official links were checked on 2026-09-01. Before execution, switch to the documentation for the exact server version and managed provider.

Historical source archive (not a current runbook)

The complete visible body from source_export is preserved below. The source had no trailing whitespace and no safety, privacy, or tracking content requiring redaction, so nothing was normalized or omitted. It is only a short MySQL 5.0-era syntax note and lacks production dependency, lock, privilege, backup, validation, and rollback boundaries. The outer four-backtick fence keeps the original three-backtick block inert.

在mysql中修改表名的SQL语句


在使用mysql时，经常遇到表名不符合规范或标准，但是表里已经有大量的数据了，如何保留数据，只更改表名呢？
 可以通过建一个相同的表结构的表，把原来的数据导入到新表中，但是这样视乎很麻烦。

能否简单使用一个SQL语句就搞定呢？当然可以，mysql5.0下我们使用这样的SQL语句就可以了。

Alter TABLE table_name RENAME TO new_table_name

例如：

Alter TABLE admin_user RENAME TO a_user