Check if remote mysql is running

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.

Thank you.

Why do you want to check remotely? You can just do the check locally on the same server as far as the requested is to make sure mysql is running. — Khaled, Jun 17 '15 at 10:11
What @Khaled said. And instead of writing your own script, it might be better to use a monitoring utility such as monit or daemontools. — Andrew Schulman, Jun 17 '15 at 10:30
Hi Khaled. I have three mysql masters in a percona cluster and a slave outside the cluster that connects to Master 1 via a float ip (Virtual IP). When mysql on Master1 is down, i want to be able to detect that, bring down the float IP on master1, then bring the float ip on Master 2. This way, the slave is still connected, but now to Master2 automatically. I am using replication with GTID enabled. The cron job will be checking the remote server if mysql is up or down every minute. The command for bringing the float ip up on master2 is done via the command line, that is why i want to use cron — The Georgia, Jun 17 '15 at 10:36
@TheGeorgia: It seems to me that you are trying to do high availability solution for mysql using cron! Did you consider using a solution like heartbeat or keepalived? — Khaled, Jun 17 '15 at 11:25
This question is being voted for closure because this is not a script-writing service. — Jenny D, Jul 05 '17 at 10:57

score 6 · Answer 1 · edited Jul 05 '17 at 10:47

Connect to mysqld using mysqladmin

MySQL Documentation says about mysqladmin ping

Check whether the server is available. The return status from mysqladmin is 0 if the server is running, 1 if it is not. This is 0 even in case of an error such as Access denied, because this means that the server is running but refused the connection, which is different from the server not running.

First, create a user with no privileges

mysql> GRANT USAGE ON *.* TO ping@'%' IDENTIFIED BY 'ping';

Create a script like this

#!/bin/bash

MYSQL_USER=ping
MYSQL_PASS=ping
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqladmin ping ${MYSQL_CONN} 2>/dev/null 1>/dev/null
MYSQLD_RUNNING=${?}
if [ ${MYSQLD_RUNNING} -eq 1 ]; then service mysql start ; fi

Place that script in a crontab as you wish

Make sure the firewall is open on 3306 so ping@'%' can connect.

score 3 · Answer 2 · answered Jun 17 '15 at 10:50

3

Checking is easy as this:

mysql -e "select 1" || echo down

You could replace the echo with an ssh command into root to start the service, but that seems a bit risky. It may have security implications. It may not be reliable. I would suggest that you should probably have some kind of proper cluster/failover software to do this. There is MySQL MMM, which we used to use some, but I understand that has gone out of favor.

answered Jun 17 '15 at 10:50

Micah Yoder

320
1
2
7

Hi Micah. This is producing a weird result like +---+ | 1 | +---+ | 1 | +---+. Not sure how to use a control statement in shell to compare to such a result and determine whether mysql is up or down. It would be nice if the result was just a 1 or 0. – The Georgia Jun 17 '15 at 11:00
@TheGeorgia Throw away the standard output and just use the return code. – Michael Hampton Jun 17 '15 at 11:13
Yes: add " >/dev/null" after the "select 1". The || is a bash-ism that said the previous command should succeed OR do the following command. – Micah Yoder Jun 18 '15 at 11:10

score 1 · Answer 3 · answered Jun 17 '15 at 12:06

Assuming you will have sshkey auth for password less login for root user to the remote machine, you can run the following command -

ssh remotemysql "if [ 'ps -efww | grep mysql| grep -v grep' ]; then echo "server is up" ; else echo "server is down"; fi"

replace echo "server is down" with the a mysql start up script. say /etc/init.d/mysqld start

score 0 · Answer 4 · answered Jun 17 '15 at 14:22

As @khaled said in the comment thread of your question, it'd be much more reliable to implement keepalived. It's not nearly as scary as you may think it is. keepalived will manage the floating IP, and you tell it to run a script in the event of a failover. Here's an article specifically on how to configure keepalived with mysql:

https://alexzeng.wordpress.com/2012/10/31/how-to-setup-mysql-ha-by-using-keepalived/

You could modify that config to set a primary and a backup instead of having them both be backup (in the event that you'd want automated failback once the primary node recovered).

score 0 · Answer 5 · answered Jul 05 '17 at 11:51

I want a bash shell script that i can run using a cron job to check if mysql on a remote server is running.

This fills me with trepidation. I can't imagine why anyone would think this is a good way to manage a service. Mysql ships with startup scripts which will integrate with systemd and sysvinit which are the standard ways to start up a service in a Linux/Unix system. Systemd has built in capability to restart a failed service - but I have never seen a default config exercising this option for very obvious reasons, and if there were a good reason for doing so, it could be invoked with a respawn from the inittab on a sysV system. If this relates to running a per-user instance of the DBMS (in the way Kmail does) then the triggerring should be controlled by the user session, not by cron.

But leaving aside the legitimacy of the question, using mysql or mysqladmin to monitor the state of the service entails exposing the credentials to connect to the database - which is not a very good idea from a security viewpoint. It also creates complications around resource management (when you get to max connections, it may look like your service is down)

You can tell if the service is running from:

the presence of a mysqld process in the output of 'ps' or in the /proc filesystem
a listening socket on the relevant port (which you can probe using netcat or detect from the output of netstat)

You'd definitely have so many use cases to remotely check the health of a database (maybe a db instance and not the whole server). Also, the question is about "remote" mysql, so checking the process of mysqld using ps is not feasible. — MKhanal, May 02 '18 at 04:20

Check if remote mysql is running

5 Answers5