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”:
Executing the above command requires the server’s password for authentication.
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:
In the above command, “new_linuxhint_database” represents the database name that will be created.
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:
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.
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:
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.
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:
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:
The output “Database changed” indicates that the new database has been selected.
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.