Renaming a MySQL database
September 13th, 2007 by Michael Otto
Say you want to rename a database in MySQL.
What you usually can’t do is “mysqladmin rename”, because that’s only available as of MySQL 5.1.7. If you have only MyISAM tables, you can just rename the folders that store the databases while the server is down. But that doesn’t work when you have at least one InnoDB table, because renaming folders wrecks the tablespace.
The key to the solution is that MySQL actually allows you to rename a table from one database into another. The rest comes easy:
We’re renaming databases a, b, c to new_a, new_b, new_c:
for i in a b c ; do mysqladmin create new_$i ; mysql -B $i -e "show tables" | tail +2 | perl -pe "s/^(.*)$/rename table $i.\1 to new_$i.\1;\n/" ; done | mysql
Michael, thanks for this.
However, my tail doesn’t accept +2, so I replaced tail with “egrep -v ‘^Tables_in’”. That also evades the first line, but in a different way.
orly # tail –version
tail (GNU coreutils) 6.4
make it: “tail -n +2″
#!/bin/sh
set -x
mysqladmin -f drop $2;
for i in $1 ; do mysqladmin create $2 ; mysql -B $i -e “show tables” | tail -n +2 | perl -pe “s/^(.*)$/rename table $i.\1 to $2.\1;\n/” ; done | mysql
mysql $1 -e “show tables”;
mysql $2 -e “show tables”;
mysqladmin -f drop $1;
GNU tail accepts a -n option and this option accepts a +X argument. The author must have been using BSD-ish tail.
KISS and use MySQL’s own parameters: mysql –batch –skip-column-names
(That’s not an en dash or em dash, but two hyphens. Bloody autoformatting.)
Summary:
mysql --batch --skip-column-names --database=information_schema -e "SELECT CONCAT('RENAME TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' TO new_', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM TABLES WHERE TABLE_SCHEMA IN ('a', 'b', 'c')" | mysql