How to Rename a MySQL Database Using mysqldump

MySQL has no RENAME DATABASE statement[1]. You rename a database by copying it under the new name and removing the old one: dump the source with mysqldump, create the target database, restore the dump into it, verify that the table and row counts match, then drop the original. Everything below is that sequence, done in an order that leaves a way back.

Prerequisites and privileges

Two accounts are involved even when they are the same login: the one that reads the source database and the one that writes the target. Check both before you start, because a dump that silently skips triggers is worse than a dump that fails.

The MySQL manual states the reading side plainly. mysqldump requires at least the SELECT privilege[2] for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES if the single-transaction option is not used, and PROCESS if the no-tablespaces option is not used.

The writing side needs less, but it needs it on the new database:

• CREATE on the target database, which is what the CREATE DATABASE statement requires[3].
• The privileges required to execute the statements the dump file contains, which the manual sums up as the appropriate CREATE privileges[2] for the objects those statements create.
• ALTER on the target database if the dump carries ALTER DATABASE statements[2], which mysqldump emits to preserve the character encodings of stored programs.
• DROP on the source database[4], for the last step only, and only after the checks pass.

If the account you are about to use does not have these, create a dedicated one rather than borrowing root. The grant syntax and the host-matching rules are covered in our guide to creating a MySQL user.

Take a backup you would actually restore from before the first command. A rename is a copy followed by a delete, and the delete is not reversible.

Step 1: Dump the source database with mysqldump

Run mysqldump from your operating system shell, not from the mysql client. It writes SQL to standard output, so redirect it to a file:

mysqldump -u dumpuser -p \
  --single-transaction \
  --routines --events --triggers \
  --databases old_dbname \
  > old_dbname.sql

What each option is for:

• --single-transaction issues a BEGIN statement[2] before dumping data, so an InnoDB dump is consistent without taking LOCK TABLES on every table.
• --routines dumps stored routines[2], meaning procedures and functions, from the dumped databases.
• --events dumps events from the dumped databases.
• --triggers dumps triggers for each dumped table; --skip-triggers is the counterpart that turns it off.
• --databases interprets the name arguments as database names[2], which is what makes mysqldump write a CREATE DATABASE statement into the file.

Do not pass --add-drop-database here. It writes a DROP DATABASE statement before each CREATE DATABASE[2] statement, which is the opposite of what a rename wants: the whole safety of this procedure rests on the source surviving until you have checked the copy.

On a server with GTIDs enabled, mysqldump adds a SET @@GLOBAL.gtid_purged statement[2] to the output by default, and that statement will not replay into a live server that already has a GTID history. Pass --set-gtid-purged=OFF when you are restoring into the same server you dumped from.

Then look at what you actually got, rather than assuming. Object classes that are missing from the file cannot appear in the restore:

grep -c 'CREATE TABLE'     old_dbname.sql
grep -c 'CREATE VIEW'      old_dbname.sql
grep -c 'CREATE TRIGGER'   old_dbname.sql
grep -c 'CREATE PROCEDURE' old_dbname.sql
grep -c 'CREATE FUNCTION'  old_dbname.sql
grep -c 'CREATE EVENT'     old_dbname.sql

Compare those counts against what the source database reports. If the trigger count is zero and the source has triggers, stop here and fix the dump; do not carry on and discover it after the drop.

Step 2: Create the target database

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

The dump file written with --databases already carries a CREATE DATABASE statement, so a restore into a fresh server would create the database for you. In a rename you want the target created deliberately, with the same character set and collation as the source, because a database created with the wrong defaults silently changes the collation of every table added to it later.

Read the source definition first:

SHOW CREATE DATABASE old_dbname;

Then create the target with those same values. CREATE DATABASE takes CHARACTER SET and COLLATE options[3], and it needs the CREATE privilege for the database:

CREATE DATABASE new_dbname
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

Substitute the character set and collation that SHOW CREATE DATABASE actually printed for your source. The values above are an example, not a recommendation.

From the shell, mysqladmin does the same thing: its create command[5] creates a new database with the name you give it. It takes no character set argument, which is exactly why the SQL statement is the better choice for a rename.

mysqladmin -u adminuser -p create new_dbname

Check the name is free before you run it. SHOW DATABASES lists what exists.

Step 3: Restore the dump into the new database

A dump written with --databases names the source database in its CREATE DATABASE and USE statements, so piping it straight into the client recreates the old name rather than the new one. Strip the database statements and target the new database on the command line:

mysqldump -u dumpuser -p \
  --single-transaction --routines --events --triggers \
  --no-create-db --databases old_dbname \
  > old_dbname.sql

mysql -u adminuser -p new_dbname < old_dbname.sql

--no-create-db suppresses the CREATE DATABASE statements[2] that are otherwise included in the output when --databases is given. The USE statement is still written, so on a dump you have already taken, remove it before restoring:

sed -e '/^USE `old_dbname`;$/d' \
    -e '/^CREATE DATABASE .*old_dbname/d' \
    old_dbname.sql > old_dbname.clean.sql

mysql -u adminuser -p new_dbname < old_dbname.clean.sql

The simpler alternative is to dump without --databases at all. Naming the database as a bare argument writes neither CREATE DATABASE nor USE, and the file restores into whatever database you point the client at:

mysqldump -u dumpuser -p \
  --single-transaction --routines --events --triggers \
  old_dbname > old_dbname.sql

mysql -u adminuser -p new_dbname < old_dbname.sql

Watch the client's exit status and read every error it prints. A restore that reports errors and still exits is a restore that lost objects, and the counts in the next step are what turn that from a suspicion into a fact.

Step 4: Verify table and row counts

Compare the two databases object by object before anything is deleted. Start with the table list, which catches a restore that stopped part-way:

SELECT table_schema, table_type, COUNT(*)
FROM information_schema.tables
WHERE table_schema IN ('old_dbname', 'new_dbname')
GROUP BY table_schema, table_type
ORDER BY table_schema, table_type;

Do not use the TABLE_ROWS column of that table as your row check. For storage engines such as InnoDB the manual says the value is an approximation, and may vary from the actual value by as much as 40% to 50%[6], and directs you to SELECT COUNT(*) for an accurate count.

Generate the real counts instead. This statement writes the SQL that counts every table in a database, which you then run against each of the two:

SELECT CONCAT(
  'SELECT ''', table_name, ''' AS t, COUNT(*) AS n FROM `',
  table_schema, '`.`', table_name, '` UNION ALL')
FROM information_schema.tables
WHERE table_schema = 'old_dbname' AND table_type = 'BASE TABLE'
ORDER BY table_name;

Then compare the two result sets. Also compare the object classes a table count does not cover, because these are the ones a dump quietly drops:

SELECT 'views'      AS kind, COUNT(*) FROM information_schema.views
  WHERE table_schema = 'new_dbname'
UNION ALL SELECT 'triggers',  COUNT(*) FROM information_schema.triggers
  WHERE trigger_schema = 'new_dbname'
UNION ALL SELECT 'routines',  COUNT(*) FROM information_schema.routines
  WHERE routine_schema = 'new_dbname'
UNION ALL SELECT 'events',    COUNT(*) FROM information_schema.events
  WHERE event_schema = 'new_dbname'
UNION ALL SELECT 'fk',        COUNT(*) FROM information_schema.table_constraints
  WHERE table_schema = 'new_dbname' AND constraint_type = 'FOREIGN KEY'
UNION ALL SELECT 'indexes',   COUNT(DISTINCT table_name, index_name)
  FROM information_schema.statistics WHERE table_schema = 'new_dbname';

Run the same query against the old database name and diff the two outputs. Every line must match. A foreign key count that came back short is the single most common outcome of a restore that hit an error and carried on.

Then run the application against the new database in a staging environment. Counts prove the objects arrived; only traffic proves they work.

Step 5: Drop the original database, only after verification

Once the counts match and the application runs, remove the source. DROP DATABASE drops all tables[4] in the database and deletes the database, and it needs the DROP privilege on that database.

DROP DATABASE old_dbname;

The statement returns the number of tables that were removed[4]. Compare that against the table count you took in the previous step; if it does not match, something was writing to the old database while you were copying it, and your new copy is already behind.

From the shell, the mysqladmin drop command deletes the database and all its tables, and it asks for confirmation unless you pass --force[5]. Leave the confirmation on.

mysqladmin -u adminuser -p drop old_dbname

Keep the dump file until the new name has been in production long enough that you would have noticed a problem. It is the only thing standing between you and a restore from last night's backup.

What a plain dump and restore leaves behind

A dump-and-restore rename moves the contents of a database. It does not move everything that referred to the database by name, and it does not move anything that lives outside the database. These are the cases that turn a clean-looking rename into a Monday morning incident.

Views, triggers, stored routines and events

mysqldump has a separate option for each of these object classes: --routines for stored routines[2], --events for events, and --triggers for triggers on each dumped table. Name all three on the command line rather than relying on a default, then confirm with the grep counts from step 1 that the file actually contains them.

Views bring a second problem the counts will not show. A view body that names its own database explicitly, as old_dbname.customers rather than customers, still points at the old database after the restore, and keeps working right up to the moment you drop it. Search the dump file for the old name before you restore:

grep -n 'old_dbname' old_dbname.sql | grep -v '^.*-- '

The same applies to stored routines, triggers and events that qualify a table with the database name, and to anything outside MySQL that does: application connection strings, ORM configuration, cron jobs, backup scripts, replication filters and monitoring checks.

DEFINER clauses point at an account, not at a database

Every view, trigger, stored routine and event carries a DEFINER attribute, and the dump file carries it with them. Restoring those objects as a different account is a privileged operation: with the SET_ANY_DEFINER privilege you can specify any account as the DEFINER attribute[7], and without it the only permitted account is your own.

If the definer account does not exist on the target server the object still restores, and then fails when it runs. The manual is specific about when: for a stored routine an error occurs at routine execution time[7] if the SQL SECURITY value is DEFINER but the definer account does not exist; for a view, when the view is referenced; for an event, at event execution time; and for a trigger the behaviour with respect to privilege checking is undefined.

This is the failure that is hardest to catch, because nothing about the restore looks wrong. Check the definers explicitly:

SELECT definer, COUNT(*) FROM information_schema.views
  WHERE table_schema = 'new_dbname' GROUP BY definer;
SELECT definer, COUNT(*) FROM information_schema.routines
  WHERE routine_schema = 'new_dbname' GROUP BY definer;

User privileges do not follow the database name

Grants are stored against the database name, not against the database. The mysql.db table's scope columns are Host, Db and User[8], and mysql.tables_priv, mysql.columns_priv and mysql.procs_priv each carry the same Db column. Rename the database and every one of those rows still names the old one.

mysqldump dumps the objects in a database. It does not dump the accounts that can reach it, so no part of the restore recreates a grant. List what the source had before you drop it:

SELECT user, host, db FROM mysql.db WHERE db = 'old_dbname';
SELECT DISTINCT user, host FROM mysql.tables_priv WHERE db = 'old_dbname';

Then reissue each grant against the new name with GRANT, and revoke the old ones. Dropping the source does not clean them up for you: when a database is dropped, privileges granted specifically for the database are not automatically dropped[4], and must be dropped manually. Our MySQL user guide has the GRANT and REVOKE syntax.

Rolling back a failed rename

The order of the five steps is the rollback plan. Until the drop in step 5, the source database is untouched and complete, so recovery is a matter of abandoning the copy rather than repairing it.

• Restore failed or the counts do not match: DROP DATABASE new_dbname, fix the dump, start again from step 1. Nothing has touched the source.
• Application traffic has already moved to the new name and something is wrong: point the connection strings back at the old name. It is still there, still complete.
• The drop in step 5 has already run: restore the dump file into a database with the old name, then reissue the grants you listed before dropping. This is the only branch that loses writes, and it loses every write made to the new database since the dump was taken.
• You renamed with RENAME TABLE instead: move the tables back the same way. The original database still exists[1], empty, so there is somewhere to move them to.

Freeze writes to the source for the duration if you can. A rename taken from a database that is still accepting traffic produces a copy that is correct as of the dump and wrong by the time you drop the original. If you cannot freeze writes, treat this as a migration with a cutover rather than a rename, and read our note on turning a schema diff into a safe migration script.

The RENAME TABLE shortcut for small schemas

For a schema of a few plain tables with no views and no triggers, there is a faster route. RENAME TABLE can move a table from one database to another, and the manual notes that using this method to move all tables from one database to a different one in effect renames the database[1], an operation for which MySQL has no single statement, except that the original database continues to exist with no tables.

CREATE DATABASE new_dbname
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

RENAME TABLE old_dbname.customers TO new_dbname.customers,
             old_dbname.orders    TO new_dbname.orders,
             old_dbname.invoices  TO new_dbname.invoices;

DROP DATABASE old_dbname;

It moves no data, so it finishes in about the time it takes to write. It is also narrower than the dump route in ways that matter:

• A table with a trigger cannot be renamed into a different database. The attempt fails with a Trigger in wrong schema[1] error.
• Views work with RENAME TABLE, except that views cannot be renamed into a different database[1] at all.
• Privileges granted specifically for a renamed table or view are not migrated to the new name[1], and must be changed manually.
• If the rename would move the table to a database on a different file system, the outcome is platform specific[1] and depends on the operating system calls used to move the files.
• Stored routines and events are not tables, so nothing moves them. You still need mysqldump --routines --events for those.

The multi-table form is worth using over a table-at-a-time loop. The rename operation is done atomically[1], no other session can access any of the tables while it is in progress, and if any error occurs the statement fails and no changes are made. Foreign keys between the listed tables therefore never point at a half-moved schema.

Because RENAME TABLE writes an empty database rather than removing one, the DROP DATABASE at the end is part of the procedure, not an afterthought.

Check the renamed schema in DbSchema

Counts tell you how many foreign keys arrived. They do not tell you which ones, or between which tables. Reading the renamed schema back as a diagram does, and it takes a minute. DbSchema connects to MySQL, reverse-engineers the structure into a model and draws it, which is the fastest way to see whether the copy is really the same shape as the original.

Reverse-engineer the renamed database

Point DbSchema at the new database and let it read the schema back. Choose MySQL as the engine, fill in host, port, user and the new database name, and DbSchema pulls the structure into a model. In the schema selection step, tick the object classes deliberately: tables, views, procedures, functions and triggers. That list is the same list of things a plain dump can leave behind, so reading it back is the check. Skip the system catalogs; information_schema, mysql, performance_schema and sys belong to the server, not to the MySQL database you renamed.

Read the foreign keys and indexes off the diagram

DbSchema lays the reverse-engineered tables out automatically and draws every foreign key as a line between them, solid for mandatory relationships and dashed for optional ones. A constraint the restore did not recreate is a line that is not there, and a relationship that came back the wrong way round is visible in the line style rather than buried in a catalogue query. Double-click a table to open its editor and read the columns and the Indexes tab.

Compare the new database against the original

A diagram catches a missing foreign key. It will not catch a column whose type drifted. Before you drop the source, reverse-engineer each database into its own model file, then run Schema > Compare Model with Other Model From File. DbSchema's Synchronization Dialog puts the two models side by side and lists every object that differs, with the direction chosen per object, so a column whose type drifted shows up as two values on one row and anything the restore lost shows up as present in one and missing in the other. The full method is in our guide to comparing two database schemas.

Saving the model to a file and schema synchronization are Pro features. Connecting, reverse-engineering and the interactive diagrams are in the free Community Edition.

Keep the model afterwards rather than throwing it away. A saved model is what makes the next schema change reviewable instead of guessed at, and it is the input to both MySQL schema version control in Git and to generated documentation of the MySQL database.

Download DbSchema and connect it to the renamed database to confirm the foreign keys and indexes came across. Reverse engineering and interactive diagrams are in the free Community Edition; the Synchronization Dialog used to compare the two models is in the Pro edition.

Sources

1. MySQL 8.4 Reference Manual: RENAME TABLE Statement
2. MySQL 8.4 Reference Manual: mysqldump - A Database Backup Program
3. MySQL 8.4 Reference Manual: CREATE DATABASE Statement
4. MySQL 8.4 Reference Manual: DROP DATABASE Statement
5. MySQL 8.4 Reference Manual: mysqladmin - A MySQL Server Administration Program
6. MySQL 8.4 Reference Manual: The INFORMATION_SCHEMA TABLES Table
7. MySQL 8.4 Reference Manual: Stored Object Access Control
8. MySQL 8.4 Reference Manual: Grant Tables