Mysql and Mysql Slave Monitoring Zabbix Template

Most of available mysql monitoring template uses one user parameter per item. To collect values for 10 items, they will run 10 mysql command (or same command 10 times and get extract desired information from result). These monitoring templates instead get complete result in one item as json content and then uses dependent items to create multiple items on zabbix server

Github links:

• Mysql Template
• Mysql Slave Template

Installation

1. Zabbix client (mysql slave server) host must have jq installed. If not, please install using

sudo apt install jq

2. Add following line in zabbix client configuration. Mysql credentials are stored in /etc/zabbix/.my.cnf for me. Make sure zabbix user has read access to mysql password file


• Myql Template

UserParameter=Mysql.Server-Status, mysql --defaults-file=/etc/zabbix/.my.cnf --defaults-group-suffix=_monitoring -N -e "show global status" | jq -c '. | split("\n")[:-1] | map (split("\t") | {(.[0]) : .[1]} ) | add ' -R -s

• Mysql Slave Template

UserParameter=Mysql.Slave-Status, mysql --defaults-file=/etc/zabbix/.my.cnf --defaults-group-suffix=_monitoring -e "show slave status \G" | sed -e "s/^\s*//g" | sed -e "s/:\s*/:/g" | jq -c '. | split("\n")[1:-1] | map (split(":") | {(.[0]) : .[1]} ) | add ' -R -s



3. Import template into zabbix
4. Apply "Mysql Slave" template to any mysql slave

Template Contents

Mysql Template: Items

Mysql Template Triggers

Mysql Tempalte Graph

Mysql Slave Template Items

Mysql Slave Tempalte Triggers

Mysql Slave Template Graph

Migrating Zabbix 2.4.6 on sqlite to Zabbix 3.0 on mysql

Earlier, we set up Zabbix 2.4.6 with sqlite to just play around with it. But soon we started using it for monitoring various servers and applications. We were obviously aware that sqlite will not scale. We occasionally noticed database lock errors on UI. Finally, we decided to set up one with mysql for production grade scalability. Now, we loved the data we already had accumulated over couple of months. So, we migrated from sqlite to mysql and at same time also upgraded the zabbix version to 3.0. Here is how we did it. 

Steps:

1. Export Sqlite DB: That was fairly simple using sqlite .dump command. It produced sql scripts with ddl and dml statements. 
2. Massage SQL Script: Script created above was still a sqlite script and will not work for mysql. We needed to do following: 
• Used this sed to convert sqlite to mysql. This worked for everything except changing AUTOINCREMENT to AUTO_INCREMENT as sqlite script didn't have CREATE TABLE and AUTOINCREMNET in same line. 
• Used sed to change 'bigint' to 'bigint unsigned' as that is used by zabbix.
  sed -e "s/bigint/bigint unsigned/g"  zabbix.mysql.sql > zabbix_final.sql
• Divided sql script into four parts as it was huge script. First one with all ‘INSERT INTO `history`’ statements. Second with all ‘INSERT INTO `history_uint`’. Third with all Indices and finally fourth with rest of statements. I used simple grep command.
3. Execute SQL Scripts: We executed all scripts with autocommit turned off. And committed after every script.
• First we executed fourth script as that had all ddl scripts for tables.
• Then simultaneously scripts with inserts to history and history_uint table were executed. 
• And finally all indices were created. Few indices failed for me with error - "specified key was too long; max key length is 767 bytes"
4. Install and Upgrade: Install zabbix 3.0 as documented here, except part regarding executing mysql script. Start zabbix server. This will upgrade db too. Check for errors in zabbix server logs. If there is any sql related error, fix it manually and then restart zabbix server.
5. Upgrade / Configure agents: Upgrade agents. This is not mandatory. But good to do. If zabbix server IP is changed, corresponding changes need to be done in agent configuration. Agents need to be restarted.
6. Finally a Duhhhhhhhh: Blob in images table were not usable after migration. So, all rows from this table were deleted. All insert statements into images table were grepped from zabbix mysql 3.0 script and executed on this database. Not a big deal unless one has added any custom images. :)