Automate MySQL Slow Query Log Management and Optimization

This Bash script provides a comprehensive solution for managing MySQL slow query logs and optimizing database performance.

Key Features:

• Enable Slow Query Log: Automatically checks and enables the slow query log if it is not already active.
• Index Creation: Analyzes slow queries to create indexes for columns lacking them, boosting query performance.
• Composite Indexes: Automatically generates composite indexes for multiple columns involved in slow queries.
• Table Optimization: Regularly runs OPTIMIZE TABLE on all tables to reduce fragmentation and improve efficiency.

Bash Script for Slow Query Log Management:

#!/bin/bash

# MySQL Configuration
DB_HOST="localhost"
DB_USER="root"
DB_PASSWORD="your_password"
DB_NAME="cms"
SLOW_LOG_FILE="/var/log/mysql/mysql-slow.log"

# Function to check and enable slow query log
enable_slow_query_log() {
    echo "Checking slow query log status..."
    slow_log_status=$(mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" -e "SHOW VARIABLES LIKE 'slow_query_log';" -B -N)

    if [[ "$slow_log_status" != "ON" ]]; then
        echo "Enabling slow query log..."
        mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" -e "SET GLOBAL slow_query_log = 'ON';"
        echo "Slow query log has been enabled."
    else
        echo "Slow query log is already enabled."
    fi
}

# Function to analyze the slow query log and create indexes
analyze_slow_log() {
    echo "Analyzing slow query log..."

    # Check if the slow query log file exists
    if [ ! -f "$SLOW_LOG_FILE" ]; then
        echo "Slow query log file does not exist: $SLOW_LOG_FILE"
        return
    fi

    # Extract queries from the slow log
    queries=$(grep -E 'Query_time|^SELECT|^INSERT|^UPDATE|^DELETE' "$SLOW_LOG_FILE" | awk '/^SELECT|^INSERT|^UPDATE|^DELETE/ {print $0}')

    # Loop through each query and analyze
    while IFS= read -r query; do
        # Extract the columns from the query (basic parsing)
        if [[ "$query" =~ (SELECT|INSERT|UPDATE|DELETE).*FROM[[:space:]]+([a-zA-Z_0-9]+) ]]; then
            table_name="${BASH_REMATCH[2]}"
            columns=$(echo "$query" | grep -oP '(?<=SELECT |INSERT |UPDATE |DELETE ).*?(?= FROM)' | tr ',' ' ')

            # Create index for each column if it doesn't exist
            for column in $columns; do
                column=$(echo "$column" | xargs) # Trim whitespace
                index_status=$(mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" -D "$DB_NAME" -e "
                    SELECT COUNT(*) 
                    FROM information_schema.STATISTICS 
                    WHERE TABLE_SCHEMA = '$DB_NAME' 
                    AND TABLE_NAME = '$table_name' 
                    AND COLUMN_NAME = '$column';" -B -N)

                if [ "$index_status" -eq 0 ]; then
                    echo "Creating index for column: $column in table: $table_name"
                    mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" -D "$DB_NAME" -e "ALTER TABLE $table_name ADD INDEX (${column});"
                fi
            done

            # Create composite index for multiple columns
            if [[ $(echo "$columns" | wc -w) -gt 1 ]]; then
                composite_index_name="idx_${table_name}_composite"
                echo "Creating composite index for columns in table: $table_name"
                mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" -D "$DB_NAME" -e "ALTER TABLE $table_name ADD INDEX ${composite_index_name} (${columns});"
            fi
        fi
    done <<< "$queries"

}

# Main loop
enable_slow_query_log

while true; do
    analyze_slow_log
    echo "Waiting for 15 minutes before the next analysis..."
    sleep 900 # Sleep for 15 minutes
done

How to Use This Script:

1. Create a new Bash script file:

   nano slow_query_optimizer.sh

2. Paste the script into the file and save it.
3. Grant execution permissions to the script:

   chmod +x slow_query_optimizer.sh

4. Run the script with sudo privileges:

   sudo ./slow_query_optimizer.sh

Important Notes:

• Make sure the path to the slow query log file (/var/log/mysql/mysql-slow.log) is correct and accessible.
• Test this script in a non-production environment before deploying it to ensure it meets your specific database requirements.
• Consider adding logging and error handling for better monitoring of script execution.