zabbix添加自定义监控模版mysql( UserParameter + Shell)-ubuntu24

环境:
os:ubuntu 24.04
zabbix:7.0.29

1.定义好获取mysql数据脚本

在被监控机器上操作
mkdir -p /etc/zabbix/scripts
vi /etc/zabbix/scripts/mysql_status.sh
输入如下内容:

#!/bin/bash
# zabbix 调用脚本监控mysql状态
# 参数 $1: 指标名称，如 ping、Threads_connected、Slow_queries、Seconds_Behind_Master

MYSQL_USER="zabbix"
MYSQL_PASS="Zabbix@123456"
MYSQL_PORT=3306
MYSQL_SOCK="/var/run/mysqld/mysqld.sock"

case $1 in
ping)
    # 判断mysql是否存活，返回1正常，0异常
    mysql -u"${MYSQL_USER}" -p"${MYSQL_PASS}" -S ${MYSQL_SOCK} -e "select 1;" >/dev/null 2>&1
    if [ $? -eq 0 ];then
        echo 1
    else
        echo 0
    fi
;;

Threads_connected|Slow_queries|Queries|Max_used_connections)
    # 获取 show global status 状态值
    mysql -u"${MYSQL_USER}" -p"${MYSQL_PASS}" -S ${MYSQL_SOCK} -N -e "show global status like '$1';" 2>/dev/null | awk '{print $2}'
;;

Seconds_Behind_Master)
    # 获取主从延迟
    mysql -u"${MYSQL_USER}" -p"${MYSQL_PASS}" -S ${MYSQL_SOCK} -N -e "show replica status\G" 2>/dev/null |grep Seconds_Behind_Master|awk '{print $2}'
;;

*)
    echo "unsupported"
exit 1
;;
esac

2.修改脚本权限

在被监控机器上操作

chmod +x /etc/zabbix/scripts/mysql_status.sh
chown zabbix:zabbix /etc/zabbix/scripts/mysql_status.sh
# 防止其他用户读取密码
chmod 700 /etc/zabbix/scripts/mysql_status.sh

3.添加配置项
vim /etc/zabbix/zabbix_agent2.d/userparameter_mysql.conf
输入如下内容:
#UserParameter=自定义key名称,命令
#$1 脚本传入的第一个参数
UserParameter=mysql.status[*],/etc/zabbix/scripts/mysql_status.sh $1

4.在被监控的mysql机器上测试脚本
# 测试存活
/etc/zabbix/scripts/mysql_status.sh ping

# 测试当前连接数
/etc/zabbix/scripts/mysql_status.sh Threads_connected

# 测试慢查询
/etc/zabbix/scripts/mysql_status.sh Slow_queries

root@zabbix-agent:/tmp# /etc/zabbix/scripts/mysql_status.sh ping
1
root@zabbix-agent:/tmp# /etc/zabbix/scripts/mysql_status.sh Threads_connected
1
root@zabbix-agent:/tmp# /etc/zabbix/scripts/mysql_status.sh Slow_queries
0

5.重启
systemctl restart zabbix-agent2
systemctl status zabbix-agent2

6.在被监控端测试看是否可以获取到数据
# 测试mysql存活
zabbix_agent2 -c /etc/zabbix/zabbix_agent2.conf -t "mysql.status[ping]"

# 获取连接数
zabbix_agent2 -c /etc/zabbix/zabbix_agent2.conf -t "mysql.status[Threads_connected]"

# 获取慢查询
zabbix_agent2 -c /etc/zabbix/zabbix_agent2.conf -t "mysql.status[Slow_queries]"

root@zabbix-agent:/tmp# zabbix_agent2 -c /etc/zabbix/zabbix_agent2.conf -t "mysql.status[ping]"
mysql.status[ping]                            [s|1]
root@zabbix-agent:/tmp# zabbix_agent2 -c /etc/zabbix/zabbix_agent2.conf -t "mysql.status[Threads_connected]"
mysql.status[Threads_connected]               [s|1]
root@zabbix-agent:/tmp# zabbix_agent2 -c /etc/zabbix/zabbix_agent2.conf -t "mysql.status[Slow_queries]"
mysql.status[Slow_queries]                    [s|0]