Zabbix enterprise monitoring-MySQL monitoring

Zabbix enterprise monitoring-MySQL monitoring

1 Preface

Zabbix does not seem to provide a clear configuration method for MySQL monitoring official tutorials. The following is the configuration method I have sorted out by referring to some documents. I will share it with you.

2 Theory

2.1 How Key values are obtained

2.1.1 Key definition of Zabbix Server

View the template's Items

 

The template defines the following Items:

Take Items "MySQL uptime" as an example. The Key content is as follows:

It can be seen that the Key value mysql. status [Uptime] is equivalent to the variable name.

2.1.2 definition of Key passing parameters in Zabbix Agens

1) Parameter definition file

1	less /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf

2) Parameter Definition Format

You can find the following lines:

1	UserParameter=mysql.status[*],echo "show global status where Variable_name='$1';" | HOME=/var/lib/zabbix mysql -N | awk '{print $$2}'

The definitions are separated by commas (,), which can be simplified as follows:

1	User-Defined parameter name, user-defined parameter value (shell command)

Note:

User-Defined parameter name: "*" indicates that Zabbix Server can input any specific variable parameter.

User-Defined parameter value: "$1" represents variable parameters that can be passed in by Zabbix Server

3) if the input parameter is "Uptime ",

1	UserParameter=mysql.status[Uptime],echo "show global status where Variable_name='Uptime';" | HOME=/var/lib/zabbix mysql -N | awk '{print $$2}'

4) The Shell specified in the User-Defined parameter value section can obtain specific values (Database authorization and database connection authorization must be configured first)

Therefore, the specified result is:

1	UserParameter=mysql.status[Uptime],165538

In this way, the MySQL value is sent to the Zabbix Server.

3. Practice

3.1 Environment Description

Zabbix Server:

Ipaddress = 10.168.0.160

Hostname = zabbix

 

Zabbix Agens:

Ipaddress = 10.168.0.99

Hostname = mysqlSer

App = mysqld

For configuration of Zabbix Server and Agens, see:

3.2 apply custom Parameters

In Zabbix Agens:

Edit/etc/zabbix/zabbix_agentd.conf in vim to enable the following two parameters:

12	Include=/etc/zabbix/zabbix_agentd.d/ UnsafeUserParameters=0

Note: The above configuration "userparameter_mysql.conf" takes effect only

3.3 grant Zabbix Agens Data Access Permissions

In Zabbix Server:

MySQL permission Configuration

1234	mysql -uroot -p GRANT SELECT ON mysql.* TO 'zabbix'@'localhost' IDENTIFIED BY 'zabbix'; flush privileges; show grants for zabbix@localhost;

3.4 configure Zabbix Agens Database Access Permissions

 

In Zabbix Agens:

3.4.1 determine the directory of the database connection file

Run the following command to view the path defined in the "HOME" directory:

1	cat /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf | egrep -v "(^#|^$)" | grep HOME

Shown as follows:

123

UserParameter=mysql.status[*],echo "show global status where Variable_name='$1';" | HOME=/var/lib/zabbix mysql -N | awk '{print $$2}'UserParameter=mysql.size[*],echo "select sum($(case "$3" in both|"") echo "data_length+index_length";; data|index) echo "$3_length";; free) echo "data_free";; esac)) from information_schema.tables$([[ "$1" = "all" || ! "$1" ]] || echo " where table_schema='$1'")$([[ "$2" = "all" || ! "$2" ]] || echo "and table_name='$2'");" | HOME=/var/lib/zabbix mysql -N UserParameter=mysql.ping,HOME=/var/lib/zabbix mysqladmin ping | grep -c alive

Note: The above indicates that the database connection file should be configured in "/var/lib/zabbix"

3.4.2 determine the MySQL PID file path

Run the following command to view the path of the MySQL PID file:

1	grep pid-file /etc/my.cnf

Shown as follows:

1	pid-file=/var/run/mysqld/mysqld.pid

3.4.3 configure the database connection file

Create a database connection file based on the obtained path.

Edit/var/lib/zabbix/. my. cnf in vim and enter the following content:

12345678910

[mysql] host     = localhost user     = zabbix password = zabbix socket   = /var/lib/mysql/mysql.sock [mysqladmin] host     = localhost user     = zabbix password = zabbix socket   = /var/lib/mysql/mysql.sock

3.5 restart and make the configuration take effect

In Zabbix Agens:

Restart the Agent service

1	/etc/init.d/zabbix-agent restart

3.6 command line validation custom KEY

In Zabbix Agens:

1	echo "show global status where Variable_name='Uptime';" | HOME=/var/lib/zabbix mysql -N | awk '{print $2}'

In Zabbix Server:

12	zabbix_get -s 10.168.0.99 -k mysql.status[Uptime] zabbix_get -s 10.168.0.99 -k mysql.version

3.7 Add a host monitoring template

In Zabbix web Console

Configuration-> Hosts-> click "Host Name"-> Templates-> Select-> Template App MySQL-> Add-> Update

3.8 confirm monitoring results

Monitoring-> Graphs

Select a specific Host name"

Graph select "MySQL bandwidth" or "MySQL operations" to view the monitoring Image

Some Zabbix Tutorials:

Compile and install Zabbix2.4.5 source code in Ubuntu 14.04

Install and deploy the distributed monitoring system Zabbix 2.06

Install and deploy the distributed monitoring system Zabbix 2.06

Install and deploy Zabbix in CentOS 6.3

Zabbix distributed monitoring system practice

Under CentOS 6.3, Zabbix monitors apache server-status

Monitoring MySQL database Parameters Using Zabbix in CentOS 6.3

Install Zabbix 2.0.6 in 64-bit CentOS 6.2

ZABBIX details: click here
ZABBIX: click here

This article permanently updates the link address: