MySQL主从复制状态监控脚本

一. 主从复制存活状态监控脚本

[root@host-47-98-97-124 scripts]# mysql-master-slave-status.sh

#!/bin/bash

port=`ss -ntlp | grep 3306 | awk '{print $4}' | awk -F":" '{print $2}'`

array=($(mysql -uroot -p123456 -e "show slave status\G"|egrep "Running|Seconds_Behind_Master" | awk '{print $2}'))

if [ "$port" == "3306" ];then

    if [ "${array[0]}" == "Yes" ] && [ "${array[1]}" == "Yes" ] && [ "${array[2]} " == "0" ];then

        echo "MySQL slave status is ok !"

    else

        echo "MySQL slave status is wrong !"
    fi
fi



注：
1.==用于字符串比较，-eq用于数字比较，这里port是字符串
2.array是一个数组是用来存储Slave_IO_Running和Slave_SQL_Running的值，另外$(cmd)这个是用来执行括号里面cmd的命令的，而array数组则是用()扩起来的

二、 主从复制延时时间监控脚本

url：https://www.cnblogs.com/kevingrace/p/6274073.html

方法一：

[root@host-47-98-97-124 scripts]# cat mysql-copy-delay-time.sh

#!/bin/bash

num=`/usr/bin/mysql -uroot -p123456 -e "show slave status\G" | grep Seconds_Behind_Master | awk '{print $2}'`

if [ $num -eq 0 ];then

    echo "Master and slave replication is consistent"

else

    echo "Master and slave replication is inconsistent"

fi


方法二：
[root@host-39-108-217-12 scripts]# cat mysql-copy-delay-time.sh 
#!/bin/bash

/usr/bin/pt-heartbeat --database zabbix --table=heartbeat --monitor --host=39.108.217.12 --user=repsta --password=123456 --log=/opt/master-slave.txt --master-server-id=1  --daemonize

cat /opt/master-slave.txt > /opt/master_slave.txt

max_time=`cat /opt/master_slave.txt  | grep -v '^$' | awk '{print $1}' | sort -k 1 -nr | head -1`

NUM=$(echo "$max_time"|cut -d"s" -f1)

if [ $NUM == "0.00" ];then

    echo "Master slave replication has no delay"

else

    echo "Master slave replication delay,time $max_time"

fi

三、主从复制数据一致性及强制同步监控脚本

[root@host-39-108-217-12 scripts]# cat mysql-data-consistent-status.sh
#!/bin/bash

NUM=`/usr/bin/pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums  --databases=zabbix h=39.108.217.12,u=repsta,p=123456,P=3306 | awk 'NR>3{sum+=$3}END{print sum}'`

if [ $NUM -eq 0 ];then

    echo "Data is consistent!"

else

    echo "Data is error!"
    /usr/bin/pt-table-sync --replicate=test.checksums h=39.108.217.12,u=repsta,p=123456 h=47.106.141.17,u=repsta,p=123456 h=47.98.97.124,u=repsta,p=123456  --print
    /usr/bin/pt-table-sync --replicate=test.checksums h=39.108.217.12,u=repsta,p=123456 h=47.106.141.17,u=repsta,p=123456 h=47.98.97.124,u=repsta,p=123456  --execute
fi