Rename a MySQL database schema safely

This example uses a backup to rename a database in a MySQL instance.

It takes only 5 easy steps:

First copy the schema (db1) with mysqldump:

% mysqldump db1 > db1.sql

Create the a new schema (db2):

% mysqladmin CREATE db2

Insert the backup into the new schema:

% mysql db2 < db1.sql

Drop the old schema:

% mysqladmin drop db1

Now update the user rights:

mysql> UPDATE db SET Db = 'db2' WHERE Db = 'db1';
mysql> UPDATE tables_priv SET Db = 'db2' WHERE Db = 'db1';
mysql> UPDATE columns_priv SET Db = 'db2' WHERE Db = 'db1';
mysql> UPDATE host SET Db = 'db2' WHERE Db = 'db1';

Done! That was easy wasn’t it?

Related posts

Tags: , ,

Comments are closed.