If you’re managing a server with MySQL or MariaDB, you know how critical it is to keep the database running. A sudden downtime can disrupt websites, applications, and critical operations. What’s worse, if the downtime happens at the same time every day, it’s even more puzzling.

In this guide, we’ll walk you through setting up an automated system to detect when MySQL goes down and restart it immediately. This approach minimizes downtime and helps you sleep a little easier knowing your database is under constant watch.

Why Does MySQL Go Down?

MySQL downtime can occur for various reasons, including:

1. Resource Overload: High server load due to insufficient memory or CPU.
2. Misconfiguration: Errors in MySQL settings.
3. Corrupted Data: Issues with the database files or indexes.
4. Scheduled Events: Unexpected results from cron jobs or backups.
5. Software Bugs: Rare but possible issues with MySQL or its dependencies.

If MySQL consistently goes down at the same time, like at 6:11 PM every day, it could be a result of a cron job, a backup script, or some other scheduled task overloading the server. While diagnosing the root cause is essential, setting up automatic detection and recovery ensures your services are minimally impacted while you investigate.

Step 1: Create an Automated Monitoring Script

The first step is to create a script that checks whether MySQL is running. If it’s down, the script will restart it and log the action.

How to Create the Script

1. Log in to your server and open a new file: nano /root/restart_mysql.sh
2. Copy and paste the following script into the file: #!/bin/bash # Check if MySQL is running if ! systemctl is-active --quiet mysql; then echo "$(date): MySQL is down, restarting..." >> /var/log/mysql-restart.log systemctl restart mysql echo "$(date): MySQL restarted." >> /var/log/mysql-restart.log fi
3. Save the file and exit:
   • Press Ctrl + O to save.
   • Press Enter to confirm the filename.
   • Press Ctrl + X to exit.
4. Make the script executable: chmod +x /root/restart_mysql.sh

What Does This Script Do?

• It checks if MySQL is running using systemctl is-active mysql.
• If MySQL is not running, it restarts the service with systemctl restart mysql.
• It logs the restart event and timestamp in /var/log/mysql-restart.log.

This script ensures that MySQL restarts automatically whenever it goes down.

Step 2: Automate the Script with Cron

The script needs to run regularly to monitor MySQL. This is where cron jobs come in. Cron is a time-based scheduler in Linux that automates tasks.

Add the Script to Cron

1. Open the crontab editor: crontab -e
2. Add the following line at the end of the file: */1 * * * * /bin/bash /root/restart_mysql.sh
3. Save and exit the editor:
   • If you’re using nano, press Ctrl + O, then Enter, and Ctrl + X.
   • If you’re using vim, press Esc, type :wq, and press Enter.
4. Verify the cron job: crontab -l You should see your new cron job listed.

What Does the Cron Job Do?

The */1 * * * * schedule means the script runs every minute. It automatically checks if MySQL is running, and if it’s down, the script restarts it immediately.

Step 3: Test the System

Now that the setup is complete, it’s time to test whether everything works as expected.

Simulate MySQL Failure

1. Stop MySQL manually: systemctl stop mysql
2. Wait for a minute to let the cron job run.
3. Check if MySQL restarted: systemctl status mysql
4. Verify the log file for a restart entry: cat /var/log/mysql-restart.log

If the system is set up correctly, you’ll see a log entry showing that MySQL was restarted, and the service should be running again.

Step 4: Investigate the Root Cause

While automatic restarts solve the immediate problem, finding the root cause of MySQL downtime is essential for long-term stability.

Tools to Help Diagnose Issues

1. Check MySQL Logs: tail -n 50 /var/log/mysql/error.log Look for errors or warnings that might point to the cause of the issue.
2. Monitor Server Resources: Use tools like top, htop, or sar to check if the server is running out of memory, CPU, or disk space.
3. Audit Scheduled Tasks: Review cron jobs or other scheduled scripts running around the time MySQL goes down: crontab -l
4. Database Health Check: Run a health check on your MySQL databases to identify corrupted tables or indexes: mysqlcheck -u root -p --all-databases

Step 5: Optional Logging for Better Monitoring

To keep track of how often MySQL restarts, you can enable logging in your script. Each restart action will be recorded in /var/log/mysql-restart.log. This log can help you identify patterns and understand how often MySQL goes down.

Sample Log Entry

Thu Dec 05 18:11:02 UTC 2024: MySQL is down, restarting...
Thu Dec 05 18:11:04 UTC 2024: MySQL restarted.