How Do I Rename a Database in MySQL?

MySQL, a widely used RDBMS, is a preferred choice for many developers in various institute projects. While working with MySQL databases, sometimes you may need to change the database name because of various reasons, such as making the name more descriptive and easier to understand. In such cases, you can change the database name by following a few simple steps as described in this write-up.

This article provides a comprehensive guide on renaming a database in MySQL.

How Do I Rename a Database in MySQL?

In MySQL, a database cannot be renamed directly by using the “RENAME” or “ALTER” command. To rename the database you need to create a new database and then import the whole data of the old database into the newly created database.

Here are the following steps that can be utilized to rename the database in MySQL:

Step 1: Log in to MySQL Server

The MySQL server can be logged in by utilizing the command line utility known as “mysql”. Type the given below command in the CMD to log in to the local MySQL server with the username “md”:

mysql -u md -p

Executing the above command requires the server’s password for authentication.

Output

The output showed that the server has been logged in.

Step 2: Create a New Database

The “CREATE DATABASE” command followed by the appropriate database name can be utilized to create a new database, as given below in the example:

CREATE DATABASE new_linuxhint_database;

In the above command, “new_linuxhint_database” represents the database name that will be created.

Output

The output showed that the database name “new_linuxhint_database” has been created.

Note: Perform steps 3 and 4 in a separate command prompt or terminal window while keeping the previous/above CMD intact(remain open).

Step 3: Create a Backup of the Old Database

Let’s create a backup of the old database by utilizing the “mysqldump” command line utility. An example of creating a backup of the “old_linuxhint_database” is given below:

mysqldump -u md -p old_linuxhint_database > dumpdatabase.sql

In the above example, the backup file name is “dumpdatabase.sql” and the greater than “>” sign denotes that the data will be saved into the mentioned file (dumpdatabase.sql). After this command, you need to enter the MySQL server’s password.

Output

The error-free output depicts that the backup of “old_linuxhint_database” has been created.

Step 4: Import Data

To import the data into the new database using the backup file, simply use the “mysql” command line utility as shown below in the example:

mysql -u md -p new_linuxhint_database < dumpdatabase.sql

In the above example, the data is being imported into the “new_linuxhint_database” from the “dumpdatabase.sql” file and the smaller than “<” signs denote that the data will be fetched from the file.

Output

The output showed that the database has been imported.

Step 5: Delete the Old Database

After importing the data into the new database again navigate back into the previous CMD, and delete the old database by utilizing the “DROP DATABASE” command as shown in the below snippet:

DROP DATABASE old_linuxhint_database;

Output

The output showed that the old database has been deleted. While the data of the old database has been transferred to a database with a new/modified name.

Step 6: Use the New Database (Optional)

The “USE” keyword can be utilized to select a particular database. An example of using the “new_linuxhint_database” is given below:

USE new_linuxhint_database;

Output

The output “Database changed” indicates that the new database has been selected.

Conclusion

A database in MySQL cannot be renamed directly utilizing the “RENAME” or “ALTER” commands. However, by following a few simple steps, you can achieve the desired result. To do so, first, log in to the MySQL server, create a new database, create a backup file of the old database, and import the backed-up data into the newly created database.

How Do I Rename a Database in MySQL?