review mysqldump options
the mysqldump default options have changed over time, its about time to review these options. for example is it a good idea to lock the tables during dump? when the tables are locked, then the application usually is blocked. another option that might make a lot of sense is to have skip-extended-insert, because by default extended-inserts are enabled in mysqldump, and they make SQLdumps very difficult to edit with an editor, what purpose do these extended inserts serve? I'm not sure, need to research that.
#1 Updated by olivier.monaco over 5 years ago
If you look at the help (http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_extended-insert) you will read that:
Use multiple-row INSERT syntax that include several VALUES lists. This results in a smaller dump file and speeds up inserts when the file is reloaded.
So extended insert is really useful to reduce dump file size and speed up reload.
Maybe you can give a try to mysqlhotcopy (http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html) to speed up the backup. But this tool can not be used for all engine.
Take a look at http://www.zmanda.com/blogs/?p=19.