mysqldump sometimes returns empty file

Question

i am running a cron job, that calls a sh script with the code bellow. I have noticed that sometimes it works, but sometimes i am getting a 0KB file. I have no idea what could be causing this 0KB or what could be done to fix this.

DATE=`date +%Y-%m-%d-%H-%m`
NAME=bkp-server-207-$DATE.sql.gz
mysqldump -u root -pXXXX@2016 xxxx | gzip > /media/backup_folder/$NAME

Answer 1

You need to find out why the command is failing. The default output of a cron job is lost, unless you redirect it to a file and check it later.

You can log it at the cron level (see http://www.thegeekstuff.com/2012/07/crontab-log/)

59 23 * * * /home/john/bin/backup.sh >> /home/john/logs/backup.log 2>&1

The 2>&1 folds stderr into the stdout, so both are saved.

Or else you can log specific commands within your script:

echo "Creating $NAME" >>/home/john/logs/backup.log
mysqldump -u root -pXXXX@2016 xxxx 2>>/home/john/logs/backup.log | gzip > /media/backup_folder/$NAME

Once you have the error output, you should have important clues as to the cause of the failure.