Copying a MySQL database FAST

Steven Alligood 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.
>
> --Dave

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_thread_concurrency, innodb_max_purge_lag, 
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.

-Steve



-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/x-pkcs7-signature
Size: 3241 bytes
Desc: S/MIME Cryptographic Signature
Url : http://plug.org/pipermail/plug/attachments/20080806/de5e0990/attachment.bin 


More information about the PLUG mailing list