Renaming database schema in MySQL

One of the routine tasks for a DBA is 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.

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).

Shell

1

2

3

4

[root@percona~]# mysqldump emp > emp.out

[root@percona~]# mysql -e "CREATE DATABASE employees;"

[root@percona~]# mysql employees < emp.out

[root@percona~]# mysql -e "DROP DATABASE emp;"

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 its 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 procedural approach at doing the rename:

a) Create the new database schema with the desired name.

b) Rename the tables from old schema to 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 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.

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

Shell

1

2

3

4

mysql>RENAME TABLE<old_schema>.table_nameTO<new_schema>.table_name;

...

$mysql<new_schema><views.out

$mysql<new_schema><stored_routines_triggers_events.out

Intricacies with above methods :

We may need to update the GRANTS for users such that they match the correct schema_name. These could 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.

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.

Shell

1

2

3

4

5

6

7

8

9

10

mysql>show databases;

+--------------------+

|Database|

+--------------------+

|information_schema|

|emp|

|mysql|

|performance_schema|

|test|

+--------------------+

Shell

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

[root@percona~]# time /tmp/rename_db localhost emp emp_test

create database emp_test DEFAULTCHARACTER SET latin1

drop trigger salary_trigger

rename table emp.__emp_newtoemp_test.__emp_new

rename table emp._emp_newtoemp_test._emp_new

rename table emp.departmentstoemp_test.departments

rename table emp.depttoemp_test.dept

rename table emp.dept_emptoemp_test.dept_emp

rename table emp.dept_managertoemp_test.dept_manager

rename table emp.emptoemp_test.emp

rename table emp.employeestoemp_test.employees

rename table emp.salaries_temptoemp_test.salaries_temp

rename table emp.titlestoemp_test.titles

loading views

loading triggers,routines andevents

Dropping database emp

real0m0.643s

user0m0.053s

sys0m0.131s

Shell

1

2

3

4

5

6

7

8

9

10

mysql>show databases;

+--------------------+

|Database|

+--------------------+

|information_schema|

|emp_test|

|mysql|

|performance_schema|

|test|

+--------------------+

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”.

Related

Akshay Suryawanshi joined Percona in March 2013. He began his role as a Support Engineer and then moved to the fast-growing Remote DBA team. Before joining Percona he worked as a MySQL DBA managing more than a dozen clients. He specializes in MySQL internals especially InnoDB.
Akshay has a Bachelor of Science in Information technology and is OCP certified in MySQL. He currently resides in Mumbai, India

Steven, I was curious about your question, so I tested the rename to a new database procedure above with both a shared tablespace and innodb_file_per_table. It works the same with both. I’m using 5.6.15.

I would like to add that if you are using Percona Cluster, changing the mysql.db table directly will not replicate to other cluster nodes. So the commands for modifying permissions needs to be run on all cluster nodes.

Also do not forget to put user/password for both [mysql] and [mysqldump] in a file first – otherwise the script cannot connect to database.

The only problem was the trigger dump cannot restored because it writes also the (source) database name into the dump:
….. TRIGGER accounts_before_insert BEFORE INSERT ON test_crm_at.accounts FOR EACH ROW BEGIN ….

And at restore this fails at the database with the new name.
I found no way to prevent the output of the database name in the mysqldump at line 28 ….

So I fix this with add following after line 28:
sed -i “s/$2\./$3\./g” /tmp/${2}_triggers${TIMESTAMP}.dump

It’s a little strange but I think with inclusion of the point in databasename. in the regex string, it ensures that it not renames other elements as the database name.

This script is almost perfect for me when I need to rename schemas from production snapshots to different names for non-production environments. However, there is one table in all of my schemas that consistently refuses to be renamed (I’ve run your script line by line to isolate it). This is what happens in the MySQL interactive client, and I don’t understand:

The target database is stock MySQL 5.5.33 on Amazon RDS, using innodb_file_per_table=ON. So I don’t have access to the base filesystem to see if the claimed files already exist (but given the refusal of arbitrary, never-used names, I’m pretty sure they don’t).

Here’s a script that can be run to get the command needed to rename all of the tables.

SET @oldSchemaName = ‘oldSchema’;
SET @newSchemaName = ‘newSchema’;

SET SESSION group_concat_max_len = 4294967295;

SELECT CONCAT(‘RENAME TABLE ‘, GROUP_CONCAT(mySchema.table_schema,’.',table_name, ' TO ‘,@newSchemaName,’.',table_name,' \n’),’;’) AS Statement
FROM information_schema.TABLES AS mySchema
WHERE mySchema.table_schema LIKE @oldSchemaName
GROUP BY mySchema.table_schema
;

It’s very simple in WAMP Server. It provide option to rename an existing database. However you can also rename database if you don’have server like WAMP, XAMP. First export you database data with structure and create new database. Import your data into new database and drop previous one.

Here is a version of the script with the fix for \G mentioned before applied, some corrections to schema names escaping characters, and a couple improvements for the messages shown (in my opinion). It works on my 5.5.47-0ubuntu0.12.04.1 version.

Also, does anybody know of a command for MySQL to not ask for credentials after a successful identification? Somehow like when you use sudo, that it doesn’t ask you for credentials for several minutes after the first time.