笺注:这是在 CentOS8编译安装Zabbix4.4.5 的基础上进行的,监控本机的MariaDB
配置本机的MariaDB数据库:( 使用数据库管理员root@localhost )
[root@centos8 ~]# mysql -u"root"
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 136
Server version: 10.3.17-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)]>
注释:
此时,数据库管理员root@localhost没有设置密码;
-h"localhost" 是可以省略的;
查看当前登录的数据库用户:
MariaDB [(none)]> Select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.001 sec)
MariaDB [(none)]>
显示所有的库:
MariaDB [(none)]> Show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| zabbix |
+--------------------+
5 rows in set (0.001 sec)
MariaDB [(none)]>
注释:库mysql是默认就有的。
再授权数据库用户zbx_monitor@localhost(密码886),只能本地登录,对库mysql、库zabbix有完全控制的权限:
MariaDB [(none)]> Create user zbx_monitor@localhost identified by '886';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> Grant all on mysql.* to zbx_monitor@localhost;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> Grant all on zabbix.* to zbx_monitor@localhost;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> Flush privileges;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]>
查看本地数据库用户zbx_monitor@localhost的权限:
MariaDB [(none)]> Show grants for zbx_monitor@localhost;
+--------------------------------------------------------------------------------------------------------------------+
| Grants for zbx_monitor@localhost |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zbx_monitor'@'localhost' IDENTIFIED BY PASSWORD '*F961C54AFEB4D281CE53D7CB8E7822890D86FFFC' |
| GRANT ALL PRIVILEGES ON `mysql`.* TO 'zbx_monitor'@'localhost' |
| GRANT ALL PRIVILEGES ON `zabbix`.* TO 'zbx_monitor'@'localhost' |
+--------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.000 sec)
MariaDB [(none)]>
######
使用本地数据库用户zbx_monitor@localhost,在命令行中直接输出MariaDB的版本信息:
[root@centos8 ~]# mysql -u"zbx_monitor" -h"localhost" -p"886" -V
mysql Ver 15.1 Distrib 10.3.17-MariaDB, for Linux (x86_64) using readline 5.1
[root@centos8 ~]#
注释:
-h"localhost" 是可以省略的;
由于权限问题,有些库是本地数据库用户zbx_monitor@localhost看不到的:
[root@centos8 ~]# mysql -u"zbx_monitor" -p"886" -e "Show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| zabbix |
+--------------------+
[root@centos8 ~]#
查看MariaDB的最大连接数:
[root@centos8 ~]# mysql -u"zbx_monitor" -p"886" -e "Show variables like '%max_connections%';"
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| extra_max_connections | 1 |
| max_connections | 151 |
+-----------------------+-------+
[root@centos8 ~]#
查看MariaDB当前打开的连接数:
[root@centos8 ~]# mysql -u"zbx_monitor" -p"886" -e "Show status like 'threads_connected';"
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 27 |
+-------------------+-------+
[root@centos8 ~]#
查看MariaDB当前处于激活状态的连接数:
[root@centos8 ~]# mysql -u"zbx_monitor" -p"886" -e "Show status like 'threads_running';"
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_running | 1 |
+-----------------+-------+
[root@centos8 ~]#
查看库zabbix里的表users的行数:
[root@centos8 ~]# mysql -u"zbx_monitor" -p"886" -e "select count(*) from zabbix.users;"
+----------+
| count(*) |
+----------+
| 2 |
+----------+
[root@centos8 ~]#
查看单个库(msyql)的大小:(以字节为单位)
[root@centos8 ~]# mysql -u"zbx_monitor" -p"886" -e "select concat(sum(DATA_LENGTH)) as data from information_schema.TABLES where table_schema = 'mysql';"
+--------+
| data |
+--------+
| 857525 |
+--------+
[root@centos8 ~]#
查看单个库(zabbix)的大小:(以字节为单位)
[root@centos8 ~]# mysql -u"zbx_monitor" -p"886" -e "select concat(sum(DATA_LENGTH)) as data from information_schema.TABLES where table_schema = 'zabbix';"
+---------+
| data |
+---------+
| 9879552 |
+---------+
[root@centos8 ~]#
查看单个库(zabbix)的大小:(以MB为单位)
[root@centos8 ~]# mysql -u"zbx_monitor" -p"886" -e "select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES where table_schema = 'zabbix';"
+--------+
| data |
+--------+
| 9.42MB |
+--------+
[root@centos8 ~]#
查看单个库(zabbix)的大小:(以GB为单位)
[root@centos8 ~]# mysql -u"zbx_monitor" -p"886" -e "select concat(round(sum(DATA_LENGTH/1024/1024/1024),2),'GB') as data from information_schema.TABLES where table_schema = 'zabbix';"
+--------+
| data |
+--------+
| 0.01GB |
+--------+
[root@centos8 ~]#
############
在Zabbix本机安装第三方库(PyMySQL),可参考:Python3脚本管理Linux下的MySQL
查看PyMySQL的版本:
[root@centos8 ~]# pip3 freeze
PyMySQL==0.10.1
[root@centos8 ~]#
注释:PyMySQL可以管理MySQL,也可以管理MariaDB
######
在Zabbix本机创建Python3脚本:(输出MariaDB的最大连接数)
[root@centos8 ~]# cat /usr/local/xx.py
#coding=utf-8
import pymysql
def db_connect():
#打开数据库连接;本地连接(localhost)、用户名、密码、其中一个库(zabbix)的名称、端口号、字符集:
db = pymysql.connect("localhost", "zbx_monitor", "886", "zabbix",3306,charset="utf8")
cursor = db.cursor()
SQL_1 = "show variables like '%max_connections%';" #SQL语句
cursor.execute(SQL_1) #执行SQL语句
Result_1 = cursor.fetchall() #使用fetchall可以获取多个元素
#print(Result_1) # (('extra_max_connections', '1'), ('max_connections', '151'))
#print(type(Result_1)) # <class 'tuple'>
#print(Result_1[1]) # ('max_connections', '151')
#print(type(Result_1[1])) # <class 'tuple'>
#print(Result_1[1][1]) # 151
#print(type(Result_1[1][1])) # <class 'str'>
Key_1 = int(Result_1[1][1])
print(Key_1)
#print(type(Key_1)) # <class 'int'>
db.close()
def main():
try:
db_connect()
except Exception as e:
print(e)
if __name__ == "__main__":
main()
设置脚本权限:
[root@centos8 ~]# chmod a+x /usr/local/xx.py
[root@centos8 ~]#
脚本运行的结果:
[root@centos8 ~]# python3 /usr/local/xx.py
151
[root@centos8 ~]#
######
在Zabbix本机创建Python3脚本:(输出MariaDB当前打开的连接数)
[root@centos8 ~]# cat /usr/local/yy.py
#coding=utf-8
import pymysql
def db_connect():
db = pymysql.connect("localhost", "zbx_monitor", "886", "zabbix",3306,charset="utf8")
cursor = db.cursor()
SQL_1 = "Show status like 'threads_connected';" #SQL语句
cursor.execute(SQL_1) #执行SQL语句
Result_1 = cursor.fetchone() #使用fetchone只能获取一个元素
#print(Result_1) # ('Threads_connected', '27')
#print(type(Result_1)) # <class 'tuple'>
#print(Result_1[1]) # 27
#print(type(Result_1[1])) # <class 'str'>
Key_1 = int(Result_1[1])
print(Key_1)
#print(type(Key_1)) # <class 'int'>
db.close()
def main():
try:
db_connect()
except Exception as e:
print(e)
if __name__ == "__main__":
main()
设置脚本权限:
[root@centos8 ~]# chmod a+x /usr/local/yy.py
[root@centos8 ~]#
脚本运行的结果:
[root@centos8 ~]# python3 /usr/local/yy.py
27
[root@centos8 ~]#
######
在Zabbix本机创建Python3脚本:(输出库zabbix里的表users的行数)
[root@centos8 ~]# cat /usr/local/zz.py
#coding=utf-8
import pymysql
def db_connect():
db = pymysql.connect("localhost", "zbx_monitor", "886", "zabbix",3306,charset="utf8")
cursor = db.cursor()
SQL_1 = "select count(*) from users;" #SQL语句
cursor.execute(SQL_1) #执行SQL语句
Result_1 = cursor.fetchone()
#print(Result_1) # (2,)
#print(type(Result_1)) # <class 'tuple'>
print(Result_1[0])
#print(type(Result_1[0])) # <class 'int'>
db.close()
def main():
try:
db_connect()
except Exception as e:
print(e)
if __name__ == "__main__":
main()
设置脚本权限:
[root@centos8 ~]# chmod a+x /usr/local/zz.py
[root@centos8 ~]#
脚本运行的结果:
[root@centos8 ~]# python3 /usr/local/zz.py
2
[root@centos8 ~]#
######
在Zabbix本机创建Python3脚本:(输出单个库(mysql)的大小,以字节为单位)
[root@centos8 ~]# cat /usr/local/qq.py
#coding=utf-8
import pymysql
def db_connect():
db = pymysql.connect("localhost", "zbx_monitor", "886", "mysql",3306,charset="utf8")
cursor = db.cursor()
SQL_1 = "select concat(sum(DATA_LENGTH)) as data from information_schema.TABLES where table_schema = 'mysql';" #SQL语句
cursor.execute(SQL_1) #执行SQL语句
Result_1 = cursor.fetchone()
#print(Result_1) # (b'857525',)
#print(type(Result_1)) # <class 'tuple'>
#print(Result_1[0]) # b'857525'
#print(type(Result_1[0])) # <class 'bytes'>
Key_1 = int(Result_1[0])
print(Key_1)
#print(type(Key_1)) # <class 'int'>
db.close()
def main():
try:
db_connect()
except Exception as e:
print(e)
if __name__ == "__main__":
main()
设置脚本权限:
[root@centos8 ~]# chmod a+x /usr/local/qq.py
[root@centos8 ~]#
脚本运行的结果:
[root@centos8 ~]# python3 /usr/local/qq.py
857525
[root@centos8 ~]#
######
在Zabbix本机创建Python3脚本:(输出单个库(zabbix)的大小,以字节为单位)
[root@centos8 ~]# cat /usr/local/kk.py
#coding=utf-8
import pymysql
def db_connect():
db = pymysql.connect("localhost", "zbx_monitor", "886", "mysql",3306,charset="utf8") # 这里也可以使用库"zabbix"
cursor = db.cursor()
SQL_1 = "select concat(sum(DATA_LENGTH)) as data from information_schema.TABLES where table_schema = 'zabbix';" #SQL语句
cursor.execute(SQL_1) #执行SQL语句
Result_1 = cursor.fetchone()
#print(Result_1) # (b'9879552',)
#print(type(Result_1)) # <class 'tuple'>
#print(Result_1[0]) # b'9879552'
#print(type(Result_1[0])) # <class 'bytes'>
Key_1 = int(Result_1[0])
print(Key_1)
#print(type(Key_1)) # <class 'int'>
db.close()
def main():
try:
db_connect()
except Exception as e:
print(e)
if __name__ == "__main__":
main()
设置脚本权限:
[root@centos8 ~]# chmod a+x /usr/local/kk.py
[root@centos8 ~]#
脚本运行的结果:
[root@centos8 ~]# python3 /usr/local/kk.py
9879552
[root@centos8 ~]#
#############
#############
修改Zabbix服务器的Agent配置文件:
[root@centos8 ~]# vi /usr/local/zabbix/etc/zabbix_agentd.conf
# UnsafeUserParameters=0
修改为:(启用该功能)
UnsafeUserParameters=1
插入:
UserParameter=command_1,/usr/bin/python3 /usr/local/xx.py
UserParameter=command_2,/usr/bin/python3 /usr/local/yy.py
UserParameter=command_3,/usr/bin/python3 /usr/local/zz.py
UserParameter=command_4,/usr/bin/python3 /usr/local/kk.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
151
[root@centos8 ~]#
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -k command_2
27
[root@centos8 ~]#
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -k command_3
2
[root@centos8 ~]#
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -k command_4
9879552
[root@centos8 ~]#
##################Zabbix添加被监控主机:
配置》主机》创建主机:(主机名称无需与被监控主机的真实主机名一致;群组可以随便选)
注释:
使用agent代理程序的接口。
IP地址: 127.0.0.1 (同一台主机可以被重复添加,只要主机名称不一样即可)
使用 TCP 10050 端口。
主机Zabbix_server_MariaDB中,给“MariaDB的最大连接数”创建监控项:
自定义名称: MariaDB command_1
键值: command_1
信息类型: 浮点数
单位: !个
备注:其他地方保持默认,点击底下的“添加”按键。
主机Zabbix_server_MariaDB中,给“MariaDB当前打开的连接数”创建监控项:
自定义名称: MariaDB command_2
键值: command_2
信息类型: 浮点数
单位: !个
备注:其他地方保持默认,点击底下的“添加”按键。
主机Zabbix_server_MariaDB中,给“库zabbix里的表users的行数”创建监控项:
自定义名称: MariaDB command_3
键值: command_3
信息类型: 浮点数
单位: !行
备注:其他地方保持默认,点击底下的“添加”按键。
主机Zabbix_server_MariaDB中,给“单个库(zabbix)的大小”创建监控项:
自定义名称: MariaDB command_4
键值: command_4
信息类型: 浮点数
单位: Bytes
备注:
使用单位“Bytes”时,结果在单位换算时会每超过1000就除以1000,保留两位小数,是四舍五入;
######
给监控项“MariaDB command_1”创建图形:
监控项:
给监控项“MariaDB command_2”创建图形:
监控项:
给监控项“MariaDB command_3”创建图形:
监控项:
给监控项“MariaDB command_4”创建图形:
监控项:
######
查看图形:
监测》图形:
############
############
修改Zabbix本机的MariaDB的最大连接数:( 没有代码就插入,要在[mysqld]下面 )
[root@centos8 ~]# vi /etc/my.cnf
[mysqld]
max_connections = 151
改为
max_connections = 1500
保存好配置文件后,重启MariaDB服务:
[root@centos8 ~]# systemctl restart mysql
[root@centos8 ~]#
再次,在Zabbix本机查看MariaDB的最大连接数:
[root@centos8 ~]# mysql -u"zbx_monitor" -p"886" -e "Show variables like '%max_connections%';"
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| extra_max_connections | 1 |
| max_connections | 1500 |
+-----------------------+-------+
[root@centos8 ~]#
[root@centos8 ~]# python3 /usr/local/xx.py
1500
[root@centos8 ~]#
[root@centos8 ~]# /usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -k command_1
1500
[root@centos8 ~]#
再次,查看图形“MariaDB command_1 image”:
注释:图形中的数据线是从右往左延伸的。
相关文章:
MariaDB的最大连接数/最大可打开表数/最大可打开文件数
Zabbix使用模板监控Linux下的MariaDB
Zabbix调用Python3脚本监控Linux下的MySQL |