MySQL主从复制状态监控脚本

雨点打透心脏的1/2处 2022-05-21 06:47 303阅读 0赞

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

[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