• MySQL5.7 多源复制监控脚本


    #!/bin/bash
    :<<BLOCK
    Version : v1.0 2018-12-21
    
    MySQL多源复制检测脚本
    监控配置放在 $CONFIG_FILE 中,内容如下
    #mysql_port:channel_name1:channel_name2:...
    3306:master196:master194
    
    检查结果存在 $RESULT_FILE 中,结果如下
    #mysql_port:channel_name1:io_thread:sql_thread:slave_lag:transactions_retries
    3306:master196:1:1:0:0
    3306:master194:1:1:0:0
    
    BLOCK
    
    export PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
    
    ZBX_USER="xxx"
    ZBX_PASSWD="************"
    MYSQL_BIN="/usr/local/mysql/bin/mysql"
    CONFIG_FILE="/etc/zabbix/plugins_conf/multi_master_slave_info"
    RESULT_FILE="/tmp/multi_master_slave_info"
    CHECK_SQL="
    SELECT DISTINCT  CONCAT('Transactions_Retries: ''',COUNT_TRANSACTIONS_RETRIES,'''') as Transactions_Retries FROM performance_schema.replication_applier_status WHERE CHANNEL_NAME='CHNAME';
    SHOW SLAVE STATUS FOR Channel 'CHNAME'G
    "
    #Low Level Discovery
    ChannelsDiscovery(){
        channle_list=(`cat $CONFIG_FILE  | awk -F":" '{for(i=2;i<=NF;i++){print $1":"$i}}'`) 
        num=$(echo ${#channle_list[@]})
    
        # echo json
        if [ "$num" != 0 ];then
            printf '{
    '
            printf '	"data":[
    '
        fi
    
        for((i=0;i<${#channle_list[@]};++i))
        {
            if [ "$i" -lt $((${num}-1)) ];then
                printf "		{ 
    "
                printf "			"{#CHANNEL_NAME}":"${channle_list[$i]}"
    "
                printf "		},
    "
            else
                printf  "		{ 
    "
                printf  "			"{#CHANNEL_NAME}":"${channle_list[$((${num}-1))]}"
    "
                printf  "		}]
    }
    "
            fi
        }
    }
    
    #被ChannelsCheck()调用,检查单个MySQL实例中每个channel的状态
    CheckEachMySQL(){
        local mysql_port=$1
        local channles_all=(`grep "^$mysql_port" $CONFIG_FILE | awk -F":" '{for(i=2;i<=NF;i++){print $i}}'`)
        for channle in ${channles_all[*]}
        do
             local check_sql=$(echo "$CHECK_SQL" | sed 's/CHNAME/'"$channle"'/g')
             local channle_res=$(echo "$check_sql" | ${MYSQL_BIN} -u$ZBX_USER -p$ZBX_PASSWD -h127.0.0.1 -P$mysql_port 2> /dev/null)
             local transactions_retries=$(echo "$channle_res" | awk '/Transactions_Retries:/{print $NF+0}')
             local slave_iO_running=$(echo "$channle_res" | awk '/Slave_IO_Running:/{if($NF=="Yes"){print 1}else{print 0}}')
             local slave_sql_running=$(echo "$channle_res" | awk '/Slave_SQL_Running:/{if($NF=="Yes"){print 1}else{print 0}}')
             local seconds_behind_master=$(echo "$channle_res" | awk '/Seconds_Behind_Master:/{print $NF}')
             echo "$mysql_port:$channle:$slave_iO_running:$slave_sql_running:$seconds_behind_master:$transactions_retries" >> $RESULT_FILE
        done
    }
    
    #复制检查总函数,通过调用CheckEachMySQL()函数检查每个MySQL实例
    ChannelsCheck(){
        > $RESULT_FILE
        chown zabbix:zabbix $RESULT_FILE
        for mysql_port in `cat $CONFIG_FILE | awk -F":" '{print $1}'`
        do
            CheckEachMySQL $mysql_port
        done
    }
    
    
    # main function
    case "$1" in
        channels_discovery)
            ChannelsDiscovery
        ;;
        channels_check)
            ChannelsCheck
        ;;
        *)
            echo "Usage:$0 {channels_discovery|channels_check}"
        ;;
    esac
    

    zabbix_agentd.d/mysql_multi_master.conf :

    UserParameter=mysql_multi_master.channels_discovery,/bin/bash /etc/zabbix/plugins/zbx_mysql_multi_master.sh channels_discovery
    UserParameter=mysql_multi_master.channels_check,/bin/bash /etc/zabbix/plugins/zbx_mysql_multi_master.sh channels_check
    UserParameter=mysql_multi_master.io_thread[*],grep -w $1 /tmp/multi_master_slave_info | awk -F":" '{print $$3}'
    UserParameter=mysql_multi_master.sql_thread[*],grep -w $1 /tmp/multi_master_slave_info | awk -F":" '{print $$4}'
    UserParameter=mysql_multi_master.slave_lag[*],grep -w $1 /tmp/multi_master_slave_info | awk -F":" '{print $$5}'
    UserParameter=mysql_multi_master.transactions_retries[*],grep -w $1 /tmp/multi_master_slave_info | awk -F":" '{print $$6}'
    
  • 相关阅读:
    基于centos的freeradius高可用lvs(UDP)
    sql server 2012的AlwaysOn高可用
    python基础题型一
    用户访一个APP或者网页流程示意图
    DNS解析流程
    crontab的定时任务实例
    Xcode设置
    Nvidia Nsight + .NET
    C++ Pointer-to-Member Selector
    C++11
  • 原文地址:https://www.cnblogs.com/wshenjin/p/10157152.html
Copyright © 2020-2023  润新知