Multi-instance practice of zabbix Monitoring MySQL

1. Practical background:

Multiple MySQL instances are deployed on a machine, each using a different port, which needs to be monitored through zabbix.

Second, the practice method and the principle explanation:

1. Create a template on Zabbix to monitor the use of the MySQL database, and import the case template:

Link: https://pan.baidu.com/s/1nXooNPMXrmaAQidRrxVOLg extraction code: ure1

two。 To create an autodiscover rule on a template, you need to define two things in the autodiscover rule:

a. The key value is used to automatically obtain the port of the MySQL instance. You need to use the host macro {$MYSQLPORT}.

b. The monitoring item prototype generates the corresponding monitoring item according to the acquired port, and needs to use the auto-discovery macro {# MYSQLPORT}

3. Define a macro {$MYSQLPORT} on the host to be monitored, corresponding to the port to be monitored, such as 3306 / 3307

4. Create scripts for automatically discovering ports and status monitoring scripts on Zabbix agent.

Description of the principle:

Obtain the port of the MySQL instance through the auto-discovery rule. {$MYSQLPORT} on the auto-discovery rule is the parameter to be passed to the agent auto-discovery script. This value is obtained from the macro {$MYSQLPORT} defined by the host, and the auto-discovery script parses it into the form of {# MYSQLPORT}: Port. The monitoring item prototype generates monitoring items based on the value of {# MYSQLPORT}. The process is as follows:

Host definition macro {$MYSQLPORT}-> auto-discovery rule key value {$MYSQLPORT}-> call the auto-discovery script on agent and resolve it to {# MYSQLPORT}: Port-> monitor item prototype {# MYSQLPORT}-> automatically generate host monitoring items

III. Environmental description:

OS:CentOS Linux release 7.6.1810 (Core)

Zabbix Server version: 4.2.4

MySQL port: 3306 and 3307

3306 socket:/tmp/mysql3306.sock

3307 socket:/tmp/mysql3307.sock

Script path: / etc/zabbix/scripts/

Create a script for monitoring in agent

1. Auto-discovery script: / etc/zabbix/scripts/discovery_mysqlport.sh

#! / bin/bash

Res= `echo $1 | sed "splash /\ nPlacement g" `

Port= ($res)

Printf'{\ n'

Printf'\ t "data": [\ n'

For key in ${! port [@]}

Do

If [["${# port [@]}"-gt 1 & & "${key}"-ne "$((${# port [@]}-1))"]]

Then

Printf'\ t {\ n'

Printf "\ t\" {# MYSQLPORT}\ ":\" ${port [${key}]}\ "},\ n"

Else [["${key}"-eq "((${# port [@]}-1))"]

Printf'\ t {\ n'

Printf "\ t\" {# MYSQLPORT}\ ":\" ${port [${key}]}\ "}\ n"

Fi

Done

Printf'\ t]\ n'

Printf'}\ n'

Execution effect

two。 Status check script: / etc/zabbix/scripts/mysql_status.sh

#! / bin/bash

Var=$1

MYSQL_USER= "zabbix"

MYSQL_PASSWORD=123456

MYSQL_SOCK_DIR= "/ tmp/mysql$2.sock"

${mysql}-u$ {MYSQL_USER}-p$ {MYSQL_PASSWORD}-S ${MYSQL_SOCK_DIR}-e "show global status;" 2 > / dev/null | grep-v Variable_name | grep "\ b$ {var}\ b" | awk'{print $2}'

3. Add monitoring items on agent

UserParameter=mysql_discovery [], / etc/zabbix/scripts/discovery_mysql_port.sh $1

UserParameter=mysql.status [], / etc/zabbix/scripts/mysql_status.sh $1 $2

4. Restart the agent service

Systemctl restart zabbix-agent

Automatically generated monitor item effect picture: