Solved

Error checking on mysqldumps on daily backup cronjob

Posted on 2008-10-03
9
971 Views
Last Modified: 2013-12-26
I have a daily cronjob where I backup all of my databases on my two linux servers.  Currently, I have no way of knowing if this even works, if any errors were reported, etc.  I am not too sure how to get these flags, but any help on making this script better is appreciated.

Much thanks!
#! /bin/sh
# usage $0
 
date=`date -I`
export localdb=`ls /var/lib/mysql -l | egrep '^d' | awk '{print $9}' | egrep -v '(\.|\.\.)' | awk -F/ '{print $1}'`
export remotedb=`ssh root@internet "ls /var/lib/mysql -l" | egrep '^d' | awk '{print $9}' | egrep -v '(\.|\.\.)' | awk -F/ '{print $1}'`
export localcount=`ls /var/lib/mysql -l | egrep '^d' | awk  '{print $9}' | wc -l`
export remotecount=`ssh root@internet "ls /var/lib/mysql -l" | egrep '^d' | awk  '{print $9}' | wc -l`
export localstored=`find /var/backup/mysql -mtime -1 -type f -print | grep intranet | wc -l`
export remotestored=`find /var/backup/mysql -mtime -1 -type f -print | grep internet | wc -l`
 
for nextdb in $localdb
   do
      mysqldump -ppassword --databases $nextdb | bzip2 > /var/backup/mysql/intranet-$nextdb-$date.sql.bz2
done
 
for remotenextdb in $remotedb
   do
      ssh root@internet "mysqldump -ppassword --databases $remotenextdb | bzip2 > /tmp/internet-$remotenextdb-$date.sql.bz2"
      scp root@internet:/tmp/internet-$remotenextdb-$date.sql.bz2 /var/backup/mysql/internet-$remotenextdb-$date.sql.bz2
      ssh root@internet "rm /tmp/internet-$remotenextdb-$date.sql.bz2"
done
 
# This check only checks if the amount stored is the same as amount of databases listed for the day.   Doesn't do any more advanced error checking yet.
 
if [ "$localcount" -ne "$localstored" ]; then
   echo "There appears to been a problem with the intranet MySQL backup process"
fi
 
if [ "$remotecount" -ne "$remotestored" ]; then
   echo "There appears to been a problem with the internet MySQL backup process"
fi
 
# erases backup files that are two weeks old
find /var/backup/mysql -mtime +14 -exec rm {} \;

Open in new window

0
Comment
Question by:jwnrb
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 500 total points
ID: 22633404

Add (echo) more messages and send crontab ouput to a log file, then check the log file (and save it):


## example crontab entry:
00 21 * * * /path/to/script/MyBackup.sh >/path/to/logs/MyBackup.log 2>&1

Open in new window

0
 
LVL 40

Expert Comment

by:omarfarid
ID: 22633751
look into the file /var/log/cron which tells which crontab jobs got executed. You may have your own logging as well from the script by redirecting output messages to file. Also, cron will send by default any output from the script (if not redirected) by email to user, so you may check your email (using mail or mailx)
0
 

Author Comment

by:jwnrb
ID: 22634456
Right now all output from cronjobs does get emailed to root, which will then get redirected to my email, that isn't the problem, I need help making better error checking like if the mysqldump worked, if the files transferred, etc.  there is quite a few things trhat can go wrong here, so any way of parsing error codes returned from mysqldump, ssh/scp, tar, etc...

0
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
LVL 40

Expert Comment

by:omarfarid
ID: 22637314
unix commands and tools return exit codes which are stored in $? env variable. Then you might check the exit code after each command and see if it executed successful. if $? is 0 then it was successful. You need to refer to each command or tool documentation for the exact meaning of each error code.
0
 

Author Comment

by:jwnrb
ID: 22637873
Do you know of such good spots to find error codes on mysqldump, ssh, scp, tar, bzip2, ...
0
 
LVL 40

Expert Comment

by:omarfarid
ID: 22638106
I have tried to search for exit codes references but could not find much. But for your script it is enough to know that the command succeeded or failed by checking if $? is 0 or non-zero. You need to redirect stderr to a file where you can see the exact error message.

http://www.gnu.org/software/tar/manual/tar.html
http://aplawrence.com/Bofcusm/2073.html
0
 
LVL 2

Expert Comment

by:tdiops
ID: 22723543
You could cut and past the snippet into a file called execcmd.sh and run your program from cron as sh execcmd.sh <any command you want to run>. This will capture all output in the log file specified and dated in the execcmd.sh script. If you really want to go wild, you could always pass in the log file pathname as an option.
#!/bin/sh
 
cmd="$*"
 
date=`date +%Y%m%d%H%M%S`
# comment below if you need hours, minutes and seconds
date=`date +%Y%m%d`
 
log=/tmp/log.$date
# redefine log to suite your needs (location and name)
 
$cmd > $log 2>&1

Open in new window

0
 
LVL 2

Expert Comment

by:tdiops
ID: 22723586
As for error codes, each program is different and most likely different on different platforms. Sometimes the man page will offer some suggestions. For example: man ssh on redhat says: ssh exits with the exit status of the remote command or with 255 if an error occurred.... and that's all it says.
0
 

Author Comment

by:jwnrb
ID: 22744044
So would you say what I have now is enough for error checking for this purpose?
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Background Still having to process all these year-end "csv" files received from all these sources (including Government entities), sometimes we have the need to examine the contents due to data error, etc... As a "Unix" shop, our only readily …
Recently, an awarded photographer, Selina De Maeyer (http://www.selinademaeyer.com/), completed a photo shoot of a beautiful event (http://www.sintjacobantwerpen.be/verslag-en-fotoreportage-van-de-sacramentsprocessie-door-antwerpen#thumbnails) in An…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question