Agent2 MySQL Template

[reedacus25]

Maybe I am just naive or unexperienced, but I cannot for the life of my figure out the DSN structure for the Agent2 MySQL plugin.

After upgrading from 4.4 to 5.0, the agent2 would not start because of overlapping mysql.something keys, from the agent2.d/mysql.conf file when using the old official mysql template.
All was working reading from the .my.cnf in the zabbix home dir.

I've imported the new Template for Agent2-MySQL from here: https://git.zabbix.com/projects/ZBX/...b/mysql_agent2

But the documentation and error messages could not be more vague:

Name            Description                                                                   Default
{$MYSQL.DSN}    System data source name such as <protocol(host:port or /path/to/socket)/>.    <Put your DSN>

No matter what combination of things I try, I can't figure out what it wants.

localhost:3306 =

• The first parameter is not URI or session

mysql(localhost:3306) =

• parse mysql(localhost:3306): first path segment in URL cannot contain colon

<mysql://localhost:3306>

• parse <mysql://localhost:3306>: first path segment in URL cannot contain colon

No matter what I try, I cannot get this to function.

Even trying to replicate the description verbosely, it doesn't work!
<protocol(hostort or /path/to/socket)/>
<mysql(localhost:3306)/>

• parse <mysql(localhost:3306)/>: first path segment in URL cannot contain colon

Hopefully someone can save my sanity here.

[aib]

From https://www.php.net/manual/en/ref.pd...connection.php

More complete examples:

mysql:host=localhost;port=3307;dbname=testdb
mysql:unix_socket=/tmp/mysql.sock;dbname=testdb

Or check this link for more examples

https://www.connectionstrings.com/mysql/

[reedacus25]

Sadly the above examples did not provide any luck.
FWIW, the agent2 is written in golang, but the front end is obviously PHP.

• mysql:host=localhost;port=3306
  • The first parameter is not URI or session
• mysql:host=127.0.0.1;port=3306
  • The first parameter is not URI or session
• <mysql:host=localhost;port=3306>
  • parse <mysql:host=localhost;port=3306>: first path segment in URL cannot contain colon
• <mysql:host=127.0.0.1;port=3306>
  • parse <mysql:host=127.0.0.1;port=3306>: first path segment in URL cannot contain colon

I even tried mimicking their example with angle brackets (as above), parentheses, trailing slash, and hopefully as many permutations as possible, all to no avail.

• <mysql:host=127.0.0.1;port=3306/>
  • parse <mysql:host=127.0.0.1;port=3306/>: first path segment in URL cannot contain colon
• <mysqlhost=127.0.0.1;port=3306)/>
  • parse <mysqlhost=127.0.0.1;port=3306)/>: first path segment in URL cannot contain colon
• <mysqlhost=127.0.0.1;port=3306)>
  • parse <mysqlhost=127.0.0.1;port=3306)>: first path segment in URL cannot contain colon

So now, in a sick twist of fate, I accidentally hit test on the item check with no value in the DSN field, and...... it works.

So... yay?
This seems so precariously under-documented, but I guess that's what guinea pigs are for?

Hopefully someone else will find this information useful, and save someone else the time I invested in debugging this.
NOTICE TO FUTURE ANSWER SEEKERS:
When setting host macros to use with the agent2 mysql template, use the same user and password macros from your old .my.cnf, and leave the DSN macro blank.

[Atsushi]

Try the following two formats for the value of the macro {$MYSQL.DSN}.

unix:/var/lib/mysql/mysql.sock
tcp://127.0.0.1:3306

Please change the socket path and IP address and port number for MySQL according to the environment.

[reedacus25]

Try the following two formats for the value of the macro {$MYSQL.DSN}.

unix:/var/lib/mysql/mysql.sock
tcp://127.0.0.1:3306

I can confirm that both of these appear to work for me.

So at least we now have "official" answers.
Again, this is the information that should be in the documentation.

[HoracioDos]

Hello
I have the same problem and I can't make it work.
zabbix_agent2.conf file looks like this:

Plugins.Mysql.Sessions.MySQL1.Uri=tcp://127.0.0.1:3306,tcp://192.168.xx.xx,unix:/var/run/mysql.sock
Plugins.Mysql.Sessions.MySQL1.User=myuser
Plugins.Mysql.Sessions.MySQL1.Password=mypwd

I've applied mysql Agent2 template to the host where mysql lives and I've created mysql user that it is needed.

CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypwd';
GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'myuser'@'%';

Thanks in advanced!

[TheImmortal]

I too have issues with logs such as these:

73790:20201103:132551.221 item "HOSTNAME:mysql.get_status_variables["{$MYSQL.DSN}","{$MYSQL.USER}","{$MYSQL.PASSWO RD}"]" became supported
73789:20201103:132651.264 item "HOSTNAME:mysql.get_status_variables["{$MYSQL.DSN}","{$MYSQL.USER}","{$MYSQL.PASSWO RD}"]" became not supported: invalid connection

Then on graphs I can see a couple of entries (so it does work for just a tiny bit), then it craps. Tried multiple approaches: all blank in macros and the entries added to agent2.conf, unix:/var/lib/mysql/mysql.sock + user/pass in the macros and the conf entries running, conf entries commented, and so on. Can't get it to properly run no matter what.

[HoracioDos]

I too have issues with logs such as these:



Then on graphs I can see a couple of entries (so it does work for just a tiny bit), then it craps. Tried multiple approaches: all blank in macros and the entries added to agent2.conf, unix:/var/lib/mysql/mysql.sock + user/pass in the macros and the conf entries running, conf entries commented, and so on. Can't get it to properly run no matter what.

I don't know what happened in your case but I will tell you how I made it work yestesday.

1) I installed the latest zabbix agent2 version 5.0.5-2 for ubuntu 20.04 Focal. You can download linux packages from the officlal repo. https://repo.zabbix.com/zabbix/5.0/u...main/z/zabbix/

2) Edit /etc/zabbix_agent2.conf . Uncomment this line and set proper URI:

Plugins.Mysql.Sessions.MySQL1.Uri=tcp://127.0.0.1:3306,tcp://192.168.XXX.XXX:3306,unix:/var/run/mysql.sock

Replace XXX with proper MySQL Server ip address

3) Create Mysql Auxiliar User for monitoring. Execute

sudo mysql -u root

and create the user and set pwd. Any name you want will work. Replace '<password> with your own.

CREATE USER 'zbx_monitor'@'%' IDENTIFIED BY '<password>';
GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'%';
FLUSH PRIVILEGES;

4) Set Macro values in zabbix server. Set mysql user and password that you created before and DON'T FORGET TO CLEAR {MYSQL.DSN} DEFAULT VALUE. it should look like the picture. I spent hours due to this bug/lack of clear documentation.


I hope this can help you!!!

[HoracioDos]

I deleted my own comment.

[TheImmortal]

I've already done that (im on CE8 though) to no avail.

[HoracioDos]

I've already done that (im on CE8 though) to no avail.

Is your zabbix server Version 5.0 LTS? Also check my steps carefully perhaps you will find some small difference.

[TheImmortal]

I now have this: http://prntscr.com/vdcl9x - which is equally intriguing.
The agent i'm using is the latest stable from the zabbix repo - zabbix-agent2-5.2.0-1.el7.x86_64 and zabbix-web-5.2.0-1.el8.noarch for the server setup

[HoracioDos]

I now have this: http://prntscr.com/vdcl9x - which is equally intriguing.
The agent i'm using is the latest stable from the zabbix repo - zabbix-agent2-5.2.0-1.el7.x86_64 and zabbix-web-5.2.0-1.el8.noarch for the server setup

Hello. You are using agent2 version 5.2. I use version 5.0.5-2 acording to server version 5.0 LTS. I think you should not mix agents and server versions. If you have the latest server version 5.2 installed it would be ok.

[TheImmortal]

The topic is about agent2, my entire point is about agent2. Thank you for pointing it out, but it's what i'm saying - i can't make the 'template mysql db for agent2' work with server 5.2, so that's not ok.
It should be (duh), but it's not., hence these posts

LE: I went back for the 10th time to read the manual. It says this:

If you had set URI in the {$MYSQL.DSN}, please define the user name and password in host macros ({$MYSQL.USER} and {$MYSQL.PASSWORD}).
Leave macros {$MYSQL.USER} and {$MYSQL.PASSWORD} empty if you use a session name. Set the user name and password in the Plugins.Mysql.<...> section of your Zabbix agent 2 configuration file.

So i do so. I empty DSN in the macros, i empty the username and password and i do this:

Plugins.Mysql.Sessions.MySQL.Uri=unix:/var/lib/mysql/mysql.sock
Plugins.Mysql.Sessions.MySQL.User=MYSQLUSER
Plugins.Mysql.Sessions.MySQL.Password=MYSQLUSERPAS S

What happens next? KAPOW!

discovery rule "HOSTNAME:mysql.replication.discovery["{$MYSQL.DSN}","{$MYSQL.USER}","{$MYSQL.PASSWO RD}"]" became not supported: Error 1045: Access denied for user 'root'@'localhost' (using password: NO)

Come on! :/

[HoracioDos]

The topic is about agent2, my entire point is about agent2. Thank you for pointing it out, but it's what i'm saying - i can't make the 'template mysql db for agent2' work with server 5.2, so that's not ok.
It should be (duh), but it's not., hence these posts

LE: I went back for the 10th time to read the manual. It says this:


So i do so. I empty DSN in the macros, i empty the username and password and i do this:




What happens next? KAPOW!


Come on! :/

I use agent2 too but in 5.0 LTS. You could try to monitor mysql using the old way with the regular agent. Follow this https://www.zabbix.com/integrations/mysql#tabfficial1