Friday, July 12, 2024
HomeProductsMySQL ToolsHow to Rename a MySQL Database

How to Rename a MySQL Database

Eventually, it appears necessary to change the name of a database in MySQL. For that purpose, we used to apply a simple RENAME DATABASE command that existed in earlier versions of MySQL. However, it became no longer available in the newer versions as it posed risks. The reason was that the RENAME DATABASE query could result in the loss of essential database contents, which clearly was undesirable.

Download dbForge Studio for MySQL

Renaming Tables Method

There is a way to perform this task quite easily. In fact, MySQL version 5.5 (and later) has InnoDB Storage Engine as the default one, which can come in handy. 
In a nutshell, we can apply the RENAME TABLE command within a MySQL prompt so as to alter the database name of a particular table while leaving the table name unchanged. But to do so, we first need to create a new database with the following mysqladmin shell command:

$ mysqladmin -u username -p"password" create newDbname

Now that we have created an empty database, we need to move the tables one by one from the former database into the newly created database with the following command:

RENAME TABLE oldDbname.table TO newDbname.table;

As you see, the query allows us to move the tables only one at a time, which isn’t very practical for large databases. Besides, the RENAME TABLE command doesn’t work for views and triggers. Instead of running the above-mentioned statement, we would have to drop and create them again.

Dumping Method

Another way to rename a database in MySQL would be to use the mysqldump shell command. This way, we can create a dumped copy of the database and import the whole database content into the new database. After that, we can drop the old database if that’s necessary.

To start with, we need to dump the old database by running the following command within your shell prompt:

$ mysqldump -u username -p"password" -R oldDbname > oldDbname.sql

The command creates a physical backup that has all database data along with stored procedures and functions.

Following that, we need to use the above-mentioned command to create a new database:

$ mysqladmin -u username -p"password" create newDbname

Finally, we need to import the dump file created in the first step to the new database:

$ mysql -u username -p"password" newDbname < oldDbname.sql

Even though this method appears to be straightforward, it may take considerable time when we deal with a larger database. So, it still leaves much to be desired.

For these reasons, we suggest having a look at the ways to rename a MySQL database more efficiently and conveniently. To illustrate them, we are going to use dbForge Studio for MySQL, an IDE that provides powerful database development, management, and administration capabilities. With its help, we can rename a database in three simple yet effective methods, namely through backup and restore, schema and data comparison, and copy database method.

Backup and Restore Method with dbForge Studio for MySQL

The essence of this method is to create a backup copy of the database and then to restore the database with a different name.

1. On the Database menu, select Backup and Restore and click Backup Database.

Call Database Backup Wizard in dbForge Studio for MySQL

2. In the Database Backup Wizard, specify the necessary options (Connection, Database, Path, Output filename) and proceed by clicking Next.

Specify general backup options

3. Select Include all objects for the backup.

Define the content for the backup

4. Specify the detailed backup options if necessary and click the Backup button.

Detailed backup options tab

5. Click Finish to complete the backup process.

Backup is complete

6. Now, let’s restore the database and give it a new name. So, select Restore Database on the Database menu.

Call Database Restore Wizard

7. In the Database Restore Wizard, specify the new database name and the backup file destination path.

Choose a backup file

8. Click Restore and upon the completion of the process, click Finish.

Database restore is complete

9. Congrats, you have successfully renamed your database in a few simple steps!

Successful database renaming via backup and restore

Schema and Data Compare Method with dbForge Studio for MySQL

Another option we can use to effectively change the name of the database in MySQL is to use the schema and data comparison functionality of the dbForge Studio for MySQL tool.

The logic behind this process is that we first need to create a new MySQL database. Following that, we can use the schema and data comparison features to compare and synchronize the structure and data of an older database to a newly created database.

1. On the Database menu, select New Database.

Create a new database within dbForge Studio for MySQL

2. Enter the Name of a database and click Apply Changes.

Enter the basic parameters for the new database

3. On the Comparison menu, click New Schema Comparison.

Call Schema Comparison Wizard

4. Specify the names of the databases to be compared. Select the older database as Source and the newly created database as Target. Click Compare to initiate schema comparison.

Set Source and Target for schema comparison

5. As a result, you can see the objects that exist only in the Source database since our newly created database is empty. Click Green Arrow to start the schema synchronization process.

Schema comparison results

6. In Schema Synchronization Wizard, select Execute the script directly against the target database and click Synchronize.

Schema Synchronization wizard

7. If we refresh the schema comparison results, we will see that the schemas of the two databases are completely identical.

Identical schemas after synchronization

8. After we have synchronized the schemas, we need to synchronize the data as well. Similarly to the previous steps, click New Data Comparison on the Comparison menu. In the Data Comparison Wizard that opens, set Source and Target for comparison.

Data comparison options

9. Click Compare to initiate the data comparison process. Analyze the differences and click Green Arrow to synchronize the data.

Data comparison result

10. In the Data Synchronization window, select  Execute the script directly against the target database and click Synchronize.

Data Synchronization Wizard

11. You now have the same SQL database but with a different name. You can now drop the database with an obsolete name.

Identical data after synchronization

Copy Database Method with dbForge Studio for MySQL

Probably, the easiest and fastest way to alter the name of a MySQL database is to apply the Copy Databases tool that is a part of dbForge Studio for MySQL.

1. On the Database menu, select Copy Databases.

Copy Databases feature in dbForge Studio for MySQL

2. In the Wizard, specify the server name in Source and Target. Select the database you want to copy from the Source column. Then, choose a Target database. Note that the names from the Source column are duplicated to the Target column. You can specify a new name for the database in Target, thus renaming the copy. Select the Include Data checkbox to copy the MySQL database along with its data. Click Green Arrow to start copying.

Initiate the database copying process

Once the database copying is complete, you will see a new database in the Database Explorer window. You can now drop the duplicate database.

As you see, the process of renaming a database within 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 to change the name of your MySQL database 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.

Download dbForge Studio for MySQL