Hello Friends,

i have just create a mysqldump script which i am using for dumping my data base and get confirmation by email.
script working fine when i missed my destination it shows the error in mail otherwise its shows success mail. but when i stop the mysqld service and run the script again i got success!! email which should be error email becoz mysqldump is not working.
Here is my script

#!/bin/sh

export PATH="$PATH:/usr/bin:/usr/local/bin"

dumpuser="root"
dumphost="node02.gtltest.com"
backupdir="/test/"
date=$(date +%Y-%m-%d-%H)
year=$(date +"%Y")
week=$(date +"%V")
hostname=$(hostname -f)
clientmailid=jeetjaisalmer@gmail.com

############## get a list of all the databases ###########
for db in $(mysql -u root -p"redhat" -NBe "show databases")
################## send them to nas #######################

do

mysqldump -u root -p"redhat" -Q -e --event --default-character-set=latin1 --single-transaction "${db}" | bzip2 | ssh ${dumpuser}@${dumphost} "mkdir -p ${backupdir}/${year}/week-${week}; cat > ${backupdir}/${year}/week-${week}/${db}-week-${week}-${year}.sql.bz2"

done

########## Send mail to acknowledge the scripts results ############
if [ $? = 0 ]; then
echo "Mysql Script excuted sucessfully on ${hostname} all data successfully dumped with name ${db}-week-${week}-${year}.sql.bz2 ${dumphost}" | mail -s " ${hostname} ${db}-week-${week}-${year}.sql.bz2 Mysqldump successfully" ${clientmailid}
else
echo "Mysql Script did not execute sucessfully on ${hostname} there is some error while script execution" | mail -s " ${hostname} ${db}-week-${week}-${year}.sql.bz2 Error while mysqldumping " ${clientmailid}
fi


Thank you....
~
~

By using the test $? at the end of your "do" loop you're not going to get the result of the mysqldump command, you're going to get the result of the end of the whole pipe.

Consider doing your dump, compress, and transfer in different stages and monitoring each stage.

for example (in pseudocode)

Like I say, just a rough code layout rather than proper scripting syntax!

1 members found this post helpful.

Its not work...
Actually i want to monitor mysqld service with script which show me error when service not running as well as error in mysqldump commands..

So what did you write?

We aren't mind readers, if you don't tell us then we can't help. What have you written so far to check your mysqld service?

so what error did you see, it is very obvious it will show some error the way it is used... but i can make eny comment only after having a look on the error.. you should have tested the command line before putting into the script.

Thanks for the reply..
actually i am getting success mail instead of error mail while my mysqld service stop..
there is must be error mail after script execusion..
i think mysqldump error code is 0 while mysqld service stop so i want some changes in my script in which it show me an error mail when mysqld service stop and i can get proper update mail of my mysql backup script..

Can we use this command for mysql service monitoring?
ps -ef | grep mysqld | grep -v grep;

Thank You

If you're running these tests as root and also assuming CentOS:

Thanks dear i got the logic and problem solved

Actually i run two this command

#ps -ef | grep mysqld | grep -v grep;
#errcode1=$?

and

#mysqldump
#errcode2=$?


and make statement two compare both value

if [ "${errcode1}" = "${errcode2}" ];

Thanks Dear....