Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What is the rename command in MySQL?
2.1.
Syntax of Rename Database in MySQL
3.
How to rename a database in MySQL?
4.
Rename Database using “mysqldump” Command
5.
Rename MySQL Database From Command Line
6.
Renaming Tables with InnoDB
7.
Rename Tables in MySQL
7.1.
Rename a Table using RENAME Statement
7.2.
Rename a Table using ALTER Statement
7.3.
Rename a Table using CREATE TABLE Statement
8.
Rename a MySQL Database Using cPanel
9.
Examples of Rename Database in SQL
10.
Frequently Asked Questions
10.1.
Can you rename a database in MySQL? 
10.2.
Can we alter databases in MySQL? 
10.3.
How do I find the database name in MySQL?
10.4.
Can renaming a Database affect the data or application that uses it?
10.5.
In which of the MySQL management tools renaming a Database is possible?
11.
Conclusion
Last Updated: May 5, 2024
Easy

Rename Database in MySQL

Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

Hello Ninja, I hope you are doing great. Do you know how to rename a Database in MySQL? If not, don't worry. We are here to enrich your knowledge and clear all your doubts. Sometimes we have multiple Databases with similar names, so renaming them makes it easy for us to differentiate them and avoid confusion. In this article, we will discuss the renaming procedure in detail.

Rename Database in MySQL

This article covers the in depth steps required to Rename a Database in MySQL along with codes, outputs, and images showing all the queries and commands used to rename a Database. We will also cover the different ways to rename a Table in MySQL.

What is the rename command in MySQL?

We can use the SQL RENAME DATABASE statement to change the database name. 

Syntax of Rename Database in MySQL

The syntax for renaming a database in MySQL is as follows:

RENAME DATABASE oldDB TO newDB;


This statement allows you to change the name of an existing database from oldDB to newDB.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

How to rename a database in MySQL?

We can use the RENAME DATABASE statement to rename a database in MySQL. Suppose we have a database called ninjas, and we want to change the name of the database to coding_ninjas. So, we can write the following command:

RENAME DATABASE ninjas TO coding_ninjas;

 

The provided command changes the database name from "ninjas" to "coding_ninjas." However, it's crucial to verify the existence of a database named "ninjas" on the database server before executing this command. Otherwise, an error will be generated as the output. This feature of renaming a database was introduced in MySQL 5.1.7 but was later deemed disruptive due to the potential for database content leaks. As a result, it was removed in MySQL 5.1.23 for security reasons. Alternative methods are available for achieving this objective.

Rename Database using “mysqldump” Command

The “mysqldump” Command is used to create a backup for an existing Database. We copy the content of an existing database into an SQL file. After that we create an empty Database with the target name. At last, we copy the content of the SQL file into the new Database with the target name and drop the existing Database with the old name that needs to be changed

Now, let’s see some steps that need to be followed for renaming the Database using the “mysqldump” command. 

Step 1

First, copy the content of an existing Database into the newly created SQL file. The command below shows an example to do this operation. Here the “MyDatabase” is the existing Database which should be renamed, and the “CopiedFile.sql” is the SQL file.

Command: 

sudo mysqldump -h localhost -u root -p MyDatabase > CopiedFile.sql


Step 2

Our next step is to create a new Database with the target name. The image below shows all the Databases before creating a new Database and after creating a new Database. Here the newly created Database is “MynewDatabase”.

Databases before creating the new Database and after creating the new Database.

Step 3

We aim to have every old database table in the new Database. The image below shows all the tables of the old Database that need to be copied into the new Database.

Tables in an old Database

Step 4

Now, copy the content of the SQL file into the new Database. The command below shows how to do this operation. Here,  “MynewDatabase” is the new Database and the “CopiedFile” is the SQL file containing all the content of the old Database.

Command

sudo -h localhost -u root -p MynewDatabase < CopiedFile.sql


Step 5

Now, you can see that all the old Database tables are copied into the new One. 

Tables in a new Database


Step 6

Our last step is to delete the old existing Database. The image below shows all the Databases before deleting the previous Database and after Deleting the old Database.

Delete the old Database

Rename MySQL Database From Command Line

To rename a MySQL database from the command line, you can follow these steps:

  1. Login to MySQL: Use the command line interface or a tool like MySQL Workbench to log in to your MySQL server using appropriate credentials.
  2. Select Database: Before renaming, ensure you are not currently using the database you intend to rename. If you are, switch to a different database using the command USE other_database_name;.
  3. Rename Database: Execute the SQL command RENAME DATABASE old_database_name TO new_database_name; to rename the database. Replace old_database_name with the current name of the database and new_database_name with the desired new name.
  4. Confirm Rename: Verify that the database has been renamed by listing the databases using the command SHOW DATABASES;.

Renaming Tables with InnoDB

When renaming tables with InnoDB storage engine in MySQL, you can use the ALTER TABLE statement:

ALTER TABLE old_table_name RENAME TO new_table_name;

 

Replace old_table_name with the current name of the table and new_table_name with the desired new name.

This operation is fast and non-blocking, as it simply updates metadata. However, be cautious when renaming tables in production environments to avoid disrupting ongoing operations or conflicting with application logic.

Rename Tables in MySQL

Rename a Table using RENAME Statement

The syntax of RENAME Statement is :  RENAME TABLE [Target Table] to [Target Name].
The image below shows an example to change the Table name. Here, the target table is “Items” and we have changed its name to “Item_Store”.

Rename a Table using RENAME Statement

Rename a Table using ALTER Statement

The syntax of ALTER Statement: ALTER TABLE [old_table_name] RENAME TO [new_table_name]. The image below shows an example to change the Table name. Here the target table is “Keyboards” and we have changed its name to “Desktops”.

Rename a Table using ALTER Statement

Rename a Table using CREATE TABLE Statement

Here, we are creating a new Table and pasting data from the old table. After that we are Deleting the table using DROP Statement.
In the image below we have changed the “Student” table to “College_Students”.

Rename a Table using CREATE TABLE Statement

Rename a MySQL Database Using cPanel

To rename a MySQL database using cPanel involves several steps:

  1. Log in to cPanel
     
  2. Access "MySQL Databases"
     
  3. Create a new database with the desired name
     
  4. Add a user and grant privileges
     
  5. Assign the user to the new database
     
  6. Backup and transfer data from the old to the new database
     
  7. Update configurations referencing the old name
     
  8. Reload your database to check the name of the database
     

These steps may vary by hosting provider and cPanel version.

Learn more, Nmap commands

Examples of Rename Database in SQL

-- Rename a database from 'old_database' to 'new_database'
RENAME DATABASE employeeDB TO updated_employeeDB;

In this example, the SQL command RENAME DATABASE is used to rename the database named 'employeeDB' to 'updated_employeeDB'. This operation changes the name of the database without affecting its contents. It's important to note that this operation may require appropriate permissions and should be executed with caution, especially in production environments.

Frequently Asked Questions

Can you rename a database in MySQL? 

Yes, you can rename a database in MySQL using the RENAME DATABASE SQL statement.

Can we alter databases in MySQL? 

No, you cannot directly alter databases in MySQL. To modify a database's structure or properties, you typically create a new one and migrate data if necessary.

How do I find the database name in MySQL?

You can find the current database name in MySQL using the SELECT DATABASE(); SQL statement.

Can renaming a Database affect the data or application that uses it?

Yes, renaming a Database can affect the data or application that uses it. This is because Database names are often referenced in SQL queries, scripts and application code. If the Database name is changed without updating these references, the application may not be able to access the data.

In which of the MySQL management tools renaming a Database is possible?

Renaming a Database is possible in MySQL management tools such as phpMyAdmin, MySQL Workbench, Navicat and HeidiSQL. These tools may require some manual updates to the references of the old Database name in the application code.

Refer to know about : Update Query in MySQL

Conclusion

This blog covers the steps to rename a Database with specific queries and outputs. We also discussed the renaming procedure of tables in different ways.
We hope you enjoyed the article and gained insight into this topic.
You can refer to Rename a Column to learn more about this topic.
Head over to our practice platform Coding Ninjas Studio to practice top problems, attempt mock tests, read interview experiences and interview bundles, follow guided paths for placement preparations, and much more!!
Happy Learning Ninja!

Previous article
ON DELETE CASCADE in MySql
Next article
RLIKE Operator in MySQL
Live masterclass