Install and configure Zabbix to monitor MySQL. tutorial _ MySQL

This article describes how to install and configure Zabbix to monitor MySQL. Zabbix has the function of displaying data on the web page. the installation environment in this article is CentOS. For more information, see Simple installation and configuration of Zabbix
1. install zabbix on the basis of the existing LAMP or LNMP and install some dependent packages:

yum -y install mysql-devel libcurl-devel net-snmp-devel

2. add a user:

groupadd zabbixuseradd zabbix -g zabbix

3. create a database and add an authorized account

create database zabbix character set utf8;grant all privileges on zabbix.* to zabbix@localhost identified by 'zabbix';

4. Compile and install zabbix
:

wget http://jaist.dl.sourceforge.net/project/zabbix/ZABBIX%20Latest%20Stable/2.2.0/zabbix-2.2.0.tar.gztar zxf zabbix-2.2.0.tar.gzcd zabbix-2.2.0./configure --prefix=/usr/local/zabbix --enable-server --enable-agent \--with-mysql --with-net-snmp --with-libcurlmake install

5. import database

mysql -uzabbix -pzabbix -hlocalhost zabbix < database/mysql/schema.sqlmysql -uzabbix -pzabbix -hlocalhost zabbix < database/mysql/images.sqlmysql -uzabbix -pzabbix -hlocalhost zabbix < database/mysql/data.sql

6. modify the configuration file

cp misc/init.d/fedora/core/zabbix_server /etc/init.d/cp misc/init.d/fedora/core/zabbix_agentd /etc/init.d/cp -R frontends/php /var/www/html/zabbixsed -i 's/^DBUser=.*$/DBUser=zabbix/g' /usr/local/zabbix/etc/zabbix_server.confsed -i 's/^.*DBPassword=.*$/DBPassword=zabbix/g' /usr/local/zabbix/etc/zabbix_server.confsed -i 's/BASEDIR=\/usr\/local/BASEDIR=\/usr\/local\/zabbix/g' /etc/init.d/zabbix_serversed -i 's/BASEDIR=\/usr\/local/BASEDIR=\/usr\/local\/zabbix/g' /etc/init.d/zabbix_agentd

7. add a service port:

Cat>/etc/services <
 
  

  
8. start the service

  

  
/etc/init.d/zabbix_server start/etc/init.d/zabbix_agentd startecho "/etc/init.d/zabbix_server start" >> /etc/rc.localecho "/etc/init.d/zabbix_agentd start" >> /etc/rc.local
  

  
9. web page configuration. after http access is configured, web login: http: // ip/zabbix
  
Zabbix monitors 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.22MYSQL_DIR=/usr/local/mysqlMYSQL=${MYSQL_DIR}/bin/mysqlMYSQLADMIN=${MYSQL_DIR}/bin/mysqladminMYSQL_SOCK="/tmp/mysql.sock"MYSQL_USER=rootMYSQL_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

  

  
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 custom 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
  

  
4. add the template to the host