zabbix监控云端mysql等实例
传统的mysql 服务器 用zabbix很容易实现，单对于云端的mysql 实例 我想大家都是用的云自带的监控。我们前端时间aws 云端mysql 实现主从 进程的出现问题，然而aws 自带的监控没有。我们就想到 zabbix 实现对aws mysql 实例的监控。
1监控的服务远程去连接 awsmysql 取监控的数值就很容易实现了。
创建用户
GRANT USAGE,PROCESS,REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO zabbixtest@'10.%' IDENTIFIED BY 'zabbixtest';
2.监控客户端 zabbix配置文件的
serParameter=mysqlstatustest[*],/data/scripts/zabbix/mysql_check.sh "$1$2$3$4"
3监控脚本重写。这个就是比较麻烦的事情 在这里我贡献给大家，我用的是zabbix3.2 版本实现的。
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
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
#!/bin/bash
mysql(){
user=$2
password=$3
hostname=$4
case $1 in
Ping)
/usr/bin/mysqladmin -u${user}-p${password} -h${hostname}ping 2>/dev/null |grep alive|wc -l
;;
Threads)
/usr/bin/mysqladmin -u${user}-p${password} -h${hostname} status 2>/dev/null |cut -f3 -d":"|cut -f1 -d"Q"
;;
Questions)
/usr/bin/mysqladmin -u${user} -p${password} -h${hostname}status 2>/dev/null |cut -f4 -d":"|cut -f1 -d"S"
;;
Slowqueries)
/usr/bin/mysqladmin -u${user} -p${password} -h${hostname}status 2>/dev/null |cut -f5 -d":"|cut -f1 -d"O"
;;
Qps)
/usr/bin/mysqladmin -u${user} -p${password} -h${hostname}status 2>/dev/null |cut -f9 -d":"
;;
Slave_IO_State)
if [ "$(/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show slave status\G" 2>/dev/null | grep Slave_IO_Running|awk '{print $2}')" == "Yes" ];then echo 1; else echo 0;fi
;;
Slave_SQL_State)
if [ "$(/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show slave status\G" 2>/dev/null | grep Slave_SQL_Running|grep -v "waiting for"|awk '{print $2}')" == "Yes" ];then echo 1; else echo 0;fi
;;
SQL_Remaining_Delay)
if [ "$(/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show slave status\G" 2>/dev/null | grep SQL_Remaining_Delay|awk '{print $2}')" == "NULL" ];then echo 0; else echo "$(/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show slave status\G" 2>/dev/null | grep SQL_Remaining_Delay|awk '{print $2}')" ;fi
;;
Key_buffer_size)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show variables like 'key_buffer_size';" 2>/dev/null | grep -v Value |awk '{print $2/1024^2}'
;;
Key_reads)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'key_reads';" 2>/dev/null | grep -v Value |awk '{print $2}'
;;
Key_read_requests)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'key_read_requests';" 2>/dev/null | grep -v Value |awk '{print $2}'
;;
Key_cache_miss_rate)
echo $(/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'key_reads';" 2>/dev/null | grep -v Value|awk '{print $2}') $(/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'key_read_requests';" 2>/dev/null | grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",$1/$2*100)}'
;;
Key_blocks_used)
/usr/bin/mysql -u${user} -p${password} -h${hostname} -e "show status like 'key_blocks_used';"2>/dev/null |grep -v Value |awk '{print $2}'
;;
Key_blocks_unused)
/usr/bin/mysql -u${user} -p${password} -h${hostname} -e "show status like 'key_blocks_unused';" 2>/dev/null | grep -v Value |awk '{print $2}'
;;
Key_blocks_used_rate)
echo $(/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'key_blocks_used';" 2>/dev/null | grep -v
Value |awk '{print $2}') $(/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'key_blocks_unused';" 2>/dev/null | grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",$1/($1+$2)*100)}'
;;
Innodb_buffer_pool_size)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show variables like 'innodb_buffer_pool_size';" 2>/dev/null |grep -v Value |awk '{print $2/1024^2}'
;;
Innodb_log_file_size)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show variables like 'innodb_log_file_size';" 2>/dev/null |grep -v Value |awk '{print $2/1024^2}'
;;
Innodb_log_buffer_size)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show variables like 'innodb_log_buffer_size';" 2>/dev/null |grep -v Value |awk '{print $2/1024^2}'
;;
Table_open_cache)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show variables like 'table_open_cache';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Open_tables)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'open_tables';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Opened_tables)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'opened_tables';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Open_tables_rate)
echo $(/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'open_tables';" 2>/dev/null | grep -v Value |awk '{print $2}') $(/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'opened_tables';" 2>/dev/null | grep -v Value |awk '{print $2}')| awk'{printf("%1.4f\n",$1/($1+$2)*100)}'
;;
Table_open_cache_used_rate)
echo $(/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'open_tables';" 2>/dev/null | grep -v Value |awk '{print $2}') $(/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show variables like 'table_open_cache';" 2>/dev/null | grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",$1/($1+$2)*100)}'
;;
Thread_cache_size)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show variables like 'thread_cache_size';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Threads_cached)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Threads_cached';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Threads_connected)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Threads_connected';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Threads_created)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Threads_created';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Threads_running)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Threads_running';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Max_used_connections)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Max_used_connections';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Max_connections)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show variables like 'Max_connections';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Max_connections_used_rate)
echo $(/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Max_used_connections';" 2>/dev/null | grep -v Value |awk '{print $2}') $(/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show variables like 'max_connections';" 2>/dev/null | grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",$1/$2*100)}'
;;
Created_tmp_disk_tables)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'created_tmp_disk_tables';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Created_tmp_tables)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'created_tmp_tables';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Table_locks_immediate)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'table_locks_immediate';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Table_locks_waited)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'table_locks_waited';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Open_files)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'open_files';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Open_files_limit)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show variables like 'open_files_limit';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Open_files_rate)
echo $(/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'open_files';" 2>/dev/null | grep -v Value |awk '{print $2}') $(/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show variables like 'open_files_limit';" 2>/dev/null | grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",$1/$2*100)}'
;;
Com_select)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'com_select';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Com_insert)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'com_insert';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Com_insert_select)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'com_insert_select';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Com_update)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'com_update';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Com_replace)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'com_replace';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Com_replace_select)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'com_replace_select';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Table_scan_rate)
echo $(/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Handler_read_rnd_next';" 2>/dev/null | grep -v Value |awk '{print $2}') $(/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'com_select';" 2>/dev/null | grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",$1/$2*100)}'
;;
Handler_read_first)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Handler_read_first';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Handler_read_key)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Handler_read_key';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Handler_read_next)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Handler_read_next';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Handler_read_prev)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Handler_read_prev';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Handler_read_rnd)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Handler_read_rnd';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Handler_read_rnd_next)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Handler_read_rnd_next';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Sort_merge_passes)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Sort_merge_passes';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Sort_range)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Sort_range';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Sort_rows)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Sort_rows';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Sort_scan)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Sort_scan';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Qcache_free_blocks)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Qcache_free_blocks';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Qcache_free_memory)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Qcache_free_memory';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Qcache_free_blocks)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Qcache_free_blocks';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Qcache_hits)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Qcache_hits';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Qcache_inserts)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Qcache_inserts';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Qcache_lowmem_prunes)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Qcache_lowmem_prunes';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Qcache_not_cached)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Qcache_not_cached';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Qcache_queries_in_cache)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Qcache_queries_in_cache';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Qcache_total_blocks)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show status like 'Qcache_total_blocks';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Query_cache_limit)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show variables like 'query_cache_limit';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Query_cache_min_res_unit)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show variables like 'query_cache_min_res_unit';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
Query_cache_size)
/usr/bin/mysql -u${user} -p${password} -h${hostname}-e "show variables like 'query_cache_size,';" 2>/dev/null |grep -v Value |awk '{print $2}'
;;
*)
echo $"Usage: ITMEname dbuserdbpassdbhost";
esac
}
mysql$1 $2 $3 $4
mysql 需要连接的数据的 用户 和密码还有 ip 就需要在模版里面写宏变量实现。
4模版的添加 rate 有几个key 我没写。
模版在附件 zabbix 3.0才能使用此模版
如果需要redis和memcache 模版 和远程监控实现方法 请联系我。
5注意事项
由于 所以的 mysql 实例的监控都是从这一台zabbix－agentd 取的数值
所以这台监控的机器 的压力很大
StartPollersUnreachable=10 server 需要优化这个参数
客户端的配置文件 需要优化超时时间和buffer 大小
6tip
脚本优化方向，可以写个crontab 把状态数据 存到临时文件里面 ，然后脚本 取这个文件里面的数值，这样连接数能降低很多，因为时间仓促 所以脚本没优化。
我们的 这一台监控 mysql redis memcached 实例 都是用的这个方式，而且都是这个机器取的 50个实例的数值（1核2gb的监控 客户端）同时还是一个zabbix－proxy。
页:
[1]