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
smime.p7s
Description: S/MIME Cryptographic Signature
/* PLUG: http://plug.org, #utah on irc.freenode.net Unsubscribe: http://plug.org/mailman/options/plug Don't fear the penguin. */
