Zabbix implements monitoring of multiple mysql processes

Three MySQL instance processes are started on one server, occupying different ports 3306, 3307, and 3308

Principle description:

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

Host definition macro {$MYSQLPORT}->auto discovery rule key {$MYSQLPORT}->call the auto discovery script on the agent and parse it into {#MYSQLPORT}: port->monitoring item prototype {#MYSQLPORT}->auto generate host monitoring item

1. Operations on MySQL multi-instance servers

1. Authorize Zabbix to monitor the MySQL account, which is required in each instance.

The account here is zabbixagent and the password is: Zabbix131

GRANT USAGE,PROCESS,REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'zabbixagent'@'localhost' IDENTIFIED BY 'Zabbix131';
flush privileges;

2. Modify the zabbix_agentd.conf configuration file

Last position increase

     UnsafeUserParameters=1
     EnableRemoteCommands=1
     Include=/etc/zabbix/etc/zabbix_agentd.conf.d/*.conf
[root@mysql zabbix]# vi /etc/zabbix/etc/zabbix_agentd.conf
     UnsafeUserParameters=1
     EnableRemoteCommands=1
     Include=/etc/zabbix/etc/zabbix_agentd.conf.d/*.conf

3. Add configuration files

[root@mysql etc]# vim /etc/zabbix/etc/zabbix_agentd.conf.d/check_mysql.conf
     UserParameter=mysql_discovery[*],/etc/zabbix/bin/discovery_mysql.sh $1 ###Automatically discover different ports UserParameter=mysql.status[*],/etc/zabbix/bin/mysql_status.sh $1 $2 ###Performance monitoring information UserParameter=mysql.ping[*],/etc/zabbix/bin/mysql_alive.sh $1 ### Is it alive? UserParameter=mysql.ms.check[*],/etc/zabbix/bin/mysql_slave_status.sh $1 ### Is the slave status normal? UserParameter=mysql.ms.time[*],/etc/zabbix/bin/mysql_slave_time.sh $1 ### Is there a delay in the slave

4. Add execution script file

[root@mysql etc]# ll /etc/zabbix/bin/
total 716
     -rwxr-xr-x 1 root root 441 Jul 22 11:36 discovery_mysql.sh
     -rwxr-xr-x 1 root root 401 Jul 22 11:36 mysql_alive.sh
     -rwxr-xr-x 1 root root 303 Jul 22 15:10 mysql_slave_status.sh
     -rwxr-xr-x 1 root root 286 Jul 22 15:10 mysql_slave_time.sh
     -rwxr-xr-x 1 root root 299 Jul 22 11:36 mysql_status.sh
     -rwxr-xr-x 1 root root 370 Jul 22 11:36 mysql_version.sh
[root@mysql etc]# more /etc/zabbix/bin/discovery_mysql.sh
     res=`echo $1| sed "s/_/\n/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\t\t\"{#MYSQLPORT}\":\"${port[${key}]}\"},\n"
     else [[ "${key}" -eq "((${#port[@]}-1))" ]]
         printf '\t {\n'
         printf "\t\t\t\"{#MYSQLPORT}\":\"${port[${key}]}\"}\n"
     fi
     done
     printf '\t ]\n'
     printf '}\n'
[root@mysql etc]# more /etc/zabbix/bin/mysql_status.sh
     #!/bin/bash
     var=$1
     mysql=/usr/local/mysql/bin/mysql
     MYSQL_USER="zabbixagent"
     MYSQL_PASSWORD=Zabbix131
     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}'
[root@mysql etc]# more /etc/zabbix/bin/mysql_alive.sh
     #!/bin/bash
     mysqladmin=/usr/local/mysql/bin/mysqladmin
     MYSQL_USERdd="zabbixagent"
     MYSQL_PASSWORD=Zabbix131
     MYSQL_SOCK_DIR="/tmp/mysql$1.sock"
     ${mysqladmin} -u${MYSQL_USER} -p${MYSQL_PASSWORD} -S ${MYSQL_SOCK_DIR} ping|grep -c alive
[root@mysql etc]# more /etc/zabbix/bin/mysql_slave_status.sh
     #!/bin/bash
     #var=$1
     mysql=/usr/local/mysql/bin/mysql
     MYSQL_USERdd="zabbixagent"
     MYSQL_PASSWORD=Zabbix131
     MYSQL_SOCK_DIR="/tmp/mysql$1.sock"
     ${mysql} -u${MYSQL_USER} -p${MYSQL_PASSWORD} -S ${MYSQL_SOCK_DIR} -e "show slave status\G;" 2> /dev/null|grep -E 'Slave_IO_Running: Yes|Slave_SQL_Running: Yes'|grep -c Yes
[root@mysql etc]# more /etc/zabbix/bin/mysql_slave_time.sh
     #!/bin/bash
     #var=$1
     mysql=/usr/local/mysql/bin/mysql
     MYSQL_USERdd="zabbixagent"
     MYSQL_PASSWORD=Zabbix131
     MYSQL_SOCK_DIR="/tmp/mysql$1.sock"
     ${mysql} -u${MYSQL_USER} -p${MYSQL_PASSWORD} -S ${MYSQL_SOCK_DIR} -e "show slave status\G;" 2> /dev/null|grep -E 'Seconds_Behind_Master'|awk '{print $2}'

5. Restart zabbix_agentd

[root@mysql zabbix_agentd.d]# systemctl restart zabbix-agent

2. Operations on the zabbix page

1. Import template_multi_MySQL.xml template information. You can also refer to the xml file to add it manually.

2. Create automatic discovery rules on the template. Two things need to be defined in the automatic discovery rules:
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 obtained port, and the automatic discovery macro {#MYSQLPORT} is required.

3. Add the newly created template to the host that needs to be monitored

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

3. Wait for data collection to complete. If there is no data, manually test the specific

For example, when performing operations on zabbixserver, such as testing whether mysql 3306 is alive, 1 means up and 0 means down.

The final effect is

Zabbix131

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in: