Rename a MySQL Database

I recently needed to rename a whole bunch of MySQL databases so I created a script to do it. I hope others find it as useful as I do.

#!/bin/sh
mysqladmin -u$1 -p$2 create $4
mysqldump -u$1 -p$2 $3 | mysql -u$1 -p$2 $4
echo "update mysql.db set Db = '$4' where Db = '$3';" | mysql -u$1 -p$2
echo "update mysql.tables_priv set Db = '$4' where Db = '$3';" | mysql -u$1 -p$2
echo "update mysql.columns_priv set Db = '$4' where Db = '$3';" | mysql -u$1 -p$2
echo "flush privileges;" | mysql -u$1 -p$2
mysqladmin -u$1 -p$2 drop $3

The script should be called as follows...

renamedb.sh [username] [password] [olddb] [newdb]

The script performs the following steps...

1. Create the new database
2. Copy the structure and data
3. Transfer all privileges
4. Flush the privileges
5. Drop the old database (will ask for confirmation)

Note that the script will effectively knock out any app/website that uses it until the database they point to is modified accordingly. Enjoy.

This is Stut.net Copyright © 2002-2012 Stuart Dallas about.me