RENAME TABLE Syntax
RENAME TABLE tbl_name TO new_tbl_name[, tbl_name2 TO new_tbl_name2,...]
The rename is done atomically, which means that no other thread can access any of the tables while the rename is running. This makes it possible to replace a table with an empty one:
CREATE TABLE new_table (...);
RENAME TABLE old_table TO backup_table, new_table TO old_table;
The rename is done from left to right, which means that if you want to swap two tables names, you have to:
RENAME TABLE old_table TO backup_table,
new_table TO old_table,
backup_table TO new_table;
As long as two databases are on the same disk you can also rename from one database to another:
RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
When you execute RENAME, you can't have any locked tables or active transactions. You must also have the ALTER and DROP privileges on the original table, and the CREATE and INSERT privileges on the new table.
If MySQL encounters any errors in a multiple-table rename, it will do a reverse rename for all renamed tables to get everything back to the original state.
RENAME TABLE was added in MySQL 3.23.23.
Taken from the MySQL Reference Manual © 2002 MySQL AB
Hope that helps