MySQL Rename Database

Last updated: October 19, 2026Oct 19, 2026 6 min read

Sooner or later you'll want to rename a database, for example from shop_old to shop_v2. Surprisingly, MySQL has no RENAME DATABASE statement. This tutorial explains why and shows three safe ways to do it, including how to move views, routines and privileges that a simple rename would miss.

1. Why There Is No RENAME DATABASE

MySQL 5.1 briefly had RENAME DATABASE, but it was removed in 5.1.23 because it could lose data in some situations. Trying it today gives a syntax error:

query.sql

RENAME DATABASE shop_old TO shop_v2;

Output

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE shop_old TO shop_v2' at line 1

So we create a new database and move or copy everything into it. Our sample shop_old contains three tables, a view and a user with privileges on it:

query.sql

SELECT table_name, table_type FROM information_schema.tables
WHERE table_schema = 'shop_old' ORDER BY table_type, table_name;

Result: 4 rows

TABLE_NAME	TABLE_TYPE
customers	BASE TABLE
orders	BASE TABLE
products	BASE TABLE
order_summary	VIEW

2. Method 1: Move Tables with RENAME TABLE (Fastest)

Figure 1: moving tables is instant because no rows are copied. Only the table's database name changes.

RENAME TABLE can move a table into another database on the same server. It only changes metadata, so even a 100 GB table moves in a moment. Several tables can be moved in one atomic statement:

move-tables.sql

CREATE DATABASE shop_v2;

RENAME TABLE shop_old.customers TO shop_v2.customers,
             shop_old.products  TO shop_v2.products,
             shop_old.orders    TO shop_v2.orders;

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

Result: 4 rows

TABLE_SCHEMA	TABLE_NAME
shop_old	order_summary
shop_v2	customers
shop_v2	orders
shop_v2	products

The foreign keys moved with the tables. But the view stayed behind, and it now points at tables that are gone:

query.sql

SELECT * FROM shop_old.order_summary;

Output

ERROR 1356 (HY000): View 'shop_old.order_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

3. Moving Views, Routines and Triggers

Views, stored procedures, functions and events are not moved by RENAME TABLE. Recreate them in the new database. SHOW CREATE VIEW gives the definition to copy:

recreate-view.sql

CREATE VIEW shop_v2.order_summary AS
  SELECT c.name, p.name AS product
  FROM shop_v2.orders o
  JOIN shop_v2.customers c ON c.id = o.customer_id
  JOIN shop_v2.products  p ON p.id = o.product_id;

DROP VIEW shop_old.order_summary;
SELECT * FROM shop_v2.order_summary;

Result: 3 rows

name	product
Lata	Kettle
Omar	Toaster
Lata	Toaster

Triggers are stricter: a table that has a trigger cannot be moved to another database at all.

trigger-move.sql

CREATE DATABASE logs_old;
CREATE DATABASE logs_new;
CREATE TABLE logs_old.events (id INT PRIMARY KEY, created_at DATETIME);
CREATE TRIGGER logs_old.events_bi BEFORE INSERT ON logs_old.events
  FOR EACH ROW SET NEW.created_at = NOW();

RENAME TABLE logs_old.events TO logs_new.events;

Output

Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 0 rows affected
Query OK, 0 rows affected
ERROR 1435 (HY000): Trigger in wrong schema

Save the trigger definition with SHOW CREATE TRIGGER, drop the trigger, move the table, then create the trigger again in the new database.

To list routines that need recreating, query information_schema.routines WHERE routine_schema = 'shop_old'; events are in information_schema.events and triggers in information_schema.triggers.

4. Moving Privileges

Database-level grants are tied to the name, so shop_app still has rights on shop_old and none on shop_v2:

query.sql

SHOW GRANTS FOR 'shop_app'@'localhost';

Result: 2 rows

Grants for shop_app@localhost
GRANT USAGE ON . TO `shop_app`@`localhost`
GRANT SELECT, INSERT, UPDATE, DELETE ON `shop_old`.* TO `shop_app`@`localhost`

Grant the same rights on the new name and revoke the old ones:

grants.sql

GRANT SELECT, INSERT, UPDATE, DELETE ON shop_v2.* TO 'shop_app'@'localhost';
REVOKE ALL PRIVILEGES ON shop_old.* FROM 'shop_app'@'localhost';
SHOW GRANTS FOR 'shop_app'@'localhost';

Result: 2 rows

Grants for shop_app@localhost
GRANT USAGE ON . TO `shop_app`@`localhost`
GRANT SELECT, INSERT, UPDATE, DELETE ON `shop_v2`.* TO `shop_app`@`localhost`

5. Finishing Up

When shop_old is empty, drop it and check the new database works:

finish.sql

SELECT COUNT(*) AS objects_left FROM information_schema.tables WHERE table_schema = 'shop_old';
DROP DATABASE shop_old;
SELECT COUNT(*) AS orders_in_new_db FROM shop_v2.orders;

Result: 1 row

objects_left
0

Result: 1 row

orders_in_new_db
3

6. Generating the RENAME Statement Automatically

With many tables, let MySQL write the statement for you:

generate.sql

CREATE DATABASE crm_old;
CREATE TABLE crm_old.leads (id INT PRIMARY KEY);
CREATE TABLE crm_old.contacts (id INT PRIMARY KEY);
CREATE TABLE crm_old.deals (id INT PRIMARY KEY);

SELECT CONCAT('RENAME TABLE ',
       GROUP_CONCAT(CONCAT('crm_old.`', table_name, '` TO crm_new.`', table_name, '`') SEPARATOR ', '),
       ';') AS statement_to_run
FROM information_schema.tables
WHERE table_schema = 'crm_old' AND table_type = 'BASE TABLE';

Result: 1 row

statement_to_run
RENAME TABLE crm_old.`contacts` TO crm_new.`contacts`, crm_old.`deals` TO crm_new.`deals`, crm_old.`leads` TO crm_new.`leads`;

7. Method 2: Dump and Restore with mysqldump

This copies the data into a new database. It is slower (every row is exported and imported) but works between servers and includes routines, events and triggers:

Terminal

# 1. export the old database (structure + data + routines + events + triggers)
mysqldump -u root -p --routines --events --triggers --single-transaction shop_old > shop_old.sql

# 2. create the new database and import into it
mysql -u root -p -e "CREATE DATABASE shop_v2 CHARACTER SET utf8mb4;"
mysql -u root -p shop_v2 < shop_old.sql

# 3. check row counts, then drop the old database
mysql -u root -p -e "DROP DATABASE shop_old;"

Dump without the --databases option, as above. With --databases the file contains CREATE DATABASE shop_old and USE shop_old, so it would restore into the old name.

8. Method 3: MySQL Shell Dump and Load

For large databases MySQL Shell's parallel utilities are much faster than mysqldump:

MySQL Shell

mysqlsh root@localhost --js
util.dumpSchemas(["shop_old"], "/backups/shop_old_dump")
util.loadDump("/backups/shop_old_dump", { schema: "shop_v2" })

9. Which Method Should You Use?

Method	Speed	Copies data?	Moves views/routines?	Best for
RENAME TABLE	Instant	No (moves it)	No: recreate them (drop triggers first)	Same server, large tables
mysqldump + restore	Slow for big data	Yes	Yes (with options)	Small/medium databases, other servers
MySQL Shell dump/load	Fast, parallel	Yes	Yes	Large databases

Stop or pause applications that write to the database.
Take a backup.
Rename/copy, recreate views and routines, move grants.
Update connection strings (jdbc:mysql://host/shop_v2) and restart the applications.
Drop the old database after verifying.

10. Summary

MySQL has no RENAME DATABASE statement.
Fastest: create the new database and move each table with RENAME TABLE old.t TO new.t.
Recreate views, procedures, functions and events; drop triggers before moving a table and recreate them after; move database-level grants.
Alternatively dump and restore with mysqldump or MySQL Shell.
Always back up first and update application connection strings.

Was this tutorial helpful?

Help Us Improve

MySQL Rename Database

1. Why There Is No RENAME DATABASE

2. Method 1: Move Tables with RENAME TABLE (Fastest)

3. Moving Views, Routines and Triggers

4. Moving Privileges

5. Finishing Up

6. Generating the RENAME Statement Automatically

7. Method 2: Dump and Restore with mysqldump

8. Method 3: MySQL Shell Dump and Load

9. Which Method Should You Use?

10. Summary