Copying a MySQL database FAST
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.
-------------- next part --------------
A non-text attachment was scrubbed...
Size: 3241 bytes
Desc: S/MIME Cryptographic Signature
Url : http://plug.org/pipermail/plug/attachments/20080806/e0b415a5/attachment.bin
More information about the PLUG