Search code examples
mysqldatabasebackupinnodb

What's the quickest way to dump & load a MySQL InnoDB database using mysqldump?

I would like to create a copy of a database with approximately 40 InnoDB tables and around 1.5GB of data with mysqldump and MySQL 5.1.

What are the best parameters (ie: --single-transaction) that will result in the quickest dump and load of the data?

As well, when loading the data into the second DB, is it quicker to:

1) pipe the results directly to the second MySQL server instance and use the --compress option

or

2) load it from a text file (ie: mysql < my_sql_dump.sql)

Solution

  • Pipe it directly to another instance, to avoid disk overhead. Don't bother with --compress unless you're running over a slow network, since on a fast LAN or loopback the network overhead doesn't matter.