MySQL Template: which are the commands?

[eric_draven]

Hi guys, do you know which are the commands to associate to these MySQL Items?

MySQL select operations per second
MySQL rollback operations per second
MySQL insert operations per second
MySQL delete operations per second
MySQL commit operations per second
MySQL bytes sent per second
MySQL bytes received per second
MySQL begin operations per second

These items are described in the MySQL Template but I'm not finding any commands to be executed on the monitored MySQL server..

Thank you

[jan.garaj]

You don't need to know any commands.
You have to include only /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf* into your Zabbix agentd config file.

Include=/etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf

* file path of userparameter_mysql.conf depends on your current OS

[eric_draven]

First of all, thank you for your reply!

My /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf contains commands about uptime, slowqueries, qps, etc, but nothing about what I asked in my previous post..Infact, from the Zabbix dashboard I'm receiving errors..

Any suggestions?

[jan.garaj]

You're absolutely right.
Your userparameter_mysql.conf contains one "tricky" line:

UserParameter=mysql.status[*],echo "show global status where Variable_name='$1';" | HOME=/var/lib/zabbix mysql -N | awk '{print $$2}'

and your MySQL template contains items definitions e.g.:
MySQL bytes received per second = mysql.status[Bytes_received]

So Zabbix agent receive request for mysql.status[Bytes_received] and it executes command
echo "show global status where Variable_name='Bytes_received';" | HOME=/var/lib/zabbix mysql -N | awk '{print $2}'
It's smart tricky solution with * - you have just one line of UserParameter, but you can discover every stat information. Official name for this feature is Flexible user parameters.

Investigation of your problem:
- try to execute this command on your server under Zabbix agent account
- or check status for your MySQL items in Zabbix frontend: Configuration -> Hosts -> your server items -> column error (descriptions for error is in title for your error icon)

[eric_draven]

As you've suggested, i've put the string:

UserParameter=mysql.status[*],echo "show global status where Variable_name='Bytes_received';" | HOME=/var/lib/zabbix mysql -N -uroot -ppassword| awk '{print $2}'

in my userparameter_mysql.conf, but it still says "Not supported by Zabbix Agent".

If I run the command manually from the command line, it works...

[eric_draven]

Sorry, I'm mistaken...

The error says "Received value [Bytes_received 336522725] is not suitable for value type [Numeric (float)]"

[jan.garaj]

Change:
UserParameter=mysql.status[*],echo "show global status where Variable_name='Bytes_received';" | HOME=/var/lib/zabbix mysql -N -uroot -ppassword| awk '{print $2}'

UserParameter=mysql.status[*],echo "show global status where Variable_name='Bytes_received';" | HOME=/var/lib/zabbix mysql -N -uroot -ppassword| awk '{print $$2}'

Manual is your fine friend :-)

https://www.zabbix.com/documentation/2.0/manual/config/items/userparameters

To use positional references unaltered, specify double dollar sign - for example, awk '{print $$2}'.

[eric_draven]

Still something wrong

Ok!

But there's still something unclear..If I want to have all items (bytes_sent, bytes_received, Com_begin, Com_delete, etc) how should I create my userparameter_mysql.conf?

If I use this:

UserParameter=mysql.status[Bytes_sent],echo "show global status where Variable_name='Bytes_sent';" | HOME=/var/lib/zabbix mysql -N -uroot -ppassword| awk '{print $$2}'
UserParameter=mysql.status[Bytes_received],echo "show global status where Variable_name='Bytes_received';" | HOME=/var/lib/zabbix mysql -N -uroot -ppassword| awk '{print $$2}'
UserParameter=mysql.status[Com_begin],echo "show global status where Variable_name='Com_begin';" | HOME=/var/lib/zabbix mysql -N -uroot -ppassword| awk '{print $$2}'
UserParameter=mysql.status[Com_delete],echo "show global status where Variable_name='Com_delete';" | HOME=/var/lib/zabbix mysql -N -uroot -ppassword| awk '{print $$2}'

Zabbix says "Not supported by Zabbix Agent". While, if I use this one:

UserParameter=mysql.status[*],echo "show global status where Variable_name='Bytes_sent';" | HOME=/var/lib/zabbix mysql -N -uroot -ppassword| awk '{print $$2}'
UserParameter=mysql.status[*],echo "show global status where Variable_name='Bytes_received';" | HOME=/var/lib/zabbix mysql -N -uroot -ppassword| awk '{print $$2}'
UserParameter=mysql.status[*],echo "show global status where Variable_name='Com_begin';" | HOME=/var/lib/zabbix mysql -N -uroot -ppassword| awk '{print $$2}'
UserParameter=mysql.status[*],echo "show global status where Variable_name='Com_delete';" | HOME=/var/lib/zabbix mysql -N -uroot -ppassword| awk '{print $$2}'

Zabbix dashboard says "Not supported by zabbix agent" and in the agent log file, I can read: " failed to add UserParameter "mysql.status[*]": duplicate key"

What is it wrong?
Please, could you provide me a full example of userparameter_mysql.conf?

Sorry, I am new to Zabbix

[jan.garaj]

Restore original content of /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf

https://www.zabbix.org/websvn/wsvn/zabbix.com/tags/2.2.1/conf/zabbix_agentd/userparameter_mysql.conf

You don't need to define another mysql UserParameters!
Add only your DB credentials to mysql command (-uroot -ppassword).

Then restart agent and disable/enable mysql items in your frontend - column Status (for immediate "rechecking" of mysql items).

[eric_draven]

Yeeeeeeeeeeeeeeeeeeeeahhhhhhhhhh, man!!!!

It woooorks!!!!

Many many many thanks!

[doug1as]

Where change file

Restore original content of /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf

https://www.zabbix.org/websvn/wsvn/zabbix.com/tags/2.2.1/conf/zabbix_agentd/userparameter_mysql.conf

You don't need to define another mysql UserParameters!
Add only your DB credentials to mysql command (-uroot -ppassword).

Then restart agent and disable/enable mysql items in your frontend - column Status (for immediate "rechecking" of mysql items).

Yeeeeeeeeeeeeeeeeeeeeahhhhhhhhhh, man!!!!

It woooorks!!!!

Many many many thanks!

I'm the original file, in which line or where the existing parameters I change / add my login and password bd MySql?