ITPub博客

首页 > 应用开发 > IT综合 > Zabbix监控mysql

Zabbix监控mysql

原创 IT综合 作者:Michael_DD 时间:2015-09-29 15:36:39 0 删除 编辑
Zabbix监控mysql


1. 安装zabbix客户端程序 (略)

2. 配置zabbix客户端配置文件
/usr/local/zabbix/etc/zabbix_agentd.conf
添加:
UnsafeUserParameters=1
Include=/usr/local/zabbix/conf/zabbix_agentd/*.conf

3.编辑/usr/local/zabbix/conf/zabbix_agentd/mysql.conf

点击(此处)折叠或打开

  1. UserParameter=mysql.Ping,mysqladmin -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock --socket=/mysql-5.6.22/run/mysql.sock ping|grep alive|wc -l
  2. UserParameter=mysql.Threads,mysqladmin -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock status|cut -f3 -d":"|cut -f1 -d"Q"
  3. UserParameter=mysql.Questions,mysqladmin -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock status|cut -f4 -d":"|cut -f1 -d"S"
  4. UserParameter=mysql.Slowqueries,mysqladmin -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock status|cut -f5 -d":"|cut -f1 -d"O"
  5. UserParameter=mysql.Qps,mysqladmin -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock status|cut -f9 -d":"
  6. UserParameter=mysql.Slave_IO_State,if [ "$(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show slave status\G"| grep Slave_IO_Running|awk '{print $2}')" == "Yes" ];then echo 1; else echo 0;fi
  7. UserParameter=mysql.Slave_SQL_State,if [ "$(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show slave status\G"| grep Slave_SQL_Running|awk '{print $2}')" == "Yes" ];then echo 1; else echo 0;fi
  8. UserParameter=mysql.Key_buffer_size,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show variables like 'key_buffer_size';"| grep -v Value |awk '{print $2/1024^2}'
  9. UserParameter=mysql.Key_reads,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'key_reads';"| grep -v Value |awk '{print $2}'
  10. UserParameter=mysql.Key_read_requests,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'key_read_requests';"| grep -v Value |awk '{print $2}'
  11. UserParameter=mysql.Key_cache_miss_rate,echo $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'key_reads';"| grep -v Value |awk '{print $2}') $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'key_read_requests';"| grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",$1/$2*100)}'
  12. UserParameter=mysql.Key_blocks_used,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'key_blocks_used';"| grep -v Value |awk '{print $2}'
  13. UserParameter=mysql.Key_blocks_unused,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'key_blocks_unused';"| grep -v Value |awk '{print $2}'
  14. UserParameter=mysql.Key_blocks_used_rate,echo $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'key_blocks_used';"| grep -v Value |awk '{print $2}') $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'key_blocks_unused';"| grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",$1/($1+$2)*100)}'
  15. UserParameter=mysql.Innodb_buffer_pool_size,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show variables like 'innodb_buffer_pool_size';"| grep -v Value |awk '{print $2/1024^2}'
  16. UserParameter=mysql.Innodb_log_file_size,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show variables like 'innodb_log_file_size';"| grep -v Value |awk '{print $2/1024^2}'
  17. UserParameter=mysql.Innodb_log_buffer_size,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show variables like 'innodb_log_buffer_size';"| grep -v Value |awk '{print $2/1024^2}'
  18. UserParameter=mysql.Table_open_cache,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show variables like 'table_open_cache';"| grep -v Value |awk '{print $2}'
  19. UserParameter=mysql.Open_tables,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'open_tables';"| grep -v Value |awk '{print $2}'
  20. UserParameter=mysql.Opened_tables,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'opened_tables';"| grep -v Value |awk '{print $2}'
  21. UserParameter=mysql.Open_tables_rate,echo $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'open_tables';"| grep -v Value |awk '{print $2}') $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'opened_tables';"| grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",$1/($1+$2)*100)}'
  22. UserParameter=mysql.Table_open_cache_used_rate,echo $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'open_tables';"| grep -v Value |awk '{print $2}') $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show variables like 'table_open_cache';"| grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",$1/($1+$2)*100)}'
  23. UserParameter=mysql.Thread_cache_size,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show variables like 'thread_cache_size';"| grep -v Value |awk '{print $2}'
  24. UserParameter=mysql.Threads_cached,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Threads_cached';"| grep -v Value |awk '{print $2}'
  25. UserParameter=mysql.Threads_connected,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Threads_connected';"| grep -v Value |awk '{print $2}'
  26. UserParameter=mysql.Threads_created,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Threads_created';"| grep -v Value |awk '{print $2}'
  27. UserParameter=mysql.Threads_running,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Threads_running';"| grep -v Value |awk '{print $2}'
  28. UserParameter=mysql.Qcache_free_blocks,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Qcache_free_blocks';"| grep -v Value |awk '{print $2}'
  29. UserParameter=mysql.Qcache_free_memory,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Qcache_free_memory';"| grep -v Value |awk '{print $2}'
  30. UserParameter=mysql.Qcache_hits,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Qcache_hits';"| grep -v Value |awk '{print $2}'
  31. UserParameter=mysql.Qcache_inserts,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Qcache_inserts';"| grep -v Value |awk '{print $2}'
  32. UserParameter=mysql.Qcache_lowmem_prunes,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Qcache_lowmem_prunes';"| grep -v Value |awk '{print $2}'
  33. UserParameter=mysql.Qcache_not_cached,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Qcache_not_cached';"| grep -v Value |awk '{print $2}'
  34. UserParameter=mysql.Qcache_queries_in_cache,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Qcache_queries_in_cache';"| grep -v Value |awk '{print $2}'
  35. UserParameter=mysql.Qcache_total_blocks,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Qcache_total_blocks';"| grep -v Value |awk '{print $2}'
  36. UserParameter=mysql.Qcache_fragment_rate,echo $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Qcache_free_blocks';"| grep -v Value |awk '{print $2}') $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Qcache_total_blocks';"| grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",$1/$2*100)}'
  37. UserParameter=mysql.Qcache_used_rate,echo $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show variables like 'query_cache_size';"| grep -v Value |awk '{print $2}') $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Qcache_free_memory';"| grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",($1-$2)/$1*100)}'
  38. UserParameter=mysql.Qcache_hits_rate,echo $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Qcache_hits';"| grep -v Value |awk '{print $2}') $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Qcache_inserts';"| grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",($1-$2)/$1*100)}'
  39. UserParameter=mysql.Query_cache_limit,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show variables like 'query_cache_limit';"| grep -v Value |awk '{print $2}'
  40. UserParameter=mysql.Query_cache_min_res_unit,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show variables like 'query_cache_min_res_unit';"| grep -v Value |awk '{print $2}'
  41. UserParameter=mysql.Query_cache_size,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show variables like 'query_cache_size';"| grep -v Value |awk '{print $2}'
  42. UserParameter=mysql.Sort_merge_passes,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Sort_merge_passes';"| grep -v Value |awk '{print $2}'
  43. UserParameter=mysql.Sort_range,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Sort_range';"| grep -v Value |awk '{print $2}'
  44. UserParameter=mysql.Sort_rows,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Sort_rows';"| grep -v Value |awk '{print $2}'
  45. UserParameter=mysql.Sort_scan,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Sort_scan';"| grep -v Value |awk '{print $2}'
  46. UserParameter=mysql.Handler_read_first,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Handler_read_first';"| grep -v Value |awk '{print $2}'
  47. UserParameter=mysql.Handler_read_key,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Handler_read_key';"| grep -v Value |awk '{print $2}'
  48. UserParameter=mysql.Handler_read_next,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Handler_read_next';"| grep -v Value |awk '{print $2}'
  49. UserParameter=mysql.Handler_read_prev,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Handler_read_prev';"| grep -v Value |awk '{print $2}'
  50. UserParameter=mysql.Handler_read_rnd,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Handler_read_rnd';"| grep -v Value |awk '{print $2}'
  51. UserParameter=mysql.Handler_read_rnd_next,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Handler_read_rnd_next';"| grep -v Value |awk '{print $2}'
  52. UserParameter=mysql.Com_select,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'com_select';"| grep -v Value |awk '{print $2}'
  53. UserParameter=mysql.Com_insert,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'com_insert';"| grep -v Value |awk '{print $2}'
  54. UserParameter=mysql.Com_insert_select,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'com_insert_select';"| grep -v Value |awk '{print $2}'
  55. UserParameter=mysql.Com_update,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'com_update';"| grep -v Value |awk '{print $2}'
  56. UserParameter=mysql.Com_replace,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'com_replace';"| grep -v Value |awk '{print $2}'
  57. UserParameter=mysql.Com_replace_select,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'com_replace_select';"| grep -v Value |awk '{print $2}'
  58. UserParameter=mysql.Table_scan_rate,echo $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Handler_read_rnd_next';"| grep -v Value |awk '{print $2}') $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'com_select';"| grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",$1/$2*100)}'
  59. UserParameter=mysql.Open_files,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'open_files';"| grep -v Value |awk '{print $2}'
  60. UserParameter=mysql.Open_files_limit,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show variables like 'open_files_limit';"| grep -v Value |awk '{print $2}'
  61. UserParameter=mysql.Open_files_rate,echo $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'open_files';"| grep -v Value |awk '{print $2}') $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show variables like 'open_files_limit';"| grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",$1/$2*100)}'
  62. UserParameter=mysql.Created_tmp_disk_tables,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'created_tmp_disk_tables';"| grep -v Value |awk '{print $2}'
  63. UserParameter=mysql.Created_tmp_tables,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'created_tmp_tables';"| grep -v Value |awk '{print $2}'
  64. UserParameter=mysql.Created_tmp_disk_tables_rate,echo $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'created_tmp_disk_tables';"| grep -v Value |awk '{print $2}') $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'created_tmp_tables';"| grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",$1/$2*100)}'
  65. UserParameter=mysql.Max_connections,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show variables like 'max_connections';"| grep -v Value |awk '{print $2}'
  66. UserParameter=mysql.Max_used_connections,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Max_used_connections';"| grep -v Value |awk '{print $2}'
  67. UserParameter=mysql.Max_connections_used_rate,echo $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Max_used_connections';"| grep -v Value |awk '{print $2}') $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show variables like 'max_connections';"| grep -v Value |awk '{print $2}')| awk '{printf("%1.4f\n",$1/$2*100)}'
  68. UserParameter=mysql.Table_locks_immediate,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Table_locks_immediate';"| grep -v Value |awk '{print $2}'
  69. UserParameter=mysql.Table_locks_waited,mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'table_locks_waited';"| grep -v Value |awk '{print $2}'
  70. UserParameter=mysql.Engine_select,echo $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'Table_locks_immediate';"| grep -v Value |awk '{print $2}') $(mysql -uroot -p123456 --socket=/mysql-5.6.22/run/mysql.sock -e "show status like 'table_locks_waited';"| grep -v Value |awk '{print $2}')| awk '{printf("%5.4f\n",$1/$2)}'


4.测试:
[root@mysqlsource etc]# zabbix_agentd -help
Zabbix Agent (daemon) v2.4.5 (revision 53282) (21 April 2015)
usage: zabbix_agentd [-Vhp] [-R <runtime option>] [-c <config-file>] [-t <item key>]
Options:
  -c --config <config-file>       Absolute path to the configuration file
  -p --print                      Print known items and exit
  -t --test <item key>            Test specified item and exit
  -h --help                       Display help information
  -V --version                    Display version number
  -R --runtime-control <option>   Perform administrative functions
Runtime control options:
  log_level_increase=<target>     Increase log level, affect all processes if target is not specified
  log_level_decrease=<target>     Decrease log level, affect all processes if target is not specified
Log level control targets:
  <pid>                           Process identifier
  <process type>                  All processes of specified type (e.g., listener)
  <process type,N>                Process type and number (e.g., listener,3)
[root@mysqlsource etc]# 
[root@mysqlsource etc]# zabbix_agentd -t mysql.Ping
mysql.Ping                                    [t|Warning: Using a password on the command line interface can be insecure.
1]
[root@mysqlsource etc]# 


5. zabbix服务端测试:
[root@zabbix zabbix]# zabbix_get -s 192.168.9.251 -k mysql.Ping
Warning: Using a password on the command line interface can be insecure.
1
[root@zabbix zabbix]# 


6. 在图形化界面上配置监控项

附件为xml模板文件:
mysql模板.txt


服务端测试可能报各类错误:
1. ZBX_NOTSUPPORTED: Unsupported item key
确认key名字配对了,并且客户端要重启
2.[root@zabbix zabbix]# zabbix_get -s 192.168.9.251 -k mysql.Innodb_log_buffer_size
sh: mysql: command not found
加全路径:/mysql/bin/mysql








来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29500582/viewspace-1811692/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Zabbix-2.4.5 安装
请登录后发表评论 登录
全部评论

注册时间:2014-02-22

  • 博文量
    326
  • 访问量
    2441118