Copying a MySQL database FAST
dave at thesmithfam.org
Sat Aug 16 09:03:31 MDT 2008
Ryan Byrd wrote:
>> Can you do this across databases? I don't think you can.
> Using INSERT INTO ... SELECT you can copy data from one database to
> another. You need the correct privileges for both.
> Enter the source database, database1:
> use database1;
> Then write to which fields in the destination database you want to
> copy to, database2:
> INSERT INTO database2.table1 (field1,field3,field9)
> SELECT table2.field3,table2.field1,table2.field4
> FROM table2;
I tested this finally and compared the results to mysqldump. It took 6.5
hours to do it this way, and the "mysqldump" way took 2 hours. In both
cases I turned off foreign key checks. Weird. I guess I'll stick with
mysqldump for now.
Thanks for the suggestions everyone! I wish MySQL had a nice
copy-on-write database copy. That'd be exactly what I need (by the way,
this isn't really for backups, but schema migration).
P.S. Thunderbird's spell check wanted to correct mysqldump to
"dumplings". I was tempted to let it make the correction, but in the end
self restraint won...
More information about the PLUG