Our focus in this guide is understanding what it takes to rename a database in MySQL from the command line. We will create a sample database and then proceed to rename it step-by-step. Let’s begin!
Step-by-Step Guide on Renaming a Database in MySQL from the Command Line
For this tutorial, we create a sample database that we will use throughout this post. In your case, you can skip this section as your database already exists.
Open your terminal and log in to MySQL via your terminal. For that, execute the following command:
Enter your password for that user account and ensure that the user account has the correct privileges to access the database and perform various operations.
Once the MySQL shell opens, go ahead and create a sample database. For our case, let’s create the “linuxhint” database with the following syntax:
You will get a confirmation that the action is successful. Verify the new database with the “show databases” command.
From the output, we can verify that our sample database is successfully created. Ensure that you can see the database that you want to rename.
Next, select the database that you want to rename. Here, we select our “linuxhint” database as follows:
Let’s quickly create two tables in our database.
We can verify the contents of our target database by checking what tables are contained in that database.
We confirmed that the two tables that we created exist in the database.
You can exit the MySQL shell to access the terminal where we start the steps to rename the database.
Although renaming your MySQL database using the cPanel is possible, you can do it directly from the command line. Unfortunately, no direct command lets you rename your MySQL database. Instead, you must utilize the “mysqldump” option.
The first thing you must do is create a backup of the database that you want to rename. Execute the following command and replace the “linuxhint” with your target database and the “newdb.sql” with your preferred name for the backup database:
With the database backup created, you must create a new database that acts as the renamed database. Ideally, we copy the contents of the database that we backed up to the new one that you will create. That way, the new database appears as the renamed version of the old database.
Here, we named our new database as “new_linuhintdb.” Note that we are using “mysqladmin” in this case, not the “mysqldump”.
Verify that the new database is created. For that, open the MySQL shell and use the “show databases” command. We can see that we have our database appearing on the list.
With the new database in place, we must restore the backup that we created earlier to the newly created database. Use the following command and replace the database name appropriately to match your case:
Open the MySQL client and select the new database that you created. Next, use the “show tables” command to verify that the new database contains the same tables as the old database. That way, you successfully rename the old database by creating a new one.
You can delete the old databases using the “drop” command to leave the new database that contains the backup. That’s how you rename a database in MySQL from the command line.
Conclusion
This post explained how you can rename a database in MySQL from the command line. We created a sample database and detailed the steps to rename it. Hopefully, you followed along and renamed your database to meet your goal. That’s it!
