How to change schema name in MySQL

How to rename a database in MySQL is detail demo that explains how you can rename Database Name or Schema Name in MySQL Server. MySQL workbench does not really provide a straight forward way to Rename the Database/ Schema name in MySQL Server. 

To rename Database or Schema in MySQL server, first of all you need to create a new database/ Schema that you can do by using below command

Create database/schema SchemaName

Once you have the database ready, now you can move the tables from old database to new database by using blow statement.

Rename Table oldDBName.TableName to NewDatabaseName.TableName

Repeate above for all the tables in database, Then you can remove the old database by using below statement

Drop database/Schema DatabaseName

MySQL Workbench Tutorial - How to Rename Database / Schema in MySQL 

Eventually, it appears necessary to change the name of a database in MySQL. For that purpose, we used to apply a simple RENAME DATABASE command that existed in earlier versions of MySQL. However, it became no longer available in the newer versions as it posed risks. The reason was that the RENAME DATABASE query could result in the loss of essential database contents, which clearly was undesirable.

Renaming Tables Method

There is a way to perform this task quite easily. In fact, MySQL version 5.5 (and later) has InnoDB Storage Engine as the default one, which can come in handy. 
In a nutshell, we can apply the RENAME TABLE command within a MySQL prompt so as to alter the database name of a particular table while leaving the table name unchanged. But to do so, we first need to create a new database with the following mysqladmin shell command:

$ mysqladmin -u username -p"password" create newDbname

Now that we have created an empty database, we need to move the tables one by one from the former database into the newly created database with the following command:

RENAME TABLE oldDbname.table TO newDbname.table;

As you see, the query allows us to move the tables only one at a time, which isn’t very practical for large databases. Besides, the RENAME TABLE command doesn’t work for views and triggers. Instead of running the above-mentioned statement, we would have to drop and create them again.

Dumping Method

Another way to rename a database in MySQL would be to use the mysqldump shell command. This way, we can create a dumped copy of the database and import the whole database content into the new database. After that, we can drop the old database if that’s necessary.

To start with, we need to dump the old database by running the following command within your shell prompt:

$ mysqldump -u username -p"password" -R oldDbname > oldDbname.sql

The command creates a physical backup that has all database data along with stored procedures and functions.

Following that, we need to use the above-mentioned command to create a new database:

$ mysqladmin -u username -p"password" create newDbname

Finally, we need to import the dump file created in the first step to the new database:

$ mysql -u username -p"password" newDbname < oldDbname.sql

Even though this method appears to be straightforward, it may take considerable time when we deal with a larger database. So, it still leaves much to be desired.

For these reasons, we suggest having a look at the ways to rename a MySQL database more efficiently and conveniently. To illustrate them, we are going to use dbForge Studio for MySQL, an IDE that provides powerful database development, management, and administration capabilities. With its help, we can rename a database in three simple yet effective methods, namely through backup and restore, schema and data comparison, and copy database method.

Backup and Restore Method with dbForge Studio for MySQL

The essence of this method is to create a backup copy of the database and then to restore the database with a different name.

1. On the Database menu, select Backup and Restore and click Backup Database.

2. In the Database Backup Wizard, specify the necessary options (Connection, Database, Path, Output filename) and proceed by clicking Next.

3. Select Include all objects for the backup.

4. Specify the detailed backup options if necessary and click the Backup button.

5. Click Finish to complete the backup process.

6. Now, let’s restore the database and give it a new name. So, select Restore Database on the Database menu.

7. In the Database Restore Wizard, specify the new database name and the backup file destination path.

8. Click Restore and upon the completion of the process, click Finish.

9. Congrats, you have successfully renamed your database in a few simple steps!

Schema and Data Compare Method with dbForge Studio for MySQL

Another option we can use to effectively change the name of the database in MySQL is to use the schema and data comparison functionality of the dbForge Studio for MySQL tool.

The logic behind this process is that we first need to create a new MySQL database. Following that, we can use the schema and data comparison features to compare and synchronize the structure and data of an older database to a newly created database.

1. On the Database menu, select New Database.

2. Enter the Name of a database and click Apply Changes.

3. On the Comparison menu, click New Schema Comparison.

4. Specify the names of the databases to be compared. Select the older database as Source and the newly created database as Target. Click Compare to initiate schema comparison.

5. As a result, you can see the objects that exist only in the Source database since our newly created database is empty. Click Green Arrow to start the schema synchronization process.

6. In Schema Synchronization Wizard, select Execute the script directly against the target database and click Synchronize.

7. If we refresh the schema comparison results, we will see that the schemas of the two databases are completely identical.

8. After we have synchronized the schemas, we need to synchronize the data as well. Similarly to the previous steps, click New Data Comparison on the Comparison menu. In the Data Comparison Wizard that opens, set Source and Target for comparison.

9. Click Compare to initiate the data comparison process. Analyze the differences and click Green Arrow to synchronize the data.

10. In the Data Synchronization window, select  Execute the script directly against the target database and click Synchronize.

11. You now have the same SQL database but with a different name. You can now drop the database with an obsolete name.

Copy Database Method with dbForge Studio for MySQL

Probably, the easiest and fastest way to alter the name of a MySQL database is to apply the Copy Databases tool that is a part of dbForge Studio for MySQL.

1. On the Database menu, select Copy Databases.

2. In the Wizard, specify the server name in Source and Target. Select the database you want to copy from the Source column. Then, choose a Target database. Note that the names from the Source column are duplicated to the Target column. You can specify a new name for the database in Target, thus renaming the copy. Select the Include Data checkbox to copy the MySQL database along with its data. Click Green Arrow to start copying.

Once the database copying is complete, you will see a new database in the Database Explorer window. You can now drop the duplicate database.

As you see, the process of renaming a database within dbForge Studio for MySQL doesn’t involve any difficulties as it is accomplished via an intuitive interface of the GUI tool. You can choose from the three options to change the name of your MySQL database according to your goals and project requirements.

Apart from that, we cannot but mention that MySQL Studio delivers extensive Refactoring functionality that will help you rename columns, tables, and views while preserving database integrity.

Can I rename schema in MySQL?

How do you rename a schema?

How do I rename a database schema?

Why can't I rename schema in MySQL Workbench?