For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:
RENAME TABLE old_db.TABLE TO new_db.TABLE; |
After the rename you have to adjust the permissions of the table!
To generate a script to generate the sql to rename all tables you can get all tablenames
from the information schmema:
SELECT concat('RENAME TABLE $1.',TABLE_NAME, ' TO $2.',TABLE_NAME, ';') FROM information_schema.TABLES WHERE table_schema='$1'; |
An alternative way is to dump all the table structur and data and import it again to mysql. This is an easy method if your database is not to big.
Use these few simple commands
mysqldump --routines -u username -p -v olddatabase > olddbdump.sql mysqladmin -u username -p create newdatabase mysql -u username -p newdatabase < olddbdump.sql |
If ALL your tables are MyIsam you just can move the files which contain the tables. For this you create the new database and shutdown mysql. Move all the files from the old database folder to the newly created one.
November 15th, 2012 at 12:53 am
Sadly, rename only works when the dbs are on the same partition. Maybe an improvement to so this online for mysql 5.6 or pt-online-schema-change.
November 15th, 2012 at 6:32 am
In the case of table renaming, you still have to move the routines.
In the case of mysqldump, make sure you include the “–routines” switch. mysqldump does not dump stored routines by default.
November 15th, 2012 at 6:59 am
Thank you Shiomi, I always forget about that switch.
November 15th, 2012 at 12:46 pm
What about mysqldbcopy?
http://dev.mysql.com/doc/workbench/en/mysqldbcopy.html
I’ve done this with a stored procedure:
1. Copy all tables
2. Copy all foreign keys
You could pipe the output from mysqldump directly to mysql so you won’t need storage space.
Make sure to check for accidental charset issues, database permissions and the events.
November 16th, 2012 at 9:38 am
we have one more way to rename the database with linux move command go to datadirectory of mysql mv old_database_name new_database_name , but i have never tired this on productions… can anyone have idea on this.