Copying a MySQL database FAST

Steven Alligood steve at bluehost.com
Wed Aug 6 11:04:00 MDT 2008


Kimball Larsen wrote:
> On Aug 5, 2008, at 8:46 PM, Dave Smith wrote:
>
>> Jeff Schroeder wrote:
>>> Dave asked:
>>>
>>>
>>>> I need to copy a MySQL database fast.
>>>>
>>>
>>> If the servers (source and destination) are on the same LAN, you 
>>> might consider using rsync.
>>
>> I'm actually copying the contents of one database to another on the 
>> *same* MySQL server, so rsync is not an option. Doubly so because the 
>> tables are all InnoDB so the two DB's share data in a common directory.
>>
>
> Would it be possible to set up 2 instances of mysql on the machine?  
> Each gets its own data dir, and copying from one db to another could 
> still use rsync to localhost.
>
> Just a thought.
>
> Good luck.
>
> -- Kimball
>

I would also suggest that you not use rsync for an InnoDB database setup 
id MySQL will still be online, since it is really just one large file.  
As soon as you rsync it and bring it back online, it will have to do a 
full repair, which usually works, but not always.

Myism files can be rsync'd live, and then a myismcheck run on all the 
files before bringing them online, but that should be a last effort 
backup file, not something you depend on for daily data copies.

If mysql is shutdown before the copy, it works fine.  If you can even do 
a FLUSH TABLES WITH READ LOCK; copy the tables, then UNLOCK TABLES; you 
will be fine with Myism files (remembering that the mysql client 
connection that has the lock has to stay open during the copy; as soon 
as the session ends, so does the lock).  Innodb seems to still update 
various things in it's table even with a read lock, so it will still 
force a repair on startup of the copy.

-Steve

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/x-pkcs7-signature
Size: 3241 bytes
Desc: S/MIME Cryptographic Signature
Url : http://plug.org/pipermail/plug/attachments/20080806/e0b415a5/attachment.bin 


More information about the PLUG mailing list