MySQL Service Monitoring and Auto-Restart Script for LNMP Environments

Background and Requirements

In WordPress or high-concurrency application environments, the MySQL service may stop unexpectedly due to resource exhaustion, excessive connections, or internal errors. Manual recovery is time-consuming and can cause prolonged service interruptions. Deploying an automatic monitoring and restart script is essential for unattended fault recovery.

Basic Monitoring Script (Port Detection)

This script checks the status of the MySQL service by detecting the default port 3306. If the port is not listening, it attempts to restart MySQL and logs the event.

#!/bin/bash
# Basic version: Monitor MySQL via port 3306 detection

LOG_FILE="/home/wwwlogs/mysql_messages"

# Check if mysqld process exists
pgrep -x mysqld &> /dev/null
if [ $? -ne 0 ]
then
    echo "MySQL stopped at time: $(date)" >> "$LOG_FILE"
    /etc/init.d/mysql restart
    sleep 15
    
    # Check port again after restart
    MYSQLPORT=$(netstat -tln | grep ":3306" | awk '{print $4}' | awk -F: '{print $2}')
    if [ "$MYSQLPORT" = "3306" ]
    then
        echo "MySQL restart successful at time: $(date)" >> "$LOG_FILE"
    else
        echo "MySQL restart failed at time: $(date)" >> "$LOG_FILE"
        # Email alert can be configured here (requires mailx or sendmail)
        # mail -s "[Alert] MySQL restart failed on $(hostname)" [email protected] < "$LOG_FILE"
    fi
else
    echo "MySQL server is running at $(date)"
fi

Script Explanation and Configuration

1. Log Path: Ensure the /home/wwwlogs/ directory exists and is writable, or modify it to another location.
2. Restart Command: Depending on your init system (systemd or sysvinit), the restart command may be systemctl restart mysql or service mysql restart. Adjust accordingly.
3. Port Detection Methods: Besides netstat, you can also use:

• ss -tln | grep :3306 (recommended, more efficient)
• lsof -i:3306
• nmap localhost -p 3306 | grep open (requires nmap)

Deployment and Scheduled Execution

Save the script (e.g., /usr/local/bin/mysql_monitor.sh), set execute permissions, and configure a cron job.

# 1. Save script and grant execute permission
chmod +x /usr/local/bin/mysql_monitor.sh

# 2. Edit current user's crontab
crontab -e

# 3. Add line to run script every 5 minutes
*/5 * * * * /bin/bash /usr/local/bin/mysql_monitor.sh >/dev/null 2>&1

# 4. Restart cron service (may be crond or cron)
systemctl restart crond  # or service cron restart

Enhanced Monitoring Script (Port + Connection Test)

Port detection alone may be insufficient if MySQL is in a "zombie" state (process exists but cannot respond to queries). This version adds a simple database connection test for greater reliability.

#!/bin/bash
# Enhanced: Port detection + database connection test

MYSQL_USER="root"                    # Monitoring DB user (create dedicated read-only user)
MYSQL_PASS="YourStrongPassword"      # Corresponding password
LOG_FILE="/var/log/mysql_monitor.log"
EMAIL_ADDR="[email protected]"       # Alert email

# Function: Check if MySQL port is listening
function check_port {
    ss -tln | grep -q ":3306 "
    return $?
}

# Function: Test database connection
function check_connection {
    timeout 5 mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -e "SELECT 1;" &> /dev/null
    return $?
}

# Function: Restart MySQL service
function restart_mysql {
    echo "[$(date)] Attempting to restart MySQL..." >> "$LOG_FILE"
    systemctl restart mysql 2>> "$LOG_FILE"
    sleep 10
}

# Function: Send alert email
function send_alert {
    local subject="[CRITICAL] MySQL service failure on $(hostname)"
    local body="MySQL service failed to restart after multiple attempts. Please check manually."
    echo "$body" | mail -s "$subject" "$EMAIL_ADDR"
}

# Main logic
if check_port && check_connection; then
    echo "[$(date)] MySQL is healthy." >> "$LOG_FILE"
    exit 0
else
    echo "[$(date)] MySQL check failed. Port or connection test error." >> "$LOG_FILE"
    restart_mysql
    
    # Check again after restart
    if check_port && check_connection; then
        echo "[$(date)] MySQL restarted successfully." >> "$LOG_FILE"
    else
        echo "[$(date)] MySQL restart FAILED." >> "$LOG_FILE"
        send_alert
        exit 1
    fi
fi

Important Notes for Enhanced Script

1. Security: Do not hardcode root password in the script. Create a dedicated monitoring user (e.g., monitor_user) with minimal USAGE privileges and store credentials in a protected file like ~/.my.cnf.
2. Dependencies: Ensure the mail command (or alternative email method) and timeout command are installed.
3. Resource Usage: Avoid overly frequent cron jobs (e.g., every minute) to prevent unnecessary system load. Every 2-5 minutes is typically sufficient.

Summary and Recommendations

Auto-restart scripts are a temporary solution for unexpected MySQL stoppages, not a root cause fix. If MySQL crashes frequently, investigate underlying issues:

• Check error logs: /var/log/mysql/error.log or /var/log/mysqld.log.
• Monitor system resources: Ensure adequate memory, CPU, disk I/O, and space.
• Optimize database configuration: Adjust key parameters like max_connections and innodb_buffer_pool_size.
• Consider high-availability solutions: For critical services, implement master-slave replication or clustering for true high availability.

Deploy either script on your LNMP server and configure the cron job to significantly reduce the risk of website downtime due to MySQL service interruptions.