笺注:这是在 Zabbix使用Agent监控CentOS6/Redhat6 的基础上进行的。
被监控主机配置MySQL数据库:( 使用数据库管理员root@localhost )
[root@localhost ~]# mysql -u"root" -p"123"
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.48-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
注释:
-h"localhost" 是可以省略的;
查看当前登录的数据库用户:
mysql> Select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql>
再授权数据库用户zbx_monitor@localhost(密码168),只能本地登录,对所有的库有完全控制的权限:
mysql> grant all on *.* to zbx_monitor@localhost identified by '168';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
查看本地数据库用户zbx_monitor@localhost的权限:
mysql> show grants for zbx_monitor@localhost;
+-----------------------------------------------------------------------------------------------------------------------------+
| Grants for zbx_monitor@localhost |
+-----------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zbx_monitor'@'localhost' IDENTIFIED BY PASSWORD '*242E46A1E8D30FE06F7CE37B55BFC25BA981D70C' |
+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
#######
修改被监控主机的Agent配置文件:
[root@zabbix_client ~]# find / -name zabbix_agentd.conf
/etc/zabbix/zabbix_agentd.conf
[root@zabbix_client ~]#
[root@zabbix_client ~]# vi /etc/zabbix/zabbix_agentd.conf
# UnsafeUserParameters=0
修改为:(启用该功能)
UnsafeUserParameters=1
接着插入代码:
- UserParameter=mysql.ping[*], mysqladmin -u"zbx_monitor" -p"168" -h"$1" -P"$2" ping
- UserParameter=mysql.get_status_variables[*], mysql -u"zbx_monitor" -p"168" -h"$1" -P"$2" -sNX -e "show global status"
- UserParameter=mysql.version[*], mysqladmin -u"zbx_monitor" -p"168" -s -h"$1" -P"$2" version
- UserParameter=mysql.db.discovery[*], mysql -u"zbx_monitor" -p"168" -h"$1" -P"$2" -sN -e "show databases"
- UserParameter=mysql.dbsize[*], mysql -u"zbx_monitor" -p"168" -h"$1" -P"$2" -sN -e "SELECT SUM(DATA_LENGTH + INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$3'"
- UserParameter=mysql.replication.discovery[*], mysql -u"zbx_monitor" -p"168" -h"$1" -P"$2" -sNX -e "show slave status"
- UserParameter=mysql.slave_status[*], mysql -u"zbx_monitor" -p"168" -h"$1" -P"$2" -sNX -e "show slave status"
复制代码
如下图:
代码的模板:
[root@zabbix_client ~]# find / -name "*_mysql.conf"
/usr/share/doc/zabbix-agent-4.4.5/userparameter_mysql.conf
[root@zabbix_client ~]#
- [root@zabbix_client ~]# cat /usr/share/doc/zabbix-agent-4.4.5/userparameter_mysql.conf |grep -v ^#
- UserParameter=mysql.ping[*], mysqladmin -h"$1" -P"$2" ping
- UserParameter=mysql.get_status_variables[*], mysql -h"$1" -P"$2" -sNX -e "show global status"
- UserParameter=mysql.version[*], mysqladmin -s -h"$1" -P"$2" version
- UserParameter=mysql.db.discovery[*], mysql -h"$1" -P"$2" -sN -e "show databases"
- UserParameter=mysql.dbsize[*], mysql -h"$1" -P"$2" -sN -e "SELECT SUM(DATA_LENGTH + INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$3'"
- UserParameter=mysql.replication.discovery[*], mysql -h"$1" -P"$2" -sNX -e "show slave status"
- UserParameter=mysql.slave_status[*], mysql -h"$1" -P"$2" -sNX -e "show slave status"
复制代码
保存好配置文件后,重启zabbix-agent:
[root@zabbix_client ~]# service zabbix-agent restart
Shutting down Zabbix agent: [确定]
Starting Zabbix agent: [确定]
被监控主机本地测试:
[root@zabbix_client ~]# mysqladmin -u"zbx_monitor" -p"168" ping
-bash: mysqladmin: command not found
[root@zabbix_client ~]#
解决方法:
[root@zabbix_client ~]# mysql -u"zbx_monitor" -p"168" -e "show variables like '%char%'"
[root@zabbix_client ~]# mysql -u"zbx_monitor" -p"168" -h"localhost" -sN -e "show variables like '%char%'" |grep character_sets_dir |awk '{print $2}'
/usr/local/mysql/share/charsets/
[root@zabbix_client ~]#
注释:
-h"localhost" 是可以省略的;
这里可以看到MySQL的安装目录为 /usr/local/mysql/
MySQL的命令存放目录为 /usr/local/mysql/bin/
可以看到命令mysql已经做了软链接:
[root@zabbix_client ~]# ll /usr/bin/mysql
lrwxrwxrwx. 1 root root 26 2月 8 10:32 /usr/bin/mysql -> /usr/local/mysql/bin/mysql
[root@zabbix_client ~]#
[root@zabbix_client ~]# which mysql
/usr/bin/mysql
[root@zabbix_client ~]#
再给命令mysqladmin做一个软链接:
[root@zabbix_client ~]# ln -s /usr/local/mysql/bin/mysqladmin /usr/bin/
[root@zabbix_client ~]#
[root@zabbix_client ~]# ll /usr/bin/mysqladmin
lrwxrwxrwx. 1 root root 31 2月 8 11:18 /usr/bin/mysqladmin -> /usr/local/mysql/bin/mysqladmin
[root@zabbix_client ~]#
[root@zabbix_client ~]# which mysqladmin
/usr/bin/mysqladmin
[root@zabbix_client ~]#
使用命令mysqladmin检测MySQL服务有没有在运行中:(在运行时)
[root@zabbix_client ~]# service mysql status
SUCCESS! MySQL running (1904)
[root@zabbix_client ~]#
[root@zabbix_client ~]# mysqladmin -u"zbx_monitor" -p"168" ping
mysqld is alive
[root@zabbix_client ~]#
[root@zabbix_client ~]# service mysql stop
Shutting down MySQL. SUCCESS!
[root@zabbix_client ~]#
MySQL服务没有在运行时:
[root@zabbix_client ~]# mysqladmin -u"zbx_monitor" -p"168" ping
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists!
[root@zabbix_client ~]#
[root@zabbix_client ~]# service mysql start
Starting MySQL.. SUCCESS!
[root@zabbix_client ~]#
数据库用户有权限的话,都可以使用这些命令:
[root@zabbix_client ~]# mysqladmin -u"root" -p"123" ping
mysqld is alive
[root@zabbix_client ~]#
使用命令mysqladmin查看MySQL的版本信息等等:
[root@zabbix_client ~]# mysqladmin -u"zbx_monitor" -p"168" -s version
mysqladmin Ver 8.42 Distrib 5.5.48, for Linux on x86_64
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version 5.5.48-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 35 min 16 sec
Threads: 2 Questions: 125 Slow queries: 0 Opens: 66 Flush tables: 1 Open tables: 59 Queries per second avg: 0.059
[root@zabbix_client ~]#
注释:这里可以看到目前MySQL服务运行了35分16秒;
使用命令mysql显示MySQL里的全部库:
[root@zabbix_client ~]# mysql -u"zbx_monitor" -p"168" -sN -e "show databases"
information_schema
discuz
mysql
performance_schema
[root@zabbix_client ~]#
使用命令mysql显示库discuz的大小:(返回值以字节为单位)
[root@zabbix_client ~]# mysql -u"zbx_monitor" -p"168" -h"localhost" -P"3306" -sN -e "select concat(round(sum(DATA_LENGTH),2)) as data from information_schema.TABLES where table_schema = 'discuz';"
231323.00
[root@zabbix_client ~]#
[root@zabbix_client ~]# mysql -u"zbx_monitor" -p"168" -h"localhost" -P"3306" -sN -e "select concat(sum(DATA_LENGTH)) as data from information_schema.TABLES where table_schema = 'discuz';"
231323
[root@zabbix_client ~]#
[root@zabbix_client ~]# mysql -u"zbx_monitor" -p"168" -h"localhost" -P"3306" -sN -e "select concat(sum(DATA_LENGTH + INDEX_LENGTH)) as data from information_schema.TABLES where table_schema = 'discuz';"
393115
[root@zabbix_client ~]#
[root@zabbix_client ~]# mysql -u"zbx_monitor" -p"168" -h"localhost" -P"3306" -sN -e "SELECT SUM(DATA_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='discuz'"
231323
[root@zabbix_client ~]#
[root@zabbix_client ~]# mysql -u"zbx_monitor" -p"168" -h"localhost" -P"3306" -sN -e "SELECT SUM(DATA_LENGTH + INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='discuz'"
393115
[root@zabbix_client ~]#
注释:
data_length + index_length 等于库的表的总大小:
data_length: 数据的实际大小
index_length: 表索引的大小
备注:这相当于在被监控主机上进行本地监控,所以被监控主机无需打开防火墙的TCP 3306端口。
##############
##############
在Zabbix服务器测试,验证能否获取被监控主机(192.168.168.130)的键值:
检测远程MySQL服务有没有在运行中:(在运行时)
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 192.168.168.130 -k mysql.ping["localhost","3306"]
mysqld is alive
[root@centos8 ~]#
查看远程MySQL的版本信息等等:
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 192.168.168.130 -k mysql.version["localhost","3306"]
mysqladmin Ver 8.42 Distrib 5.5.48, for Linux on x86_64
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version 5.5.48-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 1 hour 2 min 19 sec
Threads: 2 Questions: 162 Slow queries: 0 Opens: 66 Flush tables: 1 Open tables: 59 Queries per second avg: 0.043
[root@centos8 ~]#
只显示远程MySQL的版本信息:
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 192.168.168.130 -k mysql.version["localhost","3306"] |grep "Server version" |awk '{print $3}' |cut -d- -f 1
5.5.48
[root@centos8 ~]#
只显示远程MySQL运行了多长时间:
[root@centos8 ~]# Key_1=`/usr/local/zabbix/bin/zabbix_get -s 192.168.168.130 -k mysql.version["localhost","3306"] |grep "Uptime:" |awk -F: '{print $2}'`
[root@centos8 ~]# echo $Key_1
1 hour 2 min 50 sec
[root@centos8 ~]#
注释:这里可以看到目前MySQL服务运行了1小时2分50秒;
一旦MySQL服务重启了,MySQL服务的运行时长就会重新开始计算:
[root@zabbix_client ~]# service mysql restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[root@zabbix_client ~]#
[root@centos8 ~]# Key_1=`/usr/local/zabbix/bin/zabbix_get -s 192.168.168.130 -k mysql.version["localhost","3306"] |grep "Uptime:" |awk -F: '{print $2}'`
[root@centos8 ~]# echo $Key_1
8 sec
[root@centos8 ~]#
##################Zabbix添加被监控主机:
配置》主机》创建主机 (主机名称无需与被监控主机的真实主机名一致;群组可以随便选;IP地址那里要输入被监控主机的IP地址;端口为10050)
注释:
使用agent代理程序的接口。
使用 TCP 10050 端口。
主机添加成功:
给主机添加模板:( Template DB MySQL )
注释:模板Template DB MySQL 是Zabbix自带的。
模板添加成功:
查看主机MySQL_1继承模板的宏:
###
查看模板Template DB MySQL自带图形:(要等一段时间才会出现)
监测》图形:
查看主机MySQL_1的图形MySQL bandwidth:
注释:“还剩15分钟”是指从此刻开始算起,之前的15分钟。
######
模板Template DB MySQL自带的监控MySQL服务状态的监控项:
名称: Availability: MySQL status
键值: mysql.ping["{$MYSQL.HOST}","{$MYSQL.PORT}"]
本页拖下去:(默认已启用)
模板Template DB MySQL自带的监控MySQL服务状态的触发器:
名称: MySQL: Service is down
表达式: {MySQL_1:mysql.ping["{$MYSQL.HOST}","{$MYSQL.PORT}"].last()}=0
注释:触发器的表达式要用到监控项中的键值。
本页拖下去:(默认已启用)
假如被监控主机的MySQL服务没有在运行了,仪表板会如下图显示:(显示对应触发器的名称)
MySQL: Service is down
######
模板Template DB MySQL自带的监控MySQL服务运行时长的监控项:
名称: Info: Uptime
键值: mysql.uptime
模板Template DB MySQL自带的监控MySQL服务运行时长的触发器:
名称: MySQL: Service has been restarted (uptime < 10m)
表达式: {MySQL_1:mysql.uptime.last()}<10m
注释:触发器的表达式要用到监控项中的键值。
假如被监控主机的MySQL服务重启了,仪表板会如下图显示:(显示对应触发器的名称)
MySQL: Service has been restarted (uptime < 10m)
注释:这告警10分钟后自动消失。
###
给监控项“Info: Uptime”创建图形:(新图形的名称是自定义的)
监控项: MySQL_1: Info: Uptime
至此,查看图形就可以知道被监控主机的MySQL服务运行了多长时间:
注释:实验中,被监控主机的MySQL服务停止过一段时间,所以图形中有隔断。
注释:这里可以看到被监控主机的MySQL服务目前运行了26分35秒;一旦被监控主机的MySQL服务重启了,“最新”值就会从00:00:00重新开始。
######
模板Template DB MySQL有自动发现MySQL各个库的“自动发现规则”:
名称: Databases discovery
键值: mysql.db.discovery["{$MYSQL.HOST}","{$MYSQL.PORT}"]
在Zabbix服务器上可以使用命令行显示远程MySQL里的全部库:
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 192.168.168.130 -k mysql.db.discovery["localhost","3306"]
information_schema
discuz
mysql
performance_schema
[root@centos8 ~]#
注释:
键值的格式要改变一下,不要使用变量;
这相当于在被监控主机上进行本地监控,所以应该使用"localhost"
######
模板Template DB MySQL有自动检查MySQL各个库的大小的监控项:
监控项名称: Info: Size of database mysql
键值: mysql.dbsize["{$MYSQL.HOST}","{$MYSQL.PORT}","mysql"]
在Zabbix服务器上可以使用命令行获取到库“mysql”的大小:(返回值以字节为单位)
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 192.168.168.130 -k mysql.dbsize["localhost","3306","mysql"]
704138
[root@centos8 ~]#
######
监控项名称: Info: Size of database discuz
键值: mysql.dbsize["{$MYSQL.HOST}","{$MYSQL.PORT}","discuz"]
在Zabbix服务器上可以使用命令行获取到库“discuz”的大小:(返回值以字节为单位)
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 192.168.168.130 -k mysql.dbsize["localhost","3306","discuz"]
393115
[root@centos8 ~]#
获取到值,就可以在Zabbix服务器上编写Shell脚本进行监控了:
[root@centos8 ~]# mkdir -p /script
[root@centos8 ~]#
[root@centos8 ~]# cat /script/xx.sh
#!/bin/bash
Key_1=$(/usr/local/zabbix/bin/zabbix_get -s 192.168.168.130 -k mysql.dbsize["localhost","3306","discuz"])
echo $Key_1
if [ $Key_1 -gt 300000 ]; then
echo "----------"
echo 'Big'
fi
脚本运行的结果:
[root@centos8 ~]# bash /script/xx.sh
393115
----------
Big
[root@centos8 ~]#
相关文章:
使用Navicat远程管理MySQL
Zabbix使用模板监控MySQL主从同步状态
Zabbix调用Python3脚本监控Linux下的MySQL
Zabbix使用模板监控Windows下的MySQL
Zabbix自定义监控Linux下的MySQL
Zabbix使用模板监控Windows下的MariaDB
Zabbix使用模板监控Linux下的MariaDB |