zabbix监控mysql的一些特性

1、zabbix有自带的mysql的模板，但是需要自己去到zabbix_agent中去配置和写脚本去获取这些key所对应的value.

[root@wyl01 shell]# cat mysql_status.sh 
#!/bin/bash
mysql=/usr/local/mysql/bin/mysql

username='zabbix'
password='zabbix'

${mysql}  -u${username} -p${password} -e 'show global status'  2>/dev/null|grep "\b$1\b"|awk '{print $2}' 

2、配置zabbix_agent的key的值

[root@wyl01 zabbix_agentd.d]# cat mysql_status.conf 
UserParameter=mysql.status[*],/usr/bin/sh /etc/zabbix/shell/mysql_status.sh $1
UserParameter=mysql.ping,ps -ef |grep mysqld |grep -v grep |wc -l
UserParameter=mysql.version,/usr/local/mysql/bin/mysql -V |awk '{print $5}'|awk -F ',' '{print $1}'

mysql.status获取的是mysql数据库的一些特性。

mysql.ping获取的是mysql进程

mysql.version获取的mysql的版本号

3、我们创建一个graph,将这些监控项添加进去。

4、zabbix监控mysql性能
通过获取mysql状态值将这些状态值传递给服务器并绘制成图片，这样可以观察mysql的工作情况，通常需要获得状态变量有以下

mysql自带的模板：

MySQL bytes sent per second	mysql.status[Bytes_sent]	每间隔时间发送给所有客户端的字节数
MySQL delete operations per second	mysql.status[Com_delete]	执行delete操作的数量
MySQL bytes received per second	mysql.status[Bytes_received]	每间隔从所有客户端接收到的字节数
MySQL queries per second	mysql.status[Questions]	已经发送给服务器的查询
MySQL slow queries	mysql.status[Slow_queries]	查询时间操作 long_query_time 秒的查询的个数
MySQL begin operations per second	mysql.status[Com_begin]	开启事务的次数
MySQL commit operations per second	mysql.status[Com_commit]	提交事务的次数
MySQL insert operations per second	mysql.status[Com_insert]	执行insert操作的数量
MySQL uptime	mysql.status[Uptime]	服务器已经运行的时间（以秒为单位）
MySQL status	mysql.ping	mysql状态
MySQL select operations per second	mysql.status[Com_select]	执行select操作的数量
MySQL update operations per second	mysql.status[Com_update]	执行update操作的数量
MySQL rollback operations per second	mysql.status[Com_rollback]	回滚事务的次数
MySQL version	mysql.version	mysql版本

5.补充

mysql=/usr/local/mysql/bin/mysql

username='root'
password='123456'
if [ "$1" = "mysql.buffer_pool_usage_percent" ];then
   ${mysql} -u${username} -p${password}  -e "show global status where Variable_name='Innodb_buffer_pool_pages_data' or Variable_name='Innodb_buffer_pool_pages_total'" 2>/dev/null|awk '{a[NR]=$2}END{printf "%.1f",((a[2]/a[3])*100)}'
elif [ "$1" = 'mysql.buffer_pool_hit_rate' ];then
   ${mysql} -u${username} -p${password}  -e "show global status where Variable_name='Innodb_buffer_pool_read_requests' or Variable_name='Innodb_buffer_pool_reads';"  2>/dev/null| awk '{a[NR]=$2}END{printf "%.1f",(a[2]-a[3])/a[2]*100}'
else
   ${mysql}  -u${username} -p${password} -e 'show variables;show global status'  2>/dev/null|grep "\b$1\b"|awk '{print $2}'
fi