运维之道 | Zabbix 监控MySQL数据库状态信息

最新推荐文章于 2024-07-04 18:43:02 发布

VillianTsang

最新推荐文章于 2024-07-04 18:43:02 发布

阅读量871

点赞数

分类专栏： zabbix

本文链接：https://blog.csdn.net/VillianTsang/article/details/104341627

版权

本文详细介绍了如何使用Zabbix监控MySQL数据库的状态信息，包括zabbix-agent端的脚本配置、权限修改、配置文件更新及服务重启，以及zabbix-server端的模板创建、应用集、监控项定义、图形展示和主机模板关联等步骤。

摘要由CSDN通过智能技术生成

一、zabbix-agent 端配置

1、配置脚本获取mysql的各种参数

[root@zabbix ~]# vim /etc/zabbix/zabbix_agentd.d/mysql_status.sh

#!/bin/bash
MySQlBin=/usr/bin/mysql
MySQLAdminBin=mysqladmin
Host=localhost
if [[ $# == 1 ]];then
case $1 in
Ping)
result=$($MySQLAdminBin -h$Host ping|grep alive|wc -l)
echo $result
;;
Threads)
result=$($MySQLAdminBin -h$Host status|cut -f3 -d":"|cut -f1 -d"Q")
echo $result
;;
Questions)
result=$($MySQLAdminBin -h$Host status|cut -f4 -d":"|cut -f1 -d"S")
echo $result
;;
Slowqueries)
result=$($MySQLAdminBin -h$Host status|cut -f5 -d":"|cut -f1 -d"O")
echo $result
;;
Qps)
result=$($MySQLAdminBin -h$Host status|cut -f9 -d":")
echo $result
;;
Slave_IO_State)
result=$(if [ "$($MySQlBin -h$Host -e "show slave status\G"| grep Slave_IO_Running|awk '{print $2}')" == "Yes" ];then echo 1; else echo 0;fi)
echo $result
;;
Slave_SQL_State)
result=$(if [ "$($MySQlBin -h$Host -e "show slave status\G"| grep Slave_SQL_Running|awk '{print $2}')" == "Yes" ];then echo 1; else echo 0;fi)
echo $result
;;
Key_buffer_size)
result=$($MySQlBin -h$Host -e "show variables like 'key_buffer_size';"| grep -v Value |awk '{print $2/1024^2}')
echo $result
;;
Key_reads)
result=$($MySQlBin -h$Host -e "show status like 'key_reads';"| grep -v Value |awk '{print $2}')
echo $result
;;
Key_read_requests)
result=$($MySQlBin -h$Host -e "show status like 'key_read_requests';"| grep -v Value |awk '{print $2}')
echo $result
;;
Key_cache_miss_rate)
result=$(echo $($MySQlBin -h$Host -e "show status like 'key_reads';"| grep -v Value |awk '{print $2}') $($MySQlBin -h$Host -e "show status like 'key_read_requests';"| grep -v Value |awk '{print $2}')| awk '{if($2==0)printf("%1.4f\n",0);else printf("%1.4f\n",$1/$2*100);}')
echo $result
;;
Key_blocks_used)
result=$($MySQlBin -h$Host -e "show status like 'key_blocks_used';"| grep -v Value |awk '{print $2}')
echo $result
;;
Key_blocks_unused)
result=$($MySQlBin -h$Host -e "show status like 'key_blocks_unused';"| grep -v Value |awk '{print $2}')
echo $result
;;
Key_blocks_used_rate)
result=$(echo $($MySQlBin -h$Host -e "show status like 'key_blocks_used';"| grep -v Value |awk '{print $2}') $($MySQlBin -h$Host -e "show status like 'key_blocks_unused';"| grep -v Value |awk '{print $2}')| awk '{if(($1==0) && ($2==0))printf("%1.4f\n",0);else printf("%1.4f\n",$1/($1+$2)*100);}')
echo $result
;;
Innodb_buffer_pool_size)
result=$($MySQlBin -h$Host -e "show variables like 'innodb_buffer_pool_size';"| grep -v Value |awk '{print $2/1024^2}')
echo $result
;;
Innodb_log_file_size)
result=$($MySQlBin -h$Host -e "show variables like 'innodb_log_file_size';"| grep -v Value |awk '{print $2/1024^2}')
echo $result
;;
Innodb_log_buffer_size)
result=$($MySQlBin -h$Host -e "show variables like 'innodb_log_buffer_size';"| grep -v Value |awk '{print $2/1024^2}')
echo $result
;;
Table_open_cache)
result=$($MySQlBin -h$Host -e "show variables like 'table_open_cache';"| grep -v Value |awk '{print $2}')
echo $result
;;
Open_tables)
result