How do I rename a MySQL database (change schema name)?

MySQL doesn’t offer a simple, single command to rename an entire database (schema) in newer versions. In older versions (MySQL 5.1 and below), there was a RENAME DATABASE statement, but it’s long been deprecated and removed due to potential data corruption issues. Instead, the standard practice today is to create a new database with the desired name, move your data into it, and then drop the old database.

Below are two primary approaches: using mysqldump or renaming tables manually. Both achieve the same end result—migrating your data into a new database name.

1. Using `mysqldump` (Recommended)

1.1. Create a Backup of Your Old Database

Open your terminal or command prompt.
Run mysqldump to export your old database (e.g., old_db) to a file:
```
mysqldump -u [username] -p old_db > old_db_backup.sql
```
Replace [username] with a user who has SELECT and LOCK TABLES privileges on old_db. Enter the password when prompted.

1.2. Create a New Database

While still in the MySQL shell (or using another CLI command), create the new database:

CREATE DATABASE new_db;

1.3. Import the Backup into the New Database

Now, import the data you dumped into new_db:

mysql -u [username] -p new_db < old_db_backup.sql

1.4. Verify and Drop the Old Database

Check the contents of new_db to ensure the data migrated successfully:
```
SHOW TABLES FROM new_db;
```
If everything looks correct, you can drop the old database:
```
DROP DATABASE old_db;
```

This approach is generally the safest and easiest to revert if anything goes wrong (since you have the backup file).

2. Renaming All Tables Manually (for Larger Databases, or If You Prefer In-Place)

Create the New Database:
```
CREATE DATABASE new_db;
```

Rename Each Table:

RENAME TABLE old_db.table1 TO new_db.table1,
             old_db.table2 TO new_db.table2,
             ...;

This moves each table from the old database to the new one without dumping and re-importing.

Verify Data:

USE new_db;
SHOW TABLES;
SELECT COUNT(*) FROM table1; -- Confirm row counts or do some checks

Drop the Old Database (Optional):
```
DROP DATABASE old_db;
```

Caveat

If you have a large number of tables or complex dependencies (foreign keys, triggers, stored procedures), handling each table manually can be tedious. You may need to recreate any stored procedures, events, or triggers in the new database because they’re often tied to the schema name.

3. Best Practices

Backup First: Always keep a safe copy of your data before any structural changes.
Check for Dependencies: If you have stored procedures, views, or foreign keys, make sure to recreate or update them in the new database.
Minimize Downtime: Do it during off-peak hours or in a scheduled maintenance window if you have a production environment.
Permissions: Reassign the appropriate privileges to users on the new database schema.

4. Level Up Your Database Expertise

Renaming a database is just one aspect of managing MySQL. To go deeper into performance tuning, indexing, and other advanced topics, here are two popular courses from DesignGurus.io:

Grokking SQL for Tech Interviews
Learn to write more advanced SQL queries, optimize performance, and handle real-world data challenges.
Grokking Database Fundamentals for Tech Interviews
Get a solid foundation in database design principles, normalization, indexing strategies, and more.

5. Get Personalized Feedback with Mock Interviews

If you’re preparing for a job interview where MySQL skills are crucial, Mock Interviews from DesignGurus.io can help. You’ll receive guidance and real-time tips from ex-FAANG engineers to sharpen your approach for technical, behavioral, and system design interviews.

Conclusion

While MySQL doesn’t provide a straightforward RENAME DATABASE command anymore, you can rename (i.e., move) your database by dumping and restoring under a new name or by renaming tables individually. Always remember to backup, verify your data, and reassign permissions as needed. By following these steps and best practices, you’ll smoothly migrate your schema to its new name without losing data or causing undue downtime.

Stay curious, keep learning, and explore more MySQL tips, courses, and mock interviews at DesignGurus.io!

CONTRIBUTOR

TechGrind