7.jpg
增加key:

#MySQL
UserParameter=mysql.status[],echo "show global status where Variable_name='$1';" | HOME=/opt/zabbix/etc mysql -N | awk '{print $$2}'
UserParameter=mysql.size[
],bash -c 'echo "select sum($(case "$3" in both|"") echo "data_length+index_length";; data|index) echo "$3_length";; free) echo "data_free";; esac)) from information_schema.tables$([[ "$1" = "all" || ! "$1" ]] || echo " where table_schema=\"$1\"")$([[ "$2" = "all" || ! "$2" ]] || echo "and table_name=\"$2\"");" | HOME=/opt/zabbix/etc mysql -N'
UserParameter=mysql.ping,echo "SELECT 1 from dual;" | HOME=/opt/zabbix/etc mysql -N
UserParameter=mysql.version,echo "SELECT VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES where VARIABLE_NAME='INNODB_VERSION';" | HOME=/opt/zabbix/etc mysql -N

#processlist
UserParameter=mysql.processes,echo "select count() from information_schema.processlist;" | HOME=/opt/zabbix/etc mysql -N
UserParameter=mysql.process.sleep,echo "select count(
) from information_schema.processlist where state='';" | HOME=/opt/zabbix/etc mysql -N
UserParameter=mysql.process.active,echo "select count(*) from information_schema.processlist where state<>'';" | 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 '{print $2}'

增加/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模板
模板文件:

1 对 “zabbix监控MySQL”的想法;

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

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

发表评论

您的电子邮箱地址不会被公开。

Captcha Code