Copying a MySQL database FAST

Lonnie Olson lists at
Wed Aug 6 09:28:19 MDT 2008

On Tue, Aug 5, 2008 at 8:46 PM, Dave Smith <dave at> wrote:
> 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.

Since they are in the same MySQL server instance, you can't use replication.
Since they are innodb tables, you can't use rsync.

With these two conditions I still see an option which may be faster.
Script the creation of the new database and tables, but use an INSERT
... SELECT statement to copy the data.
This reduces the number of queries mysql has to run, and keeps the
data from having to pass all the way out to a unix pipe.

Or remove one of the two conditions I mentioned first.


More information about the PLUG mailing list