I had to perform a task which required moving tables between schemas on the same MySQL server.
One way of doing this would be:
- Dumping the original table to disk.
- Creating new empty table on the target schema.
- Importing table from disk into the target schema.
But, a simpler way exists!
The manual entry for RENAME TABLE casually describes this feature:
Which works almost instantaneously and does what it is supposed to do. This command could also be called MOVE TABLE.
Using this command, it is easy to emulate the missing RENAME DATABASE command.
- Create the new schema.
- Perform a query to generate the move commands from the source to the target schema:
Run those by copying them from your editor, or whatnot. If you're using SQLyog, CTRL-L switches the results tab to text mode for easy copying.
SELECT CONCAT('RENAME TABLE ',table_schema,'.',table_name,
' TO ','new_schema.',table_name,';')
WHERE table_schema LIKE 'old_schema';
- Drop the old schema
If you're using InnoDB foreign keys, worry not. The constraints are kept across databases, and moving the tables does not affect them - no need to move the tables in any specific order.
The move command also moves the .idb files between the database directories, if you're using innodb_file_per_table.
Hope this helps.