笺注:
这是在 CentOS8_LNMP_编译安装Zabbix5.0.12 的基础上进行的。
被监控主机的MySQL的安装可参考 LNMP一键安装包(lamp_Apache2.4用户验证+phpMyAdmin)
笺注:使用以下方法,被监控主机不用安装zabbix-agent
被监控主机的信息:
[root@localhost ~]# cat /etc/issue |head -1
CentOS release 6.9 (Final)
[root@localhost ~]#
[root@localhost ~]# uname -r
2.6.32-696.el6.x86_64
[root@localhost ~]#
[root@localhost ~]# ifconfig eth0 |grep "inet addr" |awk '{print $2}' |cut -d: -f 2
192.168.168.130
[root@localhost ~]#
被监控主机配置MySQL数据库:( 使用数据库管理员root@localhost;假如MySQL的端口为默认的TCP 3306,就可以不写 )
[root@localhost ~]# mysql -u"root" -p"123" -h"localhost" -P"3306"
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
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>
查看当前登录的数据库用户:
mysql> Select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql>
查看MySQL的版本:
mysql> Select version();
+------------+
| version() |
+------------+
| 5.5.48-log |
+------------+
1 row in set (0.00 sec)
mysql>
创建一个库db1:
mysql> Create database db1 character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.11 sec)
mysql>
mysql> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
注释:库db1的字符集为 utf8_general_ci
在库db1中创建表、插入测试的数据:
mysql> use db1;
Database changed
mysql>
mysql> Create table Table1(Name varchar(20),Age tinyint);
Query OK, 0 rows affected (0.42 sec)
mysql> INSERT into Table1(Name,Age) values ('Zhuohua',-8);
Query OK, 1 row affected (0.10 sec)
mysql> INSERT into Table1(Name,Age) values ('Python',3);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT into Table1(Name,Age) values ('小明',16);
Query OK, 1 row affected (0.00 sec)
mysql>
输出库db1的表Table1中的所有记录:
select * from db1.Table1;
在MySQL下查看当前使用的是哪个库:
mysql> Select database();
+------------+
| database() |
+------------+
| db1 |
+------------+
1 row in set (0.01 sec)
mysql>
输出当前库的表Table1中的所有记录:
Select * from Table1;
######
Select Name,Age,ABS(Age) from Table1;
注释:ABS()函数会返回数值的绝对值。
Select abs(Age) from Table1 where Name = 'Zhuohua';
Select Name,LENGTH(Name) AS 字符串的长度 from Table1;
注释:LENGTH()函数会返回字符串的长度;假如MySQL的字符编码是UTF-8,那么一个汉字将返回 3
######
MySQL服务器再授权数据库用户zhuohua@'192.168.168.154'(密码168),仅仅在使用IP地址192.168.168.154时可以进行访问,对库db1有完全控制的权限:
mysql> grant all on db1.* to zhuohua@'192.168.168.154' identified by '168';
Query OK, 0 rows affected (0.01 sec)
mysql>
查看远程数据库用户zhuohua@'192.168.168.154'的权限:
mysql> show grants for zhuohua@'192.168.168.154';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for zhuohua@192.168.168.154 |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhuohua'@'192.168.168.154' IDENTIFIED BY PASSWORD '*242E46A1E8D30FE06F7CE37B55BFC25BA981D70C' |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'zhuohua'@'192.168.168.154' |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
mysql>
被监控主机的防火墙配置:(打开TCP 3306端口)
[root@localhost ~]# cat /etc/sysconfig/iptables
# Generated by iptables-save v1.4.7 on Sat Feb 8 10:48:31 2020
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [1:156]
-A INPUT -i lo -j ACCEPT
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p tcp -m tcp --dport 22 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 80 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT
-A INPUT -p icmp -m icmp --icmp-type 8 -j ACCEPT
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT
# Completed on Sat Feb 8 10:48:31 2020
[root@localhost ~]#
保存好配置文件后,重启iptables:
[root@localhost ~]# service iptables restart
iptables:应用防火墙规则:[确定]
[root@localhost ~]#
#############
#############
Zabbix服务器的Python3的安装可参考:CentOS8安装Python3
第三方库PyMySQL的安装可参考:Python3脚本管理Linux下的MySQL
Zabbix服务器使用Python3脚本获取被监控主机的MySQL版本:
[root@centos8 ~]# cat /script/xx.py
#coding=utf-8
import pymysql
def db_connect():
#连接数据库;MySQL服务器的IP地址(192.168.168.130)、用户名、用户密码、其中一个库的名称(db1)、端口号、字符集
db=pymysql.connect("192.168.168.130", "zhuohua", "168", "db1",3306,charset="utf8")
cursor = db.cursor()
Sql_1 = "select version()"
cursor.execute(Sql_1)
Result_1 = cursor.fetchone()
print(Result_1[0])
print("-" * 10)
print(f"数据库的版本信息:{Result_1[0]}")
db.close() #关闭数据库连接
def func_main():
try:
db_connect()
except Exception as e:
print(f"数据库连接失败,原因: {e}")
if __name__ == "__main__":
func_main()
脚本运行的结果:
[root@centos8 ~]# python3 /script/xx.py
5.5.48-log
----------
数据库的版本信息:5.5.48-log
[root@centos8 ~]#
#############
Zabbix服务器使用Python3脚本输出SQL语句执行后的返回值:
[root@centos8 ~]# cat /script/xx.py
#coding=utf-8
import pymysql
def db_connect():
db=pymysql.connect("192.168.168.130", "zhuohua", "168", "db1",3306,charset="utf8")
cursor = db.cursor()
Sql_1 = "Select abs(Age) from Table1 where Name = 'Zhuohua'"
cursor.execute(Sql_1)
Results = cursor.fetchone()
#print (Results) #(8,)
#print (type(Results)) #<class 'tuple'>
print (Results[0])
#print (type(Results[0])) #<class 'int'>
db.close()
def func_main():
try:
db_connect()
except Exception as e:
print(e)
if __name__ == "__main__":
func_main()
脚本运行的结果:
[root@centos8 ~]# python3 /script/xx.py
8
[root@centos8 ~]#
设置脚本权限:
[root@centos8 ~]# chmod a+x /script/xx.py
[root@centos8 ~]#
#############
Zabbix服务器使用Python3脚本输出SQL语句执行后的返回值:
[root@centos8 ~]# cat /script/yy.py
#coding=utf-8
import pymysql
def db_connect():
db=pymysql.connect("192.168.168.130", "zhuohua", "168", "db1",3306,charset="utf8")
cursor = db.cursor()
Sql_1 = "Select Age from Table1 where Name = '小明'"
cursor.execute(Sql_1)
Results = cursor.fetchone()
print (Results[0])
db.close()
def func_main():
try:
db_connect()
except Exception as e:
print(e)
if __name__ == "__main__":
func_main()
脚本运行的结果:
[root@centos8 ~]# python3 /script/yy.py
16
[root@centos8 ~]#
设置脚本权限:
[root@centos8 ~]# chmod a+x /script/yy.py
[root@centos8 ~]#
#############
#############
修改Zabbix服务器的Agent配置文件:
[root@centos8 ~]# find / -name zabbix_agentd.conf
/root/zabbix-5.0.12/conf/zabbix_agentd.conf
/usr/local/zabbix/etc/zabbix_agentd.conf
[root@centos8 ~]#
[root@centos8 ~]# vi /usr/local/zabbix/etc/zabbix_agentd.conf
# UnsafeUserParameters=0
修改为:(启用该功能)
UnsafeUserParameters=1
接着插入:
UserParameter=command_1,/usr/bin/python3 /script/xx.py
UserParameter=command_2,/usr/bin/python3 /script/yy.py
如下图:
保存好配置文件后,重启一下Zabbix本机的客户端和服务端:
pkill -9 -U zabbix
/usr/local/zabbix/sbin/zabbix_agentd
/usr/local/zabbix/sbin/zabbix_server
######
在Zabbix服务器测试,验证能否获取本机(127.0.0.1)的键值:
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -k command_1
8
[root@centos8 ~]#
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -k command_2
16
[root@centos8 ~]#
##################
配置》主机:
在主机Zabbix server中创建监控项:
给“Zabbix服务器使用Python3脚本输出SQL语句执行后的返回值”创建监控项:
自定义名称: Oracle command_1
键值: command_1
信息类型: 数字(无正负)
备注:其他地方保持默认,点击底下的“添加”按键。
给监控项“Oracle command_1”创建图形“Oracle command_1 image”:
备注:其他地方保持默认,点击底下的“添加”按键。
查看图形“Oracle command_1 image”:
监测》主机》左击主机名称Zabbix server》图形:
选择时间范围:
使用“过滤器”,选择显示的图形:(默认会显示所有图形)
######
给监控项“Oracle command_1”创建触发器:(返回值大于或等于10就告警)
自定义名称: AGE is greater than or equal to 10
插入表达式:(监控项: Zabbix server: Oracle command_1 )
自动生成的表达式:(触发器的表达式要用到监控项中的键值)
{Zabbix server:command_1.last()}>=10
备注:其他地方保持默认,点击底下的“添加”按键。
再次查看图形“Oracle command_1 image”:(理应可以看到触发器“AGE is greater than or equal to 10”)
#############
#############
在库db1的表Table1中更改数据:
mysql> use db1;
Database changed
mysql>
mysql> Update Table1 set Age = 18 where Name = 'Zhuohua';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> Select * from Table1 where Name = 'Zhuohua';
+---------+------+
| Name | Age |
+---------+------+
| Zhuohua | 18 |
+---------+------+
1 row in set (0.00 sec)
mysql>
再次查看图形“Oracle command_1 image”:
监控项“Oracle command_1”的返回值大于或等于10时,仪表板会如下图显示:(显示对应触发器的名称)
AGE is greater than or equal to 10
#############
#############
再次在库db1的表Table1中更改数据:
mysql> use db1;
Database changed
mysql>
mysql> Update Table1 set Age = -9 where Name = 'Zhuohua';
Query OK, 1 row affected (0.13 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> Select Age from Table1 where Name = 'Zhuohua';
+------+
| Age |
+------+
| -9 |
+------+
1 row in set (0.00 sec)
mysql> Select Abs(Age) from Table1 where Name = 'Zhuohua';
+----------+
| Abs(Age) |
+----------+
| 9 |
+----------+
1 row in set (0.00 sec)
mysql>
再次查看图形“Oracle command_1 image”:
监控项“Oracle command_1”的返回值小于9时,仪表板会如下图显示:
触发器“AGE is greater than or equal to 10”会自动消失
相关文章:
MySQL的SQL语句
Zabbix调用Python3脚本监控Linux下的MySQL
Zabbix5.0.12_调用Python3脚本监控Windows下的MSSQL
Zabbix5.0.12_调用Shell脚本监控Linux下的MySQL
Zabbix5.0.12_使用模板监控MySQL主从同步状态 |