How to Rename Database in MySQL: Simple Steps

MySQL does not support a direct RENAME DATABASE command. To rename a database, you can create a new database, copy all tables from the old database to the new one, and then drop the old database.

Syntax

MySQL does not have a direct syntax to rename a database. Instead, you use a combination of commands:

• CREATE DATABASE new_db_name; to create a new database.
• RENAME TABLE old_db.table TO new_db_name.table; to move tables one by one.
• DROP DATABASE old_db; to delete the old database after moving all tables.

This process effectively renames the database by moving its contents.

sql

CREATE DATABASE new_db_name;
RENAME TABLE old_db.table1 TO new_db_name.table1;
RENAME TABLE old_db.table2 TO new_db_name.table2;
-- Repeat for all tables
DROP DATABASE old_db;

Example

This example shows how to rename a database named old_db to new_db by moving tables.

sql

CREATE DATABASE new_db;
RENAME TABLE old_db.users TO new_db.users;
RENAME TABLE old_db.orders TO new_db.orders;
DROP DATABASE old_db;

Output

Query OK, 1 row affected (for each RENAME TABLE)
Query OK, 0 rows affected (for DROP DATABASE)

Common Pitfalls

Common mistakes when renaming a database in MySQL include:

• Trying to use RENAME DATABASE which is not supported and will cause an error.
• Not moving all tables, leaving data behind.
• Dropping the old database before confirming all data is copied.
• Not updating application configurations to use the new database name.

sql

/* Wrong approach - this will cause an error */
RENAME DATABASE old_db TO new_db;

/* Correct approach - move tables then drop old database */
CREATE DATABASE new_db;
RENAME TABLE old_db.table1 TO new_db.table1;
DROP DATABASE old_db;

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 'RENAME DATABASE old_db TO new_db' at line 1

Quick Reference

Step	Command	Description
1	CREATE DATABASE new_db;	Create the new database.
2	RENAME TABLE old_db.table TO new_db.table;	Move each table to the new database.
3	DROP DATABASE old_db;	Delete the old database after moving tables.

Key Takeaways

MySQL does not support direct database renaming with a single command.

Rename a database by creating a new one and moving tables with RENAME TABLE.

Always verify all tables are moved before dropping the old database.

Update your applications to use the new database name after renaming.

Avoid using unsupported commands like RENAME DATABASE to prevent errors.