This is post is now quite old and the the information it contains may be out of date or innacurate.

If you find any errors or have any suggestions to update the information please let us know or create a pull request on GitHub

Sometime in MySQL you want to copy an entire table from one database to a separate database. One example of when you would want to do this is if you are making a backup of a table before you apply some kind of processing to that table. There is nothing worse than ruining a database table and losing valuable information. To make an instant backup is so easy that there really is no excuse not to make backups at important or risky stages.

Here is how to do it:

1
2
DROP TABLE IF EXISTS `backup_db.backup_table`;
CREATE TABLE `backup_db.backup_table` SELECT * FROM `live_db.live_table`;

This will delete your existing backup table completely, then will remake it copying all structural information and content from the live_table in the live_db.