How to Rename Database in MySQL

March 20, 2024

Introduction

As of version 5.1.23, MySQL does not feature a direct command to rename a database. The RENAME DATABASE command was removed due to its flawed design, which increased the chance of accidental data loss.

The removal of the command made renaming MySQL databases more complicated, but multiple methods to perform this action still exist.

This guide provides four methods to rename a MySQL database.

How to rename a database in MySQL.

Prerequisites

  • Command-line access to the system.
  • MySQL installed (learn how to install MySQL on Ubuntu and Windows).
  • Access to a MySQL user with sufficient access privileges (e.g., root user).
  • The cPanel server management software installed (optional).
  • phpMyAdmin installed (optional).

Note: The RENAME DATABASE command is still available in the MySQL versions between 5.1.7 and 5.1.23. However, updating MySQL and using a renaming method listed in this guide is strongly recommended from a security perspective.

How to Rename MySQL Database

Depending on the type of MySQL installation and the configuration of the server, MySQL databases can be renamed using the following methods:

The following sections describe the procedure for each method.

Rename MySQL Database via cPanel

cPanel interface offers the simplest way to rename a MySQL database. Follow the steps below to rename a database in cPanel:

1. Log in to cPanel.

2. In the Databases section, click MySQL Databases.

Location of the MySQL Databases item in the Databases section of cPanel.

3. Select the Rename action in the Actions column for the relevant database.

Location of the Rename action in the Actions column of the Current Databases list.

4. Type the new database name and click Proceed.

Renaming a database in cPanel.

Renaming may take a long time if a database is large.

Rename MySQL Database by Dumping & Reimporting

On servers that do not support graphical interfaces, use the command line to:

  • Export database content using mysqldump.
  • Create a new database with mysqladmin.
  • Move the old database content to the new database.
  • Drop the old database.

Proceed with the following steps to rename a MySQL database using CLI:

1. Log into the server.

2. Create a dump file for the old database:

mysqldump -u [username] -p[password] -R [database] > [database].sql

Replace [username] and [password] with the actual credentials for the database, and replace [database] with the name of the database. There should be no space between -p and the password. The -R flag indicates that the dump file should retain all stored procedures and functions.

For example, to create a dump file for a database named testDB with the user admin and the password test1234, type:

mysqldump -u admin -ptest1234 -R testDB > testDB.sql

3. Create a new blank database by using the mysqladmin command:

mysqladmin -u [username] -p[password] create [database]

4. Import the dump file into the new database:

mysql -u [username] -p[password] [new-database] < [old-database].sql

5. Optionally, remove the old MySQL database by dropping it:

mysqladmin -u [username] -p[password] drop [old-database]
Dropping an old database after moving the tables to a new one.

Rename MySQL Database via InnoDB (RENAME TABLE)

The InnoDB storage engine is included in all versions of MySQL since MySQL 5.5. Using InnoDB, admins can rename each table in a database to make it part of another database, effectively renaming the old database.

Proceed with the steps below to rename a MySQL database with InnoDB using the manual method or an automation script.

Manually Rename MySQL Database

Databases with fewer tables can be renamed using the manual method described below:

1. Create an empty new database:

mysqladmin -u [username] -p[password] create [new-database]

Replace [username] and [password] with user credentials and [new-database] with a name for the new database.

2. Log into the MySQL shell:

mysql -u [username] -p[password]

3. Use the RENAME TABLE command to change the table name:

RENAME TABLE [old-database].[table-name] TO [new-database].[table-name];

Replace [table-name] with the name of a table in the existing [old-database] database. Repeat this action for each table in a database. For example, move the table_test table from testDB to newDB by typing:

RENAME TABLE testDB.table_test TO newDB.table_test;
Renaming tables in InnoDB.

Rename Database Using Shell Script

For databases with many tables, execute the script below in the server OS shell to rename all the tables with a single command.

for table in `mysql -u root -p[password] -s -N -e "use [old-database];show tables;"`; do mysql -u root -p[password] -s -N -e "use [old-database];rename table [old-database].$table to [new-database].$table;"; done;

Replace [username] and [password] with user credentials and [old-database] and [new-database] with the relevant database names. The script cycles through each table in the database and renames it.

Rename MySQL Database in phpMyAdmin

If you have access to phpMyAdmin, you can quickly rename a database by following the simple procedure described below:

1. Start phpMyAdmin. In cPanel, the tool is located in the Databases section.

Location of phpMyAdmin in cPanel.

2. Select a database from the menu on the left side of the screen.

3. Click the Operations tab in the tab menu at the top of the screen.

4. Provide the new database name in the Rename database to field.

5. Select the Go button.

phpMyAdmin interface for renaming databases.

phpMyAdmin changes the name of the database automatically.

Conclusion

After following this guide, you should know how to rename a MySQL database. The article covered four popular renaming methods: cPanel, the server's CLI, InnoDB, and phpMyAdmin.

Before making any significant changes to a database, create a database backup. Read How to Back Up and Restore a MySQL Database to learn more about this procedure in MySQL.

Was this article helpful?
YesNo
Dejan Tucakov
Dejan is the Head of Content at phoenixNAP with over 8 years of experience in Web publishing and technical writing. Prior to joining PNAP, he was Chief Editor of several websites striving to advocate for emerging technologies. He is dedicated to simplifying complex notions and providing meaningful insight into data center and cloud technology.
Next you should read
How to Rename a Column in MySQL
March 30, 2020

This article offers two slick methods to rename a column in a MySQL table. Use ALTER TABLE command combined ...
Read more
Install and Get Started with MySQL Workbench on Ubuntu 18.04
March 20, 2024

Workbench is a visual tool for managing MySQL databases. Its graphical interface allows administrators and ...
Read more
How To Show a List of All Databases in MySQL
October 13, 2022

With Structured Query Language (SQL), you can easily access and manage content in all your databases. This ...
Read more
How to Install MariaDB on CentOS 7
July 14, 2019

For CentOS 7 users, MariaDB is the default database system as it replaced MySQL as the default database ...
Read more