zabbix监控云端mysql等实例

2321212dd

传统的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   dbuser  dbpass  dbhost";     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。