Ubuntu Bash to Schedule and Monitor MySQL Database Cleanup Tasks

This tutorial explains how to use Bash scripting to schedule and monitor MySQL database cleanup tasks. We'll cover the setup, scripting, and automation process, including practical examples.

Prerequisites

• A Linux-based system with Bash shell.
• MySQL installed and configured.
• Basic knowledge of Bash scripting and MySQL commands.
• cron installed for scheduling.

Step 1: Define Your Cleanup Task in SQL

Write the SQL commands required for cleaning up your database. For example, to delete records older than 30 days:

DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;

Save this query in a file called cleanup.sql.

Step 2: Create a Bash Script

Write a Bash script to execute the SQL query.

Script: db_cleanup.sh

#!/bin/bash

# Variables

DB_USER="your_username"

DB_PASS="your_password"

DB_NAME="your_database"

LOG_FILE="/var/log/db_cleanup.log"

# Execute the cleanup query

echo "[$(date)] Starting database cleanup..." >> "$LOG_FILE"

mysql -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" < /path/to/cleanup.sql 2>>"$LOG_FILE"

# Check for errors

if [ $? -eq 0 ]; then

    echo "[$(date)] Cleanup completed successfully." >> "$LOG_FILE"

else

    echo "[$(date)] Cleanup failed. Check the log for details." >> "$LOG_FILE"

fi

Replace your_username, your_password, and your_database with actual credentials.

Save the file and make it executable:

chmod +x db_cleanup.sh

Step 3: Schedule the Script Using Cron

Use cron to automate the script execution.

Open the cron editor:

crontab -e

Add a job to run the script daily at midnight:

0 0 * * * /path/to/db_cleanup.sh

Save and exit the editor.

Step 4: Monitor the Task

The script logs each execution. To monitor:

View the log file:

tail -f /var/log/db_cleanup.log

Look for successful or failed task entries.

Step 5: Advanced Monitoring with Email Notifications

Enhance monitoring by sending email alerts on errors.

Updated Script

#!/bin/bash

# Variables

DB_USER="your_username"

DB_PASS="your_password"

DB_NAME="your_database"

LOG_FILE="/var/log/db_cleanup.log"

EMAIL="your_email@example.com"

# Execute the cleanup query

echo "[$(date)] Starting database cleanup..." >> "$LOG_FILE"

mysql -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" < /path/to/cleanup.sql 2>>"$LOG_FILE"

# Check for errors

if [ $? -eq 0 ]; then

    echo "[$(date)] Cleanup completed successfully." >> "$LOG_FILE"

else

    echo "[$(date)] Cleanup failed. Check the log for details." >> "$LOG_FILE"

    mail -s "Database Cleanup Failed" "$EMAIL" < "$LOG_FILE"

fi

Replace your_email@example.com with your email address.

Ensure the mail utility is installed and configured:

sudo apt install mailutils

Example Use Case

Suppose your database my_app contains a table user_sessions storing session data. The goal is to delete sessions older than 7 days.

SQL query (cleanup.sql):

DELETE FROM user_sessions WHERE last_access < NOW() - INTERVAL 7 DAY;

Bash script (db_cleanup.sh) is configured with DB_NAME="my_app".

Scheduled in cron as shown above.

Using Bash and cron, you can automate MySQL database cleanup tasks effectively. By implementing logging and monitoring mechanisms, you ensure smooth operations and quick issue resolution. This setup is versatile and can be tailored for various database maintenance tasks.  Hope this is helpful, and I apologize if there are any inaccuracies in the information provided.