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.
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
| option | what it adds to the dump |
|---|---|
--single-transaction | reads InnoDB tables in one transaction, without locking them |
--routines or -R | stored procedures and functions, left out by default |
--events | scheduled events, left out by default |
--triggers | the 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:
| kind | n |
|---|---|
| tables | 2 |
| views | 1 |
| triggers | 1 |
| routines | 1 |
| events | 1 |
| foreign keys | 1 |
| indexes | 3 |
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';
| user | host |
|---|---|
| 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.
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.
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.
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
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.