[Help needed] I need some help with my monitoring script for my MariaDB replication

[BobWs]

Hi,

I need some help with my monitoring script for my MariaDB replication.

I wrote the script below (with some help from AI) to monitor my MariaDB master-master replication on two hosts within my LAN. MariaDB (docker version latest; MariaDB 11.1.2-MariaDB-1:11.1.2+maria~ubu2204) is installed on two Synology hosts and is set up in a master-master replication. To monitor this replication setup, I thought of using a script to check the status and print the result/output to a text file saved on the hosts. When I run this code manually in the terminal, I can see the status:

docker exec -it mariadb /bin/bash
mariadb -u root --password=[secret password]
SHOW ALL REPLICAS STATUS\G

However, when I use my script, the generated text file is created in the dedicated folder, but it remains empty. So, I don't know how to solve this and get the script to work properly. Can someone please help me with this?

Below is the full script I'm using:

#!/bin/bash

# Set the path to the host directory where you want to store the file
host_directory="/path/to/host/directory"

# Set your MariaDB container name and password
container_name="mariadb"
password="mysecretpassword"

# Set the absolute path for the output file inside the container
container_output_file="/var/log/mariadb/replication_status.txt"

# Execute commands inside the MariaDB container with volume mount
docker exec -i -v "$host_directory:$container_output_file" "$container_name" mariadb -u root --password="$password" <<EOF > "$host_directory/replication_status.txt"
SHOW ALL REPLICAS STATUS\G
EOF

# Check for errors in the command execution
if [ $? -ne 0 ]; then
    echo "Error executing MariaDB command."
    exit 1
fi

# Get the current date and time in the "YYYY-MM-DD HH:MM" format
date=$(date "+%Y-%m-%d %H:%M")

# Parse the output to check for replication errors
if grep -q "Slave_IO_Running: No" "$host_directory/replication_status.txt" || grep -q "Slave_SQL_Running: No" "$host_directory/replication_status.txt"; then
    subject="MariaDB Replication Error"
    message="Replication is not running correctly. Please investigate."
else
    subject="MariaDB Replication OK"
    message="Replication is running smoothly."
fi

# Set the shareable link to your file on the Synology NAS
file_link="YOUR_SYNOSHARE_LINK_HERE"

# Send the message to Mattermost
# Example using Mattermost webhook
mattermost_webhook_url="https://mattermost-webhook-url"
username="Monitoring"
icon_url="https://icon.example.com/icon.png"
markdown_table="| **Machine** | **Docker App** | **Subject** | **Status** | **Time** | **Log File** |
| :------ | :------ | :------ | :------ | :------: | :------: |
| Syno (DS916+) | Mariadb | $subject | $message | $date | [Replication Log]($file_link) |"

curl -i -X POST -H 'Content-Type: application/json' -d \
'{
  "username": "'"$username"'",
  "icon_url": "'"$icon_url"'",
  "text": "'"$markdown_table"'"
}' \
"$mattermost_webhook_url"

# Clean up the temporary file (if you specified an absolute path, it's not necessary)
# rm "$host_directory/replication_status.txt"

TIA

[grooverdan]

One part nit, I don't think its the main problem is 'docker exec' can't take a -v I don't think. Volumes are only mounted on run.

I'd suggest start looking at the healthcheck.sh script already in the container, (and this repo).

https://mariadb.com/kb/en/using-healthcheck-sh-script/

depends when you created the volume, you may/may not have a .my-healthcheck.cnf in your datadir corresponding to a healthcheck user that exists.

If not:

docker exec --env MYSQL_PWD="$password" "$container_name" healthcheck.sh --replication_io --replication_sql --replication

Will have a return status to say if its healthy or not.

To see what its doing:

docker exec --user mysql --env MYSQL_PWD="$password" "$container_name" bash -x -v healthcheck.sh --replication_io --replication_sql --replication

To debug your script use set -x -v at the top, and like the above, it will show you the paths taken.

[BobWs]

depends when you created the volume, you may/may not have a .my-healthcheck.cnf in your datadir corresponding to a healthcheck user that exists.

Thank you for replying! I have the .my-healthcheck.cnf in my data folder, because this is a clean install (last week) But I don't quite understand what you are trying to say that I should do! I'm sorry, but could you explain a little bit more how to use the healthcheck.sh and that it will provide what I need. Thank you!

[grooverdan]

So give the healthcheck user grants so it can can query your replication status:

    GRANT REPLICA MONITOR ON *.* TO healthcheck@'127.0.0.1';
    GRANT REPLICA MONITOR ON *.* TO healthcheck@'::1';
    GRANT REPLICA MONITOR ON *.* TO healthcheck@localhost;

(would be solved by MARIADB_HEALTHCHECK_GRANTS=REPLICA MONITOR if it was being created)

if docker exec "$container_name" healthcheck.sh --replication_io --replication_sql --replication; then
    subject="MariaDB Replication Error"
    message="Replication is not running correctly. Please investigate."
else
    subject="MariaDB Replication OK"
    message="Replication is running smoothly."
fi

[BobWs]

I have made the suggested changes in my script and the GRANT..changes into the mariadb container, but now when I run the script the output in Mattermost only show the error message, I don't know if it is really an error!

    subject="MariaDB Replication Error"
    message="Replication is not running correctly. Please investigate."

If I look in phpMyAdmin I don't see any errors in the Replications tabs log neither in my Mariadb container log. So I don't know if the script is working.

Altered script:

#!/bin/bash

# Set your MariaDB container name and password
container_name="mariadb"
password="mysecretpassword"

# Use healthcheck.sh to check replication status
if docker exec "$container_name" healthcheck.sh --replication_io --replication_sql --replication; then
    subject="MariaDB Replication Error"
    message="Replication is not running correctly. Please investigate."
else
    subject="MariaDB Replication OK"
    message="Replication is running smoothly."
fi

# Get the current date and time in the "YYYY-MM-DD HH:MM" format
date=$(date "+%Y-%m-%d %H:%M")

# Set the shareable link to your file on the Synology NAS
file_link="YOUR_SYNOSHARE_LINK_HERE"

# Send the message to Mattermost
# Example using Mattermost webhook
mattermost_webhook_url="https://mattermost-webhook-url"
username="Monitoring"
icon_url="https://icon.example.com/icon.png"
markdown_table="| **Machine** | **Docker App** | **Subject** | **Message** | **Date** |
| :------ | :------ | :------ | :------ | :------: |
| Host (DS916+) | DB-Replica | $subject | $message | $date |"

curl -i -X POST -H 'Content-Type: application/json' -d \
'{
  "username": "'"$username"'",
  "icon_url": "'"$icon_url"'",
  "text": "'"$markdown_table"'"
}' \
"$mattermost_webhook_url"

[grooverdan]

To see what its doing:

docker exec --user mysql --env MYSQL_PWD="$password" "$container_name" bash -x -v healthcheck.sh --replication_io --replication_sql --replication

[BobWs]

To see what its doing:

Okay so the output I get in Terminal is the healthcheck.sh script in detail and the output equal to SHOW ALL REPLICAS STATUS\G Here is some parts of the output:

+ _process_sql -e 'SHOW  REPLICA  STATUS\G'
+ mariadb --defaults-extra-file=/var/lib/mysql/.my-healthcheck.cnf -B -e 'SHOW  REPLICA  STATUS\G'
+ shopt -s extglob
+ read -t 5 -r
+ '[' 0 -gt 128 ']'
+ IFS=:
+ read -t 1 -r n v
+ n=Slave_IO_State
+ v='Waiting for master to send event'
+ case "$n" in
+ IFS=:
+ read -t 1 -r n v
+ n=Master_Host
+ v=192.168.1.10
...
+ n=Master_User
+ v=replicator
+ case "$n" in
+ IFS=:
+ read -t 1 -r n v
+ n=Master_Port
+ v=3306
+ case "$n" in
+ IFS=:
+ read -t 1 -r n v
+ n=Connect_Retry
+ v=10
....
+ n=Master_Log_File
+ v=source10-bin.000051
+ case "$n" in
+ IFS=:
+ read -t 1 -r n v
+ n=Read_Master_Log_Pos
+ v=4984
...
+ n=Slave_IO_Running
+ v=Yes
+ case "$n" in
+ '[' -n 1 ']'
+ '[' Yes = No ']'
+ IFS=:
+ read -t 1 -r n v
+ n=Slave_SQL_Running
+ v=Yes

Based on the output the Replica is healthy and running okay (same info I see in phpMyAdmin, Replication Tab). But the questions is now how do I get this output into a text file using my script?

[grooverdan]

docker exec "$container_name"  mariadb --defaults-extra-file=/var/lib/mysql/.my-healthcheck.cnf -B -e 'SHOW  REPLICA  STATUS\G'  > "${host_directory}/replica_status-${date}.log"

[BobWs]

@grooverdan Thanks! It works perfectly! Just the way I wanted it. Thanks again for all your help and patience!