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!
(written 2015-01-10, updated 2015-01-12)
Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles
S161 - Data Access and Security in MySQL [192] Current MySQL and PHP paths and upgrades - (2005-01-28)
[193] The wrong MySQL - (2005-01-29)
[401] What is an SQL injection attack? - (2005-08-02)
[535] MySQL permissions and privileges - (2005-12-20)
[647] Checking for MySQL errors - (2006-03-15)
[947] What is an SQL injection attack? - (2006-11-27)
[1131] MySQL - Password security (authentication protocol) - (2007-04-02)
[2204] Images in a database? How big is a database? (MySQL) - (2009-05-28)
[2263] Mysqldump fails as a cron job - a work around - (2009-06-30)
[2647] Removing duplicates from a MySQL table - (2010-02-22)
[3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
[4406] Fixing damaged MySQL tables - Error 1712 and Error 2013 - (2015-01-25)
[4481] Extracting data from backups to restore selected rows from MySQL tables - (2015-05-01)
[4491] Web Server Admin - some of those things that happen, and solutions - (2015-05-10)
[4493] Forgotten / lost MySQL root password - (2015-05-16)
A162 - Web Application Deployment - Backups and File System Management [153] Linux - where to put swap space - (2004-12-16)
[334] Symbolic links and hard links - (2005-06-02)
[554] What backup is adequate? - (2006-01-04)
[593] Finding where the disc space has gone - (2006-02-06)
[703] Copying files and preserving ownership - (2006-04-28)
[735] Boys will be boys, saved by Ubuntu - (2006-05-27)
[754] tar, jar, war, ear, sar files - (2006-06-10)
[1013] Copy multiple files - confusing error message from cp - (2006-12-30)
[1023] Finding public writeable things on your linux file system - (2007-01-06)
[1288] Linux run states, shell special commands, and directory structures - (2007-08-03)
[1439] Linux / Unix - layout of operating system files - (2007-11-20)
[1648] The tourists guide to Linux - (2008-05-20)
[1765] Dialects of English and Unix - (2008-08-21)
[1801] Will your backups work if you have to restore them? - (2008-09-18)
[1893] Some Linux and Unix tips - (2008-11-18)
[2299] How much space does my directory take - Linux - (2009-07-20)
[4056] An overpractical test of our backup strategy! - (2013-03-30)
[4063] Backups by crossover between network centres - setting up automatic scp transfers - (2013-04-13)
[4115] More or less back - what happened to our server the other day - (2013-06-14)
[4400] Commenting out an echo killed my bash backup script - (2015-01-19)
[4405] Backup procedures - via backup server - (2015-01-24)
S153 - Sourcing, Running and Configuring MySQL [489] Which MySQL server am I using? - (2005-11-07)
[515] MySQL - an FAQ - (2005-12-03)
[591] Key facts - SQL and MySQL - (2006-02-04)
[907] Browser -> httpd -> Tomcat -> MySQL. Restarting. - (2006-10-28)
[1095] Apache httpd , browser, MySQL and MySQL client downloads - (2007-02-28)
[1123] mysqldump and mysqlrestore - (2007-03-30)
[1689] Some sideways thoughts on the news - (2008-06-27)
[1731] Apache httpd, MySQL, PHP - installation procedure - (2008-08-01)
[1771] More HowTo diagrams - MySQL, Tomcat and Java - (2008-08-24)
[1935] Summary of MySQL installation on a Linux system - (2008-12-11)
[2085] MySQL - licensing issues, even with using the name - (2009-03-16)
[2209] Monitoring and Tuning your MySQL installation - (2009-05-31)
[2426] Which version of MySQL am I running? - (2009-09-26)
[2444] Potted MySQL installation - (2009-10-09)
[2445] Securing MySQL on a production server - (2009-10-09)
[2458] Cant connect to local MySQL server through socket /tmp/mysql.sock - (2009-10-17)
[4487] Starting MySQL. ERROR! The server quit without updating PID file - how we fixed it. - (2015-05-06)
Some other Articles
Philosophy behind object design - and how I applied in to a Java exampleSplitting Java strings and extracting numbersInterview conditions,and other instructions to staffRefactoring Perl applications to give them a rosy futureChecking MySQL database backups have worked (not failed)Cucumber example - test::unit, scenario outlines, datafile driven testGlobal Regular Expression matching in Ruby (using scan)Regression Testing my website - Cucumber and WatirGuide exercise to help you learn Gherkin, Cucumber and RspecA booking that looks too good to be true? It probably is too good to be true!