How to Rename MySQL Database Name in Linux

Renaming/altering the name of a MySQL database was a no-brainer in the past as all it took was a simple command prefix for the database name to be successfully changed. You just needed to execute the command:

> RENAME DATABASE name_of_your_mysql_database; 

However, the use of the above command was a reserved privilege for older MySQL RDBMS versions.

The deprecation of the above MySQL query as a way of changing the database’s original name came into fruition as a means of coping with viable security risks.

Therefore, this article is here to provide some light at the end of this tunnel through other alternative means of changing the MySQL database name without posing a database security risk.

Prerequisites

  • Have sudoer/root user privileges on the Linux system distribution you are using as you might require it to access the MySQL database like the case of the Ubuntu Linux distribution.
  • Have MySQL/MariaDB RDBMS installed and correctly configured with root database user access on the Linux system you are using.

Creating New MySQL Database

This approach is recommended for relatively small databases. Using the mysqldump shell command, an entire MySQL database dumped copy can be created as a backup copy.

Afterward, a new database with the required name is created, and then the initially created database backup copy is restored to this new database. The first step is to log in to your MySQL shell and identify the database you need to backup.

$ sudo mysql -u root -p
> SHOW DATABASES;
Check MySQL Databases in Linux
Check MySQL Databases in Linux

For demonstration purposes, we will go with the linuxshelltips_new_db database, which will replace the existing linuxshelltips_db database.

> CREATE DATABASE linuxshelltips_new_db;
> exit
Create MySQL Databases in Linux
Create MySQL Databases in Linux

Alternatively, you could create a new MySQL database using the mysqladmin command if you do not want to directly use the database shell.

$ sudo mysqladmin -u root [email protected] create linuxshelltips_new_db

Where [email protected] is the root database user password and linuxshelltips_new_db is the name of the new database.

Backup and Rename MySQL Database

The command syntax for backing up a MySQL database using the mysqldump command is as follows:

$ sudo mysqldump -u [db_username] -p[db_password] [database_name] > [generated_db_backup.sql]

Therefore, create a backup copy of the database whose name you wish to change.

$ sudo mysqldump -u root [email protected] linuxshelltips_db > linuxshelltips_db.sql
Backup MySQL Database in Linux
Backup MySQL Database in Linux

Lastly, dump the old db (linuxshelltips_db) files into the new database (linuxshelltips_new_db).

$ sudo mysql -u root [email protected] linuxshelltips_new_db < linuxshelltips_db.sql
Restore MySQL Dump to New Database
Restore MySQL Dump to New Database

Renaming Tables in MySQL Database

This approach is only viable for later MySQL versions that use InnoDB as their default storage engine.

For instance, supposing we want to rename the database linuxshelltips_new_db to linuxshelltips_revised_db with an existing database table called tasks, we would follow the following approaches.

Firstly, create the linuxshelltips_revised_db database.

$ sudo mysqladmin -u root [email protected] create linuxshelltips_revised_db

Log into the database shell.

$ sudo mysql -u root -p 

Now, the command to rename the linuxshelltips_new_db database to linuxshelltips_revised_db database while preserving the tasks database table is as follows:

> RENAME TABLE linuxshelltips_new_db.tasks TO linuxshelltips_revised_db.tasks;
Rename MySQL Database Tables
Rename MySQL Database Tables

With the above command, we have managed to change the database name associated with a specific database table name.

You are now familiar with two flexible approaches to renaming the MySQL database name on a Linux operating system environment.

Got something to say? Join the discussion.

Have a question or suggestion? Please leave a comment to start the discussion. Please keep in mind that all comments are moderated and your email address will NOT be published.