Introduction

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.

change name or renaming a database in MySQL

Prerequisites

  • 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 into cPanel

2. In the Databases section, click MySQL Databases

3.  A new page will open > scroll down to the database you want to rename > in the Actions column, click the Rename link

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 –R ExistingDatabase > ExportDB.sql

Enter your password when prompted. Replace UserName with the actual username for the database, and replace ExistingDatabase with the exact name of the database you’re changing.

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

3. Create a new blank database by entering the following:

mysql –u UserName –p “CREATE DATABASE NewDatabase”
mysql –u UserName –p “CREATE DATABASE [IF NOT EXISTS] NewDatabase”

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

mysql –u UserName –p NewDatabase < ExportDB.sql

5. Delete the old database name (optional):

mysql –u UserName –p –e “DROP DATABASE “ExistingDatabase”

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


Note: Make sure the database name isn’t already in use. If you’re not sure, add [IF NOT EXISTS] before the new database name:


Renaming Tables with InnoDB

The InnoDB storage engine is a feature included in current versions of MySQL. 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 DATABASE NewDatabase”

Replace UserName with the database username, and PassWord with the password for that account. Replace NewDatabase with any name you’d like.

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

mysql –u UserName –p"PassWord" ExistingDatabase –sNe 
'show tables' | while read table; do mysql –u UserName –p
"PassWord" –sNe "RENAME TABLE ExistingDatabase.$table TO 
NewDatabase.$table"; done

This script cycles through each table in the database and renames it. The PassWord is included in the script to save you 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

Use the RENAME TABLE command to rename a table:

RENAME TABLE ExistingDatabase.Table1 TO NewDatabase.Table1;

Replace Table1 with the name of a table in the ExistingDatabase. If you have more than one table in this database, you’ll need to repeat this action for each table.

Conclusion

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

If you’re 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.