zabbix_agentd.conf 文件添加自定义键值
UserParameter=mysql_list[],/etc/zabbix/scripts/mysql.sh
UserParameter=mysql.slave[],/etc/zabbix/scripts/mysqlmonitor.sh $1
UserParameter=mysql.statsvp[],/etc/zabbix/scripts/chk_mysql.sh $1
UserParameter=discovery.mysqluser,/etc/zabbix/scripts/discovery_mysql_user.sh
UserParameter=mysqluser.check[],/etc/zabbix/scripts/mysql_user_check.sh $1
chk_mysql.sh 性能
#!/bin/bash
# -------------------------------------------------------------------------------
# FileName: check_mysql.sh
# Revision: 1.0
# -------------------------------------------------------------------------------
# Copyright:
# License: GPL
# 用户名
MYSQL_USER='root'
# 密码
MYSQL_PWD='密码'
# 主机地址/IP
MYSQL_HOST='localhost'
# 端口
MYSQL_PORT='3306'
# 数据连接
MYSQL_CONN="/usr/bin/mysqladmin -u${MYSQL_USER} -p${MYSQL_PWD} -h${MYSQL_HOST} -P${MYSQL_PORT}"
# 参数是否正确
if [ $# -ne "1" ];then
echo "arg error!"
fi
# 获取数据
case $1 in
Uptime)
result=`${MYSQL_CONN} status 2>/dev/null |cut -f2 -d":"|cut -f1 -d"T"`
echo $result
;;
Com_update)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Com_update"|cut -d"|" -f3`
echo $result
;;
Slow_queries)
result=`${MYSQL_CONN} status 2>/dev/null |cut -f5 -d":"|cut -f1 -d"O"`
echo $result
;;
Com_select)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Com_select"|cut -d"|" -f3`
echo $result
;;
Com_rollback)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Com_rollback"|cut -d"|" -f3`
echo $result
;;
Questions)
result=`${MYSQL_CONN} status 2>/dev/null |cut -f4 -d":"|cut -f1 -d"S"`
echo $result
;;
Com_insert)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Com_insert"|cut -d"|" -f3`
echo $result
;;
Com_delete)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Com_delete"|cut -d"|" -f3`
echo $result
;;
Com_commit)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Com_commit"|cut -d"|" -f3`
echo $result
;;
Bytes_sent)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Bytes_sent" |cut -d"|" -f3`
echo $result
;;
Bytes_received)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Bytes_received" |cut -d"|" -f3`
echo $result
;;
Com_begin)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Com_begin"|cut -d"|" -f3`
echo $result
;;
Threads_connected)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Threads_connected"|cut -d"|" -f3`
echo $result
;;
Threads_running)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Threads_running"|cut -d"|" -f3`
echo $result
;;
Innodb_row_lock_current_waits)
result=`${MYSQL_CONN} extended-status 2>/dev/null |grep -w "Innodb_row_lock_current_waits"|cut -d"|" -f3`
echo $result
;;
Exec_long_time)
result=`mysql -u${MYSQL_USER} -p${MYSQL_PWD} -h${MYSQL_HOST} -P${MYSQL_PORT} -e "SELECT count(*) FROM information_schema.processlist WHERE COMMAND<>'Sleep' AND TIME >60 AND info IS NOT NULL AND user NOT IN ('root','event_scheduler','system user ','master','repl','repl1') " 2> /dev/null |sed 1d `
echo $result
;;
*)
echo "Usage:$0(Uptime|Com_update|Slow_queries|Com_select|Com_rollback|Questions|Com_insert|Com_delete|Com_commit|Bytes_sent|Bytes_received|Com_begin|Threads_connected|Threads_running|Innodb_row_lock_current_waits|Exec_long_time)"
;;
esac
mysqlmonitor.sh 主从状态
#!/bin/bash
#Desc:用于获取主从同步信息,判断主从是否出现异常,然后提交给zabbix
USER="root"
PASSWD="密码"
NAME=$1
function IO {
Slave_IO_Running=`/usr/bin/mysql -u $USER -p$PASSWD -e "show slave status\G;" 2> /dev/null |grep Slave_IO_Running |awk '{print $2}'`
if [[ $Slave_IO_Running == "Yes" ]];then
echo 0
else
echo 1
fi
}
function SQL {
Slave_SQL_Running=`/usr/bin/mysql -u $USER -p$PASSWD -e "show slave status\G;" 2> /dev/null |grep Slave_SQL_Running: |awk '{print $2}'`
if [[ $Slave_SQL_Running == "Yes" ]];then
echo 0
else
echo 1
fi
}
function Second {
Second=`/usr/bin/mysql -u $USER -p$PASSWD -e "show slave status\G;" 2> /dev/null |grep Seconds_Behind_Master: |awk '{print $2}'`
echo $Second
}
case $NAME in
io)
IO
;;
sql)
SQL
;;
second)
Second
;;
*)
echo -e "Usage: $0 [io | sql |second]"
esac
mysql.sh
echo `netstat -atnp |grep 3306 |wc -l`
discovery_mysql_user.sh 获取连接用户 自动发现项
# cat discovery_mysql_user.sh
#!/bin/bash
/usr/bin/mysql -uroot -密码 -e"SELECT count(*) num,user FROM information_schema.processlist WHERE user NOT IN ('root','event_scheduler','system user','master','repl','repl1') group by user having num>5 order by num desc ;" > /etc/zabbix/scripts/zabbix_mysql_user_count.txt 2>/dev/null
proc_array=(`tail -n +2 /etc/zabbix/scripts/zabbix_mysql_user_count.txt | awk '{a[$NF]+=$1}END{for(k in a)print a[k],k}'|cut -d" " -f2`)
length=${#proc_array[@]}
printf "{\n"
printf '\t'"\"data\":["
for ((i=0;i<$length;i++))
do
printf "\n\t\t{"
printf "\"{#USER_NAME}\":\"${proc_array[$i]}\"}"
if [ $i -lt $[$length-1] ];then
printf ","
fi
done
printf "\n\t]\n"
printf "}\n"
mysql_user_check.sh 获取mysql用户连接数量
# cat mysql_user_check.sh
#!/bin/bash
process=$1
mysql_user_count=`tail -n +2 /etc/zabbix/scripts/zabbix_mysql_user_count.txt | awk '{a[$NF]+=$1}END{for(k in a)print a[k],k}' | grep -w $1 | cut -d" " -f1`
echo "$mysql_user_count"
测试
chmod 777 discovery_mysql_user.sh mysql_user_check.sh zabbix_mysql_user_count.txt
zabbix_get -p10050 -k ‘discovery.mysqluser’ -s ip
zabbix_get -p10050 -k ‘mysqluser.check[user]’ -s ip