0

Are there any flags or tricks to speed up mysqldump for backups? Here is what we are currently using:

mysqldump --skip-opt --flush-logs --routines --triggers --allow-keywords --create-options --add-locks --quick --single-transaction --extended-insert --all-databases --events --default-character-set=utf8 | bzip2 -c > $BACKUP_PATH/$(date +\%Y_\%m_\%d_\%H_\%M_\%S).sql.bz2

I noticed that mysqldump is running in a single thread, even though our MySQL server has 8 cores. Currently this command takes greater more than 20 minutes to run.

Justin
  • 5,328
  • 19
  • 64
  • 84

1 Answers1

1

bzip2 is not a good strategy here, gzip is probably 10x times faster, but both are quite CPU bonded.

To utilize your extra cores pipe into "pigz"

Danila Ladner
  • 5,331
  • 22
  • 31
  • There's also [pbzip2](http://compression.ca/pbzip2/), pigz for .bz2. If you have enough cores available it's plenty fast. –  Jan 25 '14 at 01:19
  • Switching to `pbzip2`, did not make much difference. The slowest part seems to be the actual MySQL dump, not the compression. – Justin Jan 25 '14 at 02:36
  • mysqldump doesn't support using support multiple threads out of the box, you might want to try to use something like this: https://github.com/luobailiang/mydumper – Danila Ladner Jan 25 '14 at 03:24