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
smime.p7s
Description: S/MIME Cryptographic Signature
/* PLUG: http://plug.org, #utah on irc.freenode.net Unsubscribe: http://plug.org/mailman/options/plug Don't fear the penguin. */
