In this article, we will explain how to rename a database using SQL.
1. Introduction
In this article, we will look at one of the Data Definition Language or data description language (DDL) concepts called Rename the Database. We will see how we do this in the MySQL database.
1.1 What is Data definition language?
In SQL, Data definition language is a syntax for creating and modifying database objects like tables, schemas, views, and users. Common examples of DDL statements are
- CREATE – to create objects in the database
- ALTER – alters the structure of the database
- DROP – delete objects from the database
- TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
- COMMENT – add comments to the data dictionary
- RENAME – rename an object
2. Rename Database
As per SQL the syntax for renaming a database schema is as follows:
RENAME {DATABASE | SCHEMA} db_name TO new_db_name; However, as of now, most RDBMS systems do not support this syntax. Most RDBMS support the ALTER syntax instead which has renaming as one of the options available and some RDBMS like MySQL does not support it at all.
2.1 Rename or Alter syntax
Most RDBMS like SQL Server, PostgreSQL support the Alter command for renaming a Database. Depending upon the RDBMS, the syntax is modified.
2.1.1 SQL SERVER
The SQL server supports the Alter Database command. This command has multiple options available out of which the Database name is one. The syntax is as follows
ALTER DATABASE [Database|Schema] MODIFY NAME = [New database|schema name]
The details of the Alter command in the SQL Server are available here.
2.1.2 Postgre SQL
Postgre SQL also supports the ALTER Database command which has rename as one of its options. The syntax is as follows
ALTER DATABASE [name] RENAME to [new_name];
The Complete details of the ALTER command in POSTGRE SQL are available here.
2.1.3 MySQL
The RENAME database command was introduced in the MYSQL version 5.1. However, it is now obsolete and not supported. The syntax of the Rename Database command was as follows:
RENAME {DATABASE|SCHEMA} db_name TO new_db_name;The reason it is not supported as per MySQL is
This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23. It was intended to enable upgrading pre-5.1 databases to use the encoding implemented in 5.1 for mapping database names to database directory names. However, the use of this statement could result in a loss of database contents, which is why it was removed. Do not use RENAME DATABASE in earlier versions in which it is present. To perform the task of upgrading database names with the new encoding, use ALTER DATABASE db_name UPGRADE DATA DIRECTORY NAME instead.
3. So, how do we Rename a Database in MySQL?
There is no straightforward command to do this. However, there a couple of ways in which this can be achieved.
3.1 Rename MySQL Database from Command Line
To rename a database in MySQL, the easiest way is to use the mysqldump functionality to generate a dump of the database, create a new database, and then import the generated dump into it. To see these steps in action, we have a database called “test_db” which holds employee data. This database we will rename to “empDB”.
The Steps to perform this are as follows
3.1.1 Step 1 : Create a dump of the to-be-renamed database
To generate the dump of the database, we use the sqldump utility. The documentation of the utility is available here.
- Open the command prompt. First, check if MYSQL is in the PATH or not. If not set the PATH to MYSQL Server executable path. To check if PATH is set or not, use the command:
mysql --version
- Next, check that the database which needs renaming is present in the list of databases. To do so, we can open a “SQL Command line client” and use the command
show databases;
- In the command prompt, generate the dump file for the database. The general syntax for this is
mysqldump –u [UserName] –p[Password] –R [DB_Name] > [DB_Name].sql
Here, we will replace
So, our syntax will be –
mysqldump -u root -p -R test_db > test_db.sql
This generates a dump file in the current folder.
3.1.2 Step 2: Create a new database
Using the mysqladmin utility we can create a new and empty database through the command line itself. The documentation is available here. The general syntax to do this is:
mysqladmin -u[UserName] -p[Password] create [newDbName]
So our syntax is
mysqladmin -u root -p create empDB
To verify that the database has been created successfully, in the command prompt, type
mysql -u root -p
Once triggered, it will prompt for a password. This will open the “MySQL” prompt. Then type the command
show databases;
In the output, we should see the new database created.
3.1.3 Step 3: Import the dump file in the new database
We use the following general syntax to import the dump file in the new database.
mysql -u [UserName] -p[Password] [newDbName] < [oldDbName].sql
here we replace
So, the final syntax is
mysql -u root -p empDB < test_db.sql
3.1.4 Step 4: Delete the old Database
This step is optional but is preferred .The syntax to drop the database is
mysqladmin -u [UserName] -p[Password] drop [oldDbName]
- UserName = root
- Password will be prompted once the command is triggered
- oldDbName = the renamed-database
So, the final syntax is
mysqladmin -u root -p drop test_db
3.2 Rename the Database using InnoDB
All versions of MYSQL 5.5 and above have the InnoDB storage engine. InnoDB is a general-purpose storage engine and can be used in a way to rename a database. The InnoDB documentation is available here.
Using InnoDB engine, we are not able to rename the Database directly. But it is easy to rename tables and transfer them to a new database without copying the entire contents of the tables. This is tantamount to renaming the database itself. The steps we follow are as follows
3.2.1 Step 1 : Create a new database
Before InnoDB can rename tables to another database, the database needs to be created. We can use the sqladmin utility used in the previous method to create a new Database. As an example, we will create the “employeesdb” database.
3.2.2 Step 2: Rename tables manually
We can just manually use the rename table command and move all the tables to the different schema. While doing this, Views cannot be renamed to the other schema. The Views need to be dropped and rebuilt in the other schema. To do this,
- First enter the mysql command line by the command
mysql -u root -p
Then we can check the tables are present in the empdb and also the employeesdb.
- Then rename individual tables, we use the command:
RENAME TABLE [OLD_DB_NAME].[Table_Name] to [NEW_DB_NAME].[Table_Name]
Here we replace:
The command we use is
RENAME TABLE empdb.employees to employeesdb.employees
This effectively renames the database.
4. Summary
In general, we saw 2 main ways of renaming the database in MySQL since there is no direct way in which we can do so. We can also use other ready-made utilities like cPanel and Percona which provide scripts to rename databases.
5. Download the Source Code
In this article, we explained how to rename a database using SQL, using MySQL RDBMS. Attached are all the commands we need to run in the command prompt.
You can download the full source code of this example here: SQL Rename Database
