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 '{\n'
        printf '\t"data":[\n'
    fi

    for((i=0;i<${#channle_list[@]};++i))
    {
        if [ "$i" -lt $((${num}-1)) ];then
            printf "\t\t{ \n"
            printf "\t\t\t\"{#CHANNEL_NAME}\":\"${channle_list[$i]}\"\n"
            printf "\t\t},\n"
        else
            printf  "\t\t{ \n"
            printf  "\t\t\t\"{#CHANNEL_NAME}\":\"${channle_list[$((${num}-1))]}\"\n"
            printf  "\t\t}]\n}\n"
        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}'