Checking MySQL database backups have worked (not failed)
Archive - Originally posted on "The Horse's Mouth" - 2015-01-10 08:29:11 - Graham Ellis
We do a regular backup of of MySQL databases within our server to keep recent local data "checkpoint" sets there - they allow us to look back at what the data used to be if we have a naughty user, and in the worst case scenario of a software problem / database corruption we can roll back. However we've experienced very occasional silent failures of mysqldump due to table corruption - and that can often go on for quite a while until the damage spreads or someone accesses some obscure data - by which time there may have been lots of other, undumped (or rather dump failed) updates elsewhere. If we're lucky, repair table will fix the problem.
Rather than fail silently, I have added a check into our MySQL database dump script to send me an email if there's a failure - then I'll know at the next cycle rather than have to wait for problems to show.
/usr/local/mysql/bin/mysqldump -uwellho -pblahblah -h127.0.0.1 wellho > /home/backups/$HOUR/wellho.sql
if [ "$?" -eq 0 ]; then
echo "Success $HOUR wellho" | mail -s "wellho good dump" graham@wellho.net
else
echo "Mysqldump encountered a problem $HOUR wellho" | mail -s "wellho BAD dump" graham@wellho.net
fi
sleep 30
gzip -f /home/backups/$HOUR/wellho.sql
This example is going to become rather irritating as it emails on success too ... you'll want to take out that extra mailing once you're sure it's working!