How to Rename a MySQL Database

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.

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 in to cPanel.

2. In the Databases section, click MySQL Databases.

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

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]

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.

Conclusion

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.