Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to use zabbix to monitor mysql. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

Zabbix monitoring mysql1, monitoring planning

Before creating monitoring items, try to think clearly about what to monitor, how to monitor, how to store monitoring data, how to display monitoring data, how to deal with alarms, and so on. System planning for monitoring requires a good understanding of Zabbix, and this is just a 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 show it in a chart

2. Custom script monitoring extension Agent

The collection of monitoring data between Zabbix Server and Agent is mainly through Zabbix Server actively asking Agent for the value of a certain Key. Agent will call the corresponding function according to Key to get this value and return it to the server. The Agent version of Zabbix 2.4.7 does not have the built-in monitoring function of MySQL (but the server side provides the corresponding Template configuration), so we need to use the User Parameters function of Zabbix to add monitoring scripts for MySQL.

3. Authorize mysql login user (agent side) mysql > grant usage on *. * to zabbix@127.0.0.1 identified by '123456 login user MySQL > flush privileges;4, and configure survival detection on the server

Customize Agent Key with the UserParameter parameter.

For requirement 1, we use mysqladmin as a tool to implement it, with the following command:

# mysqladmin-h 127.0.0.1-u zabbix-p123456 ping mysqld is alive

If the MySQL status is normal, mysqld is alive will be displayed, otherwise it will indicate that the connection cannot be made. For the server, sentences such as mysqld is alive are difficult to understand. It is best for the server to receive only 1 and 0 to indicate that the service is available, and 0 means that the service is not available. So improve this command again, as follows:

# mysqladmin-h 127.0.0.1-u zabbix-p123456 ping | grep-c alive1

Putting the username and password in the command is not good for future maintenance, so we create a configuration file ".my.cnf" that contains the MySQL username and password under / var/lib/zabbix/, as follows:

User=zabbixhost=127.0.0.1password='123456'

With this file, the command is changed to

HOME=/var/lib/zabbix/ mysqladmin ping | grep-c alive1

All you need to do after this step is to add the monitoring command to the Zabbix Agent and correspond to a Key so that the Zabbox Server can get the status of the MySQL through this Key. We use mysql.ping as the Key of the MySQL state.

First of all, in removing / etc/zabbix/zabbix_agentd.conf

The comment character for the line "Include=/etc/zabbix_agentd.d/".

Second, create the userparameter_mysql.conf file in the / etc/zabbix/zabbix_agentd.d/ directory. Add the following command to the file:

UserParameter=mysql.ping,HOME=/var/lib/zabbix mysqladmin ping | grep-c alive

Use the following command to test if it is working properly.

# / usr/sbin/zabbix_agentd-t mysql.pingmysql.ping [t | 1] other performance indicators 1. Add userparameter_mysqlvim / etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf#### script UserParameter=mysql.status to monitor mysql performance [*], / etc/zabbix/zabbix_agentd.d/check_mysql.sh $1#mysql version UserParameter=mysql.version Mysql-V2.Checkout mysql.shroud Bash #-# FileName: check_mysql.sh# Revision: 1. -# Copyright:# License: GPL# username MYSQL_USER='zabbix'# password MYSQL_PWD='zabbix@123'# host address / IPMYSQL_HOST='ip'# port MYSQL_ PORT='3306'# data connection MYSQL_CONN= "/ usr/bin/mysqladmin-u$ {MYSQL_USER}-p$ {MYSQL_PWD}-h$ {MYSQL_HOST}-P$ {MYSQL_PORT}" # whether the parameter is correct if [$#-ne "1"] Thenecho "arg error!" fi# acquires data case $1 inUptime) result= `$ {MYSQL_CONN} status 2 > / dev/null | cut-f2-d ":" | cut-F1-d "T" `echo $result;;Com_update) result= `$ {MYSQL_CONN} extended-status 2 > / dev/null | grep-w "Com_update" | cut-d "|"-f3`echo $result;;Slow_queries) result= `$ {MYSQL_CONN} status 2 > / dev/null | cut-f5-d ":" | cut-F1-d "O" `echo $result Com_select) result= `$ {MYSQL_CONN} extended-status 2 > / dev/null | grep-w "Com_select" | cut-d "|"-f3`echo $result;;Com_rollback) result= `$ {MYSQL_CONN} extended-status 2 > / dev/null | grep-w "Com_rollback" | cut-d "|"-f3`echo $result;;Questions) result= `$ {MYSQL_CONN} status 2 > / dev/null | cut-f4-d ":" | cut-F1-d "S" `echo $result " Com_insert) result= `$ {MYSQL_CONN} extended-status 2 > / dev/null | grep-w "Com_insert" | cut-d "|"-f3`echo $result;;Com_delete) result= `$ {MYSQL_CONN} extended-status 2 > / dev/null | grep-w "Com_delete" | cut-d "|"-f3`echo $result;;Com_commit) result= `$ {MYSQL_CONN} extended-status 2 > / dev/null | grep-w "Com_commit" | cut-d "|"-f3`echo $result " Bytes_sent) result= `$ {MYSQL_CONN} extended-status 2 > / dev/null | grep-w "Bytes_sent" | cut-d "|"-f3`echo $result;;Bytes_received) result= `$ {MYSQL_CONN} extended-status 2 > / dev/null | grep-w "Bytes_received" | cut-d "|"-f3`echo $result;;Com_begin) result= `$ {MYSQL_CONN} extended-status 2 > / dev/null | grep-w "Com_begin" | cut-d "|"-f3`echo $result " *) echo "Usage:$0 (Uptime | Com_update | Slow_queries | Com_select | Com_rollback | Com_rollback | Com_insert | Com_delete | Com_commit | Bytes_sent | Bytes_received | Com_begin)";; esac3. Authorization: chmod + x / etc/zabbix/zabbix_agentd.d/check_mysql.shChown zabbix.zabbix / etc/zabbix/zabbix_agentd.d/check_mysql.sh4. Test on zabbix_agent:

Zabbix_agentd-t mysql.ping

5.Zabbix_server test

Zabbix_get-s ip-P port-k mysql.ping

5. Configure on the web side

* * create a host * *

Association template

Create a monitoring item

Create a drawin

View monitoring images

Other monitoring items are completed with this configuration

6. Zabbix comes with mysql monitoring item version: database version key_buffer_size:myisam index buffer size sort_buffer_size: session sort space (one for each thread) join_buffer_size: this is the minimum cache size allocated for link operations These connections use normal index scans, range scans, or connections do not apply to the index max_connections: maximum number of simultaneous connections allowed max_connect_errors: the maximum number of erroneous links allowed by a host, and subsequent links will be rejected if exceeded (default 100). You can use the flush hosts command to unreject open_files_limits: the number of files that the operating system allows mysql to open You can determine whether you need to increase the table_open_cache by the opened_tables status. If the opened_tables is relatively large and is still growing, you need to increase the table_open_cachemax-heap_tables_size: the maximum size of the established memory table (default 16m) this parameter limits the maximum value of the internal temporary table together with tmp_table_size (take the smaller of these two parameters). If the limit is exceeded, the table will become an innodb or myisam engine. (before 5.7.5, it defaults to myisam,5.7.6 and starts with innodb, which can be adjusted by the internal_tmp_disk_storage_engine parameter.) Max_allowed_packet: maximum size of a package # GET INNODB INFO#INNODB variablesinnodb_version:innodb_buffer_pool_instances: divides the innodb buffer pool into specified multiple (default 1) innodb_buffer_pool_size:innodb buffer pool sizes, and 5.7.5 introduces innodb_buffer_pool_chunk_size Innodb_doublewrite: number of doublewrite (default enabled) innodb_read_io_threads:IO read threads # number of innodb_write_io_threads:IO write threads # number and size of pages in the innodb statusinnodb_buffer_pool_pages_total:innodb buffer pool equal to the number of pages in the innodb_buffer_pool_size/ (16024) innodb_buffer_pool_pages_data:innodb buffer pool containing data # GET MYSQL HITRATE1, Query cache hit ratio Qcache_hits/ (Qcache_hits+Com_select) if Qcache_hits+Com_select0 Otherwise, 02, thread cache hit rate if Connections0, 1-Threads_created/Connections, otherwise 03, myisam key cache hit rate if Key_read_requests0, 1-Key_reads/Key_read_requests, otherwise 04, myisam key cache write hit rate if Key_write_requests0, 1-Key_writes/Key_write_requests, otherwise 05, keyblock usage if Key_blocks_used+Key_blocks_unused0 Then Key_blocks_used/ (Key_blocks_used+Key_blocks_unused), otherwise 06, create temporary table ratio of disk storage if Created_tmp_disk_tables+Created_tmp_tables0, then Created_tmp_disk_tables/ (Created_tmp_disk_tables+Created_tmp_tables), otherwise 07, connection utilization if max_connections0, then threads_connected/max_connections, otherwise 08, open file ratio if open_files_limit0, then open_files/open_files_limit Otherwise 09, table cache usage if table_open_cache0, then open_tables/table_open_cache, otherwise 0 after reading the above content, do you have any further understanding of how to use zabbix to monitor mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.