Rename Database in MySQL

How To Rename a Database In MySQL?

Sometimes a user might want to temporarily update a database name, or for other technical reasons want to rename the database in mysql to something more meaningful. In these situations, you can rename an existing database using the SQL Rename Database statement. The Syntax to rename a database in mysql is:

or

Let's look at an example of using this command. Suppose you want to rename a database named "IT" to "Information Technologies". You can use the following query to rename the database in mysql from "IT" to "Information Technologies":

The above query renames the database from "IT" to "Information Technologies". But, before running this query, we need to make sure that a database named "IT" exists on the database server. Otherwise, this command will throw an error in the output.

This was added in MySQL 5.1.7 but was found to be disruptive as it could lead to database content leaks, and was removed in MySQL 5.1.23 for security reasons. There are other methods used for this purpose and in the subsequent sections of this article, we will see each of them in detail.

Rename a MySQL Database Using cPanel

cPanel is an interface that allows you to create and manage your existing MySQL databases. To rename a MySQL database using cPanel, you must log into the cPanel interface and select MySQL from the Databases section.

A new web page will open listing all the current MySQL databases. You must go to the database you want to rename and select the rename link in the Actions column.

Here, you can enter the desired new database name and save the result by clicking the Proceed button.

The page will reload and your database name will be successfully changed.

Rename MySQL Database From Command Line

This is another method to rename database in mysql by simply dumping up the existing database, creating a backup, and restoring the backup with the new database name. This method starts with the dump up of the database and the creation of a backup file. You can then restore the backup file with the new name of the desired database. The first step is to create a new database with the command:

where new_name_of_database is the name of the required database, uname is the MySQL username, and pass is the password for login. Now you can export the backup file to dump your existing database:

You can now run the following command to import a backup file that restores data from the exported BackupFile.sql file to a new database named new_name_of_database by running the command:

The `final step is to drop the old database by running the following command:

This method is only efficient with small databases as large databases can have backup and restore problems.

Renaming Tables with InnoDB

The `InnoDB storage engine was included in all versions since release 5.5 for renaming databases by renaming individual tables. To do this, you must first create a new database with the name you want to change the old database to, rename all the tables in the old database to the new database name, and then drop the old database. Let's see the detailed steps to do so.

The `first step is to create a new empty database using the following command:

where new_name_of_database is the name of the new database. The next step is to rename all the tables in the old database to the names of the new database tables using the following command:

Now, you can delete the old database. Before doing this, make sure that the old database is emptied and that you have renamed all the tables to ensure there is no data loss as it is irreversible. To drop the database you can use the following command: