Friday, March 22, 2019

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:



Installation

  1. Zabbix client (mysql slave server) host must have jq installed. If not, please install using
    2. 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


Posted by at
Labels: , , , ,

36 comments:

  1. Priyank PujaraApril 16, 2019 at 3:19 PM

    Hi Nitin,

    I have tried your MySQL server template but it gives me error in zabbxi portal:

    error: split is not defined
    . | split("\n")[:-1] | map (split("\t") | {(.[0]) : .[1]} ) | add
    ^^^^^
    error: split is not defined
    . | split("\n")[:-1] | map (split("\t") | {(.[0]) : .[1]} ) | add
    ^^^^^
    2 compile errors

    Can you please suggest what should I do?

    ReplyDelete
    Replies
    1. Nitin JainApril 17, 2019 at 9:25 AM

      Hi Priyank. Can you please check version of jq on your linux server? You can check this by running command 'jq --version'. If it is less than 1.5.1, please upgrade to latest version.

      Delete
  2. thinkdifferblogspotsMay 29, 2019 at 2:27 AM

    Hi Nitin, Above seems interesting and getting the json data.
    But not able to get items details in zabbix UI. May I know where is this JSON Path(preprocessing) storing the json data which is eventually queried by jq?

    ReplyDelete
    Replies
    1. Nitin JainMay 29, 2019 at 12:09 PM

      It is stored in item mysql_server_status or mysql_slave_status. Please check if your zabbix client is configured to work as active client.

      Delete
  3. thinkdifferblogspotsJune 2, 2019 at 11:10 PM

    Thanks for the response, configuring zabbix client active made it working.

    ReplyDelete
  4. Nitin JainJune 4, 2019 at 1:38 PM

    Glad to hear that. Good luck.

    ReplyDelete
  5. UnknownJune 18, 2019 at 6:36 PM

    Hi Nitin,

    My zabbix client configuration is as following:-

    UserParameter=mysql_server_status, mysql --defaults-file=/var/lib/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

    and with above configuration "zabbix_agent -t mysql_server_status" is working fine and giving expected result. But zabbix server GUI is not showing any data. Please help.

    ReplyDelete
    Replies
    1. Nitin JainJune 25, 2019 at 1:52 PM

      Is your zabbix configured to work as active agent? Please ensure ServerActive and Hostname is defined in zabbix agent configuration.

      Delete
    2. Dominik DrążykFebruary 26, 2020 at 4:01 AM

      I think you need to change the beginning: UserParameter=mysql_server_status to UserParameter=Mysql.Server-Status
      Otherwise you'll get errors about unsupported item.

      Delete
  6. OlexandrAugust 5, 2019 at 2:25 PM

    Hello,
    I have issue when importing MySQL Slave template. I have opened issue at Github:
    https://github.com/nitzien/zabbix-misc/issues/4

    ReplyDelete
  7. UnknownSeptember 4, 2019 at 1:49 PM

    Hi.
    the thing is that, my server-status is on https, hence i keep getting this response


    400 Bad Request
    Bad Request
    Your browser sent a request that this server could not understand.

    Reason: You're speaking plain HTTP to an SSL-enabled server port.
    Instead use the HTTPS scheme to access this URL, please.

    Apache/2.4.18 (Ubuntu) Server at server.com Port 443


    How can i resolve this

    any idea?

    ReplyDelete
    Replies
    1. UnknownSeptember 4, 2019 at 2:22 PM

      sorry commented in the wrong window

      Delete
    2. Nitin JainSeptember 27, 2019 at 3:07 PM

      This involve communication only between agent and zabbix. I do not understand SSL issue that you are facing here. Please elaborate more on your problem.

      Delete
  8. UnknownSeptember 4, 2019 at 2:22 PM

    sorry commented in the wrong window

    ReplyDelete
  9. AnonymousSeptember 27, 2019 at 12:39 PM

    Hi,

    I'm getting this error after installing using your instructions.

    Details Cannot send request

    Cannot send request: wrong item type.


    Kindly advise how to troubleshoot this error. thank you!

    ReplyDelete
    Replies
    1. Nitin JainSeptember 27, 2019 at 3:05 PM

      Can you please post where are you seeing this error and logs if you have them?

      Delete
    2. AnonymousOctober 3, 2019 at 8:53 AM

      Hi Mr. Nitin. Thank you for your reply. I'm getting this error after editing userparameter_mysql.conf on my slave server:

      :/etc/zabbix/zabbix_agentd.d]$ cat userparameter_mysql.conf | grep slave
      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



      UPON RESTART:
      journalctl -xn

      Oct 03 11:21:54 KPGACSDBPRD zabbix_agentd[22711]: zabbix_agentd [22711]: ERROR: cannot add user parameter "Mysql.Slave-Status, mysql --defaults-fi
      Oct 03 11:21:54 KPGACSDBPRD systemd[1]: zabbix-agent.service: control process exited, code=exited status=1


      Thank you in advance.

      Delete
  10. AnonymousOctober 3, 2019 at 9:07 AM

    OK, I managed to make the client run now. items in the templates are already in the portal. But I'm getting this error


    25728:20191003:113441.206 discovery rule "KPGACSDBPRD:0" became not supported: Unsupported item key.
    25729:20191003:113441.571 discovery rule "KPGACSDBPRD:1" became not supported: Unsupported item key.

    Does this require discovery rules?

    Thank you in advance

    ReplyDelete
    Replies
    1. Nitin JainOctober 7, 2019 at 4:33 PM

      I don't understand these errors. Is there any other item configured for this host which may be causing this?

      Delete
  11. Nitin JainOctober 7, 2019 at 4:33 PM

    Adding comment so that I get a notification. :)

    ReplyDelete
  12. Al BondOctober 19, 2019 at 4:30 PM

    Hi. I have tried your MySQL server template but it gives me error.
    zabbix_agentd -t Mysql.Server-Status
    Mysql.Server-Status [t|mysql: [ERROR] unknown variable 'defaults-group-suffix=_monitoring'

    ReplyDelete
    Replies
    1. Nitin JainNovember 5, 2019 at 9:09 AM

      This must be because you don't have mysql client config my.cnf. Please check that.

      Delete
  13. Wahe y RakelOctober 23, 2019 at 3:36 PM

    This comment has been removed by the author.

    ReplyDelete
  14. UnknownNovember 12, 2019 at 4:30 PM

    Hi Nitin, mysql slave data not coming where server data coming in graph. All configuration are as same as you mentioned in your blog. I used both mysql & mysql slave in zabbix client configuration and both template are attached with mysql master & master replication server model

    ReplyDelete
    Replies
    1. Nitin JainDecember 6, 2019 at 10:29 AM

      Can you please check if you have set up your client for active mode?

      Delete
  15. 1337rootNovember 20, 2019 at 4:11 PM

    Hello,

    I use mysqldump script with --dump-slave option. This option stop SQL Theard for dump and script triggering useful notifications. Can u know, how disable trigger when mysqldump is running?

    ReplyDelete
    Replies
    1. Nitin JainDecember 6, 2019 at 10:28 AM

      You may set up maintenance window for that time period in Zabbix.

      Delete
  16. martin vellyNovember 24, 2019 at 12:45 PM

    This comment has been removed by a blog administrator.

    ReplyDelete
  17. rudranshjha1November 28, 2019 at 1:05 AM

    This comment has been removed by a blog administrator.

    ReplyDelete
  18. DCDecember 11, 2019 at 2:23 AM

    hello Nitin, I have zabbix 4.4.3 and the template does not work, when seeing the data it shows me an error and says no data.

    ReplyDelete
    Replies
    1. Nitin JainDecember 11, 2019 at 11:36 AM

      Is your zabbix configured to work as active agent? Please ensure ServerActive and Hostname is defined in zabbix agent configuration.

      Also, can you please check zabbix agent logs to check what is error?

      Delete
  19. IDJanuary 18, 2020 at 3:09 AM

    Thank you for you tutorial, I'd really appreciate it. Wold you mind answering how different is for monitoring a dbin Windows. I like to monitor my db server which runs in Windows. (Engine database Mysql)

    ReplyDelete
    Replies
    1. Nitin JainJanuary 24, 2020 at 10:53 AM

      Should not be different because this uses mysql commandline to get data. As long as you can run mysql command from commandline, this should work.

      Delete
  20. BakhtiyorJune 4, 2020 at 2:02 PM

    Hello Nitin Jain. I'm writing a question because I found this is the only place I can ask a question related to MySQL Zabbix Slave monitoring template. I have tried many templates: yours and others, but mostly I have one common problem. May be it is not directly related to exactly this one template, but I have the same problem with all the other templates. The problem as follows:
    when I try get the value of this:
    zabbix_agentd -t "mysql.slave_status[Slave_IO_Running]"
    I have clear and correct expected response:
    mysql.slave_status[Slave_IO_Running] [t|Yes]

    But if I try to get the value of this:
    zabbix_agentd -t "mysql.slave_status[Slave_SQL_Running]"

    The response is not as expected:
    mysql.slave_status[Slave_SQL_Running] [t|Yes
    Reading]

    There is a new line and some additional words in response, so zabbix complains:
    Preprocessing failed for: Yes.System
    1. Failed: cannot convert value of type "string" from boolean format: invalid value format

    But in case if the slave is stopped the response as expected:
    mysql.slave_status[Slave_SQL_Running] [t|No]

    Can you suggest what is wrong with that?

    ReplyDelete
  21. Henry Pan (HP)June 3, 2021 at 5:53 PM

    Any update on Zabbix 5.4?

    ReplyDelete

Subscribe to: Post Comments (Atom)