Solved

Error checking on mysqldumps on daily backup cronjob

Posted on 2008-10-03
9
960 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
Utilizing an array to gracefully append to a list of EmailAddresses
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now