How to Rename a MySQL Database

Renaming a MySQL database is a common task when reorganizing projects, fixing naming conventions, or preparing for a new environment. MySQL does not have a single built-in command to rename a database (unlike tables), so the process involves either exporting and importing or using a series of commands. This guide covers the two safest methods—using mysqldump and using phpMyAdmin—and explains the alternative RENAME TABLE approach for smaller databases. You’ll need command-line access or phpMyAdmin, plus sufficient privileges to create and drop databases.

What You’ll Need

• MySQL server access (local or remote) with credentials that have CREATE, DROP, SELECT, and INSERT privileges.
• Command-line terminal (for mysqldump method) or phpMyAdmin web interface.
• Enough disk space to hold a temporary dump file (if using mysqldump).
• For the RENAME TABLE method: no foreign key constraints referencing the tables, or the ability to temporarily disable them.

Step-by-Step

Choose one of the following methods based on your environment and database size.

Method 1: Using mysqldump (Recommended for Most Cases)

1. Create a backup dump of the existing database. Open a terminal and run:
   mysqldump -u username -p old_db_name > backup.sql
   You’ll be prompted for the password. This creates a SQL file containing the database structure and data.
2. Create the new database. Log into MySQL with:
   mysql -u username -p
   Then run:
   CREATE DATABASE new_db_name;
   Exit the MySQL prompt with EXIT;.
3. Import the dump into the new database. Run:
   mysql -u username -p new_db_name < backup.sql
   Enter the password when prompted. This populates the new database with all tables and data.
4. Verify the import. Log into MySQL and run:
   USE new_db_name; then SHOW TABLES; to confirm all tables are present. You can also compare row counts with SELECT COUNT(*) FROM table_name; for a few critical tables.
5. Drop the old database. After confirming the new database is correct, run:
   DROP DATABASE old_db_name;
   This permanently removes the old database. Make sure you have a backup before doing this.

Method 2: Using phpMyAdmin (GUI)

1. Log into phpMyAdmin and select the database you want to rename from the left sidebar.
2. Click the "Operations" tab at the top.
3. In the "Rename database to" field, type the new database name.
4. Choose the collation (usually leave as is) and check the option "Adjust privileges" if you want to keep user permissions for the new name.
5. Click "Go". phpMyAdmin will run the rename process (it creates a new database, copies tables, and drops the old one). Confirm the success message.

Method 3: Using RENAME TABLE (For Small Databases with No Foreign Keys)

1. Create the new database: CREATE DATABASE new_db_name;
2. For each table in the old database, run a RENAME statement. For example, if you have tables users and orders, run:
   RENAME TABLE old_db.users TO new_db.users;
RENAME TABLE old_db.orders TO new_db.orders;
   Repeat for every table. You can generate the commands with a query like:
   SELECT CONCAT('RENAME TABLE old_db.', table_name, ' TO new_db.', table_name, ';') FROM information_schema.tables WHERE table_schema='old_db';
3. After moving all tables, drop the empty old database: DROP DATABASE old_db;

Common Mistakes

• Forgetting to update application connection strings. After renaming, any application using the old database name will fail to connect.
• Not checking for foreign key constraints when using RENAME TABLE. This method fails if constraints reference tables across databases.
• Skipping the backup. A failed import or accidental drop can result in permanent data loss.
• Running out of disk space during mysqldump or import, which can corrupt the dump file.
• Using the wrong case for database names on systems with case-sensitive table names (Linux). MySQL database names are case-sensitive on Unix-like systems.

Tips

• Always perform a dry run on a test environment first, especially for large databases.
• Use --routines and --triggers flags with mysqldump to include stored procedures and triggers: mysqldump --routines --triggers -u user -p old_db > backup.sql
• If you have many tables, generate the RENAME TABLE statements with a query as shown above to avoid manual typing.
• Consider using a transaction to avoid partial moves: start a transaction, perform all renames, then commit (though DDL statements may cause implicit commits).
• After renaming, update any cron jobs, scripts, or configuration files that reference the old database name.

Troubleshooting

Error: "Access denied" when creating or dropping database. Ensure your user has the necessary privileges. You may need to ask your administrator to grant CREATE, DROP, and SELECT privileges.

Import fails with "ERROR 1049: Unknown database". The new database was not created before import. Run the CREATE DATABASE command first.

RENAME TABLE fails with foreign key errors. Drop the foreign key constraints before renaming, then re-add them after. Alternatively, use the mysqldump method which handles constraints automatically.

Data appears missing after import. Check the dump file size and the import output for errors. Sometimes the import stops on an error; use --force with mysql to continue, but be aware of potential data inconsistencies.

phpMyAdmin rename fails with a timeout. For large databases, the operation may take too long. Increase the PHP execution time limit in your phpMyAdmin config, or use the command-line method instead.

Frequently Asked Questions

Can I rename a MySQL database with a single command?

No, MySQL does not provide a direct RENAME DATABASE statement. You must use one of the methods described above.

Does renaming a database affect user permissions?

Yes, privileges granted on the old database name will not apply to the new name. You'll need to re-grant privileges or use phpMyAdmin's "Adjust privileges" option.

Is it safe to rename a database while the application is running?

It's not recommended. The application may fail to connect during the process. Schedule a maintenance window and stop the application before renaming.

Can I rename a database in MySQL Workbench?

MySQL Workbench does not have a direct rename feature. You can use the mysqldump method from the terminal, or use the SQL editor to run the commands.

What happens to views and stored procedures during rename?

Views and stored procedures are stored in the database and are included in the dump if you use --routines. They will be recreated in the new database. However, views may reference the old database name in their definition; you may need to update them after renaming.

Can I rename a database on a live production server without downtime?

There is always some downtime during the process, even if brief. For minimal impact, use the RENAME TABLE method with a script that moves tables quickly, but this is risky with foreign keys. The safest approach is planned downtime.

Related

• How to Write an Ad Hoc Query for a Specific Data Question
• IAM for AI Workloads: How to Choose
• Nord CE 6 vs Oppo F33 Pro: Which Fits You?