How to rename a database in MySQL?

Previously, MySQL allowed you to rename a database by running a simple command:

RENAME DATABASE db_name TO new_db_name;

It was added to MySQL 5.1.7 but was found to do more harm than good, because the use of this statement could result in the loss of database contents, which is why it was removed.

So there’s an easy way to rename a database in MySQL is to create a new empty database, then rename each table in turn into the new database:

RENAME TABLE db_name.table TO new_db_name.table;

Note that this command does not work for views, so you have to drop and create view instead.

So to do it properly, you have to dump the old database, create a new database and import the dump file into the new database, then drop the old database.

Dumping using `mysqldump`

First, dump the old database:

$ mysqldump -u username -ppassword -R db_name > db_name.sql

Note that there’s no space between p and password. The -R flag is to make sure stored procedures and functions are included in the dump file.

Then create a new database:

$ mysqladmin -u username -ppassword create new_db_name

And lastly, import the dump file to the new database:

$ mysql -u username -ppassword new_db_name < db_name.sql

Dumping using TablePlus

In TablePlus, you have two options to backup and restore a MySQL database:

1. Using Import & Export Wizard

To export database:

Connect to the old database
Select tables
Choose File > Export… (Or right click > Export…)
Choose tab SQL and hit Export

export MySQL database

To import SQL Dump file:

Connect to the new database
Choose File > Import > From SQL Dump…
Select Dump file and hit Import

import MySQL database

2. Using Backup & Restore feature

To backup a database:

Choose File > Backup… (Or click backup button from the welcome screen)
Select connection
Select database
Add options if you want, and hit Start backup…

backup MySQL database

To restore a database:

Choose File > Restore… (Or click restore button from the welcome screen)
Select connection
Select database
Add options if you want, and hit Start restore…
Select dump file

Restore MySQL database

Then wait for the restore process to be done.

Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more. 

Dumping using mysqldump

Dumping using TablePlus

Dumping using `mysqldump`