上一篇文章使用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官方的模板相同。
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=zabbixagentpassword=zabbixagent[mysqladmin]user=zabbixagentpassword=zabbixagent
3.添加zabbix子配置文件mysql_status.conf
### mysql db infomationuserparameter=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 aliveuserparameter=mysql.version,echo select version();|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -n#### mysql master informationuserparameter=mysql.master.slave_count,echo show slave hosts;|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -n|wc -luserparameter=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 -luserparameter=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 informationuserparameter=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=$1case $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 ;;esac
4.添加监控模板
附件中包含对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” 博客，请务必保留此出处