#!/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}'