使用zabbix全方位监控MySQL

regw333

对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 statusG    查看InnoDB存储引擎状态
show engine myisam statusG    查看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 文件

1 2 3 4 5 6

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157

#!/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_STATE  Transaction 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 statusG'|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 statusG'|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 statusG'|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 statusG'|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 statusG'|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 statusG'|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 statusG'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "queries in queue"|awk '{print $5}'                         ;;         Innodb_hash_seaches)                         echo 'show engine innodb statusG'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "hash searches"|awk '{print $1}'                         ;;        Innodb_non_hash_searches)                         echo 'show engine innodb statusG'|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 statusG'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "node heap"|awk '{print $8}'                        ;;        Innodb_mutex_os_waits)                         echo 'show engine innodb statusG'|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 statusG'|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 statusG'|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

ybx13218464908

附件在哪里