Skip to content
Zabbix template for MySQL with replication
Branch: master
Clone or download

Latest commit

Latest commit ce6c9da Jan 17, 2019

Files

Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
Screenshots Screenshot replaced Jan 17, 2019
LICENSE
README.md
zbx_mysql_with_replication.xml

README.md

Zabbix Template for MySQL with replication

  • Based on the official Zabbix MySQL template
  • Uses Zabbix agent user parameters (passive checks)
  • The "{$DB_NAME}" macro and "mysql.size[{$DB_NAME}]" key can be used to monitor all (or specific) DB size
  • Template has several triggers. Zabbix will generate a problem if MySQL or slave isn't running

Configuration

  1. Install and configure Zabbix agent (https://www.zabbix.com/download)

  2. Backup and remove existing user parameters in the file

> /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf
  1. Provide MySQL credentials and set correct permissions (example for Ubuntu)
cat /etc/mysql/debian.cnf
[client]
user     = debian-sys-maint
password = Password^$76u75

chmod 640 /etc/mysql/debian.cnf
chown root:zabbix /etc/mysql/debian.cnf
  1. Add required user user parameters. Mind, it is an example. You can use default user parameters with minor changes to get the same information.
# Flexible parameter to determine database or table size. On the frontend side, use keys like mysql.size[zabbix,history,data].
# Key syntax is mysql.size[<database>,<table>,<type>]. # Database may be a database name or "all". Default is "all". # Table may be a table name or "all". Default is "all". # Returns value in bytes.
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\"");" | /usr/bin/mysql --defaults-extra-file=/etc/mysql/debian.cnf -N'

# MySQL version and status
UserParameter=mysql.ping,mysqladmin --defaults-extra-file=/etc/mysql/debian.cnf ping | grep -c alive
UserParameter=mysql.version,mysql --defaults-extra-file=/etc/mysql/debian.cnf -V

# Flexible parameter to grab global variables. On the frontend side, use keys like mysql.status[Com_insert].
UserParameter=mysql.extended_status[*],/usr/bin/mysqladmin --defaults-extra-file=/etc/mysql/debian.cnf extended-status variables |awk {'print $$2"| "$$4'} | grep "$1|" | awk {'print $$2'}

# Replication monitoring
UserParameter=mysql.slave_lagging,/usr/bin/mysql --defaults-extra-file=/etc/mysql/debian.cnf -Bse "show slave status\\G" | grep Seconds_Behind_Master | awk '{print $$2}' | sed -e 's/^NULL$/-1/; s/![0-9]+/-1/' | awk '{if($2 ~ /d/) {print 100} else {print $2}}'

UserParameter=mysql.slave_status[*],/usr/bin/mysql --defaults-extra-file=/etc/mysql/debian.cnf -e "show slave status\\G"|grep -i $1 | awk '{print $$2}'
  1. Restart Zabbix agent and test commands
systemctl restart zabbix-agent

zabbix_agentd -t "mysql.slave_status[Slave_SQL_Running]"
zabbix_agentd -t "mysql.extended_status[Com_delete]"
  1. Import template, review and link it to a MySQL server

  2. Result latest_data

You can’t perform that action at this time.