How can I create a bash script for MySQL status/replication that only notifies of failure once per hour?

Question

I am using CentOS 6.2.

I followed Alex Williams guide to configuring MySQL through HAProxy found here.

Instead of having HAProxy check the status of MySQL and replication, I have the scripts running once a minute in crontab. If either of them fails I have them echo the failure to a text file; /opt/repl.txt or /opt/mysql.txt.

I have a script that checks if these files exist, and if so sends out an email notifying personnel that the failure occurred. This is also configured in crontab to run once a minute.

Basically, what I am trying to accomplished is this; I would like the script to only send out the emails at the initial failure, and once an hour after that until the issue is resolved. I am not entirely sure how to do this with bash scripting. The last time something failed it was sending out an email a minute about it, and that is definitely not what I'd like to have happen. Any help would be much appreciated.

Answer 1

Modify your script to touch a file when it sends an email and to not send an email if the file is < 1 hour old.

Answer 2

I use pt-table-checksum from Percona.com to do this.

1. Execute pt-table-checksum on the primary
2. Check the tables on the replica using the script provided in the documentation for pt-table-checksum. This query will only return output if there are errors, and I direct this output to a file.
3. If there is output, then send email. Otherwise, stay quiet.

Here is the abbreviated code:

mysql-checksum () {
OUTFILE=/tmp/outfile
mysql --execute "SELECT db, tbl, chunk, this_cnt-master_cnt AS cnt_diff, \
        this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc) AS crc_diff \
        FROM test.checksum \
        WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc);" > $OUTFILE
# Print a status message.
# Any output from above command is bad.
if [ -s $OUTFILE ]; then
        # List out the bad tables.
        BADTABLES=`tail +2 $OUTFILE | cut -f1,2 | tr '\t' '.' | tr '\n' ','`

        echo "ERROR: Bad checksum on these tables [$BADTABLES]. For details see $HOST:$OUTFILE." | mail -s "mysql checksum error" admin@example.org
        return 1
fi
}