9 低级发现Mysql多实例

主要讲item原型

启动mysql多实例

3307 3308

[[email protected] ~]# cp /etc/my.cnf /etc/my3307.cnf
[[email protected]-node2 ~]# cp /etc/my.cnf /etc/my3308.cnf

原始的

9 低级发现Mysql多实例

修改内容如下

[[email protected] ~]# grep ‘^[a-Z]‘ /etc/my3308.cnf
datadir=/data/3308
socket=/data/3308/mysql.sock
symbolic-links=0
port=3308
log-error=/data/3308/mariadb.log
pid-file=/data/3308/mariadb.pid

[[email protected]-node2 ~]# grep ‘^[a-Z]‘ /etc/my3307.cnf
datadir=/data/3307
socket=/data/3307/mysql.sock
symbolic-links=0
port=3307
log-error=/data/3307/mariadb.log
pid-file=/data/3307/mariadb.pid

初始化

 mysql_install_db --user=mysql --defaults-file=/etc/my3307.cnf

9 低级发现Mysql多实例

启动

mysqld_safe --defaults-file=/etc/my3307.cnf &

9 低级发现Mysql多实例

查看端口

9 低级发现Mysql多实例

添加3306实例

[[email protected] ~]# mkdir /data/3306
[[email protected]-node2 ~]# In -s /var/lib/mysql/mysql.sock /data/3306
-bash: In: command not found

[[email protected]-node2 ~]# ln -s /var/lib/mysql/mysql.sock /data/3306
[[email protected]-node2 ~]# 
[[email protected]-node2 ~]# find /data -name ‘*.sock‘
/data/3307/mysql.sock
/data/3308/mysql.sock
/data/3306/mysql.sock

3、编写脚本测试

[[email protected] ~]# chmod 755 /data/*

[[email protected] ~]# ll -d /data/*
drwxr-xr-x 2 root root 23 6月 21 09:58 /data/3306
drwxr-xr-x 5 mysql root 4096 6月 21 09:48 /data/3307
drwxr-xr-x 5 mysql root 4096 6月 21 09:53 /data/3308

[[email protected] ~]# netstat -lntp|awk -F "[ :] " ‘/mysqld/{print$5}‘
3306
3307
3308

编写脚本获取多实例并输出为json格式

[[email protected] ~]# vim /etc/zabbix/zabbix_agentd.d/discovery_mysql.sh 
[[email protected]-node2 ~]# cat /etc/zabbix/zabbix_agentd.d/discovery_mysql.sh

#!/bin/bash
#mysql low-lever discovery
res=`netstat -lntp|grep mysql |awk -F "[ :] " ‘{print $5}‘`
port=($res)
printf ‘{‘
printf ‘"data":[‘
for key in ${!port[@]}
do
        if [[ "${#port[@]}" -gt 1 && "${key}"  -ne "$((${#port[@]}-1))" ]];then
                printf ‘{‘
                printf ""{#MYSQLPORT}":"${port[${key}]}"},"
        else [[ "${key}" -eq "((${#port[@]}-1))" ]]
                printf ‘{‘
                printf ""{#MYSQLPORT}":"${port[${key}]}"}"
        fi
done
printf ‘]‘
printf ‘}‘

可以先看下网络的 discovery

[[email protected] ~]# zabbix_agentd -p |grep discovery

9 低级发现Mysql多实例

执行 mysql的discovery

[[email protected] ~]# sh /etc/zabbix/zabbix_agentd.d/discovery_mysql.sh 
{"data":[{"{#MYSQLPORT}":"3306"},{"{#MYSQLPORT}":"3307"},{"{#MYSQLPORT}":"3308"}]}
# 转化为json
[[email protected]-node2 ~]# sh /etc/zabbix/zabbix_agentd.d/discovery_mysql.sh |python -m json.tool
{
    "data": [
        {
            "{#MYSQLPORT}": "3306"
        },
        {
            "{#MYSQLPORT}": "3307"
        },
        {
            "{#MYSQLPORT}": "3308"
        }
    ]
}

写自定义的key

[[email protected] ~]# cat /etc/zabbix/zabbix_agentd.d/discovery_mysql.conf
UserParameter=discovery_mysql, sh /etc/zabbix/zabbix_agentd.d/discovery_mysql.sh

重启agent，获取

9 低级发现Mysql多实例

授权

netstat -nltp

p必须root用户才能执行

脚本中需要为zabbix用户授权netstat的命令

# 方法一：sudo

# 方法二：
去掉脚本的sudo，添加suid
ll `which netstat` chmod u s /usr/bin/netstat

重新获取

zabbix_get -s 192.168.194.132 -p 10050 -k "discovery_mysql"

9 低级发现Mysql多实例

2、web界面

添加自动发现规则

9 低级发现Mysql多实例

创建item原型

自动发现需要item的key 去传参

定义mysql的配置文件

[[email protected] /etc/zabbix/zabbix_agentd.d]# cat userparameter_mysql.conf
UserParameter=mysql.status[*],echo "show global status where Variable_name=‘$2‘;" | HOME=/var/lib/zabbix mysql -uroot -proot -h 127.0.0.1 -P $1 -N | awk ‘{print $$2}‘

几个实例的用户名密码都修改mysql密码

mysqladmin -h 127.0.0.1 -uroot password ‘root‘ -P 3306
mysqladmin -h 127.0.0.1 -uroot password ‘root‘ -P 3307
mysqladmin -h 127.0.0.1 -uroot password ‘root‘ -P 3308

test

[[email protected] ~]# mysql -uroot -proot -h127.0.0.1 -P3306

MariaDB [(none)]> use mysql

MariaDB [mysql]> show GLOBAL VARIABLES like ‘port‘;
 --------------- ------- 
| Variable_name | Value |
 --------------- ------- 
| port          | 3306  |
 --------------- ------- 
1 row in set (0.00 sec)

重启agent

server端测试

[[email protected] /etc/zabbix]# zabbix_get -s 192.168.194.132 -p 10050 -k "mysql.status[3306,Com_begin]"
0
[[email protected]-node1 /etc/zabbix]# zabbix_get -s 192.168.194.132 -p 10050 -k "mysql.status[3306,Bytes_sent]"
19645211