Emulating The Missing RENAME DATABASE Command in MySQL

I'll approach this topic from another direction.

I had to perform a task which required moving tables between schemas on the same MySQL server.

One way of doing this would be:

1. Dumping the original table to disk.
2. Creating new empty table on the target schema.
3. Importing table from disk into the target schema.

But, a simpler way exists!

The manual entry for RENAME TABLE casually describes this feature:

As long as two databases are on the same file system, you can use RENAME TABLE to move a table from one database to another:

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

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.

1. Create the new schema.
2. Perform a query to generate the move commands from the source to the target schema:
   

   SELECT CONCAT('RENAME TABLE ',table_schema,'.',table_name,
   ' TO ','new_schema.',table_name,';')
   FROM information_schema.TABLES
   WHERE table_schema LIKE 'old_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.
3. Drop the old schema

Some notes:

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.