[Zabbix] zabbix监控mysql数据库

1、编写定期收集mysql信息脚本，并指定到对应目录

cat /usr/local/zabbix/script/mysql_monitor.sh

#!/bin/bash
dbpath='/data/mysql/bin/mysql'
#mysql的用户
dbuser='root'
#mysql的密码
dbpass='123456'
#mysql的端口
dbport='3306'
#mysql的socket文件
dbsocket='/data/mysql/tmp/mysql.sock'
#mysql-status日志路径
dbstatuspath=/tmp/mysql_status_monitor.log
#mysql-engine-status日志路径
dbenginestatuspath=/tmp/mysql_engine_innodb_status.log
#self-define-script
dbselfpath=/tmp/mysql_self_status.log

#查询mysql-status信息
${dbpath} -u${dbuser} -p${dbpass} -S${dbsocket} -P${dbport} -BNe "show global status;" > ${dbstatuspath}
#查询mysql-engine-innodb信息
${dbpath} -u${dbuser} -p${dbpass} -S${dbsocket} -P${dbport} -BNe "show engine innodb status\G" > ${dbenginestatuspath}
#检查集群节点
nodecount=`export MYSQL_PWD=${dbpass};${dbpath} -u${dbuser} --socket=/data/mysql/data/mysql.sock -P${dbport} -BNe "select count(*) from performance_schema.replication_group_members where MEMBER_STATE != 'ONLINE'"`
echo -e "nodecount ${nodecount}" > ${dbselfpath}

根据自己的数据库配置，修改相应的参数，修改完成之后授权脚本执行权限：

1	chmod +x /usr/local/zabbix/script/mysql_monitor.sh

2、将脚本添加到定时任务，每分钟执行一次

crontab -l

1	* * * * * sh /usr/local/zabbix/script/mysql_monitor.sh

3、编写获取数据脚本，对定时生成的log文件进行信息筛选

cat /usr/local/zabbix/script/mysql_get_data.sh

case $1 in
	nodecount)
       #查询集群节点存活状态
       cat /tmp/mysql_self_status.log |awk '/nodecount/ {print $2}' |head -1 
       ;;
	uptime)
       cat /tmp/mysql_status_monitor.log | awk '/Uptime/ {print $2}' |head -1
       ;;
    com_select)
       cat /tmp/mysql_status_monitor.log | awk '/Com_select/ {print $2}'
       ;;
    com_insert)
       cat /tmp/mysql_status_monitor.log | awk '/Com_insert/ {print $2}'|head -1
       ;;
    com_update)
       cat /tmp/mysql_status_monitor.log | awk '/Com_update/ {print $2}'|head -1
       ;;
    com_delete)
       cat /tmp/mysql_status_monitor.log | awk '/Com_delete/ {print $2}'|head -1
       ;;
    connections)
       cat /tmp/mysql_status_monitor.log | awk '/Connections/ {print $2}'|head -1
       ;;
    thread_cached)
       cat /tmp/mysql_status_monitor.log | awk '/Threads_cached/ {print $2}'
       ;;
    threads_connected)
       cat /tmp/mysql_status_monitor.log | awk '/Threads_connected/ {print $2}'
       ;;
    thread_created)
       cat /tmp/mysql_status_monitor.log | awk '/Threads_created/ {print $2}'            
       ;;
    Threads_running)
       cat /tmp/mysql_status_monitor.log | awk '/Threads_running/ {print $2}'
       ;;
    table_locks_immediate)
       cat /tmp/mysql_status_monitor.log | awk '/Table_locks_immediate/ {print $2}'
       ;;
    table_locks_waited)
       cat /tmp/mysql_status_monitor.log | awk '/Table_locks_waited/ {print $2}'
       ;;
    slow_launch_threads)
       cat /tmp/mysql_status_monitor.log | awk '/Slow_launch_threads/ {print $2}'
       ;;
    slow_queries)
       cat /tmp/mysql_status_monitor.log | awk '/Slow_queries/ {print $2}'
       ;;
    qps)
       uptime=`cat /tmp/mysql_status_monitor.log | awk '/Uptime/ {print $2}' |head -1`
       questions=`cat /tmp/mysql_status_monitor.log | awk '/Questions/ {print $2}'`
       echo $(printf "%.2f" `echo "scale=2;${questions}/${uptime}"|bc`)
       ;;
    tps)
       uptime=`cat /tmp/mysql_status_monitor.log | awk '/Uptime/ {print $2}' |head -1`
       com_commit=`cat /tmp/mysql_status_monitor.log | awk '/Com_commit/ {print $2}'`
       com_rollback=`cat /tmp/mysql_status_monitor.log | awk '/Com_rollback/ {print $2}'|head -1`
       com_sum=$((${com_commit}+${com_rollback}))
       echo $(printf "%.2f" `echo "scale=2;${com_sum}/${uptime}"|bc`)
       ;;
    innodb_buffer_read_hits)
       innodb_buffer_pool_reads=`cat /tmp/mysql_status_monitor.log | awk '/Innodb_buffer_pool_reads/ {print $2}'|head -1`
       innodb_buffer_pool_read_requests=`cat /tmp/mysql_status_monitor.log | awk '/Innodb_buffer_pool_read_requests/ {print $2}'|head -1`
       innodb_buffer_read_diff=$((${innodb_buffer_pool_read_requests}-${innodb_buffer_pool_reads}))
       echo $(printf "%.2f" `echo "scale=2;${innodb_buffer_read_diff}/${innodb_buffer_pool_read_requests}"|bc`)
       ;;
    table_cache_hit)
       cat /tmp/mysql_status_monitor.log | awk '/Opened_tables/ {print $2}'
       ;;
    thread_cache_hits)
       thread_created=`cat /tmp/mysql_status_monitor.log | awk '/Threads_created/ {print $2}'`
       connections=`cat /tmp/mysql_status_monitor.log | awk '/Connections/ {print $2}'|head -1`
       thread_cache_diff=$((${connections}-${thread_created}))
       echo $(printf "%.2f" `echo "scale=2;${thread_cache_diff}/${connections}"|bc`)
       ;;
    create_tmp_tables_hits)
       created_tmp_tables=`cat /tmp/mysql_status_monitor.log | awk '/Created_tmp_tables/ {print $2}'`
       created_tmp_disk_tables=`cat /tmp/mysql_status_monitor.log | awk '/Created_tmp_disk_tables/ {print $2}'`
       echo $(printf "%.2f" `echo "scale=2;${created_tmp_disk_tables}/${created_tmp_tables}"|bc`)
       ;;
    binlog_cache_disk_use)
       cat /tmp/mysql_status_monitor.log | awk '/Binlog_cache_disk_use/ {print $2}'
       ;;
    table_locks_immediate)
       cat /tmp/mysql_status_monitor.log | awk '/Table_locks_immediate/ {print $2}'
       ;;
    table_locks_waited)
       cat /tmp/mysql_status_monitor.log | awk '/Table_locks_waited/ {print $2}'
       ;;
    innodb_row_lock_waits)
       cat /tmp/mysql_status_monitor.log | awk '/Innodb_row_lock_waits/ {print $2}'
       ;;               
    *)
       ;;
esac

4、编写zabbix监控项文件,通过/usr/local/zabbix/script/mysql_get_data.sh脚本去获取对应监控的数值

cat /usr/local/zabbix/etc/zabbix_agentd.conf.d/mysql_monitor_parameter.conf

#集群节点数量
UserParameter=nodecount[*],sh /usr/local/zabbix/script/mysql_get_data.sh nodecount
#系统运行时间
UserParameter=uptime[*],sh /usr/local/zabbix/script/mysql_get_data.sh uptime
#查看select语句的执行数
UserParameter=com_select[*],sh /usr/local/zabbix/script/mysql_get_data.sh com_select
#查看insert语句的执行数
UserParameter=com_insert[*],sh /usr/local/zabbix/script/mysql_get_data.sh com_insert
#查看update语句的执行数
UserParameter=com_update[*],sh /usr/local/zabbix/script/mysql_get_data.sh com_update
#查看delete语句的执行数
UserParameter=com_delete[*],sh /usr/local/zabbix/script/mysql_get_data.sh com_delete
#查看试图连接到MySQL(不管是否连接成功)的连接数
UserParameter=connections[*],sh /usr/local/zabbix/script/mysql_get_data.sh connections
#查看线程缓存内的线程的数量
UserParameter=thread_cached[*],sh /usr/local/zabbix/script/mysql_get_data.sh thread_cached
#当线程打开连接数
UserParameter=threads_connected[*],sh /usr/local/zabbix/script/mysql_get_data.sh threads_connected
#查看创建用来处理连接的线程数。如果Threads_created较大，你可能要增加thread_cache_size值
UserParameter=thread_created[*],sh /usr/local/zabbix/script/mysql_get_data.sh thread_created
#查看激活的(非睡眠状态)线程数
UserParameter=threads_running[*],sh /usr/local/zabbix/script/mysql_get_data.sh threads_running
#查看创建时间超过slow_launch_time秒的线程数。
UserParameter=slow_launch_threads[*],sh /usr/local/zabbix/script/mysql_get_data.sh slow_launch_threads
#查看查询时间超过long_query_time秒的查询的个数。
UserParameter=slow_queries[*],sh /usr/local/zabbix/script/mysql_get_data.sh slow_queries
#在服务器上执行语句的数量，只包括客户端发送给服务器的语句，并不包括存储项目执行的语句
UserParameter=qps[*],sh /usr/local/zabbix/script/mysql_get_data.sh qps
#事物提交数量com_commit
#事物回滚数量com_rollback
UserParameter=tps[*],sh /usr/local/zabbix/script/mysql_get_data.sh tps
#在buffer pool满足不了逻辑读的请求，而必须直接从硬盘读取的数量innodb_buffer_pool_reads
#逻辑读的请求数innodb_buffer_pool_read_requests
#buffer_read命中率
UserParameter=innodb_buffer_read_hits[*],sh /usr/local/zabbix/script/mysql_get_data.sh innodb_buffer_read_hits
#打开表的数量open_tables
#已经打开表的数量opened_tables
#table_cache命中率
UserParameter=table_cache_hit[*],sh /usr/local/zabbix/script/mysql_get_data.sh table_cache_hit
#thread_cache命中率
UserParameter=thread_cache_hits[*],sh /usr/local/zabbix/script/mysql_get_data.sh thread_cache_hits
#执行语句过程中创建的临时表created_tmp_tables
#当创建的内部临时表过大，mysql会自动将表从内存中转换为磁盘上的表created_tmp_disk_tables
#在磁盘上创建临时表的比例
UserParameter=create_tmp_tables_hits[*],sh /usr/local/zabbix/script/mysql_get_data.sh create_tmp_tables_hits
#事物使用binlog但是临时二进制日志超过binlog_cache_size，使用临时文件存储事物的语句，如果该值不为0，需要调整binlog_cache_size
UserParameter=binlog_cache_disk_use[*],sh /usr/local/zabbix/script/mysql_get_data.sh binlog_cache_disk_use
#可以立即授予表锁请求的次数
UserParameter=table_locks_immediate[*],sh /usr/local/zabbix/script/mysql_get_data.sh table_locks_immediate
#一个表锁的请求不能立即被授予，需要等待的次数,如果该值过高，说明数据库有性能问题，应该优化sql语句，表分区或者读写分离
UserParameter=table_locks_waited[*],sh /usr/local/zabbix/script/mysql_get_data.sh table_locks_waited
#操作innodb表等待行数的次数
UserParameter=innodb_row_lock_waits[*],sh /usr/local/zabbix/script/mysql_get_data.sh innodb_row_lock_waits

5、修改/usr/local/zabbix/etc/zabbix_agentd.conf配置文件,添加该行参数：

1	Include=/usr/local/zabbix/etc/zabbix_agentd.conf.d/

添加完成之后，需要重启zabbix_agent客户端

6、通过zabbix-web控制台，自定义一个模板，新建监控项、触发器、图形等内容，下面链接是我创建的比较简单的mysql模板，上传到百度云，仅供参考，点击下载

7、上面模式中tps和qps的算法是按照自数据库启动获取的参数值与uptime相除获取的每秒内执行数，下面计算另一个取值方法：取单位时间内前后值得差值，得到的差值除以时间间隔，获取单位时间内的qps和tps值，修改配置文件/usr/local/zabbix/script/mysql_get_data.sh：

qps)
   #uptime=`cat /tmp/mysql_status_monitor.log | awk '/Uptime/ {print $2}' |head -1`
   #questions=`cat /tmp/mysql_status_monitor.log | awk '/Questions/ {print $2}'`
   #echo $(printf "%.2f" `echo "scale=2;${questions}/${uptime}"|bc`)
   cat /tmp/mysql_status_monitor.log | awk '/Questions/ {print $2}'
   ;;
tps)
   #uptime=`cat /tmp/mysql_status_monitor.log | awk '/Uptime/ {print $2}' |head -1`
   com_commit=`cat /tmp/mysql_status_monitor.log | awk '/Com_commit/ {print $2}'`
   com_rollback=`cat /tmp/mysql_status_monitor.log | awk '/Com_rollback/ {print $2}'|head -1`
   com_sum=$((${com_commit}+${com_rollback}))
   echo ${com_sum}
   #echo $(printf "%.2f" `echo "scale=2;${com_sum}/${uptime}"|bc`)
   ;;

修改zabbix模板监控项，将tps、qps的监控项下添加如下图所示进程：

亦可点击此处下载，获取该模板，并导入zabbix。