ZABBIX 监控MYSQL

zabbix客户端操作

1.编写自定义监控MYSQL的脚本

[root@localhost ~]# mkdir /etc/zabbix/scripts
[root@localhost ~]# vim /etc/zabbix/scripts/check_mysql.sh
#!/bin/bash
uptime=$(mysqladmin status|awk '{print $2}')
case $1 in
        slave_status)
                #mysql主从状态
                mysql -e 'show slave status\G' | grep 'Yes' | wc -l
                ;;
        Bytes_sent)
                #发送的流量
                mysqladmin extended-status|grep "Bytes_sent"|awk '{print $4}'
                ;;
        Bytes_received)
                #接收的流量
                mysqladmin extended-status|grep "Bytes_received"|awk '{print $4}'
                ;;
        #mysql operations 操作(增删改查)
        Com_insert)
                mysqladmin extended-status|grep -w "Com_insert"|awk '{print $4}'
                ;;
        Com_delete)
                mysqladmin extended-status|grep -w "Com_delete"|awk '{print $4}'
                ;;
        Com_update)
                mysqladmin extended-status|grep -w "Com_update"|awk '{print $4}'
                ;;
            Com_select)
                mysqladmin extended-status|grep -w "Com_select"|awk '{print $4}'
                ;;
                #提交事务量
        Com_commit)
                mysqladmin extended-status|grep -w "Com_commit"|awk '{print $4}'
                ;;
                #回滚事务量
        Com_rollback)
                mysqladmin extended-status|grep -w "Com_rollback"|awk '{print $4}'
                ;;
        #每秒查询处理量
        qps)
                question=$(mysqladmin status|awk '{print $6}')
                qps=$(($question/$uptime))
                echo $qps
                ;;
    #每秒处理事务数
        tps)
                commit=$(mysqladmin extended-status|grep -w "Com_commit"|awk '{print $4}')
                rollback=$(mysqladmin extended-status|grep -w "Com_rollback"|awk '{print $4}')
                tps=$[($commit+$rollback)/$uptime]
                echo $tps
                ;;
        #数据库大小
        db_size)
                db_size=$(mysql -D information_schema -e "select sum(DATA_LENGTH) from tables where table_schema='mysql'"|sed -n '2p')
                echo ${db_size}
                ;;
        #表大小
        tb_size)
                mysql -D information_schema -e "select sum(DATA_LENGTH) from tables where table_schema='mysql' and table_name='user'"|sed -n '2p'
                ;;

esac

2.编辑客户端配置文件

[root@localhost ~]# vim /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf
UserParameter=mysql.status[*],/etc/zabbix/scripts/check_mysql.sh $1
[root@localhost ~]# chmod +x /etc/zabbix/scripts/check_mysql.sh
[root@localhost ~]# systemctl restart zabbix-agent
[root@localhost ~]# ss -ntl | grep 10050

3.到zabbix服务端检测键值是否可用

下载测试工具
[root@localhost ~]# yum -y install zabbix-get
开始测试

[root@localhost ~]# zabbix_get -s 192.168.141.154 -k mysql.status[slave_start]

 [root@localhost ~]

执行上面的命令，发现zabbix_get 取不到值，显示为NUll，原因是zabbix用户没有执行脚本的权限，我们去授权一个用户，修改以下两个地方

出现这个报错

ERROR 1227 (42000) at line 1: Access denied; you need (at least one of) the SUPER,REPLICATION CLIENT privilege(s) for this operation

也可用此方法解决

[root@localhost ~]# mysql
MariaDB [(none)]> grant all on *.* to zabbix@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit
Bye
[root@localhost ~]# visudo
#下面没有注释的是我们自己添加的
 #Same thing without a password
 #%wheel        ALL=(ALL)       NOPASSWD: ALL
zabbix          ALL=(ALL)       NOPASSWD: ALL
[root@localhost ~]# vim /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf
#在执行脚本前面加上sudo
UserParameter=mysql.status[*],sudo /etc/zabbix/scripts/check_mysql.sh $1
[root@localhost ~]# systemctl restart zabbix-agent

修改完后再次执行

[root@localhost ~]# zabbix_get -s 192.168.141.154 -k mysql.status[slave_status]
2
[root@localhost ~]# zabbix_get -s 192.168.141.154 -k mysql.status[Bytes_sent]
68556
[root@localhost ~]# zabbix_get -s 192.168.141.154 -k mysql.status[Bytes_received]
8200366
[root@localhost ~]# zabbix_get -s 192.168.141.154 -k mysql.status[Com_insert]
15906
[root@localhost ~]# zabbix_get -s 192.168.141.154 -k mysql.status[Com_delete]
81
[root@localhost ~]# zabbix_get -s 192.168.141.154 -k mysql.status[Com_update]
39
[root@localhost ~]# zabbix_get -s 192.168.141.154 -k mysql.status[Com_select]
20
[root@localhost ~]# zabbix_get -s 192.168.141.154 -k mysql.status[Com_commit]
4009
[root@localhost ~]# zabbix_get -s 192.168.141.154 -k mysql.status[Com_rollback]
0
[root@localhost ~]# zabbix_get -s 192.168.141.154 -k mysql.status[qps]
3
[root@localhost ~]# zabbix_get -s 192.168.141.154 -k mysql.status[tps]
0
[root@localhost ~]# zabbix_get -s 192.168.141.154 -k mysql.status[db_size]
555650
[root@localhost ~]# zabbix_get -s 192.168.141.154 -k mysql.status[tb_size]
512

4.测试键值都可用后，可以去web界面配置监控项了
配置需要注意的事项

添加触发器和图形正常操作即可