Fastest method of producing a copy of a MySQL database

Question

We have a very large database that we need to occasionally replicate on our dev+staging machines.

At the moment we use mysqldump and then import the database script using "mysql -u xx -p dbname < dumpscript.sql"

This would be fine if it didn't take 2 days to complete!

Is it possible to simply copy the entire database as a file from one server to another and skip the whole export/import nonsense?

Cheers

Answer 1

there are couple of solutions:

• have a separate replication slave you can stop at any time and take the file-level backup
• if you use the innodb engine - you can take file system level snapshot [eg with lvm] and then copy the files over to your test environment
• if you have plenty of tables/databases - you can paralleled the dumping and restoring process to speed things up.