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

You should check that the mysqldump succeeded. If it causes an error (like your password was wrong, or the named database does not exist), it will have no output on stdout, therefore the gzip will generate 0 bytes. An error is indicated by the exit status ($?) returned as nonzero. — Bill Karwin
– Bill Karwin, Commented Dec 21, 2017 at 21:55
Another possibility: the dump was created as a 0KB file because the filesystem you were writing to was full at the time the backup was attempted (even if it's no longer full when you look at it). No matter what the cause, it's important to implement some error checking in your backup script. — Bill Karwin
– Bill Karwin, Commented Dec 21, 2017 at 22:22
The script is definetly not the problem, because it succeeds most of the time. I have it running once every night. In one week, it succeeded 5 and failed (0KB) twice. The disk space is also not the issue. It is just a couple of hundred k bytes, and the disk has several free GB of space. Any other guesses? Could you help me with the error checking? — yurividal
– yurividal, Commented Dec 22, 2017 at 1:57

Bill Karwin · Accepted Answer · 2017-12-22 02:16:10Z

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.

Collectives™ on Stack Overflow

mysqldump sometimes returns empty file

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related