使用zabbix全方位监控MySQL

绝代码农

　　上一篇文章使用zabbix自带MySQL监控模板监控MySQL
　　对MySQL的监控不够详细。本文继续探讨对MySQL的详细监控，包括MySQL实例，MySQL主从复制和MySQL存储引擎等。
　　本文使用的MySQL版本是5.5
　　本文使用的模板主要通过FROMDUAL提供的模板更改而成，FROMDUAL官方使用Perl语言编写采集脚本然后通过zabbix trapper的方式推送数据到zabbix server。我觉得FROMDUAL官方提供的配置方式繁琐，并且我对Perl语言又不熟悉，于是阅读官方的Perl脚本后，生出想要重新用Shell语言来实现的想法。模板中的item名称有变更，其他的大体和FROMDUAL官方的模板相同。
　　

　　

　　1.监控原理

　　show global status;    查看全局状态
　　show global variables; 查看全局变量设置
　　mysqladmin             MySQL管理工具

　　show master status;    查看Master状态
　　show slave status;       查看Slave状态
　　show binary logs;       查看二进制日志文件
　　show engine innodb status\G 查看InnoDB存储引擎状态
　　show engine myisam status\G 查看MyISAM存储引擎状态
　　

　　还有通过查看information_schema 这个数据库获取InnoDB存储引擎相关信息
　　

　　2.添加MySQL监控账号
　　GRANT USAGE,PROCESS,SUPER,REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'zabbixagent'@'localhost' IDENTIFIED BY 'zabbixagent';
　　flush privileges;
　　在/usr/local/zabbix/etc/目录下创建一个 .my.cnf 文件

[mysql]
user=zabbixagent
password=zabbixagent
[mysqladmin]
user=zabbixagent
password=zabbixagent

　　

　　3.添加zabbix子配置文件mysql_status.conf

### MySQL DB Infomation
UserParameter=mysql.status
[*],echo "show global status where Variable_name='$1';"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $$2}'
UserParameter=mysql.variables
[*],echo "show global variables where Variable_name='$1';"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $$2}'
UserParameter=mysql.ping,mysqladmin --defaults-file=/usr/local/zabbix/etc/.my.cnf ping|grep -c  alive
UserParameter=mysql.version,echo "select version();"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N
#### MySQL Master Information
UserParameter=mysql.master.Slave_count,echo "show slave hosts;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|wc -l
UserParameter=mysql.master.Binlog_file,echo "show master status;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $1}'|awk -F. '{print $1}'
UserParameter=mysql.master.Binlog_number,echo "show master status;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $1}'|awk -F. '{print $2}'
UserParameter=mysql.master.Binlog_position,echo "show master status;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $2}'
UserParameter=mysql.master.Binlog_count,echo "show binary logs;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|wc -l
UserParameter=mysql.master.Binlog_total_size,echo "show binary logs;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{sum+=$NF}END{print  sum}'
#### MySQL Slave Information
UserParameter=mysql.slave.Seconds_Behind_Master,echo "show slave status\G"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf|grep "Seconds_Behind_Master"|awk '{print $2}'
UserParameter=mysql.slave.Slave_IO_Running,echo "show slave status\G"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf|grep "Slave_IO_Running"|awk '{print $2}'
UserParameter=mysql.slave.Slave_SQL_Running,echo "show slave status\G"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf|grep "Slave_SQL_Running"|awk '{print $2}'
UserParameter=mysql.slave.Relay_Log_Pos,echo "show slave status\G"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf|grep "Relay_Log_Pos"|awk '{print $2}'
UserParameter=mysql.slave.Exec_Master_Log_Pos,echo "show slave status\G"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf|grep "Exec_Master_Log_Pos"|awk '{print $2}'
UserParameter=mysql.slave.Read_Master_Log_Pos,echo "show slave status\G"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf|grep "Read_Master_Log_Pos"|awk '{print $2}'

#### MySQL InnoDB Information
#
UserParameter=mysql.innodb
[*],/usr/local/zabbix/bin/mysql_innodb_status.sh $1

####MySQL MyISAM Information
#

　　

　　InnoDB相关的部分监控项目需要单独用脚本获取
　　mysql_innodb_status.sh

#!/bin/bash
#Get InnoDB Row Lock Details and InnoDB Transcation Lock Memory
#mysql> SELECT SUM(trx_rows_locked) AS rows_locked, SUM(trx_rows_modified) AS rows_modified, SUM(trx_lock_memory_bytes) AS lock_memory FROM information_schema.INNODB_TRX;
#+-------------+---------------+-------------+
#| rows_locked | rows_modified | lock_memory |
#+-------------+---------------+-------------+
#|    NULL |      NULL |    NULL |
#+-------------+---------------+-------------+
#1 row in set (0.00 sec)
#+-------------+---------------+-------------+
#| rows_locked | rows_modified | lock_memory |
#+-------------+---------------+-------------+
#|       0 |       0 |     376 |
#+-------------+---------------+-------------+
#Get InnoDB Compression Time
#mysql> SELECT SUM(compress_time) AS compress_time, SUM(uncompress_time) AS uncompress_time FROM information_schema.INNODB_CMP;
#+---------------+-----------------+
#| compress_time | uncompress_time |
#+---------------+-----------------+
#|       0 |         0 |
#+---------------+-----------------+
#1 row in set (0.00 sec)

#Get InnoDB Transaction states
#TRX_STATETransaction execution state. One of RUNNING, LOCK WAIT, ROLLING BACK or COMMITTING.
#mysql> SELECT LOWER(REPLACE(trx_state, " ", "_")) AS state, count(*) AS cnt from information_schema.INNODB_TRX GROUP BY state;
#+---------+-----+
#| state   | cnt |
#+---------+-----+
#| running |   1 |
#+---------+-----+
#1 row in set (0.00 sec)


innodb_metric=$1
case $innodb_metric in
   Innodb_rows_locked)
            value=$(echo "SELECT SUM(trx_rows_locked) AS rows_locked, SUM(trx_rows_modified) AS rows_modified, SUM(trx_lock_memory_bytes) AS lock_memory FROM information_schema.INNODB_TRX;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N| awk '{print $1}')
            if [ "$value" == "NULL" ];then
             echo 0
            else
             echo $value
            fi
          ;;
   Innodb_rows_modified)
            value=$(echo "SELECT SUM(trx_rows_locked) AS rows_locked, SUM(trx_rows_modified) AS rows_modified, SUM(trx_lock_memory_bytes) AS lock_memory FROM information_schema.INNODB_TRX;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N| awk '{print $2}')
            if [ "$value" == "NULL" ];then
             echo 0
            else
             echo $value
            fi
          ;;
   Innodb_trx_lock_memory)
            value=$(echo "SELECT SUM(trx_rows_locked) AS rows_locked, SUM(trx_rows_modified) AS rows_modified, SUM(trx_lock_memory_bytes) AS lock_memory FROM information_schema.INNODB_TRX;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N| awk '{print $3}')
            if [ "$value" == "NULL" ];then
             echo 0
            else
             echo $value
            fi
          ;;
    Innodb_compress_time)
            value=$(echo "SELECT SUM(compress_time) AS compress_time, SUM(uncompress_time) AS uncompress_time FROM information_schema.INNODB_CMP;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $1}')
            echo $value
            ;;
     
   Innodb_uncompress_time)
            value=$(echo "SELECT SUM(compress_time) AS compress_time, SUM(uncompress_time) AS uncompress_time FROM information_schema.INNODB_CMP;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $2}')
            echo $value
            ;;   
     Innodb_trx_running)
             value=$(echo 'SELECT LOWER(REPLACE(trx_state, " ", "_")) AS state, count(*) AS cnt from information_schema.INNODB_TRX GROUP BY state;'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep running|awk '{print $2}')
             if [ "$value" == "" ];then
              echo 0
             else
              echo $value
             fi
            ;;
     Innodb_trx_lock_wait)
             value=$(echo 'SELECT LOWER(REPLACE(trx_state, " ", "_")) AS state, count(*) AS cnt from information_schema.INNODB_TRX GROUP BY state;'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep lock_wait|awk '{print $2}')
             if [ "$value" == "" ];then
              echo 0
             else
              echo $value
             fi
            ;;
  Innodb_trx_rolling_back)
             value=$(echo 'SELECT LOWER(REPLACE(trx_state, " ", "_")) AS state, count(*) AS cnt from information_schema.INNODB_TRX GROUP BY state;'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep rolling_back|awk '{print $2}')
             if [ "$value" == "" ];then
              echo 0
             else
              echo $value
             fi
            ;;
  Innodb_trx_committing)
             value=$(echo 'SELECT LOWER(REPLACE(trx_state, " ", "_")) AS state, count(*) AS cnt from information_schema.INNODB_TRX GROUP BY state;'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep committing|awk '{print $2}')
             if [ "$value" == "" ];then
              echo 0
             else
              echo $value
             fi
            ;;
 Innodb_trx_history_list_length)
             echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "History list length"|awk '{print $4}'
            ;;
  Innodb_last_checkpoint_at)
             echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Last checkpoint at"|awk '{print $4}'
            ;;
   Innodb_log_sequence_number)
             echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Log sequence number"|awk '{print $4}'
            ;;
  Innodb_log_flushed_up_to)
             echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Log flushed up to"|awk '{print $5}'
            ;;
   Innodb_open_read_views_inside_innodb)
             echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "read views open inside InnoDB"|awk '{print $1}'
            ;;
    Innodb_queries_inside_innodb)
            echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "queries inside InnoDB"|awk '{print $1}'
            ;;
    Innodb_queries_in_queue)
            echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "queries in queue"|awk '{print $5}'
            ;;
    Innodb_hash_seaches)
            echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "hash searches"|awk '{print $1}'
            ;;
     Innodb_non_hash_searches)
            echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "non-hash searches/s"|awk '{print $4}'
            ;;
     Innodb_node_heap_buffers)
            echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "node heap"|awk '{print $8}'
             ;;
     Innodb_mutex_os_waits)
            echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Mutex spin waits"|awk '{print $9}'
             ;;
     Innodb_mutex_spin_rounds)
            echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Mutex spin waits"|awk '{print $6}'|tr -d ','
             ;;
     Innodb_mutex_spin_waits)
            echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Mutex spin waits"|awk '{print $4}'|tr -d ','
             ;; 
           *)
          echo "wrong parameter"
          ;;
esac

　　

　　

　　4.添加监控模板
　　附件中包含对MySQL实例，MySQL Master，MySQL Slave和MySQL InnoDB的监控

　　

　　

　　参考文章：

　　http://www.percona.com/doc/percona-monitoring-plugins/1.0/cacti/mysql-templates.html
　　http://hi.baidu.com/ytjwt/item/2bbd69a0869d1ef314329b6a
　　http://www.fromdual.com/mpm-installation-guide
　　http://www.fromdual.com/mysql-performance-monitor
　　http://dev.mysql.com/doc/refman/5.5/en/information-schema.html
　　

　　FROMDUAL官方模板和脚本下载地址如下，感兴趣的可以看看
　　http://support.fromdual.com/admin/public/download.php?operation=download&file_name=mysql_performance_monitor-0.9.3.tar.gz&id=1996
　　

　　

　　

　　

[MySQL] 使用zabbix全方位监控MySQL

相关帖子

电脑加内存条直接加上可以使用吗

无法使用此iphone解决方法

【云原生】如何快速部署Kubernetes

教你使用HTML/CSS和Three.js的喷火龙小游戏

浅谈Nodejs+nodemailer怎么发送邮件

php使用GD库创建图片缩略图的方法

使用原生querySelectorAll和forEach来实现点击切换

node Express学习之聊聊中间件

笔记本要一直充电好还是不充电好？

绝代码农 LV4