Why do I need this?
In mysql 5.5 the RENAME DATABASE functionality went away, but the manual does show that RENAME TABLE works:
RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
While is is good, it’s a pain when there lots of tables, and leaves lots of room for typos and human error. The best way to guard against this is scripting.
I like to do this as a multi part process, but that gives me time to verify everything along the way (I’m paranoid about my databases).
Part 1: Create the new database
You need to create a database to rename the tables into:
mysql> CREATE DATABASE newdb;
Part 2 – getting the list of tables
From the unix command line you’ll have to tell mysql to display the tables. I pipe a command into mysql and use head -n -1 to show everything except for the first line.
echo "show tables;"|mysql --defaults-file=/etc/mysql/credentials.cnf dbname|head -n -1 >~/tables.txt
Now review your tables.txt to make sure that you have the list of tables from the database. If you are satisfied that this worked, then it time for part 3.
Part 3: Creating the script:
This is as simple as:
cat ~/tables.txt |xargs -L 1|awk '{print "RENAME TABLE dbname."$1" TO newdb."$1";"}' >~/rename.txt
I’m using xargs to enforce one table per line and awk to do the formatting.
Part 4: Performing the rename
Now, look at ~/rename.txt. You will see that this generated a series of mysql rename commands, for example:
RENAME TABLE dbname.settings TO newdb.settings;
Now in MySQL make sure you have newdb created, and you can then either paste the contents of rename.txt to your mysql or if you want you can pipe it into mysql:
cat ~/rename.txt|mysql --defaults-file=/etc/mysql/credentials.cnf
You’ll now have moved all your tables from dbname to newdb.
Now go do the other housekeeping, such as granting access to newdb and locking it down properly.