MySQL monitoring troubles

[Sergey]

Here is a piece of my client config:

### Set of parameter for monitoring MySQL server (v3.23.42 and later)
### Change -u and add -p if required
#UserParameter=mysql.ping,mysqladmin -uroot ping|grep alive|wc -l
#UserParameter=mysql.uptime,mysqladmin -uroot status|cut f2 -d”:”|cut -f1 -d”T”
#UserParameter=mysql.threads.,mysqladmin -uroot status|cut f3 -d”:”|cut -f1 -d”Q”
#UserParameter=mysql.questions.,mysqladmin -uroot status|cut f4 -d”:”|cut -f1 -d”S”
#UserParameter=mysql.slowqueries.,mysqladmin -uroot status|cut f5 -d”:”|cut -f1 -d”O”
#UserParameter=mysql.qps.,mysqladmin -uroot status|cut -f9 d”:”
#UserParameter=version.mysql.,mysql -V

And here is a piece of config from manual:

### Set of parameter for monitoring MySQL server (v3.23.42 and later)
### Change -u and add -p if required
#UserParameter=mysql[ping],mysqladmin -uroot ping|grep alive|wc -l
#UserParameter=mysql[uptime],mysqladmin -uroot status|cut f2 -d”:”|cut -f1 -d”T”
#UserParameter=mysql[threads],mysqladmin -uroot status|cut f3 -d”:”|cut -f1 -d”Q”
#UserParameter=mysql[questions],mysqladmin -uroot status|cut f4 -d”:”|cut -f1 -d”S”
#UserParameter=mysql[slowqueries],mysqladmin -uroot status|cut f5 -d”:”|cut -f1 -d”O”
#UserParameter=mysql[qps],mysqladmin -uroot status|cut -f9 d”:”
#UserParameter=version[mysql],mysql -V

If I write mysql[ping] instead mysql.ping than zabbix_agent says: 'Warning: Can't add user specify key "mysql[ping]" incorrect key!' If I leave mysql.ping, than zabbix_get -s<IP> -p<port> -k"mysql.ping" works, but when I add a new action with this key it will say "Incorrect trigger expression. [{MyHost:mysql.ping}=0]".
If somebody was confront with this difficult, please, write about!

[Calimero]

The right key to use is what is in the zabbix_agentd.conf file.

So in your case it must be "mysql.ping" (you need to uncomment that line in you conf file and make sure the mysql user specified in the file has access to mysql, of course).

Regarding the trigger, there's a syntax problem in your example.

Triggers are defined as follows:
{hostname:key.function(function_arg)}#1

So with mysql.ping it would be like:
{MyHost:mysql.ping.last(0)}#1

When combining zabbix a zabbix userparameter item (with dynamic args) and MySQL's status variables, you can get some interesting info: check whether replication slave is running, queries per second by type (how many selects, inserts, ...), ...

[Sergey]

Sorry, You are right: it was a syntax problem...
Thanks a lot!

[nieuwhier]

This is an old one... but I have a question about the user...

I see user root with -u used. But on my servers the mysql user root has a password connected to it. Don't you ?

What user and what rights should I use for mysqladmin to work but not to compromise the mysql security ?

[Calimero]

mysqladmin ping and mysqladmin status can be used with the lowest privilege in MySQL: "USAGE".

The USAGE privilege allows the user to connect and ... that's it. No access to any database. But you can get status and configuration variables with SHOW (GLOBAL STATUS|VARIABLES) LIKE '...' which is interesting for monitoring.

GRANT USAGE ON *.* TO 'zabbix'@'localhost';

or... if you insist on specifying a password:
GRANT USAGE ON *.* TO 'zabbix'@'localhost' IDENTIFIED BY 'my password';

[vidom]

It is possible to use Checksum MySQL

[tranformer66102]

Hi

« By scaling-out with MySQL, we were recently able to add 3 million new users in less than a month. We chose MySQL Enterprise for two reasons: first, access to super-sharp support technicians who clearly know what they're doing and can help us overcome our toughest problems. Second, the new MySQL Enterprise monitoring tools give us unprecedented views into real-time and historical DB performance -to troubleshoot problems, balance workloads, and predict future resource requirements. »