- Welcome to Geeksww.com
How to rename MySQL DB by moving tables
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?
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.
tags cloud
popular searches
free download for mysql database server 5.1.5, bison, gearman, source code, php, install cairo, laptop, mysql, java, linux, install mysql, mysql initialization, mysql mysql, tools, ubuntu
Similar Tutorials:
- opensource-db-tools - A set of open source tools for MySQL
- Unique primary keys(PKs) for MySQL using Redis
- MySQL GUI Tools - Query Browser
- What should be monitored on a busy Database/MysQL server?
- How to speed up a Mysql replica?
Tutorials in 'Database Management Systems > MySQL' (more):
- What should be monitored on a busy Database/MysQL server?
- How to speed up a Mysql replica?
- The server requested authentication method unknown to the client [phpmyadmin]
- How to set default values for MySQL JSON columns
- How to delete rows in table when PK is referenced in another table
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