zabbix监控MySQL

  sre

增加key:
<>'';" | HOME=/opt/zabbix/etc mysql -N

#TPS 每秒事务数 TPS = (Com_commit + Com_rollback) / seconds
#InnoDB Buffer命中率 Innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%
UserParameter=mysql.innodb_buffer_pool_reads,echo "show global status where Variable_name='innodb_buffer_pool_reads';" | HOME=/opt/zabbix/etc mysql mysql -N | awk '{print 2}'
UserParameter=mysql.buffer_pool_read_requests,echo "show global status where Variable_name='innodb_buffer_pool_read_requests';" | HOME=/opt/zabbix/etc mysql mysql -N | awk '{print
2}'

#InnoDB Buffer利用率(1-Innodb_buffer_pool_pages_free)/Innodb_buffer_pool_pages_data
UserParameter=mysql.buffer_pool_free,echo "show global status where Variable_name='Innodb_buffer_pool_pages_free';" | HOME=/opt/zabbix/etc mysql mysql -N | awk '{print 2}'
UserParameter=mysql.buffer_pool_data,echo "show global status where Variable_name='Innodb_buffer_pool_pages_data';" | HOME=/opt/zabbix/etc mysql mysql -N | awk '{print
2}'
UserParameter=mysql.buffer_pool_hit_rate,echo "select hit_rate/10 from information_schema.innodb_buffer_pool_stats;" | HOME=/opt/zabbix/etc mysql mysql -N |sed -n 1p
UserParameter=mysql.buffer_pool_used_rate,echo "SELECT 100*(1-FREE_BUFFERS/POOL_SIZE) from information_schema.innodb_buffer_pool_stats;" |HOME=/opt/zabbix/etc mysql mysql -N

#脏块百分率
UserParameter=mysql.buffer_pool_dirty,echo "show global status where Variable_name='Innodb_buffer_pool_pages_dirty';" | HOME=/opt/zabbix/etc mysql mysql -N | awk '{print 2}'
UserParameter=mysql.buffer_pool_dirty_rate,echo "SELECT 100*a.VARIABLE_VALUE/b.VARIABLE_VALUE from information_schema.GLOBAL_STATUS a,information_schema.GLOBAL_STATUS b where a.variable_name='INNODB_BUFFER_POOL_BYTES_DIRTY' and b.VARIABLE_NAME='INNODB_BUFFER_POOL_BYTES_DATA';" |HOME=/opt/zabbix/etc mysql mysql -N

#Tmp Table状况(临时表状况)
UserParameter=mysql.TMP,echo "show global status where Variable_name='Created_tmp_tables';" | HOME=/opt/zabbix/etc mysql mysql -N | awk '{print2}'

增加/opt/zabbix/etc/.my.cnf
内容为:

[mysql]
host=localhost
user=root
password=123456
socket=/tmp/mysql.sock

[mysqladmin]
host=localhost
user=root
password=123456
socket=/tmp/mysql.sock

重启客户端mysql模板
模板文件:

One Reply to “zabbix监控MySQL”

  • 若不使用socket模式,则改为:
    [mysql]
    host=localhost
    user=root
    password=123456
    port=3306

    [mysqladmin]
    host=localhost
    user=root
    password=123456
    port=3306

LEAVE A COMMENT

Captcha Code