able to use galera cluster to check if mariadb is down then send email as notification?

[et4...@gmail.com]

As mentioned in question title...

I am wondering if there is a way to check if any of the mariadb is down then send a notification?

[Lammert Bies]

In my case all cluster nodes also run a webserver and a PHP script running on each webserver is called periodically from an external uptime service. This PHP script checks some internal Galera variables and outputs a text depending on the Galera internal state. The text "RUNNING" is returned to the external uptime service when the cluster node is running and synced, and a failure code otherwise. The uptime service sends emails when a node is down, based on the return value.

To give you an idea what the PHP script is doing, I have copied it here. With some changes you might be able to run it periodically with cron on each cluster node with the php command line interpreter and send an email directly from PHP if an error is detected.

<?php

        date_default_timezone_set( "Europe/Amsterdam" );

        $SQL_base_link = mysqli_connect( 'localhost', 'dbuser', 'dbpassword' ) or die( mysqli_error( $SQL_base_link ) );

        mysqli_set_charset( $SQL_base_link, "utf8mb4" ) or die( mysqli_error( $SQL_base_link ) );

        $sql_query = "select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'wsrep_cluster_status';";

        $sql_result = mysqli_query( $SQL_base_link, $sql_query );

        if ( $sql_result ) {

                $line = mysqli_fetch_array( $sql_result, MYSQLI_ASSOC );

                if ( strtolower( $line['VARIABLE_VALUE'] == 'disconnected' ) ) { printf( "FAILURE 1\n" ); exit( 1 ); }

        }

        else { printf( "FAILURE 2\n" ); exit( 1 ); }

        $sql_query = "select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'wsrep_connected';";

        $sql_result = mysqli_query( $SQL_base_link, $sql_query );

        if ( $sql_result ) {

                $line = mysqli_fetch_array( $sql_result, MYSQLI_ASSOC );

                if ( strtolower( $line['VARIABLE_VALUE'] == 'off' ) ) { printf( "FAILURE 3\n" ); exit( 1 ); }

        }

        else { printf( "FAILURE 4\n" ); exit( 1 ); }

        $sql_query = "select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'wsrep_local_state_comment';";

        $sql_result = mysqli_query( $SQL_base_link, $sql_query );

        if ( $sql_result ) {

                $line = mysqli_fetch_array( $sql_result, MYSQLI_ASSOC );

                if ( strtolower( $line['VARIABLE_VALUE'] == 'synced' ) ) { printf( "FAILURE 5\n" ); exit( 1 ); }

        }

        else { printf( "FAILURE 6\n" ); exit( 1 ); }

        printf( "RUNNING\n" );

?>

[et4...@gmail.com]

Hello Lammert Bies,

Thanks for your reply,  I am currently using nginx as web server and have my maria db in it.  The server only contains the database.

I thought writing bash script would be easier as I do not need to setup anything else.

I am still new at this, esp with bash script too.  Seeing your php syntaxes, I do have a bit of idea such as writing a bash script to check the status, but not like php or python, I have no idea how I can catch a value (return) back about how the status then do more process on it depending on the value returned

I can see that first of all, you are trying to connect to the db, if connection success then check status such as node status and all.