When working with MySQL databases, you may encounter the need to change a database name. However, MySQL no longer supports a direct renaming option. The RENAME DATABASE command, available in earlier versions, was removed due to the risk of data loss. Despite this, renaming a database in MySQL is still possible. This article explores the available methods to accomplish this task safely and effectively.
Contents
- How to rename databases in MySQL using mysqldump shell commands
- How to rename databases in MySQL using dbForge Studio for MySQL
- How to rename databases in MySQL Workbench
- How to rename databases in phpMyAdmin
- Best methods to rename a MySQL database
- Conclusion
How to rename databases in MySQL using mysqldump shell commands
Renaming a database in MySQL involves creating a new empty database with the desired name and transferring the contents from the old database. Once the transfer is complete, the old database can be archived or dropped as needed.
This process can be accomplished using the mysqldump utility, which enables you to create a dump of the old database and import its contents into the new one.
First, we need to dump the old database. Run the below command within your shell prompt to create a physical backup that has all database data along with stored procedures and functions:
$ mysqldump -u username -p"password" -R oldDbname > oldDbname.sqlThe next step is to create a new database with the desired name:
$ mysqladmin -u username -p"password" create newDbnameFinally, we import the dump file into the new database:
$ mysql -u username -p"password" newDbname < oldDbname.sqlThe drawback of this method is that it may take considerable time, especially when dealing with large databases.
Another way out is using visual tools, such as dbForge Studio for MySQL.
How to rename databases in MySQL using dbForge Studio for MySQL
Graphical user interfaces (GUI tools) are highly favored alternatives to the command line due to their intuitive design and the ability to speed up task performance by switching to visual mode.
This article will focus on dbForge Studio for MySQL, an IDE built to manage database-related tasks for MySQL and MariaDB in every aspect of database development, management, and administration. This includes SQL coding, database comparison and synchronization, data migration, user management, source control, and more, all within a user-friendly visual interface.
To rename databases in dbForge Studio for MySQL, you can choose from three options:
- Backup and restore
- Schema and data comparison and synchronization
- Database copying
Now, let us delve into each of these methods in detail.
Backup and restore database under a different name
This method involves creating a backup of the database and restoring it into a new database with the desired name. With dbForge Studio for MySQL, this process is simple and intuitive.
For example, if you want to rename the sakila test database to sakila_new, follow these steps:
Click Database > Tasks > Backup Database, or select the desired database from the Database Explorer pane on the left, then choose Tasks > Backup Database from the context menu.
The Database Backup Wizard will be launched. Specify the connection, the database to create a backup, the path to the folder for storing the output backup file, and the backup file name.
In the Backup content section, include both the structure and all objects into the backup file.
Specify the additional options if necessary and the errors handling behavior if needed. Click Backup. When the backup file is created, click Finish.
The next step is to create a new database with the desired name, sakila_new. You can do this using the standard CREATE DATABASE command or through a visual interface as follows:
Go to Database > New Database. In the visual editor, enter the name of the database, sakila_new. Click Apply Changes to create the database.
The sakila_new database is now present in the Database Explorer list. It is an empty database, and we want to migrate the schema and data from sakila into sakila_new. We can achieve that with the help of the backup file we created earlier for sakila.
Right-click the sakila_new database in the Database Explorer pane, select Tasks > Restore Database.
In the Database Restore Wizard, check the details, browse to the backup file created, and select it. Click Restore.
We have successfully restored the sakila backup in the sakila_new database, and we have two identical databases under different names.
The database under the old name can be dropped, as all data are saved in a database under the new name.
Compare and synchronize the database schema and data
dbForge Studio for MySQL provides powerful tools for comparing and synchronizing database schemas and table data. While this functionality is typically used to identify and analyze discrepancies between databases across development, staging, and production environments, it can also copy a database schema and data into a new empty database.
To begin, create an empty database with the desired name, such as sakila_test. Once it appears in the list of databases, navigate to Comparison > New Schema Comparison to start the process.
In the New Schema Comparison window, define the databases to be compared. Set the older database as Source and the newly-created database with the desired name as Target. Click Compare.
You can see all schema objects that exist only in the Source database since our new database is empty. Click green arrow to start the schema synchronization process that will copy the sakila schema into the sakila_test database.
To confirm the results, refresh the schema comparison results. The schemas of these two databases are completely identical.
After we have synchronized the schemas, we need to synchronize the data as well. Similarly to the previous steps, click Comparison > New Data Comparison and set Source and Target for comparison.
Click Compare to start the data comparison process. Review the results and click green arrow.
When the task is complete, we will have two identical databases with different names.
Thus, we can drop the old database.
Copy database with dbForge Studio for MySQL
The easiest method of creating a new database with the identical schema and data under the new name with one click is offered by the Copy Database option is available in dbForge Studio for MySQL.
Click Database > Tasks > Copy Database
Specify the Source and Target servers and select the database to copy from the Source column.
By default, the names from the Source column are duplicated to the Target column, but these names are editable. Specify a new name for the database in Target, renaming the copy.
Check Include Data to copy the MySQL database along with its data and click green arrow to start copying.
When the process is complete, you can see the copy of the database under the new name in the list of databases.
As you see, the process of renaming databases with dbForge Studio for MySQL doesn’t involve any difficulties as it is accomplished via an intuitive interface of the GUI tool. You can choose from the three options according to your goals and project requirements.
Apart from that, we cannot but mention that MySQL Studio delivers extensive Refactoring functionality that will help you rename columns, tables, and views while preserving database integrity.
How to rename databases in MySQL Workbench
MySQL Workbench is the default IDE designed for MySQL specialists. It is free of charge and it offers decent functionality to perform various standard database tasks in MySQL, though its functionality is significantly less robust than in dbForge Studio for MySQL.
Database renaming is not supported in MySQL Workbench. It is usually recommended to use the mysqldump utility separately.
The available option suggests renaming tables to alter the database name of a particular table. This operation will transfer the table with all data from the old database into the new one under the desired name. InnoDB, the default MySQL storage engine, allows this.
Create a new empty database under the new desired name in MySQL Workbench. Click the + icon in the Schemas section of the Navigator or use the CREATE DATABASE command.
Name the empty database and click Apply. The database will appear in the list under Schemas.
Apply the following command to change the database name of the particular table:
RENAME TABLE oldDbname.table TO newDbname.table;For instance, we want to transfer the actor table from the sakila database into the sakila_new database. Execute the below command:
RENAME TABLE sakila.actor TO sakila_new.actor;Refresh the database, and you can see that the table with all data has been transferred to sakila_new.
You can do it with all other tables to move them into the new database. However, the query allows moving tables one at a time, which isn’t practical for large databases. Besides, the RENAME TABLE command doesn’t work for views and triggers.
How to rename databases in phpMyAdmin
Many MySQL specialists rely on phpMyAdmin for their daily tasks. As a web-based application, it is accessible from any location, and it offers a range of tools for routine database management. On the other hand, it lacks advanced features found in dbForge Studio for MySQL.
phpMyAdmin enables users to rename databases by creating a copy under a new name and automatically deleting the old database as follows:
1. Select the necessary database from the list on the left and proceed to the Operations tab.
2. In the Rename database to field, enter the new database name. Cclick Go.
3. You will be prompted to confirm creating a database under the new name and dropping the old database. Click OK.
After that, the database under the new name will appear in the list, while the previous database under the old name is dropped.
This method is simple; however, it suggests immediate deleting the database under the old name, which may cause risks of data loss if some failure takes place during the process.
Best methods to rename a MySQL database
We have explored various methods of renaming databases in MySQL, both via the command-line tools and using such GUI tools like dbForge Studio for MySQL, Workbench, and phpMyAdmin. The below table compiles all these methods together.
Edit the contents of the below table to make it more readable, and present the results as a table
| Tool | Available Methods | Advantages | Disadvantages |
| mysqldump | Create a new empty database and dump and import data under new name | Uses standard commands; fast performance | Requires familiarity with mysqldump |
| dbForge Studio for MySQL | Backup and restore old database under new nameSchema/data synchronizationCopy database | Multiple methods supported; visual mode simplifies tasks | Only available in paid editions |
| MySQL Workbench | Transfer tables with data from old database into the new database with the desired name | Uses standard SQL command | Allows transferring tables only, no views or triggers |
| phpMyAdmin | Copy database under new name and drop old database | Visual mode simplifies tasks | Risk of data loss due to automatic deletion of old database |
Conclusion
Renaming databases in MySQL is a common task for database administrators, requiring reliable methods to execute effectively. Although MySQL no longer supports the old direct renaming command, it provides alternatives for this purpose. With the addition of GUI tools, such as dbForge Studio for MySQL, users can complete the process more quickly and easily without manually typing commands.
dbForge Studio offers several renaming methods, adaptable to various scenarios, among the many other options of database management in MySQL and MariaDB. To explore the capabilities of the Studio, download a 30-day fully functional free trial, install it, and experience its full potential.
