zabbix之監(jiān)控mysql云服務(wù)

【參考文檔】：http://blog.51cto.com/hzcsky/1876697
技術(shù)難點：mysql服務(wù)為云服務(wù)器，無法安裝zabbix agent，只能通過代理的方式，連接到mysql服務(wù)，獲取監(jiān)控數(shù)據(jù)。
實現(xiàn)過程：

1.找一臺可以連接mysql服務(wù)的，有zabbix agent的服務(wù)器。（可以通過提供的用戶名密碼連接到mysql服務(wù)）。

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個參數(shù)均為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文件，可以自行導(dǎo)入到zabbix模板中）
https://my.oschina.net/leeypp1/blog/2963164
放到其他博客了，太長簡書不支持 :(
導(dǎo)入成功后可以發(fā)現(xiàn)模板Template Linux mysql status

5.前端頁面配置

（1）添加主機


image.png

（2）綁定模板


image.png

（3）編輯宏，寫入腳本所需要的變量
image.png

6數(shù)據(jù)檢驗查看

.添加完成后在最新數(shù)據(jù)中查看方才添加主機的監(jiān)控項，是否有數(shù)據(jù)上報

image.png

7.實現(xiàn)原理

以鍵值為mysql.status[Ping,{$DBUSER},{$DBPASS},{$DBHOST},{$DBPORT}]的監(jiān)控項為例
從編輯采集腳本的時候可以發(fā)現(xiàn)，mysql_check.sh腳本需要5個參數(shù)來進行數(shù)據(jù)采集

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

而在此監(jiān)控項中，5個參數(shù)分別為Ping,{$DBUSER},{$DBPASS},{$DBHOST},{$DBPORT},其中ping為監(jiān)控項名稱，其余四個變量為在宏中定義的用于連接mysql服務(wù)的參數(shù)

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.

傳入?yún)?shù)中有特殊字符。解決方法：更新zabbix_agentd.conf，設(shè)置UnsafeUserParameters=1

leeypp@foxmail.com (如果你有疑問，請聯(lián)系我)

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • Zabbix簡介 Zabbix官方網(wǎng)站Zabbix中文文檔 本文系統(tǒng)環(huán)境是CentOS7x86_64, Zabbi...
    Zhang21閱讀 8,336評論 0贊 37
  • 一、snmp（簡單網(wǎng)絡(luò)管理協(xié)議simple network management protocol） SNMP協(xié)議...
    Net夜風(fēng)閱讀 1,984評論 0贊 1
  • 一、架構(gòu)設(shè)計及環(huán)境規(guī)劃： 架構(gòu)設(shè)計圖： 架構(gòu)設(shè)計說明： 1. 基礎(chǔ)架構(gòu)為LAMP環(huán)境，采用keepalived實現(xiàn)...
    Bogon閱讀 10,838評論 1贊 10
  • “以后結(jié)婚的肯定不是你最愛的”這是他分手后對我最銘心的一句話，其實我知道他的意思是我不可能和他結(jié)婚。 時間說長不長...
    一籠花卷閱讀 344評論 1贊 1
  • 這樣的年紀(jì)，是個尷尬的年紀(jì)，想必所有人都會忌諱說“老”，心里卻又暗自感慨… 也難怪，感情和事業(yè)要由不穩(wěn)定走向穩(wěn)定，...
    只愿靜靜安好閱讀 317評論 0贊 0

友情鏈接更多精彩內(nèi)容