论坛 › Zabbix › 使用zabbix全方位监控MySQL

regw333 发表于 2014-12-29 08:43:28

使用zabbix全方位监控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 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

user=zabbixagent
password=zabbixagent

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 -calive
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{printsum}'

#### 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_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 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 发表于 2015-1-20 22:48:36

附件在哪里

查看完整版本: 使用zabbix全方位监控MySQL