How to rename MySQL DB by moving tables

Category:

Tutorials > Database Management Systems > MySQL > Administration

RENAME DATABASE statement was removed from MySQL because it was found to be dangerous.

However, MySQL still supports the RENAME TABLE command. See http://dev.mysql.com/doc/refman/5.7/en/rename-table.html for conditions.

Please note that renaming databases is a tricky job and you should research properly before making any such change to any database environment of any significance.

The commands below are simple shell commands that generate statements to rename tables (ie. move them from one database to another). I highly recommend taking a full, valid backup of your database.

I used MacOS X for testing, however the commands should work on Linux and other operating systems with simple modifications (see below for more):

export old_db=test;
export new_db=test_new;
mysql -Ns -uroot -e "use $old_db; SHOW TABLES;" | xargs -I {} echo "RENAME TABLE $old_db.{} TO $new_db.{};";

Output will look similar to:

RENAME TABLE test.blocked_list TO test_new.blocked_list;
RENAME TABLE test.dump TO test_new.dump;
...

In order to execute them right away, do the following (please be careful on important systems):

mysql -Ns -uroot -e "use $old_db; SHOW TABLES;" | xargs -I {} echo "RENAME TABLE $old_db.{} TO $new_db.{};" | mysql -uroot

Please note that you may need to change the database username ('root' in examples above) and/or include database password using the -p option of mysql

Take a look at Working with BASH environment variables for information on how to use environment variables in BASH shell

Did this tutorial help a little? How about buy me a cup of coffee?

Buy me a coffee at ko-fi.com

Please feel free to use the comments form below if you have any questions or need more explanation on anything. I do not guarantee a response.

IMPORTANT: You must thoroughy test any instructions on a production-like test environment first before trying anything on production systems. And, make sure it is tested for security, privacy, and safety. See our terms here.

Comments (write a comment):

In the case of InnoDB tables (at least) this is a very dangerous thing to do! It should only be done with MyISAM tables, and only when mysqld is not running!

In the case of InnoDB tables and databases, information about their names is in the file "ibdata1", and tables and files that are renamed via the file system may be inaccessible from within mysql.

Since you recommend taking full backups first, it would seem a better-supported solution would be to modify the backup and then restore it. This can be done on the fly, without compromising the integrity of the backup:

# sed 's/OldName/NewName/' <dump.sql | mysql

That's how I've successfully renamed databases in the past. Posted by: Jan S on Apr 21, 2016

@Jan: We're not making any changes to DB files directly.
Instead the script/command only generates MySQL commands to rename tables.

Basically, MySQL is performing the actual renaming action using RENAME TABLE statement.

Backups (not necessarily a SQL dump) was suggested in case something goes wrong. A restore was not suggested as part of renaming the database job. Whereas, in your case, a logical next step is restoring from the modified dump file (which may not be applicable in most large databases). Posted by: Geeksww on Apr 22, 2016

Oops! My bad for not reading closely enough. I thought you were using the file system to rename tables. Posted by: Jan S on Apr 28, 2016

leave a comment on tutorial leave a comment