Copying a MySQL database FAST

Gary Thornock gthornock at yahoo.com
Wed Aug 6 16:47:02 MDT 2008


--- Dave Smith <dave at thesmithfam.org> wrote:
> I need to copy a MySQL database fast. Let's pretend it's for
> backup purposes, and the user is impatient. The database has
> about 15,000,000 rows spread non-uniformly across 30 InnoDB
> tables. I've tried mysqldump, like so:
>
>    mysql -u root -e "create database new_db" dump -u root
>    mysqlold_db | mysql -u root new_db
>
> But it takes about an hour.
>
> Unfortunately, mysqlhotcopy is not an option either since it
> only works with ISAM and MyISAM tables.
>
> Any other ideas? I need some speeeeeed....
>
> --Dave

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.

- Gary




More information about the PLUG mailing list