zabbix-3.2.4 monitoring MySQL server performance

Environment: CENTOS7
has been installed
Zabbix-agent-3.2.4-2.el7.x86_64
Zabbix-server-mysql-3.2.4-2.el7.x86_64
Zabbix-web-3.2.4-2.el7.noarch
Zabbix-web-mysql-3.2.4-2.el7.noarch
Zabbix-release-3.2-1.el7.noarch
Zabbix-get-3.2.4-2.el7.x86_64

To view the zabbix_agentd.conf configuration file:

pidfile=/var/run/zabbix/Zabbix_agentd.pidlogfile=/var/log/zabbix/zabbix_agentd.loglogfilesize=0server=127.0. 0.1//When the system is set up with more than one IP, you need to specify an IP with level two agent or server communication, if the system has only one IP, it is also recommended to specify an IPlistenport=10050  Serveractive=127.0. 0.1//In active mode, the serveractive is a level two proxy server or server with the default port of 10051 Hostname=127.0. 0.1//Manually customize a host name, which can be the same as the hostname of the system, or not, this parameter can be turned on or off according to the actual situation, it is recommended to turn off this parameter and enable the Hostnameitem parameter Include=/etc/zabbix/zabbix_agentd.d/*. conf

Start MySQL performance monitoring
You can use the MySQL template that comes with Zabbix, but you also need to prepare a script for MySQL status on the MySQL server Chk_mysql.sh,zabbix call this script to get MySQL running information.

1. Add MySQL monitoring information in config file zabbix_agentd.conf:

 # Vim/etc/zabbix/zabbix_agentd.confuserparameter  =mysql.version,mysql-< Span style= "COLOR: #000000" >vuserparameter  =mysql.status[*],/root/chk_mysql.sh $ 1  //chk_mysql.sh path according to actual situation userparameter  =mysql.ping,mysqladmin-uzabbix -pzabbix -p3306 -h127.0.0.1  Ping | Grep-c Alive 
//mysql database name: Zabbix, Password: Zabbix; host address and zabbix_agentd.conf in hostname consistent   

2. mysql operation add MySQL account:
mysql>GRANT process,super,replication CLIENT on * * to [e-mail protected] ' 127.0.0.1 ' identified by ' Zabbix ';

3.MySQL Monitor script chk_mysql.sh

#!/bin/bash#-------------------------------------------------------------------------------# filename:check_mysql.sh# Revision:1.0# Date: -/ ./ the# author:dengyun# Email: [Email protected]# website:www.ttlsa.com# Description: # Notes:~# -------------------------------------------------------------------------------# Copyright: -(c) dengyun# LICENSE:GPL # user name Mysql_user='Zabbix'# password Mysql_pwd='Zabbix'# Host Address/Ipmysql_host='127.0.0.1'# port Mysql_port='3306'# Data Connection Mysql_conn="/usr/bin/mysqladmin-u${mysql_user}-p${mysql_pwd}-h${mysql_host}-p${mysql_port}"# parameters are correctif[$#-ne"1"];then Echo"Arg error!"fi # Getting data Case$1 inchUptime) 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"|cut-d"|"-F3 ' Echo $result;; Com_begin) Result= ' ${mysql_conn} extended-status |grep-w"Com_begin"|cut-d"|"-F3 ' Echo $result;; *) echo"usage:$0 (uptime| com_update| slow_queries| Com_select| com_rollback| questions| Com_insert| com_delete| Com_commit| Bytes_sent| bytes_received| Com_begin)";; Esac

check_mysql.sh

To add a check script permission:
[Email protected]~] #chmod u+x/root/chk_mysql.sh
[Email protected]~] #chown-R Zabbix.zabbix

4.go to the Zabbix server to check whether the basic MySQL server information can be obtained correctly:
[email protected]~]# zabbix_get-s 127.0.0.1-p10050-k "system.cpu.load[all,avg15]"; //127.0.0. 1 consistent with server in zabbix_agentd.conf
0.050000
[[Email protected]~]#

Zabbix Web Settings
1. Build MySQL host groups Group
Template is provided by Zabbix system, enter Zabbix Web backend, configuration-->hosts groups--> Click "Create Host Group"--Select Template tab, select Templates " Templateapp mysql,templdate OS Linux ", Last click on update
　　
2, building the hosts
template is provided by Zabbix system, enter Zabbix Web background,configuration-->hosts--> Click on your host name--> select template selection tab, select Templates "Template App MySQL", then click on the "Add" button on the left, then click the "Update" button

3, monitoring Performance view
Once the monitoring script is working, you will see the MySQL monitoring Performance view in graph below Zabbix-server's Host, monitoring-->graphs-->host (select MySQL server) graph ( Choose the MySQL option), you can see the performance monitoring view, Zabbix self-made template by default there are 2 performance graphs, these graphs to wait for a period of time to draw out

　　

Report:
View the database configuration information under the Zabbix Web Management Section Project
[[Email protected]~]# vim/xxxx/zabbix.conf.php

<?PHP//Zabbix GUI configuration file.Global$DB; $DB ['TYPE']                              ='MySQL'; $DB ['SERVER'] ='127.0.0.1'; $DB ['PORT']                              ='3306'; $DB ['DATABASE'] ='Zabbix'; $DB ['USER']                              ='Zabbix'; $DB ['PASSWORD'] ='Zabbix';//Schema name. Used for IBM DB2 and PostgreSQL.$DB ['SCHEMA']                   ="'; $ZBX _server='localhost'; $ZBX _server_port='10051'; $ZBX _server_name="'; $IMAGE _format_default=image_format_png;?>

zabbix-3.2.4 monitoring MySQL server performance