Shulou(Shulou.com)06/01 Report--

Today, let's take a look at how zabbix monitors mysql performance. Using the template that comes with mysql, you can monitor the following contents: OPS (add, delete, modify and query), mysql request traffic bandwidth, mysql response traffic bandwidth, and finally attach the corresponding monitoring chart!

Write check_mysql.sh scripts

To obtain mysql performance metrics data, you need to modify the corresponding database information

# vim / usr/local/zabbix-2.4.4/scripts/chk_mysql.sh

# the script is as follows:

#! / bin/bash

#-

# FileName: check_mysql.sh

# Revision: 1.0

# Date: 2015-06-09

# Author: DengYun

# Email: dengyun@ttlsa.com

# Website: www.ttlsa.com

# Description:

# Notes: ~

#-

# Copyright: 2015 (c) DengYun

# License: GPL

# user name

MYSQL_USER='zabbix'

# password

MYSQL_PWD='123456'

# Host address / IP

MYSQL_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}"

# whether the parameter is correct

If [$#-ne "1]; then

Echo "arg error!"

Fi

# get 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" | 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

Modify zabbix_agentd.conf

Add a custom key, and add the following on the last line:

# get mysql version UserParameter=mysql.version,mysql-V# to obtain mysql performance metrics. This is the script UserParameter=mysql.status [*] defined above. / usr/local/zabbix-2.4.4/scripts/chk_mysql.sh $running gets the mysql running status UserParameter=mysql.ping,mysqladmin-uzabbix-p123456-P3306-h227.0.0.1 ping | grep-c alive

Note: please pay attention to modify your database information and zabbix path information

Restart zabbix# killall zabbix-agentd# / usr/local/zabbix-2.4.4/bin/zabbix_agentd or # service zabbix_agentd restartLink MySQL template

The template is provided by the zabbix system. Enter the zabbix web backend, configuration-- > hosts-- > Click your host name-- > Select the template tab, select the template "Template App MySQL", and finally click update.

Zabbix Monitoring mysql

Data viewing

If the configuration is normal, you can see two monitoring charts in graph: request traffic bandwidth, response traffic bandwidth and ops. Click monitoring-- > graphs-- > Select your CVM, and select Graph "MySQL bandwidth" and "MySQL operations" respectively. The monitoring charts are as follows (click to enlarge the picture):

Zabbix Monitoring mysql

Zabbix Monitoring mysql

Ways to solve common mistakes

If you find that there is no data in the monitoring, please troubleshoot the following questions

1. Whether the zabbix client is restarted

two。 Whether the script has permission to execute

3. Whether the database has permissions

4. Is there a problem with the environment variable?

5. Please look at the zabbix item column, mouse over the red fork, there is an error prompt.

6. If the database password is saved in the script, it will cause the monitoring to have no data and will constantly report errors as Warning: Using a password on the command line interface can be insecure. Account password and other configurations need to be added to the my.cnf.

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.