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.
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 newDbnameNow 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.sqlThe 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 newDbnameFinally, we need to import the dump file created in the first step to the new database:
$ mysql -u username -p"password" newDbname < oldDbname.sqlEven 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.
2. In the Database Backup Wizard, specify the necessary options (Connection, Database, Path, Output filename) and proceed by clicking Next.
3. Select Include all objects for the backup.
4. Specify the detailed backup options if necessary and click the Backup button.
5. Click Finish to complete the backup process.
6. Now, let’s restore the database and give it a new name. So, select Restore Database on the Database menu.
7. In the Database Restore Wizard, specify the new database name and the backup file destination path.
8. Click Restore and upon the completion of the process, click Finish.
9. Congrats, you have successfully renamed your database in a few simple steps!
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.
2. Enter the Name of a database and click Apply Changes.
3. On the Comparison menu, click New Schema Comparison.
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.
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.
6. In Schema Synchronization Wizard, select Execute the script directly against the target database and click Synchronize.
7. If we refresh the schema comparison results, we will see that the schemas of the two databases are completely identical.
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.
9. Click Compare to initiate the data comparison process. Analyze the differences and click Green Arrow to synchronize the data.
10. In the Data Synchronization window, select Execute the script directly against the target database and click Synchronize.
11. You now have the same SQL database but with a different name. You can now drop the database with an obsolete name.
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.
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.
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.
