Monitoring of MySQL replication and alerting when replication is not working

Question

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

• mysqld_exporter
• pt-heartbeat
• Monitoring MySQL Replication lag with Prometheus & pt-heartbeat
• Monitoring MySQL with Prometheus and Grafana Slides
• OSMC 2017 | Monitoring MySQL with Prometheus and Grafana by Julien Pivotto

Answer 1

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