Three ways to monitor mysql with zabbix 1. Just install the agent 2. Enable template monitoring 3. Enable template monitoring for custom scripts
Zabbix has a mysql monitoring template by default. The default is already in zabbix 2.2 and above. Template name: Template App MySQL. If not, go to the zabbix official download URL: https://zabbix.org/mw/images/d/d4/Template_App_MySQL-2.2.0.xml, and then in the configuration of the front-end web interface of the Zabbix server-> template page Select Import to import the downloaded template file in a new page.
#Simulation environment mysql1 172.16.160.161 #zabbix server mysql2 172.16.160.162 #zabbix agent
# mysqlConfiguration- 在 zabbix agent 上 OPE ## Add account login information, etc. No account password is required when executing the mysql mysqladmin script command, and no more warnings are issued $ cat /etc/my.cnf [mysqld] datadir = / var / lib / mysql socket = / var / lib / mysql / mysql.sock validate_password = off innodb_file_per_table = 1 character_set_server = utf8 init_connect = 'SET NAMES utf8' symbolic-links = 0 log-error = / var / log / mysqld.log pid-file = / var / run / mysqld / mysqld.pid
[mysql] #Add the following user = mysqlcheck password = mysqlchechk [client] user = mysqlcheck password = mysqlchechk [mysqladmin] user = mysqlcheck password = mysqlchechk
## Add account password mysql> GRANT USAGE ON *. * TO ‘mysqlcheck’ @ ‘localhost’ IDENTIFIED BY ‘mysqlcheck’; mysql> FLUSH PRIVILEGES;
# zabbixConfiguration- operate on zabbix agent ## Install zabbix_agent and modify the configuration file $ rpm -Uv http://repo.zabbix.com/zabbix/3.2/rhel/7/x86_64/zabbix-agent-3.2.6-1 .el7.x86_64.rpm $ yum install -y zabbix-agent
$ cat /etc/zabbix/zabbix_agentd.conf | grep -v ^ # | grep -v ^ $ PidFile = / var / run / zabbix / zabbix_agentd.pid LogFile = / var / log / zabbix / zabbix_agentd.log LogFileSize = 0 Server = 172.16.160.161 #point to zabbix server ListenPort = 10050 ListenIP = 0.0.0.0 ServerActive = 172.16.160.161 #point to zabbix server Hostname = mysql2 #name to be used when configuring host on zabbix server Include = / etc / zabbix / zabbix_agentd.d /*.conf #This line is $ pointing to the template file
## Add the configuration file .my.cnf used to associate mysql, no account password is required when executing the mysql mysqladmin script command, and no warning will be prompted
The mysql.sock file finds the path by finding / -name mysql.sock $ cat /etc/zabbix/.my.cnf [mysql] host = localhost user = mysqlcheck password = mysqlcheck socket = /var/lib/mysql/mysql.sock [mysqladmin] host = localhost user = mysqlcheck password = mysqlchechk socket = /var/lib/mysql/mysql.sock
## Modify the mysql path in the zabbix template configuration file This file is used to specify how Zabbix Agent obtains MySQL data. We need to modify all / var / lib / zabbix to the path of the current .my.cnf file / etc / zabbix. $ sed -i ‘[email protected] / var / lib / [email protected] / etc / [email protected]‘ /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf
$ cat /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf | grep -v ^ # | grep -v ^ $ UserParameter = mysql.status [*], echo "show global status where Variable_name = '$ 1';" | HOME = / etc / zabbix mysql -N | awk '{print $$ 2}' UserParameter = mysql.size [*], bash -c '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 = / etc / zabbix mysql -N 'UserParameter = mysql.ping, HOME = / etc / zabbix mysqladmin ping | grep -c alive UserParameter = mysql.version, mysql -V ## Save and restart Zabbix Agent after setup is complete $ systemctl restart zabbix-agent.service
## Test mysqladmin command without account password $ mysqladmin ping | grep -c alive 1
#zabbixPage Configuration- 在 zabbix serveroperation ## Create host Configuration—Hosts—Create host host name, group selection, agent IP address and port, and then click update ## Use template Configuration—Hosts—Host name—Tempaltes Search for MySQL, add the pop-up template Template App MySQL, and then click Update. Note: After selecting the template, click the Add button. ## View the monitoring effect Monitoring—Graphs, select the group, then select the host and the graph displayed. At this time, the mysql bandwidth and mysql operations will be added to the graph, and there will be output information.
#Summary description: After installing zabbix_agent, you can already monitor the performance parameters of the mysql server, but if you need to further monitor and do some simple monitoring, you need to use the template that comes with zabbix; however, if you only need to do the Simple monitoring, zabbix's own template can fully meet the requirements; if there is a higher demand, you need to write your own script, or use fromdual plugins. Reference: http://www.fromdual.com/mpm-installation-guide
Zabbix has some predefined keys in the template, but usually, it doesn't meet our needs. Fortunately, zabbix provides a custom key method, so we can flexibly monitor all kinds of data we want to monitor. ## Enable $ vi /etc/zabbix/zabbix_agentd.conf UnsafeUserParameters = 1
$ cat /etc/zabbix/zabbix_agentd.conf | grep -v ^ # | grep -v ^ $ PidFile = / var / run / zabbix / zabbix_agentd.pid LogFile = / var / log / zabbix / zabbix_agentd.log LogFileSize = 0 Server = 172.16.160.161 #point to zabbix server ListenPort = 10050 ListenIP = 0.0.0.0 ServerActive = 172.16.160.161 #point to zabbix server Hostname = mysql2 #name to be used when configuring host on zabbix server Include = / etc / zabbix / zabbix_agentd.d /*.conf #This line points to the template file UnsafeUserParameters = 1
In the monitoring system of Zabbix, it is usually implemented by Zabbix Server and Zabbix Agent. Many monitoring-based items are built into Zabbix Agent.
These monitoring items are basic monitoring items such as CPU, file system, network, disk, etc. For the monitoring of self-developed services, Zabbix provides a good framework for users to implement monitoring and alarming. The following will take adding monitoring for MySQL as an example to introduce how to add Custom monitoring.
lab environment
1.NySQL 192.168.2.6 (agent)
2.Zabbix Server 172.30.1.208
Roles: Zabbix Agent, Zabbix Server, MySQL, Template Step one, monitoring planning
Before you create a monitoring item, you should consider what you want to monitor, how to monitor, how to store monitoring data, how to display monitoring data, and how to handle alarms. To plan the monitoring system, you need to know Zabbix very well, here is just the requirement for monitoring.
Requirement 1: Monitor the status of MySQL, and issue an alarm when the status is abnormal. Requirement 2: Monitor the operation of MySQL and display it with charts; Step 2: Use a custom script to monitor the extended agent.
The collection of monitoring data between Zabbix Server and Agent is mainly through the Zabbix Server to actively ask the Agent for the value of a Key, and the Agent will call the corresponding function to obtain this value according to the Key and return it to the Server. Zabbix 2.4.7 Agent does not have built-in MySQL monitoring function (but the Server provides corresponding Template configuration), so we need to use Zabbix User Parameters function to add monitoring script for MySQL.
Trapper works:
The monitored host periodically pushes data to Zabbix Server according to the time interval set by the user. Here we mainly introduce Agent.
Agent works:
The Agent is installed on the monitored host, and regularly monitors the resources and applications of the machine on a regular basis, and then sends the data to ZabbixServer for processing. The working mode of the Agent is divided into Passive Check and Active Check.
Passive Check: Zabbix Server initiates a data request, and the Agent responds to the corresponding data.
Active Check: The Agent first retrieves the list of monitoring items from Zabbix Server, and then periodically sends the corresponding data to the Zabbix Server. Zabbix Server itself has some predefined monitoring types, and for administrators who do not have predefined ones, they need to define it themselves. Provided "UserP
"arameter" parameter, so that users can customize the data they want to obtain according to their needs.
"UserParameter" syntax: UserParameter = <key>, <command>
The user defines a key; as a command, this command is used to obtain the data the user wants to monitor, that is, the value of the key;
After setting the UserParameter parameter, when configuring monitoring items for the host or template, enter the custom key above in "key"
The name is fine.
If I want to get the version of Mysql Server, I can define "UserParameter" like this:
Open the ../etc/zabbix_agentd.conf configuration file under the Zabbix Agent installation path, turn the page to the last page, type the following
Row:
UserParameter = mysql.version, mysql -V
Here our custom key name is "mysql.version". The command "mysql -V" is used to get the MySQL version number, which is actually the key pair.
Value.
The principle of UserParameter parameter implementation is generally speaking, that is, we must first be familiar with Mysql commands, and get the desired ones through Mysql commands.
The data is then assigned to a custom key. Finally, this value is obtained through Zabbix Server and displayed through images.
The following uses Agent to monitor the performance of mysql.
Authorize mysql login user (agent side):
mysql> grant usage on *. * to [email protected] identified by ‘123456’;
mysql> flush privileges;
agent side:
1. Use the UserParameter parameter to customize the Agent Key.
For demand one, we use the tool mysqladmin to implement, the command is as follows:
If the MySQL status is normal, mysqld is alive will be displayed, otherwise it will prompt that the connection is not available. For the server side, sentences such as mysqld is alive are not easy to understand. The server side is best to receive only 1 and 0. 1 means that the service is available and 0 means that the service is unavailable. Then improve this command as follows:
linux: /etc/zabbix/zabbix-agentd.conf.d # mysqladmin -uzabbix -p‘123456 ‘-h127.0.0.1 ping | grep -c alive
1
Putting the username and password in the command is not good for future maintenance, so we create a configuration file ".my.cnf" containing MySQL username and password under / etc / zabbix /, as follows: (I have not done this here)
[client]
user = zabbix
host = 192.168.2.6
password = 123456
With this file the command is changed to
linux: /etc/zabbix/zabbix-agentd.conf.d # HOME = / etc / zabbix / mysqladmin ping | grep -c alive
After doing this step, all you need to do is to add this monitoring command to the Zabbix Agent and correspond to a key, so that Zabbox Server can get the status of MySQL through this key. We use mysql.ping as the key for MySQL status.
First remove the comment that contains the sub-configuration file path in /etc/zabbix/zabbix-agentd.conf and modify it
Include = / etc / zabbix / zabbix-agentd.conf.d / (name it according to your own situation, don't forget to create this directory)
Second, create a userparameter_mysql.conf file in the etc / zabbix / zabbix_agentd.conf.d / directory. Add the following command to the file:
"UserParameter" in this command indicates that this is a user-defined script; the content after the "=" sign is the key; "mysql.ping" is the Key, and the command after the "," sign will initiate the acquisition from the Zabbix Server to the " mysql.ping "is called when requested for this key, and the return value is returned to the server.
After saving and exiting, you can use the following command to test whether it works properly.
At the same time, you can also use the zabbix_get command on the server side to test to obtain the specified client side data from the server side, as follows:
Then download and install the MySQL monitoring template (I use script monitoring here + zabbix comes with a MySQL template)
https://github.com/yangcvo/zabbix.2.4/
I use the zabbix template App MySQL
Create host 192.168.2.6
Association template
Create item
Create graphics
Viewing surveillance images
Set alarm, create trigger, action (when STATUS = 0, alarm)
carry out testing
Stop the database (test environment)
linux: ~ # /etc/init.d/mysql stop
Check Mail
The email reminds me that the database STATUS = 0 is down
Restore the database
linux: ~ # /etc/init.d/mysql start
The email reminds me that the database STATUS = 1 is UP
Zabbix monitor mysql performance
Pass these status values to the server by drawing mysql status values and draw them as pictures, so that you can observe the working status of mysql, usually you need to get the status variables with
Com_update: number of updates performed by mysql
Com_select: Number of queries executed by mysql
Com_insert: number of inserts performed by mysql
Com_delete: number of deletions performed
Com_rollback: number of operations to perform rollback
Bytes_received: number of bytes accepted
Bytes_sent: number of bytes sent
Slow_queries: number of slow query statements
Monitor mysql script (chk_mysql.sh written on the client)
# Parameter is correct
if [$ # -ne "1"]; then
echo "arg error!"
fi
# retrieve data
case $ 1 in
Uptime)
result = `$ {MYSQL_CONN} status | cut -f2 -d": "| cut -f1 -d" T "`
echo $ result
;;
Com_update)
result = `$ {MYSQL_CONN} extended-status | grep -w" Com_update "| cut -d" | "-f3`
echo $ result
;;
Slow_queries)
result = `$ {MYSQL_CONN} status | cut -f5 -d": "| cut -f1 -d" O "`
echo $ result
;;
Com_select)
result = `$ {MYSQL_CONN} extended-status | grep -w" Com_select "| cut -d" | "-f3`
echo $ result
;;
Com_rollback)
result = `$ {MYSQL_CONN} extended-status | grep -w" Com_rollback "| cut -d" | "-f3`
echo $ result
;;
Questions)
result = `$ {MYSQL_CONN} status | cut -f4 -d": "| cut -f1 -d" S "`
echo $ result
;;
Com_insert)
result = `$ {MYSQL_CONN} extended-status | grep -w" Com_insert "| cut -d" | "-f3`
echo $ result
;;
Com_delete)
result = `$ {MYSQL_CONN} extended-status | grep -w" Com_delete "| cut -d" | "-f3`
echo $ result
;;
Com_commit)
result = `$ {MYSQL_CONN} extended-status | grep -w" Com_commit "| cut -d" | "-f3`
echo $ result
;;
Bytes_sent)
result = `$ {MYSQL_CONN} extended-status | grep -w" Bytes_sent "| cut -d" | "-f3`
echo $ result
;;
Bytes_received)
result = `$ {MYSQL_CONN} extended-status | grep -w" Bytes_received "|
"arameter" parameter, so that users can customize the data they want to obtain according to their needs.
"UserParameter" syntax: UserParameter = <key>, <command>
The user defines a key; as a command, this command is used to obtain the data the user wants to monitor, that is, the value of the key;
After setting the UserParameter parameter, when configuring monitoring items for the host or template, enter the custom key above in "key"
The name is fine.
If I want to get the version of Mysql Server, I can define "UserParameter" like this:
Open the ../etc/zabbix_agentd.conf configuration file under the Zabbix Agent installation path, turn the page to the last page, type the following
Row:
UserParameter = mysql.version, mysql -V
Here our custom key name is "mysql.version". The command "mysql -V" is used to get the MySQL version number, which is actually the key pair.
Value.
The principle of UserParameter parameter implementation is generally speaking, that is, we must first be familiar with Mysql commands, and get the desired ones through Mysql commands.
The data is then assigned to a custom key. Finally, this value is obtained through Zabbix Server and displayed through images.
The following uses Agent to monitor the performance of mysql.
Authorize mysql login user (agent side):
mysql> grant usage on *. * to [email protected] identified by ‘123456’;
mysql> flush privileges;
agent side:
1. Use the UserParameter parameter to customize the Agent Key.
For demand one, we use the tool mysqladmin to implement, the command is as follows:
If the MySQL status is normal, mysqld is alive will be displayed, otherwise it will prompt that the connection is not available. For the server side, sentences such as mysqld is alive are not easy to understand. The server side is best to receive only 1 and 0. 1 means that the service is available and 0 means that the service is unavailable. Then improve this command as follows:
linux: /etc/zabbix/zabbix-agentd.conf.d # mysqladmin -uzabbix -p‘123456 ‘-h127.0.0.1 ping | grep -c alive
1
Putting the username and password in the command is not good for future maintenance, so we create a configuration file ".my.cnf" containing MySQL username and password under / etc / zabbix /, as follows: (I have not done this here)
[client]
user = zabbix
host = 192.168.2.6
password = 123456
With this file the command is changed to
linux: /etc/zabbix/zabbix-agentd.conf.d # HOME = / etc / zabbix / mysqladmin ping | grep -c alive
After doing this step, all you need to do is to add this monitoring command to the Zabbix Agent and correspond to a key, so that Zabbox Server can get the status of MySQL through this key. We use mysql.ping as the key for MySQL status.
First remove the comment that contains the sub-configuration file path in /etc/zabbix/zabbix-agentd.conf and modify it
Include = / etc / zabbix / zabbix-agentd.conf.d / (name it according to your own situation, don't forget to create this directory)
Second, create a userparameter_mysql.conf file in the etc / zabbix / zabbix_agentd.conf.d / directory. Add the following command to the file:
"UserParameter" in this command indicates that this is a user-defined script; the content after the "=" sign is the key; "mysql.ping" is the Key, and the command after the "," sign will initiate the acquisition from the Zabbix Server to the " mysql.ping "is called when requested for this key, and the return value is returned to the server.
After saving and exiting, you can use the following command to test whether it works properly.
At the same time, you can also use the zabbix_get command on the server side to test to obtain the specified client side data from the server side, as follows:
Then download and install the MySQL monitoring template (I use script monitoring here + zabbix comes with a MySQL template)
https://github.com/yangcvo/zabbix.2.4/
I use the zabbix template App MySQL
Create host 192.168.2.6
Association template
Create item
Create graphics
Viewing surveillance images
Set alarm, create trigger, action (when STATUS = 0, alarm)
carry out testing
Stop the database (test environment)
linux: ~ # /etc/init.d/mysql stop
Check Mail
The email reminds me that the database STATUS = 0 is down
Restore the database
linux: ~ # /etc/init.d/mysql start
The email reminds me that the database STATUS = 1 is UP
Zabbix monitor mysql performance
Pass these status values to the server by drawing mysql status values and draw them as pictures, so that you can observe the working status of mysql, usually you need to get the status variables with
Com_update: number of updates performed by mysql
Com_select: Number of queries executed by mysql
Com_insert: number of inserts performed by mysql
Com_delete: number of deletions performed
Com_rollback: number of operations to perform rollback
Bytes_received: number of bytes accepted
Bytes_sent: number of bytes sent
Slow_queries: number of slow query statements
Monitor mysql script (chk_mysql.sh written on the client)
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
