Shell script to check if mysql is up or down

Question

I want a bash shell script that i can run using a cron job to check if mysql on a remote server is running. If it is, then do nothing, other start the server.

The cronjob will be checking the remote server for a live (or not) mysql every minute. I can write the cron job myself, but i need help with the shell script that checks if a remote mysql is up or down. The response after a check if up or down is not important. But the check is important.

Solution 1

You can use below script

#!/bin/bash

USER=root
PASS=root123

mysqladmin -h remote_server_ip -u$USER -p$PASS processlist ###user should have mysql permission on remote server. Ideally you should use different user than root.

 if [ $? -eq 0 ]
        then
                echo "do nothing"
        else
                ssh remote_server_ip  ###remote server linux root server password should be shared with this server.
                service mysqld start
 fi

Solution 2

The script in the selected answer works great, but requires that you have the MySQL client installed on the local host. I needed something similar for a Docker container and didn't want to install the MySQL client. This is what I came up with:

# check for a connection to the database server
#
check=$(wget -O - -T 2 "http://$MYSQL_HOST:$MYSQL_PORT" 2>&1 | grep -o mariadb)

while [ -z "$check" ]; do
    # wait a moment
    #
    sleep 5s

    # check again
    #
    check=$(wget -O - -T 2 "http://$MYSQL_HOST:$MYSQL_PORT" 2>&1 | grep -o mariadb)
done

This is a little different, in that it will loop until a database connection can be made. I am also using MariaDB instead of the stock MySQL database. You can change this by changing the grep -o mariadb to something else - I'm not sure what MySQL returns on a successful connection, so you'll have to play with it a bit.

Comments

[Thomas Böhm]

Thanks for that answer, it helped me a lot. However, in my version I had to write $check instead of "$check", which might be wrong in your posted answer.