美文网首页
zabbix之监控mysql云服务

zabbix之监控mysql云服务

作者: leeypp | 来源:发表于2018-11-29 17:26 被阅读0次

【参考文档】：http://blog.51cto.com/hzcsky/1876697
技术难点：mysql服务为云服务器，无法安装zabbix agent，只能通过代理的方式，连接到mysql服务，获取监控数据。
实现过程：

1.找一台可以连接mysql服务的，有zabbix agent的服务器。（可以通过提供的用户名密码连接到mysql服务）。

2.修改zabbix agent的配置文件如下：

[root@s-zabbix /etc/zabbix/zabbix_agentd.d]# cat userparameter_mysql.conf  |grep -Ev '#|$^'
UserParameter=mysql.status[*],/etc/zabbix/scripts/mysql_check.sh $1  $2  $3  $4 $5

我的配置只有这一行，其中5个参数均为zabbix前端需要配置后传入的项。

3.编辑采集脚本

[root@s-zabbix /etc/zabbix/zabbix_agentd.d]# cat /etc/zabbix/scripts/mysql_check.sh
#!/bin/bash
mysql(){
  user=$2
  password=$3
  hostname=$4
  port=$5
  case $1 in
       Ping)
       /usr/bin/mysqladmin -u${user}  -p${password} -h${hostname} -P${port}  ping 2>/dev/null |grep alive|wc -l
       ;;
       Threads)
       /usr/bin/mysqladmin   -u${user}  -p${password} -h${hostname} -P${port}   status 2>/dev/null |cut -f3 -d":"|cut -f1 -d"Q"
       ;;
       Questions)
       /usr/bin/mysqladmin -u${user} -p${password} -h${hostname} -P${port}  status 2>/dev/null |cut -f4 -d":"|cut -f1 -d"S"
       ;;
       Slowqueries)
       /usr/bin/mysqladmin -u${user} -p${password} -h${hostname} -P${port}  status 2>/dev/null |cut -f5 -d":"|cut -f1 -d"O"
       ;;
       Qps)
       /usr/bin/mysqladmin -u${user} -p${password} -h${hostname} -P${port}  status 2>/dev/null |cut -f9 -d":"
       ;;
       Slave_IO_State)
       if [ "$(/usr/bin/mysql -u${user} -p${password} -h${hostname} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -e "show status like 'key_reads';" 2>/dev/null | grep -v Value|awk '{print $2}') $(/usr/bin/mysql -u${user} -p${password} -h${hostname} -P${port}  -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} -P${port}   -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} -P${port}   -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -e "show status like 'open_tables';" 2>/dev/null | grep -v Value |awk '{print $2}') $(/usr/bin/mysql -u${user} -p${password} -h${hostname} -P${port}  -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} -P${port}  -e "show status like 'open_tables';" 2>/dev/null | grep -v Value |awk '{print $2}') $(/usr/bin/mysql -u${user} -p${password} -h${hostname} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -e "show status like 'open_files';" 2>/dev/null | grep -v Value |awk '{print $2}') $(/usr/bin/mysql -u${user} -p${password} -h${hostname} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -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} -P${port}  -e "show variables like 'query_cache_size,';" 2>/dev/null |grep -v Value |awk '{print $2}'
       ;;
       *)
        echo $"Usage: ITMEname   dbuser  dbpass  dbhost dbport";
    esac
}
mysql  $1 $2 $3 $4 $5

4.配置template

（下面是我的template.xml文件，可以自行导入到zabbix模板中）
https://my.oschina.net/leeypp1/blog/2963164
放到其他博客了，太长简书不支持 :(
导入成功后可以发现模板Template Linux mysql status

5.前端页面配置

（1）添加主机


image.png

（2）绑定模板


image.png
（3）编辑宏，写入脚本所需要的变量
image.png

6数据检验查看

.添加完成后在最新数据中查看方才添加主机的监控项，是否有数据上报

image.png

7.实现原理

以键值为mysql.status[Ping,{$DBUSER},{$DBPASS},{$DBHOST},{$DBPORT}]的监控项为例
从编辑采集脚本的时候可以发现，mysql_check.sh脚本需要5个参数来进行数据采集

[root@s-zabbix /etc/zabbix/scripts]# ./mysql_check.sh 
Usage: ITMEname   dbuser  dbpass  dbhost dbport

而在此监控项中，5个参数分别为Ping,{$DBUSER},{$DBPASS},{$DBHOST},{$DBPORT},其中ping为监控项名称，其余四个变量为在宏中定义的用于连接mysql服务的参数

8.遇到的问题

 21571:20181129:151330.452 error reason for "mysql-prod-statics:mysql.status[Threads,{$DBUSER},{$DBPASS},{$DBHOST},{$DBPORT}]" changed: Special characters "\, ', ", `, *, ?, [, ], {, }, ~, $, !, &, ;, (, ), <, >, |, #, @, 0x0a" are not allowed in the parameters.

传入参数中有特殊字符。解决方法：更新zabbix_agentd.conf，设置UnsafeUserParameters=1

leeypp@foxmail.com (如果你有疑问，请联系我)

相关文章

网友评论

      本文标题：zabbix之监控mysql云服务

      本文链接：https://www.haomeiwen.com/subject/kvpfcqtx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|zabbix之监控mysql云服务|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！