Solved

Error checking on mysqldumps on daily backup cronjob

Posted on 2008-10-03
9
972 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

 
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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

The following is a collection of cases for strange behaviour when using advanced techniques in DOS batch files. You should have some basic experience in batch "programming", as I'm assuming some knowledge and not further explain the basics. For some…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
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…

752 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