Zabbix监控Mysql数据库信息

zabbix-agent端监控mysql数据 保证zabbix先安装成功 1.在zabbix-agent端安装mysql数据库

yum -y install mariadb mariadb-server

2.开启mysql数据库

systemctl start mariadb

3.mysql数据库监控的内容有：

mysql的吞吐量mysql的常规操作（增删改查）QPS（Questions Per second:）每秒能处理多少次请求数TPS（Transactions Per Second）每秒查询处理的事务数mysql库大小和表大小监控innodb缓冲池的read命中率 mkdir /etc/zabbix/scripts/ cd /etc/zabbix/scripts/ vim mysql.sh #!/bin/bash case $1 in #mysql吞吐量 #流量发送 Bytes_sent) mysqladmin extended-status|grep "Bytes_sent"|awk '{print $4}' ;; #流量接收 Bytes_received) mysqladmin extended-status|grep "Bytes_received"|awk '{print $4}' ;; #mysql数据库增删改查 #增 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}' ;; #mysql的性能指标 qps) q1=`mysql -s -e "show global status like 'Question%'"|awk '{print $NF}'` t1=`uptime |awk '{print $3}'|sed "s/,//"|awk -F ":" '{print $1*3600+$2*60}'` n=`echo "scale=4;$q1/$t1"|bc` echo $n ;; tps) c1=`mysql -s -e "show global status like 'Com_commit'"|awk '{print $NF}'` r1=`mysql -s -e "show global status like 'Com_rollback'"|awk '{print $NF}'` t1=`uptime |awk '{print $3}'|sed "s/,//"|awk -F ":" '{print $1*3600+$2*60}'` n1=$((c1+r1)) n=`echo "scale=4;$n1/$t1"|bc` echo $n ;; #mysql的库大小、表大小 #mysql库大小 db_size) mysql -e "select sum(data_length)from information_schema.tables where table_schema='mysql';"|sed '1d' ;; tb_size) mysql -e "select sum(data_length)from information_schema.tables where table_schema='mysql'and table_name='user';"|sed '1d' ;; #监控innodb缓冲池的read命中率 innodb_read) mysql -e "show global status like 'innodb%read%';" | grep Innodb_buffer_pool_read_requests | awk '{print $2}' ;; esac

4.增加监控项（自定义key）

[root@localhost scripts]# cd /etc/zabbix/zabbix_agentd.d/ [root@localhost zabbix_agentd.d]# vim mysql.conf UserParameter=mysql[*],/etc/zabbix/scripts/mysql.sh $1

4.1.赋予脚本权限

[root@localhost zabbix_agentd.d]# chmod +x /etc/zabbix/scripts/mysql.sh

5.重启agent服务！！！

[root@localhost zabbix_agentd.d]# systemctl restart zabbix-agent

6.zabbix-server服务端安装zabbix-get命令

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

测试自定义key: mysql[*]是否能获取数据

[root@localhost ~]# zabbix_get -s 192.168.232.136 -k mysql[Bytes_sent] 154749 [root@localhost ~]# zabbix_get -s 192.168.232.136 -k mysql[Bytes_received] 3001 [root@localhost ~]# zabbix_get -s 192.168.232.136 -k mysql[Com_insert] 0

7.zabbix页面添加监控项 创建主机 创建监控项 添加图形 监测图形