zabbix 监控mysql的方法

zabbix部署文档

zabbix部署完之后

zabbix-agent操作

 1.监控mysql,首先要先安装mysql

[root@localhost ~]# yum -y install mariadb mariadb-server

2.编写mysql监控项的脚本

在zabbix-agent先授权个用户 不然测试时没有权限

[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 33
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant all>
[root@localhost ~]# mkdir /etc/zabbix/scipts
[root@localhost ~]# cd /etc/zabbix/scipts/
[root@localhost scipts]# vim mysql.sh 
#!/bin/bash
mysql="mysql -ucheck -p123"
case $1 in 
 # mysql主从状态
 slave_status)
  $mysql -e "show slave status\G" |grep "Yes" |wc -l
 ;; 
 # mysql流量 接受
 Bytes_received)
  mysqladmin extended-status |grep "Bytes_received" |awk '{print $4}'
 ;;
 # mysql流量 发送
 Bytes_sent)
  mysqladmin extended-status |grep "Bytes_sent" |awk '{print $4}'
 ;;
 # mysql常规操作 增
 Com_insert)
  mysqladmin extended-status |grep -w "Com_insert" |awk '{print $4}'
 ;;
 # mysql常规操作 删
 Com_delete)
  mysqladmin extended-status |grep -w "Com_delete" |awk '{print $4}'
 ;;
 # mysql常规操作 改
 Com_update)
  mysqladmin extended-status |grep -w "Com_update" |awk '{print $4}'
		;;
 # mysql常规操作 查
 Com_select)
  mysqladmin extended-status |grep -w "Com_select" |awk '{print $4}'
 ;;
 # mysql tps
 tps)
  mysqladmin status |awk '{print $6/$2}'
 ;;
 # mysql qps=(rollback+commit)/uptime
 qps)
  rollback=$(mysqladmin extended-status |grep -w "Com_rollback" |awk '{print $4}')
  commit=$(mysqladmin extended-status |grep -w "Com_commit" |awk '{print $4}')
  uptime=$(mysqladmin status |awk '{print $2}')
  count=$[$rollback+$commit]
  echo "$count $uptime" > /tmp/a.txt
  cat /tmp/a.txt |awk '{print $1/$2}'
 ;;
 # 库大小 我们这里拿mysql库举例
 db)
  $mysql -e "select sum(data_length) from information_schema.tables where table_schema='mysql'" |sed -n '2p'
 ;;
 # 表大小 我们这里拿mysql下面的user表举例
 tb)
  $mysql -e "select sum(data_length) from information_schema.tables where table_schema='mysql' and table_name='user'" |sed -n '2p'
 ;;
esac

3.自定义键值key 重启zabbix-agent

[root@localhost scipts]# cd /etc/zabbix/zabbix_agentd.d/
[root@localhost zabbix_agentd.d]# vim mysql.conf
UserParameter=mysql[*],/etc/zabbix/scipts/mysql.sh $1
[root@localhost zabbix_agentd.d]# systemctl restart zabbix-agent

4.在zabbix-server测试 先安装zabbix-get

[root@localhost ~]# yum -y install zabbix-get

[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[slave_status]
2
[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[Bytes_received]
850970
[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[Bytes_sent]
224906
[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[Com_insert]
3001
[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[Com_delete]
135
[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[Com_update]
128
[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[Com_select]
19
[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[qps]
0.864842
[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[tps]
1.92936
[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[db]
555118
[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[tb]
420

报错处理

[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[slave_status]
sh: /etc/zabbix/scipts/mysql.sh: 权限不够

脚本执行权限不够 去zabbix-agent 加权限
[root@localhost zabbix_agentd.d]# chmod +x /etc/zabbix/scipts/mysql.sh 

[root@localhost ~]# zabbix_get -s 192.168.27.137 -k mysql[slave_status]
ERROR 1227 (42000) at line 1: Access denied; you need (at least
case $1 in 
 # mysql主从状态
 slave_status)
  $mysql -e "show slave status\G" |grep "Yes" |wc -l
 ;; 

zabbix页面上添加监控项和图形

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

查看mysql流量数据

在这里插入图片描述

zabbix 监控mysql的方法

扫一扫手机访问