上一篇文章使用zabbix自带MySQL监控模板监控MySQL对MySQL的监控不够详细。本文继续探讨对MySQL的详细监控，包括MySQL实例，MySQL主从复制和MySQL存储引擎等。本
上一篇文章 使用zabbix自带mysql监控模板监控mysql
对MySQL的监控不够详细。本文继续探讨对MySQL的详细监控，包括MySQL实例，MySQL主从复制和MySQL存储引擎等。
本文使用的MySQL版本是5.5
本文使用的模板主要通过FROMDUAL提供的模板更改而成，FROMDUAL官方使用Perl语言编写采集脚本然后通过zabbix trapper的方式推送数据到zabbix server。我觉得FROMDUAL官方提供的配置方式繁琐，并且我对Perl语言又不熟悉，于是阅读官方的Perl脚本后，生出想要重新用Shell语言来实现的想法。模板中的item名称有变更，其他的大体和FROMDUAL官方的模板相同。
红技shop网上购物系统
下载
红技SHOP是一款智能化的通用型网络商城系统，取市面上众多的同类商城系统之精华，去除其它同类商品的不足之处和复杂烦琐的无用功能，用红技独有研发技术不断地加以提炼，使系统体积小而功能全面所有功能都能发辉作用。红技SHOP无论在系统稳定性、代码优化、运行效率、负荷能力、安全性能、功能可操控性和程序可维护性等方面都居国内外同类网上购系统商品的领先者。红技SHOP是专业的网络商城的WEB软件开发单位，因为
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 文件
[mysql] user=zabbixagent password=zabbixagent [mysqladmin] user=zabbixagent password=zabbixagent3.添加zabbix子配置文件mysql_status.conf
### MySQL DB Infomation UserParameter=mysql.status[*],echo "show global status where Variable_name='$1';"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $$2}' UserParameter=mysql.variables[*],echo "show global variables where Variable_name='$1';"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $$2}' UserParameter=mysql.ping,mysqladmin --defaults-file=/usr/local/zabbix/etc/.my.cnf ping|grep -c alive UserParameter=mysql.version,echo "select version();"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N #### MySQL Master Information UserParameter=mysql.master.Slave_count,echo "show slave hosts;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|wc -l 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}' 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}' UserParameter=mysql.master.Binlog_position,echo "show master status;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $2}' UserParameter=mysql.master.Binlog_count,echo "show binary logs;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|wc -l 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}' #### MySQL Slave Information 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}' 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}' 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}' 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}' 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}' 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}' #### MySQL InnoDB Information # UserParameter=mysql.innodb[*],/usr/local/zabbix/bin/mysql_innodb_status.sh $1 ####MySQL MyISAM Information #InnoDB相关的部分监控项目需要单独用脚本获取
mysql_innodb_status.sh
#!/bin/bash #Get InnoDB Row Lock Details and InnoDB Transcation Lock Memory #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; #+-------------+---------------+-------------+ #| rows_locked | rows_modified | lock_memory | #+-------------+---------------+-------------+ #| NULL | NULL | NULL | #+-------------+---------------+-------------+ #1 row in set (0.00 sec) #+-------------+---------------+-------------+ #| rows_locked | rows_modified | lock_memory | #+-------------+---------------+-------------+ #| 0 | 0 | 376 | #+-------------+---------------+-------------+ #Get InnoDB Compression Time #mysql> SELECT SUM(compress_time) AS compress_time, SUM(uncompress_time) AS uncompress_time FROM information_schema.INNODB_CMP; #+---------------+-----------------+ #| compress_time | uncompress_time | #+---------------+-----------------+ #| 0 | 0 | #+---------------+-----------------+ #1 row in set (0.00 sec) #Get InnoDB Transaction states #TRX_STATE Transaction execution state. One of RUNNING, LOCK WAIT, ROLLING BACK or COMMITTING. #mysql> SELECT LOWER(REPLACE(trx_state, " ", "_")) AS state, count(*) AS cnt from information_schema.INNODB_TRX GROUP BY state; #+---------+-----+ #| state | cnt | #+---------+-----+ #| running | 1 | #+---------+-----+ #1 row in set (0.00 sec) innodb_metric=$1 case $innodb_metric in Innodb_rows_locked) 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}') if [ "$value" == "NULL" ];then echo 0 else echo $value fi ;; Innodb_rows_modified) 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}') if [ "$value" == "NULL" ];then echo 0 else echo $value fi ;; Innodb_trx_lock_memory) 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}') if [ "$value" == "NULL" ];then echo 0 else echo $value fi ;; Innodb_compress_time) 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}') echo $value ;; Innodb_uncompress_time) 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}') echo $value ;; Innodb_trx_running) 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}') if [ "$value" == "" ];then echo 0 else echo $value fi ;; Innodb_trx_lock_wait) 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}') if [ "$value" == "" ];then echo 0 else echo $value fi ;; Innodb_trx_rolling_back) 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}') if [ "$value" == "" ];then echo 0 else echo $value fi ;; Innodb_trx_committing) 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}') if [ "$value" == "" ];then echo 0 else echo $value fi ;; Innodb_trx_history_list_length) echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "History list length"|awk '{print $4}' ;; Innodb_last_checkpoint_at) echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Last checkpoint at"|awk '{print $4}' ;; Innodb_log_sequence_number) echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Log sequence number"|awk '{print $4}' ;; Innodb_log_flushed_up_to) echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Log flushed up to"|awk '{print $5}' ;; Innodb_open_read_views_inside_innodb) 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}' ;; Innodb_queries_inside_innodb) echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "queries inside InnoDB"|awk '{print $1}' ;; Innodb_queries_in_queue) echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "queries in queue"|awk '{print $5}' ;; Innodb_hash_seaches) echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "hash searches"|awk '{print $1}' ;; Innodb_non_hash_searches) echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "non-hash searches/s"|awk '{print $4}' ;; Innodb_node_heap_buffers) echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "node heap"|awk '{print $8}' ;; Innodb_mutex_os_waits) echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Mutex spin waits"|awk '{print $9}' ;; Innodb_mutex_spin_rounds) 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 ',' ;; Innodb_mutex_spin_waits) 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 ',' ;; *) echo "wrong parameter" ;; esac4.添加监控模板
附件中包含对MySQL实例，，MySQL Master，MySQL Slave和MySQL InnoDB的监控
参考文章：
FROMDUAL官方模板和脚本下载地址如下，感兴趣的可以看看
?operation=download&file_name=mysql_performance_monitor-0.9.3.tar.gz&id=1996
本文出自 “Linux SA John” 博客，请务必保留此出处
