Chinaunix首页 | 论坛 | 博客

风行者

首页　| 　博文目录　| 　关于我

jun270148122

  • 博客访问： 287423
  • 博文数量： 73
  • 博客积分： 0
  • 博客等级： 民兵
  • 技术积分： 452
  • 用 户 组： 普通用户
  • 注册时间： 2014-09-22 17:07
个人简介

心态决定命运

文章分类

全部博文（73）

文章存档

2017年（21）

2016年（27）

2015年（21）

2014年（4）

我的朋友
最近访客
推荐博文
相关博文
使用zabbix全方位监控MySQL

分类： 系统运维

2016-03-21 15:26:26

原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://john88wang.blog.51cto.com/2165294/1596272

上一篇文章 使用zabbix自带MySQL监控模板监控MySQL

对MySQL的监控不够详细。本文继续探讨对MySQL的详细监控，包括MySQL实例，MySQL主从复制和MySQL存储引擎等。

本文使用的MySQL版本是5.5

本 文使用的模板主要通过FROMDUAL提供的模板更改而成，FROMDUAL官方使用Perl语言编写采集脚本然后通过zabbix trapper的方式推送数据到zabbix server。我觉得FROMDUAL官方提供的配置方式繁琐，并且我对Perl语言又不熟悉，于是阅读官方的Perl脚本后，生出想要重新用Shell 语言来实现的想法。模板中的item名称有变更，其他的大体和FROMDUAL官方的模板相同。



1.监控原理

show global status;       查看全局状态

show global variables;    查看全局变量设置

mysqladmin                MySQL管理工具

show master status;       查看Master状态

show slave status;        查看Slave状态

show binary logs;         查看二进制日志文件

show engine innodb status\G    查看InnoDB存储引擎状态

show engine myisam status\G    查看MyISAM存储引擎状态


还有通过查看information_schema 这个数据库获取InnoDB存储引擎相关信息


2.添加MySQL监控账号

GRANT USAGE,PROCESS,SUPER,REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'zabbixagent'@'localhost' IDENTIFIED BY 'zabbixagent';

flush privileges;

在/usr/local/zabbix/etc/目录下创建一个 .my.cnf 文件

点击(此处)折叠或打开

  1. [mysql]
  2. user=zabbixagent
  3. password=zabbixagent
  4. [mysqladmin]
  5. user=zabbixagent
  6. password=zabbixagent


点击(此处)折叠或打开

  1. [mysql]
  2. user=zabbixagent
  3. password=zabbixagent
  4. [mysqladmin]
  5. user=zabbixagent
  6. password=zabbixagent

3.添加zabbix子配置文件mysql_status.conf

点击(此处)折叠或打开

  1. ### MySQL DB Infomation
  2. UserParameter=mysql.status[*],echo "show global status where Variable_name='$1';"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $$2}'
  3. UserParameter=mysql.variables[*],echo "show global variables where Variable_name='$1';"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $$2}'
  4. UserParameter=mysql.ping,mysqladmin --defaults-file=/usr/local/zabbix/etc/.my.cnf ping|grep -c alive
  5. UserParameter=mysql.version,echo "select version();"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N
  6.  
  7. #### MySQL Master Information
  8. UserParameter=mysql.master.Slave_count,echo "show slave hosts;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|wc -l
  9. UserParameter=mysql.master.Binlog_file,echo "show master status;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $1}'|awk -F. '{print $1}'
  10. UserParameter=mysql.master.Binlog_number,echo "show master status;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $1}'|awk -F. '{print $2}'
  11. UserParameter=mysql.master.Binlog_position,echo "show master status;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $2}'
  12. UserParameter=mysql.master.Binlog_count,echo "show binary logs;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|wc -l
  13. UserParameter=mysql.master.Binlog_total_size,echo "show binary logs;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{sum+=$NF}END{print sum}'
  14.  
  15. #### MySQL Slave Information
  16. UserParameter=mysql.slave.Seconds_Behind_Master,echo "show slave status\G"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf|grep "Seconds_Behind_Master"|awk '{print $2}'
  17. UserParameter=mysql.slave.Slave_IO_Running,echo "show slave status\G"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf|grep "Slave_IO_Running"|awk '{print $2}'
  18. UserParameter=mysql.slave.Slave_SQL_Running,echo "show slave status\G"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf|grep "Slave_SQL_Running"|awk '{print $2}'
  19. UserParameter=mysql.slave.Relay_Log_Pos,echo "show slave status\G"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf|grep "Relay_Log_Pos"|awk '{print $2}'
  20. UserParameter=mysql.slave.Exec_Master_Log_Pos,echo "show slave status\G"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf|grep "Exec_Master_Log_Pos"|awk '{print $2}'
  21. UserParameter=mysql.slave.Read_Master_Log_Pos,echo "show slave status\G"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf|grep "Read_Master_Log_Pos"|awk '{print $2}'
  22.  
  23.  
  24.  
  25. #### MySQL InnoDB Information
  26. #
  27. UserParameter=mysql.innodb[*],/usr/local/zabbix/bin/mysql_innodb_status.sh $1
  28.  
  29.  
  30. ####MySQL MyISAM Information
  31. #


InnoDB相关的部分监控项目需要单独用脚本获取

mysql_innodb_status.sh

点击(此处)折叠或打开

  1. #!/bin/bash
  2.  
  3. #Get InnoDB Row Lock Details and InnoDB Transcation Lock Memory
  4. #mysql> SELECT SUM(trx_rows_locked) AS rows_locked, SUM(trx_rows_modified) AS rows_modified, SUM(trx_lock_memory_bytes) AS lock_memory FROM information_schema.INNODB_TRX;
  5. #+-------------+---------------+-------------+
  6. #| rows_locked | rows_modified | lock_memory |
  7. #+-------------+---------------+-------------+
  8. #| NULL | NULL | NULL |
  9. #+-------------+---------------+-------------+
  10. #1 row in set (0.00 sec)
  11.  
  12. #+-------------+---------------+-------------+
  13. #| rows_locked | rows_modified | lock_memory |
  14. #+-------------+---------------+-------------+
  15. #| 0 | 0 | 376 |
  16. #+-------------+---------------+-------------+
  17.  
  18. #Get InnoDB Compression Time
  19. #mysql> SELECT SUM(compress_time) AS compress_time, SUM(uncompress_time) AS uncompress_time FROM information_schema.INNODB_CMP;
  20. #+---------------+-----------------+
  21. #| compress_time | uncompress_time |
  22. #+---------------+-----------------+
  23. #| 0 | 0 |
  24. #+---------------+-----------------+
  25. #1 row in set (0.00 sec)
  26.  
  27.  
  28. #Get InnoDB Transaction states
  29.  
  30. #TRX_STATE Transaction execution state. One of RUNNING, LOCK WAIT, ROLLING BACK or COMMITTING.
  31.  
  32. #mysql> SELECT LOWER(REPLACE(trx_state, " ", "_")) AS state, count(*) AS cnt from information_schema.INNODB_TRX GROUP BY state;
  33. #+---------+-----+
  34. #| state | cnt |
  35. #+---------+-----+
  36. #| running | 1 |
  37. #+---------+-----+
  38. #1 row in set (0.00 sec)
  39.  
  40.  
  41.  
  42.  
  43.  
  44. innodb_metric=$1
  45.  
  46. case $innodb_metric in
  47.    Innodb_rows_locked)
  48.                       value=$(echo "SELECT SUM(trx_rows_locked) AS rows_locked, SUM(trx_rows_modified) AS rows_modified, SUM(trx_lock_memory_bytes) AS lock_memory FROM information_schema.INNODB_TRX;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N| awk '{print $1}')
  49.                       if [ "$value" == "NULL" ];then
  50.                          echo 0
  51.                       else
  52.                          echo $value
  53.                       fi
  54.                     ;;
  55.    Innodb_rows_modified)
  56.                       value=$(echo "SELECT SUM(trx_rows_locked) AS rows_locked, SUM(trx_rows_modified) AS rows_modified, SUM(trx_lock_memory_bytes) AS lock_memory FROM information_schema.INNODB_TRX;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N| awk '{print $2}')
  57.                       if [ "$value" == "NULL" ];then
  58.                          echo 0
  59.                       else
  60.                          echo $value
  61.                       fi
  62.                     ;;
  63.    Innodb_trx_lock_memory)
  64.                       value=$(echo "SELECT SUM(trx_rows_locked) AS rows_locked, SUM(trx_rows_modified) AS rows_modified, SUM(trx_lock_memory_bytes) AS lock_memory FROM information_schema.INNODB_TRX;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N| awk '{print $3}')
  65.                       if [ "$value" == "NULL" ];then
  66.                          echo 0
  67.                       else
  68.                          echo $value
  69.                       fi
  70.                     ;;
  71.       Innodb_compress_time)
  72.                       value=$(echo "SELECT SUM(compress_time) AS compress_time, SUM(uncompress_time) AS uncompress_time FROM information_schema.INNODB_CMP;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $1}')
  73.                       echo $value
  74.                       ;;
  75.         
  76.      Innodb_uncompress_time)
  77.                       value=$(echo "SELECT SUM(compress_time) AS compress_time, SUM(uncompress_time) AS uncompress_time FROM information_schema.INNODB_CMP;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $2}')
  78.                       echo $value
  79.                       ;;
  80.          Innodb_trx_running)
  81.                          value=$(echo 'SELECT LOWER(REPLACE(trx_state, " ", "_")) AS state, count(*) AS cnt from information_schema.INNODB_TRX GROUP BY state;'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep running|awk '{print $2}')
  82.                          if [ "$value" == "" ];then
  83.                             echo 0
  84.                          else
  85.                             echo $value
  86.                          fi
  87.                         ;;
  88.        Innodb_trx_lock_wait)
  89.                          value=$(echo 'SELECT LOWER(REPLACE(trx_state, " ", "_")) AS state, count(*) AS cnt from information_schema.INNODB_TRX GROUP BY state;'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep lock_wait|awk '{print $2}')
  90.                          if [ "$value" == "" ];then
  91.                             echo 0
  92.                          else
  93.                             echo $value
  94.                          fi
  95.                         ;;
  96.     Innodb_trx_rolling_back)
  97.                          value=$(echo 'SELECT LOWER(REPLACE(trx_state, " ", "_")) AS state, count(*) AS cnt from information_schema.INNODB_TRX GROUP BY state;'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep rolling_back|awk '{print $2}')
  98.                          if [ "$value" == "" ];then
  99.                             echo 0
  100.                          else
  101.                             echo $value
  102.                          fi
  103.                         ;;
  104.     Innodb_trx_committing)
  105.                          value=$(echo 'SELECT LOWER(REPLACE(trx_state, " ", "_")) AS state, count(*) AS cnt from information_schema.INNODB_TRX GROUP BY state;'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep committing|awk '{print $2}')
  106.                          if [ "$value" == "" ];then
  107.                             echo 0
  108.                          else
  109.                             echo $value
  110.                          fi
  111.                         ;;
  112.  Innodb_trx_history_list_length)
  113.                          echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "History list length"|awk '{print $4}'
  114.                         ;;
  115.     Innodb_last_checkpoint_at)
  116.                          echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Last checkpoint at"|awk '{print $4}'
  117.                         ;;
  118.  
  119.    Innodb_log_sequence_number)
  120.                          echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Log sequence number"|awk '{print $4}'
  121.                         ;;
  122.     Innodb_log_flushed_up_to)
  123.                          echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Log flushed up to"|awk '{print $5}'
  124.                         ;;
  125.    Innodb_open_read_views_inside_innodb)
  126.                          echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "read views open inside InnoDB"|awk '{print $1}'
  127.                         ;;
  128.         Innodb_queries_inside_innodb)
  129.                         echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "queries inside InnoDB"|awk '{print $1}'
  130.                         ;;
  131.         Innodb_queries_in_queue)
  132.                         echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "queries in queue"|awk '{print $5}'
  133.                         ;;
  134.         Innodb_hash_seaches)
  135.                         echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "hash searches"|awk '{print $1}'
  136.                         ;;
  137.        Innodb_non_hash_searches)
  138.                         echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "non-hash searches/s"|awk '{print $4}'
  139.                         ;;
  140.        Innodb_node_heap_buffers)
  141.                         echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "node heap"|awk '{print $8}'
  142.                        ;;
  143.        Innodb_mutex_os_waits)
  144.                         echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Mutex spin waits"|awk '{print $9}'
  145.                        ;;
  146.        Innodb_mutex_spin_rounds)
  147.                         echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Mutex spin waits"|awk '{print $6}'|tr -d ','
  148.                        ;;
  149.        Innodb_mutex_spin_waits)
  150.                         echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Mutex spin waits"|awk '{print $4}'|tr -d ','
  151.                        ;;
  152.  
  153.                    *)
  154.                     echo "wrong parameter"
  155.                     ;;
  156.  
  157. esac

4.添加监控模板

附件中包含对MySQL实例，MySQL Master，MySQL Slave和MySQL InnoDB的监控



参考文章：

http://dev.mysql.com/doc/refman/5.5/en/information-schema.html


FROMDUAL官方模板和脚本下载地址如下，感兴趣的可以看看




本文出自 “Linux SA John” 博客，请务必保留此出处http://john88wang.blog.51cto.com/2165294/1596272

阅读(2925) | 评论(0) | 转发(0) |
0

上一篇：docker 学习1

下一篇：Python实现ssh批量登录并执行命令

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6