How to Rename a MySQL Database


As an administrator, you may need to change the name of a database. However, for security, the command to rename a database directly was removed in MySQL 5.1.23.

This guide provides three options to rename a MySQL database.

rename a mysql database


  • The cPanel server management software (optional)
  • An SSH login to the server, if working remotely
  • A user account with sudo or root privileges
  • Access to the command line/terminal window
  • A user account and password for the MySQL database

Rename a MySQL Database Using cPanel

Servers configured with cPanel offer the easiest way to rename a MySQL database.

1. Log in to cPanel.

2. In the Databases section, click MySQL Databases.

working with databases in cPanel

3.  A new page will open. Scroll down to the database you want to rename and select the Rename link under the Actions column.

renaming a database in cPanel

4. Type the new database name, then click Proceed.

Rename MySQL Database from Command Line

If you’re working on a server that doesn’t support cPanel, you’ll need to create a new database and import the data.

1. Log into the server, and open a command line / terminal window. (If you’re working remotely, connect to the server via SSH.)

2. Create a dump file for the database:

mysqldump –u [UserName] –p[Password] –R [DB_Name] > [DB_Name].sql

Replace [UserName] and [Password] with the actual credentials for the database, and replace [DB_Name] with the exact name of the database you’re changing. 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.

You may want to copy this file to a different location as a backup.

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

mysqladmin –u [UserName] –p[Pasword] create [New_DB_Name]
creating mysql databases

Note: Make sure the database name isn’t already in use.

4. Import the dump file into the new database you created:

mysql –u [UserName] –p[Password] [New_DB_Name] < [DB_Name].sql

5. Delete the old MySQL database name (optional):

mysqladmin -u [Username] –p[Password] drop [DB_Name]

It won’t hurt if you skip this step. However, it can help you keep a clean database environment.

Renaming Tables with InnoDB

The InnoDB storage engine is a feature included in all versions of MySQL since MySQL 5.5. It can be used to create a new database, then rename each table from the old database to the new database.

1. Start by creating a new database:

mysql –u [UserName] –p[Password] create [New_DB_Name]

Replace [UserName] with the database username, and [Password] with the password for that account. Replace [New_DB_Name] with any name you’d like.

2. Use a script to rename all the tables in the database:

mysql –u [UserName] –p[Password] [DB_Name] –sNe 
'show tables' | while read table; do mysql –u [UserName] –p
[PassWord] –sNe "RENAME TABLE [DB_Name].$table TO 
[New_DB_Name].$table"; done

The script above cycles through each table in the database and renames it. Provide your password in the script to avoid having to enter it for each cycle.

3. If you have a very small database, you can move the tables manually. This can be less intimidating than running a script, but time-consuming. Start by logging into the MySQL shell:

mysql –u [UserName] –p[Password]

Use the RENAME TABLE command to rename a table:

RENAME TABLE [DB_Name].[Table1] TO New_DB_Name.Table1;

Instead [Table1], type the name of a table in the existing [DB_Name] database. If you have more than one table in this database, you’ll need to repeat this action for each table.

rename a mysql table


By following this guide, you should now know how to rename a MySQL database.

When working with an older version of MySQL (5.1.7 and older), you may have the ability to use the RENAME DATABASE command. It is strongly recommended that you update your database for security and stability, and use the renaming methods in this guide.

Was this article helpful?
Dejan Tucakov
Dejan is the Head of Content at phoenixNAP with over 7 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
November 19, 2019

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
July 23, 2019

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
  • © 2021 Copyright phoenixNAP | Global IT Services. All Rights Reserved.