zarplata / zabbix-agent-extension-mysql

Zabbix extension for monitoring mysql.

zabbix-agent-extension-mysql

zabbix-agent-extension-mysql - this extension for monitoring mysql.

Supported features

This extension obtains stats of three types:

Global stats

• Aborted clients
• Aborted connects
• Bytes received
• Bytes sent
• Com begin
• Com commit
• Com delete
• Com insert
• Com rollback
• Com select
• Com update
• Innodb rows deleted
• Innodb rows inserted
• Innodb rows read
• Innodb rows updated
• Queries
• Slow queries
• Threads running
• Uptime

Process stats

• Processlist count
• Max query time

Galera stats

• Galera cluster size
• Galera cluster state uuid
• Galera cluster status
• Galera connected
• Galera internal state EVS Protocol
• Galera cluster gcom uuid
• Galera local state
• Galera local state comment
• Galera local state uuid
• Galera protocol version
• Galera provider name
• Galera ready

Slave stats

• Mysql io running
• Mysql sql running
• Mysql Seconds behind master
• Mysql master host
• Mysql master port

Installation

Manual build

# Building
git clone https://github.com/zarplata/zabbix-agent-extension-mysql.git
cd zabbix-agent-extension-mysql
make

#Installing
make install

# By default, binary installs into /usr/bin/ and zabbix config in /etc/zabbix/zabbix_agentd.conf.d/ but,
# you may manually copy binary to your executable path and zabbix config to specific include directory

Arch Linux package

# Building
git clone https://github.com/zarplata/zabbix-agent-extension-mysql.git
git checkout pkgbuild

makepkg

#Installing
pacman -U *.tar.xz

Requirements

Add mysql users:

GRANT REPLICATION CLIENT,PROCESS ON *.* TO 'zabbix'@'127.0.0.1'  IDENTIFIED BY 'zabbix';

Dependencies

zabbix-agent-extension-mysql requires zabbix-agent v3.4+ to run.

Zabbix configuration

In order to start getting metrics, it is enough to import template and attach it to monitored node.

WARNING: You must define macro with name - {$ZABBIX_SERVER_IP} in global or local (template) scope with IP address of zabbix server.

zabbix-agent-extension-mysql's Issues

mysql.wsrep_evs_repl_latency.average

Good morning,
i have one issue with 3 Items:
mysql.wsrep_evs_repl_latency.average.[galera]
mysql.wsrep_evs_repl_latency.maximum.[galera]
mysql.wsrep_evs_repl_latency.minimum.[galera]
My system is Debian 10 ,MariaDB 10.4 with Galera Cluster (Active-Active-Active)
In log file of my agent no problem but form the log file of the server i see:

1950:20200605:112524.683 item "MY-HOST-SERVER:mysql.wsrep_evs_repl_latency.average.[galera]" became not supported: Preprocessing failed for: 0/0/0/0/0

1. Failed: cannot perform regular expression "(^\d+.\d+)/(\d+.\d+)/(\d+.\d+)" match for value of type "string": pattern does not match

No data from zabbix trapper items

Hello the template it's working because in latest data I can get Galera stats and the returned value it's OK but I don't get any data with the items configured as Zabbix Trapper.
What can be wrong?
Thank you very much, regards.

missing port macro?

hello the author,

have you forgotten about {$ZABBIX_SERVER_PORT} ??

user params in zabbix-agent-extension-mysql.conf take 6 parameters but only 5 are passed ( the zabbix port is missing )

agent's debug shows
31028:20190716:104201.278 EXECUTE_STR() command:' /usr/bin/zabbix-agent-extension-mysql -z 172.25.1.12 -p localhost:3306 -m "secretpass" --user zabbixaccount --pass process --type ' len:109 cmd_result:'--type requires argu'

Wrong plugins folder for zabbix agent

Hi,

In the Makefile, the path for zabbix_agentd plugins directory line 42 is not correct.

It's:
cp ${CONFIG} /etc/zabbix/zabbix_agentd.conf.d/${CONFIG}

But on linux (Ubuntu 18.04 in my case), it's :
cp ${CONFIG} /etc/zabbix/zabbix_agentd.d/${CONFIG}

Thanks for the work ;)

Invalid tag during import the temlate

Hi,

I've tried to import the template, but got the error:
Invalid tag "/zabbix_export/templates/template(1)/discovery_rules/discovery_rule(1)/item_prototypes/item_prototype(11)": unexpected tag "timeout".

I have zabbix server 3.4.15

zabbix-agent-extension-mysql and MariaDB 10.4.13 compatibility

Seems that zabbix-agent-extension-mysql doesn't work correctly with MariaDB 10.4.
zabbix-agent-extension-mysql always returns "OK", even if replication is broken or in down state.

zabbix-agent-mysql version: 20200228.15_04e8ed1
MariaDB version: 10.4.13
OS: Debian 10 buster

mysql -BNe "show grants for zabbix@localhost;"
GRANT PROCESS, SHOW DATABASES, REPLICATION CLIENT, SHOW VIEW ON . TO zabbix@localhost IDENTIFIED BY PASSWORD '****'

mysql -e "show slave status\G" | grep Running
Slave_IO_Running: No
Slave_SQL_Running: No

/etc/zabbix/bin/zabbix-agent-mysql -n unix -m /tmp/mysql.sock --user zabbix --pass **** --discovery
{"data":[{"{#TYPE}":"galera"},{"{#TYPE}":"slave"}]}

/etc/zabbix/bin/zabbix-agent-mysql -n unix -m /tmp/mysql.sock --user zabbix --pass **** --type slave
OK

Mysql query max time

Hi, this alert is continuously triggered because it takes the value of master-slave related processes. Event_scheduler and system user with command daemon and connect. I believe this should be skipped in this check?

build error on CentOS 8.2.2004

I'm getting the error when I try to build on CentOS 8.2.2004:

Fetch dependencies
dep ensure -v
make: dep: Command not found
make: *** [Makefile:37: deps] Error 127

[Warning] Aborted connection to db: 'unconnected' user: 'unauthenticated' host: '127.0.0.1' (This connection closed normally without authentication)

Hi.
I am running "zabbix-agent-extension-mysql" on galera (mariadb 10.4).
What cause the warning log as like this issue's title?
I guess It seems to be a ping test for healthcheck.
If i remove "zabbix-agent-extension-mysql", the warning log disapper.
Is there a way to disable the warning log while using "zabbix-agent-extension-mysql".

zabbix-agent-extension-mysql/vendor/src: No such file or directory

I'm trying to install, but I get the following during make on CentOS 7.4.

[root@zabbix-testing ~]# git clone https://github.com/zarplata/zabbix-agent-extension-mysql.git
Cloning into 'zabbix-agent-extension-mysql'...
remote: Counting objects: 22, done.
remote: Compressing objects: 100% (20/20), done.
remote: Total 22 (delta 1), reused 20 (delta 1), pack-reused 0
Unpacking objects: 100% (22/22), done.
[root@zabbix-testing ~]# cd zabbix-agent-extension-mysql
[root@zabbix-testing zabbix-agent-extension-mysql]# make
Clean dependencies
rm -rf vendor/*
Clean builded binaries
rm -rf .out/
Done
Fetch dependencies
git submodule update --init
Build
ln -s /root/zabbix-agent-extension-mysql/vendor/ /root/zabbix-agent-extension-mysql/vendor/src
ln: failed to create symbolic link ‘/root/zabbix-agent-extension-mysql/vendor/src’: No such file or directory
make: *** [build] Error 1

Thanks.

Unsupported item key for "mysql.discovery,,,slave"

Hi

I have successfully installed your MySQL Template (import xml, global macros, bin, agent config)
So now I have
Unsupported item key. for mysql.discovery[{$MYSQL_DSN},{$MYSQL_USER},{$MYSQL_PASS},slave]

How I can fix it?
Thanx and best regards.

---

Zabbix server/agent: 3.4.10
MySQL: 5.7.22
Ubuntu: 18.04

connection refused

Hello, zabbix-agent-extension-mysql no connect my mariaDB:
can't do query SHOW GLOBAL STATUS.
└─ dial tcp [::1]:3306: connect: connection refused
or:
can't get galera stats.
└─ can't do query SHOW GLOBAL STATUS LIKE 'wsrep_%'.
└─ dial tcp 127.0.0.1:3306: connect: connection refused

Local user true authorize on mysql client.

fqnd not supported for zabbix sender

send hostname instead fqdn

build error on Ubuntu 18.04: recipe for target 'build' failed

When I try to make, I get the following error on Ubuntu 18.04:
/bin/sh: 1: go: not found Makefile:27: recipe for target 'build' failed make: *** [build] Error 127

Agent doesn't send any data except number of processes

I already enabled logging to see what might be the issue, and it seems like the agent just doesn't send anything. (I replaced all ips and passwords with placeholders, the correct values are used on the server)

5552:20190401:190058.216 EXECUTE_STR() command:' /usr/bin/zabbix-agent-extension-mysql -z serverip -m "mysqlip:3306" --user mysqluser --pass mysqlpass --type global' len:2 cmd_result:'OK'
5552:20190401:190058.216 Sending back [OK]
5552:20190401:190058.216 __zbx_zbx_setproctitle() title:'listener #2 [waiting for connection]'
5558:20190401:190059.164 In send_buffer() host:'serverip' port:serverport entries:0/100
5558:20190401:190059.165 End of send_buffer():SUCCEED

When I execute the command manually:

/usr/bin/zabbix-agent-extension-mysql -z serverip -m "mysqlip:3306" --user mysqluser --pass mysqlpass --type mysql

It just returns:

OK

I expected it to return all the status data next to "OK". Is that not a correct assumption?
In the web interface I see a value for mysql processes, but everything else shows "no data".
Could you give me a pointer on where to debug further?

No Slave data in Zabbix

Hello,

I've installed everything according to the doc, but I don't see any data in Zabbix for this machine. The only stats number which I see is Number of running Mysql, which is 2 apparently.

When running /usr/bin/zabbix-agent-extension-mysql -m 127.0.0.1:3306 --user zabbix --pass zabbix --discovery, I get:

{"data":[{"{#TYPE}":"galera"},{"{#TYPE}":"slave"}]}

And when running nc -l -p 10051 and /usr/bin/zabbix-agent-extension-mysql --type mysql -z 127.0.0.1:

ZBXD▒{"request":"sender data","data":[{"host":"sentry","key":"mysql.Aborted_clients.[mysql]","value":"4","clock":1531198605},{"host":"sentry","key":"mysql.Aborted_connects.[mysql]","value":"36544","clock":1531198605},{"host":"sentry","key":"mysql.Innodb_rows_deleted.[mysql]","value":"10576602","clock":1531198605},{"host":"sentry","key":"mysql.Innodb_rows_inserted.[mysql]","value":"12592798","clock":1531198605},{"host":"sentry","key":"mysql.Innodb_rows_read.[mysql]","value":"1080566589","clock":1531198605},{"host":"sentry","key":"mysql.Innodb_rows_updated.[mysql]","value":"190552776","clock":1531198605},{"host":"sentry","key":"mysql.Com_begin.[mysql]","value":"246001943","clock":1531198605},{"host":"sentry","key":"mysql.Com_commit.[mysql]","value":"246001943","clock":1531198605},{"host":"sentry","key":"mysql.Com_rollback.[mysql]","value":"0","clock":1531198605},{"host":"sentry","key":"mysql.Com_delete.[mysql]","value":"19388949","clock":1531198605},{"host":"sentry","key":"mysql.Com_insert.[mysql]","value":"14701782","clock":1531198605},{"host":"sentry","key":"mysql.Com_select.[mysql]","value":"636614","clock":1531198605},{"host":"sentry","key":"mysql.Com_update.[mysql]","value":"211902306","clock":1531198605},{"host":"sentry","key":"mysql.Queries.[mysql]","value":"611657481","clock":1531198605},{"host":"sentry","key":"mysql.Slow_queries.[mysql]","value":"0","clock":1531198605},{"host":"sentry","key":"mysql.Uptime.[mysql]","value":"10691336","clock":1531198605},{"host":"sentry","key":"mysql.Threads_running.[mysql]","value":"2","clock":1531198605},{"host":"sentry","key":"mysql.Bytes_received.[mysql]","value":"742709057113","clock":1531198605},{"host":"sentry","key":"mysql.Bytes_sent.[mysql]","value":"748511737330","clock":1531198605}],"clock":1531198605}

There is a lot of data in there, but why don't I see those numbers in Zabbix?