MySQL Copy Table from One Database to Another
March 3rd, 2008Read More mysql, programming, web development
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:
-
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.
Related Blogs
http://www.phpclasses.org/browse/package/4017.html
http://www.sematopia.com/?p=61
http://www.yinfor.com/blog/archives/2008/02/mysql_backup_and_recovery_meth.html
June 12th, 2008 at 11:42 pm
Just remember that this won't create the table using the same engine as the existing table and it won't recreate the primary keys or indexes.
To do this you have to use:
CREATE TABLE new_table_name LIKE old_table_name;
then you populate it with the data from the old table with:
INSERT INTO new_table_name SELECT * FROM old_table_name;
If the copied old table has many keys then it may help to speed the INSERT if you turn off the keys using the following before the INSERT:
ALTER TABLE new_table_name DISABLE KEYS;
And then after the INSERT:
ALTER TABLE new_table_name ENABLE KEYS;
These two statements are not supported by all MySQL Engine types though. InnoDB being one of them in MySQL ver 5.x
Cheers
Julz
June 13th, 2008 at 8:47 am
Nice one Julz
Yes if you want to do more than simply backup the table data and want to actually make a proper fully functional copy of the table then the above is definitely the best way to do so.
October 2nd, 2008 at 2:31 pm
hi, i want to update my table2 with only the new data that i have entered from table1. hopefully on a daily basis
how wud i go about this? pls help
tony
October 3rd, 2008 at 7:57 am
any one? please
October 12th, 2008 at 7:56 pm
Very useful tip, thanks to Julz and Admin