zabbix mysql监控项

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