Copying a MySQL database FAST

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


On Tue, Aug 5, 2008 at 8:46 PM, Dave Smith <dave at thesmithfam.org> 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.
http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

Or remove one of the two conditions I mentioned first.

--lonnie



More information about the PLUG mailing list