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;
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
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 -pid@098765 create linuxshelltips_new_db
Where id@098765 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 -pid@098765 linuxshelltips_db > linuxshelltips_db.sql
Lastly, dump the old db (linuxshelltips_db) files into the new database (linuxshelltips_new_db).
$ sudo mysql -u root -pid@098765 linuxshelltips_new_db < linuxshelltips_db.sql
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 -pid@098765 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;
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.
