mysqlmariadbreplicationmonit

How to set Monit up to monitor MariaDB replication?


I use Monit 5.27.2 and MariaDB 10.5.15 on my Debian 11 servers.

My servers are set up in Multi-Source MariaDB Replication.

Sometimes, replication stops and I am not notified. I have not seen anywhere a prepared configuration for Monit to send such alerts.

The errors are visible with the following query:

SHOW SLAVE STATUS;

With those values particularly interesting:

slave_io_running and slave_sql_running values

last_error value

I have not found any way to use Monit to monitor directly the result of a SQL statement. However, it seems possible to monitor the return code of a command with versions before Monit 5.29.0 (and the output of a command after).

I can probably prepare a Bash/Shell script that will check the output of the SQL statement and then a Monit config that will send me an alert in case of issue. If I do, I will post this as an answer here. I was just wondering whether there was already a known solution to this.

I expect this to work with MySQL.

I intend to test this with those SQL queries:

STOP SLAVE;
START SLAVE;

Solution

  • I got this to work as I expected. I did this on all servers sources of the replication.

    Installing

    Here is the file monit_replication.sh:

    #!/bin/bash   
    user="YOUR_USER"
    password="YOUR_PASSWORD"
    
    result=`echo "show slave status"|mysql -EsB --user="$user" --password="$password"`
    
    contains_with_yes(){
            if echo "$1"|grep -i "$2"|grep -i "Yes" > /dev/null; then
                    echo "$2 ok"
            else
                    echo "$2 not ok"
                    exit 1
            fi
    }
     
    contains_with_yes "$result" "Slave_IO_Running"
    contains_with_yes "$result" "Slave_SQL_Running"
    exit 0
    

    Made this executable:

    chmod 700 /YOUR_PATH_TO_THE_FILE/monit_replication.sh
    

    Added to /etc/monit/conf-enabled/mysql:

     check program MySQL_replication with path "/YOUR_PATH_TO_THE_FILE/monit_replication.sh"
       every 120 cycles
       if status > 0 then alert
       group mysql 
    

    Testing

    Ran this SQL:

    STOP SLAVE;
    

    Restarted monit:

    /etc/init.d/monit restart
    

    Found in /var/log/monit.log:

    [2022-07-04T10:46:55+0000] error    : 'MySQL_replication' status failed (1) -- Slave_IO_Running not ok
    

    Received this email:

    monit alert -- Status failed MySQL_replication
    
    Status failed Service MySQL_replication
    
        Date:        Mon, 04 Jul 2022 10:46:55
        Action:      alert
        Host:        YOUR_HOST
        Description: status failed (1) -- Slave_IO_Running not ok
    
    Your faithful employee,
    Monit
    

    This validates the test. Restoring replication with this SQL query:

    START SLAVE;
    

    Other solutions

    If any simpler solution based on Monit can alert me of a replication failure on MariaDB, I would be happy to accept it as the solution to the question.