Copying a MySQL database FAST

[Gary Thornock]

--- Gary Thornock <gthornock at yahoo.com> wrote:
> I have a quick-and-dirty perl script for this.  It's a bit
> more than I want to try to paste in an email, though.  You
> can grab it at
> 
>   http://thornock.us/files/mysqlcopy.perl.bz2
> 
> if you're interested.
> 
> Basically it just iterates through the tables in the old
> database, creates tables with the same names and structure
> in the new database and does INSERT INTO ... SELECT.
> 
> One caveat: CREATE TABLE ... LIKE won't copy foreign key
> constraints.  If you have any of those, you'll have to
> recreate them yourself.

If you were interested in improving upon the script, I can
think offhand of a couple of things that would makt it
better.  (Remember, this is "quick and dirty".  There's
plenty of room for improvement before it's "good".)

- An option to do the copy in a single transaction, or to
  lock the source tables before copying, would help to
  ensure that the destination data is consistent even if
  there are new writes happening on the source while you
  copy.
- You may get some performance improvement if you disable
  keys on the destination table before doing the inserts
  and enable them again afterward.  That can still take
  a Long Time (I've seen tables that take hours just to
  re-enable the keys), but it may still be faster than
  doing mass inserts with the keys enabled.

Because of the way the script copies the data, it's not
possible to make it work across two servers.  But we have
mysqldump for that :)

- Gary


PGP Key ID: 071B173D
Fingerprint: ED30 B048 6833 56B4 28C0 CE52 F12B 884A 071B 173D