Renaming database schema in MySQL

One of the routine tasks for a DBA is MySQL renaming database schemas, and as such MySQL added a command to carry out that purpose called “RENAME DATABASE <database_name>”. However, this command just made it through a few minor releases before being discontinued (from MySQL 5.1.7 to 5.1.23). Here’s a link to the reference manual regarding the command http://dev.mysql.com/doc/refman/5.1/en/rename-database.html. Vadim wrote a MySQL Performance Blog post about this a few years ago where he mentions the dangerous nature of this command – that post was appropriately headlined, “Dangerous Command.” Today we will see what are the ways in which a database schema can be renamed and which of them is the quickest.

How to Rename MySQL Databases

Method 1: A well-known method for renaming database schema is by dumping the schema using Mysqldump and restoring it in another schema, and then dropping the old schema (if needed).

Although the above method is easy, it is time and space consuming. What if the schema is more than a 100GB? There are methods where you can pipe the above commands together to save on space, however, it will not save time.

To remedy such situations, there is another quick method to rename schemas, however, some care must be taken while doing it.

Method 2: MySQL has a very good feature for renaming tables that even works across different schemas. This rename operation is atomic and no one else can access the table while it is being renamed. This takes a short time to complete since changing a table’s name or its schema is only a metadata change. Here is the procedural approach at doing the rename:

• a) Create the new database schema with the desired name.
• b) Rename the tables from old schema to the new schema, using MySQL’s “RENAME TABLE” command.
• c) Drop the old database schema.

If there are views, triggers, functions, stored procedures in the schema, those will need to be recreated too. MySQL’s “RENAME TABLE” fails if there are triggers exists on the tables. To remedy this we can do the following things :

1) Dump the triggers, events and stored routines in a separate file. This is done using -E, -R flags (in addition to -t -d which dumps the triggers) to the mysqldump command. Once triggers are dumped, we will need to drop them from the schema, for RENAME TABLE command to work.

2) Generate a list of only “BASE” tables. These can be found using a query on information_schema.TABLES table.

3) Dump the views in an out file. Views can be found using a query on the same information_schema.TABLES table.

4) Drop the triggers on the current tables in the old_schema.

5) Restore the above dump files once all the “Base” tables found in step #2 are renamed.

&nbsp;

Intricacies with above methods :

• We may need to update the GRANTS for users such that they match the correct schema_name. These could be fixed with a simple UPDATE on mysql.columns_priv, mysql.procs_priv, mysql.tables_priv, mysql.db tables updating the old_schema name to new_schema and calling “Flush privileges;”.

Although “method 2” seems a bit more complicated than the “method 1”, this is totally scriptable. A simple bash script to carry out the above steps in proper sequence can help you save space and time while renaming database schemas next time.

We on the Percona Remote DBA team have written a script called “rename_db” that works in the following way :

To demonstrate the use of this script, we used a sample schema “emp”, created test triggers, stored routines on that schema. We will try to rename the database schema using the script, which takes some seconds to complete as opposed to time-consuming dump/restore method.

As you can see in the above output the database schema “emp” was renamed to “emp_test” in less than a second.

Lastly, we are happy to share the script we used above for “method 2”.

Percona and MySQL work better together.