Copy and Rename a Database

Two common questions: “How do I copy a database?” and “How do I rename one?” MySQL has no single statement for either, but both are easy.

There is no RENAME DATABASE

An old RENAME DATABASE command existed in MySQL 5.1 for a short time and was removed because it could lose data. Do not look for it.

Copy a database with mysqldump

mysqldump writes a database as SQL statements. You can load that into a new database.

1
2
3
4
5

# 1. Create the target database
mysql -u root -p -e "CREATE DATABASE shop_copy CHARACTER SET utf8mb4"

# 2. Dump the source and load it into the target
mysqldump -u root -p --single-transaction --routines --triggers --events shop | mysql -u root -p shop_copy

• --single-transaction gives a consistent snapshot of InnoDB tables without locking them.
• --routines --triggers --events also copy stored programs (triggers are included by default, the others are not).

This is the standard way to make a test copy of production data (anonymise it first if it contains personal data) or to move a database to another server.

Rename by moving the tables

If you really want a different name, create the new database and move each table into it with RENAME TABLE. That is a quick metadata operation, not a copy.

1
2
3
4
5
6
7
8
9
10

CREATE DATABASE old_name;
CREATE TABLE old_name.customers (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE old_name.orders (id INT PRIMARY KEY, customer_id INT);
INSERT INTO old_name.customers VALUES (1, 'Maya');

CREATE DATABASE new_name;
RENAME TABLE old_name.customers TO new_name.customers,
             old_name.orders    TO new_name.orders;

SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema IN ('old_name', 'new_name') ORDER BY table_schema, table_name;

1
2
3
4
5
6
7

+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| new_name     | customers  |
| new_name     | orders     |
+--------------+------------+
2 rows in set

The data moved with the tables. Then drop the old, now empty database:

1
2

DROP DATABASE old_name;
SELECT * FROM new_name.customers;

1
2
3
4
5
6

+----+------+
| id | name |
+----+------+
|  1 | Maya |
+----+------+
1 row in set

What RENAME TABLE does not move

• Views, triggers, stored procedures and events. Their definitions mention the old database name. Recreate them, or use a dump instead.
• Privileges. Grants on old_name. do not follow. Re-grant on new_name..
• Foreign keys between moved tables are updated as long as all related tables are moved in one RENAME TABLE statement (as above).
• Application settings. Change the connection string.

Which method?