使用zabbix监控mysql的三种方式

来源:转载

使用zabbix监控mysql的三种方式1.只是安装agent2.启用模板监控3.启用自定义脚本的模板监控
zabbix中默认有mysql的监控模板。默认已经在zabbix2.2及以上的版本中。模板名称:Template App MySQL。如果没有则要去zabbix官方下载 url:https://zabbix.org/mw/images/d/d4/Template_App_MySQL-2.2.0.xml,然后在Zabbix服务器的前端Web界面的组态->模板页面中选择汇入,在新的页面中导入下载的模板文件。
#模拟环境mysql1172.16.160.161 #zabbix servermysql2172.16.160.162 #zabbix agent
#mysql配置-在zabbix agent上操作##添加帐户登录信息等,执行mysql mysqladmin脚本命令时不需要帐户密码,也不再提示警告$ cat /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockvalidate_password = offinnodb_file_per_table = 1character_set_server=utf8init_connect='SET NAMES utf8'symbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid
[mysql] #添加以下内容user=rootpassword=Maks.123[client]user=rootpassword=Maks.123[mysqladmin]user=rootpassword=Maks.123
##添加帐户密码mysql> GRANT USAGE ON *.* TO 'mysqlcheck'@'localhost' IDENTIFIED BY 'mysqlcheck';mysql> FLUSH PRIVILEGES;
#zabbix配置-在zabbix agent上操作##安装zabbix_agent并修改配置文件$ rpm -Uv http://repo.zabbix.com/zabbix/3.2/rhel/7/x86_64/zabbix-agent-3.2.6-1.el7.x86_64.rpm$ yum install -y zabbix-agent
$ cat /etc/zabbix/zabbix_agentd.conf |grep -v ^# |grep -v ^$PidFile=/var/run/zabbix/zabbix_agentd.pidLogFile=/var/log/zabbix/zabbix_agentd.logLogFileSize=0Server=172.16.160.161#指向zabbix服务器ListenPort=10050ListenIP=0.0.0.0ServerActive=172.16.160.161 #指向zabbix服务器Hostname=mysql2 #zabbix服务器上配置主机的时候要用的名称Include=/etc/zabbix/zabbix_agentd.d/*.conf #这一行是指向模板文件的$
##添加用来关联mysql的配置文件.my.cnf,mysql.sock文件通过find / -name mysql.sock找出路径$ cat /etc/zabbix/.my.cnf [mysql]host = localhostuser = mysqlcheckpassword = mysqlchecksocket= /var/lib/mysql/mysql.sock[mysqladmin]host = localhostuser = mysqlcheckpassword = mysqlchechksocket= /var/lib/mysql/mysql.sock
##修改zabbix模板配置文件中的mysql路径这个文件用于制定Zabbix Agent如何获取MySQL数据我们需要将所有的/var/lib/zabbix修改为当前.my.cnf文件的路径/etc/zabbix。$ sed -i '[email protected]/var/lib/[email protected]/etc/[email protected]' /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf
$ cat /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf |grep -v ^#|grep -v ^$UserParameter=mysql.status[*],echo "show global status where Variable_name='$1';" | HOME=/etc/zabbix mysql -N | awk '{print $$2}'UserParameter=mysql.size[*],bash -c 'echo "select sum($(case "$3" in both|"") echo "data_length+index_length";; data|index) echo "$3_length";; free) echo "data_free";; esac)) from information_schema.tables$([[ "$1" = "all" || ! "$1" ]] || echo " where table_schema=/"$1/"")$([[ "$2" = "all" || ! "$2" ]] || echo "and table_name=/"$2/"");" | HOME=/etc/zabbix mysql -N'UserParameter=mysql.ping,HOME=/etc/zabbix mysqladmin ping | grep -c aliveUserParameter=mysql.version,mysql -V##设置完 成之后保存并重启Zabbix Agent$ systemctl restart zabbix-agent.service
##测试不用帐户密码就可以执行mysqladmin命令$ mysqladmin ping | grep -c alive1
#zabbix页面配置-在zabbix server上操作##创建主机Configuration—Hosts—创建主机主机名称,群组选择,agent的IP地址和端口,然后点更新##使用模板Configuration—Hosts—主机名称—Tempaltes,搜索MySQL,添加弹出的模板Template App MySQL,然后点更新注意:选完模板后,要点下添加按钮才行。##查看监控效果Monitoring—Graphs,选择组,然后选择host及展示的图形这时候图形里会多出mysql bandwidth和mysql operations,而且有输出信息
#总结说明说明一下,安装zabbix_agent后,已经可以监控mysql服务器的性能参数,不过如果还需要进一步监控做些简单的监控,这时候就要用zabbix自带的模板;然而如果只需要对mysql数据库做简单的监控,zabbix自带的模板完全能够满足要求;如果有更高的需求那需要自己写脚本,或者使用fromdual插件等。参考:http://www.fromdual.com/mpm-installation-guide
zabbix在模板中预定义了一些key,但通常情况,并不能满足我们的需求。幸运的是zabbix提供了自定义key的方法,因此我们可以灵活的监控各种我们想要监控的数据。##启用$ vi /etc/zabbix/zabbix_agentd.conf UnsafeUserParameters=1
$ cat /etc/zabbix/zabbix_agentd.conf |grep -v ^# |grep -v ^$PidFile=/var/run/zabbix/zabbix_agentd.pidLogFile=/var/log/zabbix/zabbix_agentd.logLogFileSize=0Server=172.16.160.161#指向zabbix服务器ListenPort=10050ListenIP=0.0.0.0ServerActive=172.16.160.161 #指向zabbix服务器Hostname=mysql2 #zabbix服务器上配置主机的时候要用的名称Include=/etc/zabbix/zabbix_agentd.d/*.conf #这一行是指向模板文件的UnsafeUserParameters=1
$ vi /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf#UserParameter=mysql.status[*],echo "show global status where Variable_name='$1';" | HOME=/etc/zabbix mysql -N | awk '{print $$2}'UserParameter=mysql.status[*],/script/getmysqlinfo.sh $1 $2
$ cat /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf |grep -v ^#|grep -v ^$UserParameter=mysql.status[*],/script/getmysqlinfo.sh $1 $2 UserParameter=mysql.size[*],bash -c 'echo "select sum($(case "$3" in both|"") echo "data_length+index_length";; data|index) echo "$3_length";; free) echo "data_free";; esac)) from information_schema.tables$([[ "$1" = "all" || ! "$1" ]] || echo " where table_schema=/"$1/"")$([[ "$2" = "all" || ! "$2" ]] || echo "and table_name=/"$2/"");" | HOME=/etc/zabbix mysql -N'UserParameter=mysql.ping,HOME=/etc/zabbix mysqladmin ping | grep -c aliveUserParameter=mysql.version,mysql -V$
脚本内容:可从https://github.com/flashyhl/Zabbix-Monitor/blob/master/zabbix_mysql/script/getmysqlinfo.sh下载并修改修改变量定义和删除-u$User -p$Password,不在命令中使用帐户密码,因为我们前面已经在my.cnf文件中配置了帐户密码
```bash$ cat /script/getmysqlinfo.sh#!/bin/bash
#Name: MySQLMontior.sh#From: flashyhl <2015/08/06>#Action: Zabbix monitoring mysql plug-in
MySQlBin=/usr/bin/mysqlMySQLAdminBin=mysqladminHost=172.16.160.162User=rootPassword=Maks.123
if [[ $# == 1 ]];then case $1 in Ping)result=`$MySQLAdminBin -h$Host ping|grep alive|wc -l`echo $result;; Threads)result=`$MySQLAdminBin -h$Host status|cut -f3 -d":"|cut -f1 -d"Q"`echo $result;; Questions)result=`$MySQLAdminBin -h$Host status|cut -f4 -d":"|cut -f1 -d"S"`echo $result;; Slowqueries)result=`$MySQLAdminBin -h$Host status|cut -f5 -d":"|cut -f1 -d"O"`echo $result;; Qps)result=`$MySQLAdminBin -h$Host status|cut -f9 -d":"`echo $result;; Slave_IO_State)result=`if [ "$($MySQlBin -h$Host -e "show slave status/G"| grep Slave_IO_Running|awk '{print $2}')" == "Yes" ];then echo 1; else echo 0;fi`echo $result;; Slave_SQL_State)result=`if [ "$($MySQlBin -h$Host -e "show slave status/G"| grep Slave_SQL_Running|awk '{print $2}')" == "Yes" ];then echo 1; else echo 0;fi`echo $result;; Key_buffer_size)result=`$MySQlBin -h$Host -e "show variables like 'key_buffer_size';"| grep -v Value |awk '{print $2/1024^2}'`echo $result;; Key_reads)result=`$MySQlBin -h$Host -e "show status like 'key_reads';"| grep -v Value |awk '{print $2}'`echo $result;; Key_read_requests)result=`$MySQlBin -h$Host -e "show status like 'key_read_requests';"| grep -v Value |awk '{print $2}'`echo $result;; Key_cache_miss_rate)result=`echo $($MySQlBin -h$Host -e "show status like 'key_reads';"| grep -v Value |awk '{print $2}') $($MySQlBin -h$Host -e "show status like 'key_read_requests';"| grep -v Value |awk '{print $2}')| awk '{if($2==0)printf("%1.4f/n",0);else printf("%1.4f/n",$1/$2*100);}'`echo $result;; Key_blocks_used)result=`$MySQlBin -h$Host -e "show status like 'key_blocks_used';"| grep -v Value |awk '{print $2}'`echo $result;; Key_blocks_unused)result=`$MySQlBin -h$Host -e "show status like 'key_blocks_unused';"| grep -v Value |awk '{print $2}'`echo $result;; Key_blocks_used_rate)result=`echo $($MySQlBin -h$Host -e "show status like 'key_blocks_used';"| grep -v Value |awk '{print $2}') $($MySQlBin -h$Host -e "show status like 'key_blocks_unused';"| grep -v Value |awk '{print $2}')| awk '{if(($1==0) && ($2==0))printf("%1.4f/n",0);else printf("%1.4f/n",$1/($1+$2)*100);}'`echo $result;; Innodb_buffer_pool_size)result=`$MySQlBin -h$Host -e "show variables like 'innodb_buffer_pool_size';"| grep -v Value |awk '{print $2/1024^2}'`echo $result;; Innodb_log_file_size)result=`$MySQlBin -h$Host -e "show variables like 'innodb_log_file_size';"| grep -v Value |awk '{print $2/1024^2}'`echo $result;; Innodb_log_buffer_size)result=`$MySQlBin -h$Host -e "show variables like 'innodb_log_buffer_size';"| grep -v Value |awk '{print $2/1024^2}'`echo $result;; Table_open_cache)result=`$MySQlBin -h$Host -e "show variables like 'table_open_cache';"| grep -v Value |awk '{print $2}'`echo $result;; Open_tables)result=`$MySQlBin -h$Host -e "show status like 'open_tables';"| grep -v Value |awk '{print $2}'`echo $result;; Opened_tables)result=`$MySQlBin -h$Host -e "show status like 'opened_tables';"| grep -v Value |awk '{print $2}'`echo $result;; Open_tables_rate)result=`echo $($MySQlBin -h$Host -e "show status like 'open_tables';"| grep -v Value |awk '{print $2}') $($MySQlBin -h$Host -e "show status like 'opened_tables';"| grep -v Value |awk '{print $2}')| awk '{if(($1==0) && ($2==0))printf("%1.4f/n",0);else printf("%1.4f/n",$1/($1+$2)*100);}'`echo $result;; Table_open_cache_used_rate)result=`echo $($MySQlBin -h$Host -e "show status like 'open_tables';"| grep -v Value |awk '{print $2}') $($MySQlBin -h$Host -e "show variables like 'table_open_cache';"| grep -v Value |awk '{print $2}')| awk '{if(($1==0) && ($2==0))printf("%1.4f/n",0);else printf("%1.4f/n",$1/($1+$2)*100);}'`echo $result;; Thread_cache_size)result=`$MySQlBin -h$Host -e "show variables like 'thread_cache_size';"| grep -v Value |awk '{print $2}'`echo $result;; Threads_cached)result=`$MySQlBin -h$Host -e "show status like 'Threads_cached';"| grep -v Value |awk '{print $2}'`echo $result;; Threads_connected)result=`$MySQlBin -h$Host -e "show status like 'Threads_connected';"| grep -v Value |awk '{print $2}'`echo $result;; Threads_created)result=`$MySQlBin -h$Host -e "show status like 'Threads_created';"| grep -v Value |awk '{print $2}'`echo $result;; Threads_running)result=`$MySQlBin -h$Host -e "show status like 'Threads_running';"| grep -v Value |awk '{print $2}'`echo $result;; Qcache_free_blocks)result=`$MySQlBin -h$Host -e "show status like 'Qcache_free_blocks';"| grep -v Value |awk '{print $2}'`echo $result;; Qcache_free_memory)result=`$MySQlBin -h$Host -e "show status like 'Qcache_free_memory';"| grep -v Value |awk '{print $2}'`echo $result;; Qcache_hits)result=`$MySQlBin -h$Host -e "show status like 'Qcache_hits';"| grep -v Value |awk '{print $2}'`echo $result;; Qcache_inserts)result=`$MySQlBin -h$Host -e "show status like 'Qcache_inserts';"| grep -v Value |awk '{print $2}'`echo $result;; Qcache_lowmem_prunes)result=`$MySQlBin -h$Host -e "show status like 'Qcache_lowmem_prunes';"| grep -v Value |awk '{print $2}'`echo $result;; Qcache_not_cached)result=`$MySQlBin -h$Host -e "show status like 'Qcache_not_cached';"| grep -v Value |awk '{print $2}'`echo $result;; Qcache_queries_in_cache)result=`$MySQlBin -h$Host -e "show status like 'Qcache_queries_in_cache';"| grep -v Value |awk '{print $2}'`echo $result;; Qcache_total_blocks)result=`$MySQlBin -h$Host -e "show status like 'Qcache_total_blocks';"| grep -v Value |awk '{print $2}'`echo $result;; Qcache_fragment_rate)result=`echo $($MySQlBin -h$Host -e "show status like 'Qcache_free_blocks';"| grep -v Value |awk '{print $2}') $($MySQlBin -h$Host -e "show status like 'Qcache_total_blocks';"| grep -v Value |awk '{print $2}')| awk '{if($2==0)printf("%1.4f/n",0);else printf("%1.4f/n",$1/$2*100);}'`echo $result;; Qcache_used_rate)result=`echo $($MySQlBin -h$Host -e "show variables like 'query_cache_size';"| grep -v Value |awk '{print $2}') $($MySQlBin -h$Host -e "show status like 'Qcache_free_memory';"| grep -v Value |awk '{print $2}')| awk '{if($1==0)printf("%1.4f/n",0);else printf("%1.4f/n",($1-$2)/$1*100);}'`echo $result;; Qcache_hits_rate)result=`echo $($MySQlBin -h$Host -e "show status like 'Qcache_hits';"| grep -v Value |awk '{print $2}') $($MySQlBin -h$Host -e "show status like 'Qcache_inserts';"| grep -v Value |awk '{print $2}')| awk '{if($1==0)printf("%1.4f/n",0);else printf("%1.4f/n",($1-$2)/$1*100);}'`echo $result;; Query_cache_limit)result=`$MySQlBin -h$Host -e "show variables like 'query_cache_limit';"| grep -v Value |awk '{print $2}'`echo $result;; Query_cache_min_res_unit)result=`$MySQlBin -h$Host -e "show variables like 'query_cache_min_res_unit';"| grep -v Value |awk '{print $2}'`echo $result;; Query_cache_size)result=`$MySQlBin -h$Host -e "show variables like 'query_cache_size';"| grep -v Value |awk '{print $2}'`echo $result;; Sort_merge_passes)result=`$MySQlBin -h$Host -e "show status like 'Sort_merge_passes';"| grep -v Value |awk '{print $2}'`echo $result;; Sort_range)result=`$MySQlBin -h$Host -e "show status like 'Sort_range';"| grep -v Value |awk '{print $2}'`echo $result;; Sort_rows)result=`$MySQlBin -h$Host -e "show status like 'Sort_rows';"| grep -v Value |awk '{print $2}'`echo $result;; Sort_scan)result=`$MySQlBin -h$Host -e "show status like 'Sort_scan';"| grep -v Value |awk '{print $2}'`echo $result;; Handler_read_first)result=`$MySQlBin -h$Host -e "show status like 'Handler_read_first';"| grep -v Value |awk '{print $2}'`echo $result;; Handler_read_key)result=`$MySQlBin -h$Host -e "show status like 'Handler_read_key';"| grep -v Value |awk '{print $2}'`echo $result;; Handler_read_next)result=`$MySQlBin -h$Host -e "show status like 'Handler_read_next';"| grep -v Value |awk '{print $2}'`echo $result;; Handler_read_prev)result=`$MySQlBin -h$Host -e "show status like 'Handler_read_prev';"| grep -v Value |awk '{print $2}'`echo $result;; Handler_read_rnd)result=`$MySQlBin -h$Host -e "show status like 'Handler_read_rnd';"| grep -v Value |awk '{print $2}'`echo $result;; Handler_read_rnd_next)result=`$MySQlBin -h$Host -e "show status like 'Handler_read_rnd_next';"| grep -v Value |awk '{print $2}'`echo $result;; Com_select)result=`$MySQlBin -h$Host -e "show status like 'com_select';"| grep -v Value |awk '{print $2}'`echo $result;; Com_insert)result=`$MySQlBin -h$Host -e "show status like 'com_insert';"| grep -v Value |awk '{print $2}'`echo $result;; Com_insert_select)result=`$MySQlBin -h$Host -e "show status like 'com_insert_select';"| grep -v Value |awk '{print $2}'`echo $result;; Com_update)result=`$MySQlBin -h$Host -e "show status like 'com_update';"| grep -v Value |awk '{print $2}'`echo $result;; Com_replace)result=`$MySQlBin -h$Host -e "show status like 'com_replace';"| grep -v Value |awk '{print $2}'`echo $result;; Com_replace_select)result=`$MySQlBin -h$Host -e "show status like 'com_replace_select';"| grep -v Value |awk '{print $2}'`echo $result;; Table_scan_rate)result=`echo $($MySQlBin -h$Host -e "show status like 'Handler_read_rnd_next';"| grep -v Value |awk '{print $2}') $($MySQlBin -h$Host -e "show status like 'com_select';"| grep -v Value |awk '{print $2}')| awk '{if($2==0)printf("%1.4f/n",0);else printf("%1.4f/n",$1/$2*100);}'`echo $result;; Open_files)result=`$MySQlBin -h$Host -e "show status like 'open_files';"| grep -v Value |awk '{print $2}'`echo $result;; Open_files_limit)result=`$MySQlBin -h$Host -e "show variables like 'open_files_limit';"| grep -v Value |awk '{print $2}'`echo $result;; Open_files_rate)result=`echo $($MySQlBin -h$Host -e "show status like 'open_files';"| grep -v Value |awk '{print $2}') $($MySQlBin -h$Host -e "show variables like 'open_files_limit';"| grep -v Value |awk '{print $2}')| awk '{if($2==0)printf("%1.4f/n",0);else printf("%1.4f/n",$1/$2*100);}'`echo $result;; Created_tmp_disk_tables)result=`$MySQlBin -h$Host -e "show status like 'created_tmp_disk_tables';"| grep -v Value |awk '{print $2}'`echo $result;; Created_tmp_tables)result=`$MySQlBin -h$Host -e "show status like 'created_tmp_tables';"| grep -v Value |awk '{print $2}'`echo $result;; Created_tmp_disk_tables_rate)result=`echo $($MySQlBin -h$Host -e "show status like 'created_tmp_disk_tables';"| grep -v Value |awk '{print $2}') $($MySQlBin -h$Host -e "show status like 'created_tmp_tables';"| grep -v Value |awk '{print $2}')| awk '{if($2==0)printf("%1.4f/n",0);else printf("%1.4f/n",$1/$2*100);}'`echo $result;; Max_connections)result=`$MySQlBin -h$Host -e "show variables like 'max_connections';"| grep -v Value |awk '{print $2}'`echo $result;; Max_used_connections)result=`$MySQlBin -h$Host -e "show status like 'Max_used_connections';"| grep -v Value |awk '{print $2}'`echo $result;; Processlist)result=`$MySQlBin -h$Host -e "show processlist" | grep -v "Id" | wc -l`echo $result;; Max_connections_used_rate)result=`echo $($MySQlBin -h$Host -e "show status like 'Max_used_connections';"| grep -v Value |awk '{print $2}') $($MySQlBin -h$Host -e "show variables like 'max_connections';"| grep -v Value |awk '{print $2}')| awk '{if($2==0)printf("%1.4f/n",0);else printf("%1.4f/n",$1/$2*100);}'`echo $result;;Connection_occupancy_rate)result=`echo $($MySQlBin -h$Host -e "show status like 'Threads_connected';"| grep -v Value |awk '{print $2}') $($MySQlBin -h$Host -e "show variables like 'max_connections';"| grep -v Value |awk '{print $2}')| awk '{if($2==0)printf("%1.4f/n",0);else printf("%5.4f/n",$1/$2*100);}'`echo $result;;
Table_locks_immediate)result=`$MySQlBin -h$Host -e "show status like 'Table_locks_immediate';"| grep -v Value |awk '{print $2}'`echo $result;; Table_locks_waited)result=`$MySQlBin -h$Host -e "show status like 'table_locks_waited';"| grep -v Value |awk '{print $2}'`echo $result;; Engine_select)result=`echo $($MySQlBin -h$Host -e "show status like 'Table_locks_immediate';"| grep -v Value |awk '{print $2}') $($MySQlBin -h$Host -e "show status like 'table_locks_waited';"| grep -v Value | awk '{print $2}') | awk '{if($2==0)printf("%1.4f/n",0);else printf("%5.4f/n",$1/$2*100);}'`echo $result;;*)echo -e "/033[33mUsage: ./getmysqlinfo {Ping|Threads|Questions|Slowqueries|Qps|Slave_IO_State|Slave_SQL_State|Key_buffer_size|Key_reads|Key_read_requests|Key_cache_miss_rate|Key_blocks_used|Key_blocks_unused|Key_blocks_used_rate|Innodb_buffer_pool_size|Innodb_log_file_size|Innodb_log_buffer_size|Table_open_cache|Open_tables|Opened_tables|Open_tables_rate|Table_open_cache_used_rate|Thread_cache_size|Threads_cached|Threads_connected|Threads_created|Threads_running|Qcache_free_blocks|Qcache_free_memory|Qcache_hits|Qcache_inserts|Qcache_lowmem_prunes|Qcache_not_cached|Qcache_queries_in_cache|Qcache_total_blocks|Qcache_fragment_rate|Qcache_used_rate|Qcache_hits_rate|Query_cache_limit|Query_cache_min_res_unit|Query_cache_size|Sort_merge_passes|Sort_range|Sort_rows|Sort_scan|Handler_read_first|Handler_read_key|Handler_read_next|Handler_read_prev|Handler_read_rnd|Handler_read_rnd_next|Com_select|Com_insert|Com_insert_select|Com_update|Com_replace|Com_replace_select|Table_scan_rate|Open_files|Open_files_limit|Open_files_rate|Created_tmp_disk_tables|Created_tmp_tables|Created_tmp_disk_tables_rate|Max_connections|Max_used_connections|Processlist|Max_connections_used_rate|Table_locks_immediate|Table_locks_waited|Engine_select|Connection_occupancy_rate} /033[0m"
;; esacfi```

分享给朋友:
您可能感兴趣的文章:
随机阅读: