Score:0

Monitoring of MySQL replication and alerting when replication is not working

jp flag

I have a question about monitoring of MySQL replication and in particular about alerting using Grafana/Prometheus.

We have 2 MySQL(MariaDB to be exact) instances for which replication is configured: One Master and one Slave. They can change roles and Master an become Slave and vice versa from time to time (I use MariaDB Maxscale proxy which makes this switch very easy)

I want to monitor replication status and delay between master and slave. I've installed mysqld_exporter for that and I see data in Grafana now. But I would like also to send alerts in case when replication is not working or when slave is not caching up with master for some reason.

The question: how to configure alerting for the scenario above for the case when MAster and Replica can switch roles.

Perhaps there are some articles on this topic (I haven't found though)

If the setup is static - I think I can configure alerts, but how to handle the dynamic setup? Thanks a lot!

P.S. For now I have found following resources, but none of them tells on how to handle alerting for dynamic setup

Dmitry avatar
jp flag
I got a suggestion to use this rule: https://awesome-prometheus-alerts.grep.to/rules#rule-mysql-1-6 Will try it out
Dmitry avatar
jp flag
It worked. The tick was to check the ID of the Master along with the replication lag.
Score:1
jp flag

The query like this one

( (mysql_slave_status_seconds_behind_master - mysql_slave_status_sql_delay) and ON (instance) mysql_slave_status_master_server_id > 0 ) > 30

from https://awesome-prometheus-alerts.grep.to/rules#rule-mysql-1-6 solved the issue which I had.

My problem was that I didn't know how to check if the mysql instance is running as a secondary replica or it was the master. And this part of the query solved this puzzle for me:

mysql_slave_status_master_server_id > 0 
c4f4t0r avatar
nl flag
please post the query in your answer and remove the external link, Thanks and welcome to this amazing site :)
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.