Back to all posts

How to Rename a MySQL Database Using mysqldump

For a developer or DBA renaming a MySQL database that other things point at; the commands, the privileges and the checks are given in full.

On this page

MySQL has no RENAME DATABASE statement[1]. To rename a database, copy it under the new name with mysqldump, check the copy, then drop the old database:

mysqldump -u root -p --single-transaction --routines --events --triggers old_dbname > old_dbname.sql
# use the character set and collation that SHOW CREATE DATABASE old_dbname prints
mysql -u root -p -e "CREATE DATABASE new_dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci"
mysql -u root -p new_dbname < old_dbname.sql
# compare the two databases (step 3), then:
mysql -u root -p -e "DROP DATABASE old_dbname"

Then grant your users their privileges on the new name. Grants stay with the old name.

The four steps of a rename: 1, dump old_dbname with mysqldump into old_dbname.sql; 2, create new_dbname and restore the file into it; 3, count the objects and rows in both databases; 4, DROP DATABASE old_dbname. Until step 4 old_dbname is untouched, so a rollback drops new_dbname and starts again; after step 4 only the dump file brings it back

Every output below comes from MySQL 9.1, run on this example database:

CREATE DATABASE old_dbname;
USE old_dbname;
CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(100) NOT NULL);
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT NOT NULL, total DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id));
INSERT INTO customers VALUES (1,'Ada'),(2,'Linus');
INSERT INTO orders VALUES (10,1,25.00),(11,1,40.00),(12,2,15.50);
CREATE VIEW order_totals AS SELECT c.name, SUM(o.total) AS total
  FROM customers c JOIN orders o ON o.customer_id = c.id GROUP BY c.name;
CREATE TRIGGER orders_round BEFORE INSERT ON orders
  FOR EACH ROW SET NEW.total = ROUND(NEW.total, 2);
CREATE PROCEDURE count_orders() SELECT COUNT(*) AS orders FROM old_dbname.orders;
CREATE EVENT nightly_check ON SCHEDULE EVERY 1 DAY DO SELECT COUNT(*) FROM orders;
CREATE USER app@'%' IDENTIFIED BY 'apppass';
GRANT SELECT, INSERT, UPDATE, DELETE ON old_dbname.* TO app@'%';

Step 1: Dump the database with mysqldump

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

mysqldump -u root -p --single-transaction --routines --events --triggers \
  old_dbname > old_dbname.sql
optionwhat it adds to the dump
--single-transactionreads InnoDB tables in one transaction, without locking them
--routines or -Rstored procedures and functions, left out by default
--eventsscheduled events, left out by default
--triggersthe triggers of each table, included by default

Stop writes to old_dbname before you dump it. A write made after the dump is not in the copy.

Name the database without --databases. With that option, the file holds its own USE old_dbname line, which overrides the database on the mysql command line. Restored "into" new_dbname, such a file replaced the tables of old_dbname with the dumped ones and left new_dbname empty. A row written to old_dbname after the dump was gone.

If the server uses GTIDs (global transaction identifiers, for replication), add --set-gtid-purged=OFF. Without it, the dump holds a SET @@GLOBAL.GTID_PURGED statement[2]. Restoring that dump on the same server stopped with ERROR 3546.

Dump as root. mysqldump needs SELECT on the tables, SHOW VIEW for views and TRIGGER for triggers[2], and SHOW_ROUTINE for stored routines[3]. Dumped by an account without TRIGGER and SHOW_ROUTINE, the file held the tables but no trigger and no procedure. mysqldump printed no message and exited with status 0.

Step 2: Create the new database and restore the dump

A new database gets the server's default character set and collation, and every table you create in it later inherits them. Read the source's values first:

SHOW CREATE DATABASE old_dbname;
CREATE DATABASE `old_dbname` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */

Create the new database with the same values[4]. If a database of that name exists already, the statement fails with ERROR 1007 and overwrites nothing:

CREATE DATABASE new_dbname
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

The shorter mysqladmin -u root -p create new_dbname takes neither option, so it gives the new database the server's defaults.

Restore the dump by naming the new database on the mysql command line:

mysql -u root -p new_dbname < old_dbname.sql

Restore as root, too. The dump sets a DEFINER on every view, trigger, routine and event, and creating an object for another account needs more than the privileges on new_dbname. With binary logging on, an account that held every privilege on new_dbname got as far as the first trigger. There the restore stopped with ERROR 1419, "You do not have the SUPER privilege and binary logging is enabled". If an error leaves new_dbname half built, drop it and run this step again.

Step 3: Compare the two databases

Before you delete anything, compare the copy with the source. Run this query, then run it again with old_dbname in place of new_dbname:

SELECT 'tables'     AS kind, COUNT(*) AS n FROM information_schema.tables
  WHERE table_schema = 'new_dbname' AND table_type = 'BASE TABLE'
UNION ALL SELECT 'views',     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 'foreign keys', 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';

Both databases returned the same counts:

kindn
tables2
views1
triggers1
routines1
events1
foreign keys1
indexes3

A 0 in new_dbname where old_dbname has a trigger or a routine means that the dump left the object out.

For rows, run SELECT COUNT(*) on each table in both databases; orders held 3 rows in each. The TABLE_ROWS column of information_schema.tables is no check: for InnoDB it may vary from the actual value by as much as 40% to 50%[5].

Counts show only that the objects and rows arrived. Before step 4, point the application at new_dbname in staging and run it.

Step 4: Drop the old database

DROP DATABASE drops all tables in the database and deletes the database. It needs the DROP privilege on the database[6]. Run it only after the comparison has passed:

DROP DATABASE old_dbname;
Query OK, 3 rows affected (0.09 sec)

DROP DATABASE returns the number of tables removed[6], and it counted the view too: 2 tables and 1 view make 3. From the shell, mysqladmin -u root -p drop old_dbname does the same, after asking you to confirm.

Keep old_dbname.sql until new_dbname works in production. After the drop, that file is the only way back.

Grant the privileges on the new name

Grants are stored against the database name, in the Db column of mysql.db, tables_priv, columns_priv and procs_priv[7]. DROP DATABASE leaves the grant rows in place[6], and mysqldump copies no accounts. List the accounts that have a grant on the old name:

SELECT user, host FROM mysql.db WHERE db = 'old_dbname';
userhost
app%

SHOW GRANTS FOR 'app'@'%' prints that account's grants as statements you can copy. Grant the same on new_dbname, then revoke the grant on old_dbname, which works after the drop too:

GRANT SELECT, INSERT, UPDATE, DELETE ON new_dbname.* TO 'app'@'%';
REVOKE ALL ON old_dbname.* FROM 'app'@'%';

Our guide to creating a MySQL user has the syntax.

Find what still names the old database

A view moves to the new name with the restore. MySQL stores a view's query with the database name filled in. mysqldump writes the query without the name, so the restored view reads the tables of new_dbname.

A procedure, trigger or event keeps its body as written. The procedure count_orders arrives in new_dbname with old_dbname.orders still in its body. Once old_dbname is dropped, calling the procedure fails:

CALL new_dbname.count_orders();
ERROR 1049 (42000) at line 1: Unknown database 'old_dbname'

Search the dump file for these bodies before you drop old_dbname:

grep -nE '`?old_dbname`?\.' old_dbname.sql
136:SELECT COUNT(*) AS orders FROM old_dbname.orders ;;

The pattern needs a dot after the name, so it skips the dump's comment lines. Create each body it finds again in new_dbname, with the new name.

What a dump-and-restore rename carries: in the dump, so new_dbname gets them, are tables and their rows, indexes and foreign keys, views rewritten to read new_dbname, and triggers, stored routines and events; still naming old_dbname after the restore are the grants in mysql.db, tables_priv, columns_priv and procs_priv, routine, trigger and event bodies that write old_dbname.table_name, and connection strings, cron jobs, backup scripts and monitoring checks

Objects in other databases are not in the dump. A view or routine elsewhere that names old_dbname breaks after the drop. A foreign key from another database makes the drop fail with ERROR 3730, "Cannot drop table 'customers' referenced by a foreign key constraint". Search information_schema across every database for the old name. Then search connection strings, ORM configuration, cron jobs, backup scripts, replication filters and monitoring checks.

Rename by moving the tables with RENAME TABLE

RENAME TABLE moves a table to another database. Moving every table renames the database in effect, but old_dbname itself stays, empty[1]. It needs ALTER and DROP on each old table, and CREATE and INSERT on the new one.

RENAME TABLE moves only tables. Dump the triggers, routines and events first, and each view by name:

mysqldump -u root -p --no-create-info --no-data --routines --events --triggers \
  old_dbname > objects.sql
mysqldump -u root -p --no-data old_dbname order_totals > views.sql

Then drop the trigger and move every table in one statement:

CREATE DATABASE new_dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
DROP TRIGGER old_dbname.orders_round;
RENAME TABLE old_dbname.customers TO new_dbname.customers,
             old_dbname.orders    TO new_dbname.orders;

Restore the two files into new_dbname, then drop old_dbname as in step 4. objects.sql creates the trigger again on new_dbname.orders:

mysql -u root -p new_dbname < objects.sql
mysql -u root -p new_dbname < views.sql

The foreign key of orders moved with the table and pointed at new_dbname.customers. What RENAME TABLE refuses or leaves behind:

  • A table that still has a trigger fails with ERROR 1435 (HY000): Trigger in wrong schema.
  • A view in the list fails with ERROR 1450 (HY000): Changing schema from 'old_dbname' to 'new_dbname' is not allowed.
  • A failure anywhere in the list moves no table, because the statement is atomic[1]. With a view in the list, customers stayed in old_dbname too.
  • Privileges granted for a renamed table are not migrated to the new name[1], and the database's own grants stay behind. Grant them as above.

Rename in cPanel or phpMyAdmin

cPanel's Manage My Databases page has a Rename action. cPanel's documentation says that it creates a new database, moves the data, recreates the grants and stored code, then deletes the old database and its grants. It also terminates all active connections to the database.

phpMyAdmin puts "Rename database to" on the database's Operations page. Its source creates the new database, copies the routines, tables, views, constraints and events, then drops the old database. It moves the privileges only when "Adjust privileges" is ticked.

Take a dump before you use either tool. Both drop the old database in the same operation. Update configuration files and applications yourself.

Check the renamed database in DbSchema

DbSchema draws the renamed database as a diagram, so you see which foreign keys arrived, not only how many. Click Connect to Database, choose MySQL, and enter the host, port, user and new_dbname in the connection dialog. In Reverse Engineer the Database, tick new_dbname and press Load. DbSchema only reads the MySQL database and changes nothing in it.

DbSchema draws every foreign key as a line between two tables, so a constraint that the restore did not recreate is a missing line. To see a table's indexes and foreign keys, double-click its header to open the Edit Table dialog. While DbSchema is connected, OK in that dialog also runs your changes in the database.

A MySQL schema reverse-engineered into a DbSchema ER diagram, with every foreign key drawn between its nine tables

If you reverse-engineered old_dbname into a DbSchema model before the drop, DbSchema can compare every column of new_dbname with it. Save the model to a file. In the connection dialog's Access and Model tab, Schema Mapping lets a schema of the model stand for a schema of another name: map old_dbname to new_dbname. Then choose Database → Refresh Model from Database. DbSchema compares the model with new_dbname and says "Schemas are identical", or counts the differences for you to review. Refresh Model from Database is part of schema synchronization, which needs DbSchema Pro.

DbSchema's Schema Synchronization dialog reporting 3 differences between the model and the database, with Review Differences and Refresh Model

Download DbSchema at https://dbschema.com/download.html, connect it to the renamed database and check that the foreign keys and indexes arrived. Connecting, reverse-engineering and the diagrams are in the free Community Edition. Saving the model to a file and comparing it with the database need DbSchema Pro.

Sources

  1. MySQL 8.4: RENAME TABLE Statement
  2. MySQL 8.4: mysqldump
  3. MySQL 8.4: Privileges Provided by MySQL
  4. MySQL 8.4: CREATE DATABASE Statement
  5. MySQL 8.4: The INFORMATION_SCHEMA TABLES Table
  6. MySQL 8.4: DROP DATABASE Statement
  7. MySQL 8.4: Grant Tables

See the renamed schema as a diagram

DbSchema connects to MySQL, reverse-engineers the renamed database and draws every foreign key, so you can check the copy before you drop the original. The Community Edition is free.