Copying a MySQL database FAST
steve at bluehost.com
Wed Aug 6 10:56:07 MDT 2008
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.
On the same server, in the same instance of mysql, you can do the copy
in SQL (sorry, I don't remember the syntax). Speed will depend on how
you do it (locking writes versus not, how much you copy at a time, how
fast the entire copy must be done, impact allowed to the existing
databases, etc). You can actually write a script to select the first
50,000 and copy them, then the next, which will allow other operations
to queue up and run between the copies. Or of you can write lock, you
should be able to copy all at once, with MySQL using internal temp
tables to do the copy).
Now, the speed of this will also depend on your drives and IO. If you
have single mirrored drives, it will be a certain speed, where if you
have multiple striped drives with small segments, it will be quicker.
Hardware setup, software tweaking, and how you copy will all play a part
(but then you knew that).
I might also suggest that you split up your InnoDB file into multiple
files, which is both nice for the OS and can be better on performance
(depending on a lot of factors). In the my.cnf file,
innodb_file_per_table=1 will do that for all newly created tables. The
drawback is that you have to dump, drop, create, import on any existing
databases/tables to get them into separate files, and you still cannot
completely treat them as Myism files, since MySQL will still look them
up through references in the ibdata1 file.
If you haven't done it already, I would also suggest looking at the
performance tuning for InnoDB, things like the
innodb_buffer_pool_size, innodb_flush_log_at_trx_commit, etc, tuned to
your environment, of course. Also check out your buffer sizes, cache
hits, and temp table sizes.
-------------- next part --------------
A non-text attachment was scrubbed...
Size: 3241 bytes
Desc: S/MIME Cryptographic Signature
Url : http://plug.org/pipermail/plug/attachments/20080806/de5e0990/attachment.bin
More information about the PLUG