How to configure zabbix to monitor mysql Performance

Obtain the mysql status values to pass these status values to the server and draw them into an image. This allows you to observe the working status of mysql. Generally, you need to obtain the following status variables:

Com_update: number of updates executed by mysql
Com_select: number of queries executed by mysql
Com_insert: Number of inserts executed by mysql
Com_delete: number of deleted tasks
Com_rollback: Number of rollback operations
Bytes_received: Number of bytes accepted
Bytes_sent: Number of bytes sent
Slow_queries: number of slow query statements

1. Create a mysql Performance Monitoring script

#! /Bin/bash
# Create by zhengdazhi 2014.09.22
MYSQL_DIR =/usr/local/mysql
MYSQL =$ {MYSQL_DIR}/bin/mysql
MYSQLADMIN =$ {MYSQL_DIR}/bin/mysqladmin
MYSQL_SOCK = "/tmp/mysql. sock"
MYSQL_USER = root
MYSQL_PWD = root
 
ARGS = 1
If [$ #-ne "$ ARGS"]; then
Echo "Please input one arguement :"
Fi
Case $1 in
Uptime)
Result = '$ {MYSQLADMIN}-u $ {MYSQL_USER}-p $ {MYSQL_PWD}-S $ MYSQL_SOCK status | cut-f2-d ": "| cut-f1-d" T "'
Echo $ result
;;
Com_update)
Result = '$ {MYSQLADMIN}-u $ {MYSQL_USER}-p $ {MYSQL_PWD}-S $ MYSQL_SOCK extended-status | grep-w "Com_update" | cut-d "|" -f3'
Echo $ result
;;
Slow_queries)
Result = '$ {MYSQLADMIN}-u $ {MYSQL_USER}-p $ {MYSQL_PWD}-S $ MYSQL_SOCK status | cut-f5-d ": "| cut-f1-d" O "'
Echo $ result
;;
Com_select)
Result = '$ {MYSQLADMIN}-u $ {MYSQL_USER}-p $ {MYSQL_PWD}-S $ MYSQL_SOCK extended-status | grep-w "Com_select" | cut-d "|" -f3'
Echo $ result
;;
Com_rollback)
Result = '$ {MYSQLADMIN}-u $ {MYSQL_USER}-p $ {MYSQL_PWD}-S $ MYSQL_SOCK extended-status | grep-w "Com_rollback" | cut-d "|" -f3'
Echo $ result
;;
Questions)
Result = '$ {MYSQLADMIN}-u $ {MYSQL_USER}-p $ {MYSQL_PWD}-S $ MYSQL_SOCK status | cut-f4-d ": "| cut-f1-d" S "'
Echo $ result
;;
Com_insert)
Result = '$ {MYSQLADMIN}-u $ {MYSQL_USER}-p $ {MYSQL_PWD}-S $ MYSQL_SOCK extended-status | grep-w "Com_insert" | cut-d "|" -f3'
Echo $ result
;;
Com_delete)
Result = '$ {MYSQLADMIN}-u $ {MYSQL_USER}-p $ {MYSQL_PWD}-S $ MYSQL_SOCK extended-status | grep-w "Com_delete" | cut-d "|" -f3'
Echo $ result
;;
Com_commit)
Result = '$ {MYSQLADMIN}-u $ {MYSQL_USER}-p $ {MYSQL_PWD}-S $ MYSQL_SOCK extended-status | grep-w "Com_commit" | cut-d "|" -f3'
Echo $ result
;;
Bytes_sent)
Result = '$ {MYSQLADMIN}-u $ {MYSQL_USER}-p $ {MYSQL_PWD}-S $ MYSQL_SOCK extended-status | grep-w "Bytes_sent" | cut-d "|" -f3'
Echo $ result
;;
Bytes_received)
Result = '$ {MYSQLADMIN}-u $ {MYSQL_USER}-p $ {MYSQL_PWD}-S $ MYSQL_SOCK extended-status | grep-w "Bytes_received" | cut-d "|" -f3'
Echo $ result
;;
Com_begin)
Result = '$ {MYSQLADMIN}-u $ {MYSQL_USER}-p $ {MYSQL_PWD}-S $ MYSQL_SOCK extended-status | grep-w "Com_begin" | cut-d "|" -f3'
Echo $ result
;;
 
*)
Echo "Usage: $0 (Uptime | Com_update | Slow_queries | Com_select | Com_rollback | Questions )"
;;
Esac

2. Modify the client configuration file

View the mysql monitoring template that comes with zabbix

Screenshot 00

It can be seen that this template is the key for reading mysql. status. Therefore, the custom key name added to the client configuration file should also be mysql. status.

Vim/usr/local/zabbix_agentd/etc/zabbix_agentd.conf
# Enable user-defined configuration
UnsafeUserParameters = 1
# Add mysql monitoring
UserParameter = mysql. status [*],/usr/local/zabbix_agent/bin/checkmysqlperformance. sh $1 $2
Restart the client
3. Test
[Root @ localhost bin] #./zabbix_get-s 127.0.0.1-k mysql. status [Com_update]
77503