zabbix监控mysql的一些特性

约定不等于承诺〃 2022-05-15 01:35 216阅读 0赞

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,将这些监控项添加进去。

![70][]

![70 1][]

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

mysql自带的模板：

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

**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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5bDk1Mjc_size_16_color_FFFFFF_t_70][]

[70]: /images/20220515/8cb370f690f74d97ab83f19d00c1d42b.png
[70 1]: /images/20220515/4acf41e6245f4cf3bdd65f727952ca64.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5bDk1Mjc_size_16_color_FFFFFF_t_70]: /images/20220515/f4d9e8724d34411f9730ebd0a22847b2.png