MySQL backup recommendations

Question

I need to backup my MySQL database on my current system. I use the mysqldump command in a cron job using a shell script.

Here is roughly what I am doing:

 #!/bin/bash fileName=$(date +%H-%M) mysqldump -ubackup -hserver1.local.com -A database1 > /backup/$filename.sql 

It will take about 1 hour to finish my question:

I need to compress the data, so I would like to know if I should first backup the file as pure sql and then compress it or compress it right away from the mysqldump command?

Answer 1

To reduce the use of intermediate disk space, you can compress on the fly:

 mysqldump (options) | bzip2 -c > /backup/$filename.sql.bz2 

This means that you do not have to write all uncompressed SQL data to a file, and then read back to compress it.