Check MySQL Status Ubuntu

MySQL is one of the most popular and commonly used database management systems for web applications. It is simple to set up, configure and manage, making it one of the best choices for new and experienced users.

However, sometimes the MySQL server stops due to errors or misconfiguration. This guide will show you how to check the status of the MySQL server and start it if it is down. We will implement concepts such as systemd, crontab, and bash scripting to perform such action.

Pre-requisites

Before we begin, ensure that you have:

Installed and configured the MySQL server
Have access to root or account with sudo enabled

Once we have the above requirements, we can get started.

Check MySQL Status – Systemd

The first method we shall focus on before covering how to create a script is to use the systemd manager.

Systemd is a powerful Linux init system and service manager that allows starts, stops, and monitors the statuses of daemons and services. It additionally offers features such as logging and tracking usage, etc. Thus, it is a common tool for system administrators.

To use systemd to check for MySQL service, use the command as:

$ sudo systemctl start mysql.service

Once you execute the above command, then systemd will start the service assuming it does not run into any errors. To check the status of the service, use the command:

$ sudo systemctl start mysql.service

This will give you the output below showing the service is running.

Check MySQL Status – MySQLadmin

We can also use a tool such as mysqladmin. A MySQL server administration command-line utility to check the status of the MySQL server.

Use the command as:

$ mysqladmin -u root -p status

If the MySQL server is up and running, you will get output as shown below:

Uptime: 35 Threads: 1 Questions: 4 Slow queries: 0 Opens: 103 Flush tables: 3 Open tables: 24 Queries per second avg: 0.114

Bash Script

With the information we have of the two methods discussed above, we can implement a fairly simple bash script to check if the service is running and start it if it’s not.

Step 1: Check if the service is running
The first thing our script should do is to check if the service is running; we can get this from the output from systemd as:

$ systemctl status mysql.service | grep “active”

Step 2: Redirect Standard Error to standard output
Once we grep for the status of the service, we can redirect the EOF to the /dev/null and a file descriptor as:

$ systemctl status mysql.service | grep “active” > /dev/null 2>&1

Step 3: Get Return Value
In the next step, we check the return value from the above command using the $?

As shown:

if [ $? != 0 ]

Step 4: Putting it together
Now that we have the functionality of the script all laid out, we can put together the script as:

#!/bin/bash
systemctl status mysql.service | grep 'active' > /dev/null 2>&1
if [ $? != 0 ]
then
systemctl start mysql.service
fi

Now save the script and make it executable

$ sudo chmod 755 mysql_checker.sh

Step 5: Tell Cron
And the last step for us to do is let cron know about our script and automatically manage it.

We can do this using the command:

$ sudo crontab -e

Enter the following lines.

*/5 * * * * /home/ubuntu/mysql_checker.sh

This will allow cron to run the script every 5 minutes and start the service.

Conclusion

In this tutorial, we used systemd to check for the status of MySQL and restart it. We also implemented a bash script and cron to check automatically handle the check and restart process.

As usual, thank you for reading and Happy Shells.