Migrating tables

There are different ways to migrate huge amount of data (tables) in MySQL. The most common approach everyone follows is to create a new table with the same structure as the old one and then insert the records using a select on the old table. Eg:

create table new_tbl_name like old_db_name.tbl_name;
insert into new_tbl_name select * from old_db_name.tbl_name;

But a little googling helped me to find a very good alternative to this.

alter table old_db_name.tbl_name rename new_db_name.tbl_name;

It’s a very good method compared to all others. It doesn’t create a temporary table but just moves the data definition file and the data file to the new DB location. It works well for MyISAM and InnoDB