数据库表一致性监控

 
 

  
  已实现，监控脚本在主库：ali-sz02-qgz-db-master-027上
 
 
 
 

  
  # 检查主从一致性
 
 
0 4 * * * /data/scripts/db_scripts/check_mysql_table_diff.sh > /tmp/check_mysql_table_diff.log 2>&1

#!/bin/bash -x

. /etc/profile > /dev/null 2>&1

pppp=$0
echo $pppp

my_cnf="/etc/my.cnf"
mysql_sock="/data/mysqldata/mysql.sock"
mysql_port="3306"

rd=`date +%F`
cur_time=`date +'%Y%m%d%H%M%S'`

log_file_full="/tmp/check_mysql_table_full.${rd}.log"
log_file_diff="/tmp/check_mysql_table_diff.${rd}.log"

mysql_user="xxxxxxxx"
mysql_passwd="xxxxxxxxxxxxx"

eth0=`ifconfig eth0|grep 'inet addr'|awk '{print $2}'|awk -F: '{print $2}'`
eth1=`ifconfig eth1|grep 'inet addr'|awk '{print $2}'|awk -F: '{print $2}'`


mailFunc() {
  	STATU=$1
  	echo "this message is from $0 in $HOSTNAME $eth1 ." >> $log_file
  	/data/scripts/sendEmail -s smtp.xiaoniu66.com -f wlkj.monitor@xiaoniu66.com  \
                     -t xxxxxxxxxxx@xiaoniu66.com \
                     -xu xxxxxxxxx@xiaoniu66.com -xp 'xxxxxxxxxx'          \
                     -u "[MONTOR]-[DB-Diff]-[QGZ]-[${HOSTNAME}]-[${eth1}] Checksum ${STATU}"           \
                     -o message-file=$log_file_diff
}

echo "$mysql_passwd"|pt-table-checksum --nocheck-replication-filters  --no-check-binlog-format --replicate=op_monitor.checksums --ignore-databases=mysql --ignore-databases=mysql -uroot --ask-pass -S $mysql_sock > $log_file_full 2>&1

if [ $? -ne 0 ]
then
    echo "$mysql_passwd"|pt-table-checksum --nocheck-replication-filters  --no-check-binlog-format --replicate=op_monitor.checksums --ignore-databases=mysql --ignore-databases=mysql --replicate-check-only -uroot --ask-pass -S $mysql_sock > $log_file_diff 2>&1
	mailFunc "Fail"
else
    echo "[MONTOR]-[DB-Diff]-[QGZ]-[${HOSTNAME}]-[${eth1}] Checksum OK" > $log_file_diff
	mailFunc "OK"
fi