Copying a MySQL database FAST

[Dave Smith]

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).

--Dave


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...