Zabbix Low-level auto-discovery MySQL Multi-instance

1, low-level automatic discovery overview

Zabbix's low-level Autodiscover (LLD) is suitable for monitoring multiple instances, monitoring changes in data (partitions, network cards).

Automatic Discovery (LLD) provides a way to automatically create monitoring items, triggers, and graphics for different entities on your computer. For example, Zabbix can automatically start monitoring file systems or network interfaces on your machine without having to manually create monitoring entries for each file system or network interface. In addition, you can configure Zabbix to remove unwanted monitoring items based on the actual results that are found after periodic execution.

In Zabbix, six types of discovery items are supported:

Discovery of System files

Discovery of network Interfaces

Discovery of CPU and CPU cores

Discovery of SNMP OIDs

Discovery using ODBC SQL queries

Discovery of Windows Services

Users can define their own discovery types, as long as they follow a specific JSON protocol.

The general structure of the discovery process is as follows.

First, the user creates a discovery rule in the configuration → templates → discovery column. Discovery rules include (1) artifacts that identify the necessary entities (for example, file systems or network Interfaces) and (2) monitoring items that should be created based on the value of the item, triggers, and prototypes of the graphics

A project that discovers the necessary entities is like a regular item seen elsewhere: The server queries the value of the project for the Zabbix agent (or any type of setting for the project), and the agent responds with a text value. The difference is that the value of the agent response should contain a list of discovered entities in a specific JSON format. The details that are found by custom inspectors in this format are the most important, because the return value must contain a macro-value pair. For example, the item "Net.if.discovery" may return two pairs of key values: "{#IFNAME}" → "lo" and "{#IFNAME}" → "eth0".

These macros are used for names, key values, and other prototype fields, and then use the received values for each discovered entity to create actual monitoring items, triggers, graphs, or even hosts.

When the server receives the value of the discovery item, it looks at the macro-value pairs, each of which generates actual monitoring items, triggers, and graphs based on the prototype. In the "Net.if.discovery" example above, the server will generate a set of monitoring items, triggers and graphs for the Loop Interface "Lo", and another set for the interface "Eth0".

2, MySQL multi-instance

2.1 What is MySQL multi-instance

MySQL multi-instance is to open a number of different service ports on one machine (such as: 3306,3307), run multiple MySQL service processes, through different sockets to listen to different service ports to provide their own services

Features of 2.2 MySQL multi-instance

Efficient use of server resources, when a single server resource is left, can take advantage of the remaining resources to provide more services

Conserve server resources

Resource preemption problem, when a service instance service is high concurrency or when slow query is turned on, it consumes more memory, CPU, disk IO resources, causing the quality of service on other instances on the server to degrade

2.3 Deployment of MySQL multiple instances in two ways

The first is to use multiple configuration files to launch different processes to implement multiple instances, the advantages of this method is simple, simple configuration, the disadvantage is not easy to manage.

The second is the use of the official Mysqld_multi with a separate configuration file to achieve multiple instances, this way to customize the configuration of each instance is not too, the advantage is easy to manage, centralized management

3, MySQL Single instance monitoring

The MySQL template is linked to the host and monitored to see the latest data

4. Open MySQL Multi-instance

4.1 Preparing the configuration file

[Email protected] ~]# CP/ETC/MY.CNF/ETC/MY3307.CNF

[Email protected] ~]# VIM/ETC/MY3307.CNF

[Mysqld]

datadir=/data/3307/

Socket=/data/3307/mysql.sock

port=3307

User=mysql

Symbolic-links=0

[Mysqld_safe]

Log-error=/data/3307/mysqld.log

Pid-file=/data/3307/mysqld.pid

[Email protected] ~]# CP/ETC/MY3307.CNF/ETC/MY3308.CNF

[Email protected] ~]# sed-i ' s#3307#3308#g '/etc/my3308.cnf

4.2 Initializing the database

Initializing the database 3307

[Email protected] ~]# mysql_install_db--user=mysql--defaults-file=/etc/my3307.cnf

[Email protected] ~]# Mysqld_safe--defaults-file=/etc/my3307.cnf &

Initializing the database 3308

[Email protected] ~]# mysql_install_db--user=mysql--defaults-file=/etc/my3308.cnf

[Email protected] ~]# Mysqld_safe--defaults-file=/etc/my3308.cnf &

4.3 Check if multiple instances are healthy

[Email protected] ~]# Netstat-lntup|grep mysqld

TCP 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1648/mysqld

TCP 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 5434/mysqld

TCP 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 6021/mysqld

[Email protected] ~]# mkdir/data/3306

[Email protected] ~]# ln-s/var/lib/mysql/mysql.sock/data/3306/

Check

[Email protected] ~]# find/data-name "*.sock"

/data/3307/mysql.sock

/data/3308/mysql.sock

/data/3306/mysql.sock

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

[Email protected] ~]# ll-d/data/*

drwxr-xr-x 2 root root 6 Month 09:58/data/3306

drwxr-xr-x 5 MySQL root 4096 6 Month 09:48/data/3307

drwxr-xr-x 5 MySQL root 4096 6 Month 09:53/data/3308

[[email protected] ~]# netstat-lntp|awk-f "[:]+" '/mysqld/{print$5} '

3306

3307

3308

5. Write Scripts and test

Scripting gets multiple instances and outputs to JSON format

[Email protected] ~]# mkdir/etc/zabbix/scripts

[Email protected] ~]# cd/etc/zabbix/scripts

[Email protected] scripts]# vim discover.sh

#!/bin/bash

#mysql Low-level discovery

res= ' sudo netstat-lntp|awk-f ' [: \t]+ ' '/mysqld/{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 '}\n '

commands to authorize Netstat for Zabbix users in the script

Method One:

Sed-i ' 98a zabbix\tall= (All) \tnopasswd:/bin/netstat '/etc/sudoers

Sed-i ' [email protected]^defaults [email protected] #Defaults [email protected] '/etc/sudoers

Method Two:

Remove the script sudo, add suid

Usermod-s/bin/bash Zabbix

chmod U+s/usr/bin/netstat

Test scripts

[Email protected] scripts]# sh discover.sh

{"Data": [{"{#MYSQLPORT}": "3306"},{"{#MYSQLPORT}": "3307"},{"{#MYSQLPORT}": "3308"}]}

6. Custom Key

[Email protected] ~]# cd/etc/zabbix/zabbix_agentd.d/

[Email protected] zabbix_agentd.d]# vim mysql.conf

userparameter=mysql.discovery,sh/etc/zabbix/scripts/discover.sh

[Email protected] zabbix_agentd.d]# systemctl restart Zabbix-agent.service

In the server-side command-line test

[Email protected] ~]# zabbix_get-s 192.168.1.51-k mysql.discovery

{"Data": [{"{#MYSQLPORT}": "3306"},{"{#MYSQLPORT}": "3307"},{"{#MYSQLPORT}": "3308"}]}

Add a custom key

[Email protected] zabbix_agentd.d]# vim mysql.conf

userparameter=mysql.discovery,sh/etc/zabbix/scripts/discover.sh

Userparameter=mysql-status[*],echo "show global status where Variable_name= ' $ $ ';" |mysql-s/data/$1/mysql.sock-n | awk ' {print $$2} '

Userparameter=mysql-ping[*],mysqladmin-s/data/$1/mysql.sock Ping | Grep-c Alive

Userparameter=mysql-version,mysql-v

[Email protected] zabbix_agentd.d]# systemctl restart Zabbix-agent.service

in the Server -side command line for testing

[Email protected] ~]# zabbix_get-s 192.168.1.51-k mysql-ping[3306]

1

[Email protected] ~]# zabbix_get-s 192.168.1.51-k mysql-ping[3307]

1

[Email protected] ~]# zabbix_get-s 192.168.1.51-k mysql-ping[3308]

1

7. Server-side web interface operation

Create automatic discovery rules, add Monitoring item prototypes, trigger types, graphical prototypes, and create them with reference to the existing rules of the system

To simplify the operation, import the templates directly and link the templates to the host

See the latest data after successfully linking templates

Part of the reference source: https://www.qstack.com.cn/archives/108.html

Zabbix Low-level auto-discovery MySQL Multi-instance