What's the quickest way to dump & load a MySQL InnoDB database using mysqldump?
backup, database, innodb, mysql
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.
Problem
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)