How to Safely Rename a MySQL Database

MySQL once provided a rename database db_old to db_new command, but it was removed because it could cause large transactions or fail with MyISAM tables, so renaming a database now requires manual procedures.

Method 1 – Export and import with mysqldump

Export the old database (including tables, views, triggers, events, stored procedures, and functions) and then import it into a new database. Example commands:

root@debian-ytt1:/home/ytt# time mysqldump --login-path=root_ytt --set-gtid-purged=off \
    --single-transaction --routines --events yttdb_old > /tmp/yttdb_old.sql

root@debian-ytt1:/home/ytt# time mysql --login-path=root_ytt -D yttdb_new < /tmp/yttdb_old.sql

On a test database with 2002 tables (≈826 MB), the export took about 2 minutes 24 seconds and the import took about 12 minutes 27 seconds. After the import, drop the old database to complete the rename.

Method 2 – Rename tables in bulk

First export non‑table objects (views, triggers, procedures, functions, events) from the old database, then drop them, rename all tables, and finally re‑import the exported objects.

Export non‑table objects:

root@debian-ytt1:/home/ytt# time mysqldump --login-path=root_ytt -t -d -n \
    --set-gtid-purged=off --triggers --routines --events yttdb_old > /tmp/yttdb_old_other_object.sql

Export all views:

view_list=$(mysql --login-path=root_ytt -e "SELECT table_name FROM information_schema.views WHERE table_schema='yttdb_old';" -s | tr '
' ' ')
mysqldump --login-path=root_ytt --set-gtid-purged=off --triggers=false yttdb_old $view_list > /tmp/yttdb_old_view_lists.sql

Batch‑drop functions, procedures, triggers, views, and events using generated DROP statements (examples shown in the original article).

Rename all tables with a single generated statement:

SET group_concat_max_len = 18446744073709551615;
SELECT CONCAT('rename table ', GROUP_CONCAT(CONCAT('yttdb_old.',table_name,' to yttdb_new.',table_name)))
FROM information_schema.TABLES
WHERE table_schema = 'yttdb_old' AND table_type = 'BASE TABLE' INTO @rename_lists;
PREPARE s1 FROM @rename_lists;
EXECUTE s1;
DROP PREPARE s1;

The bulk rename completed in about 55 seconds. Finally, import the previously exported non‑table objects into the new database:

time mysql --login-path=root_ytt -D yttdb_new < /tmp/yttdb_old_other_object.sql
time mysql --login-path=root_ytt -D yttdb_new < /tmp/yttdb_old_view_lists.sql

Verify that the number of tables, triggers, procedures, functions, and events in the new database matches the original.

Method 3 – Historical binlog‑based approach

If binary logging is enabled with the STATEMENT format, you can set up a replica, configure replicate-rewrite-db=yttdb_old->yttdb_new, let the replica catch up, and then promote it to master when the rename is needed. This method is not recommended because of its limited applicability and lack of standardization.

Summary

If the data volume is small, the simple mysqldump export/import (Method 1) is recommended.

If the data volume is large, the bulk table‑rename approach (Method 2) provides a much faster solution.

For extremely large datasets, external ETL tools that read binary logs or other mechanisms are required, as MySQL alone cannot efficiently rename the database.