sql_tooner.sh

#!/bin/bash
#author: salman sk
# Script to perform SQL tuning and optimization for MySQL

LOG_DIR="logs"
LOG_FILE="$LOG_DIR/sql_tuning_$(date +%Y%m%d_%H%M%S).log"

mkdir -p "$LOG_DIR"

CONFIG_FILE="config.conf"
MYSQL_ROOT_PASS=$(grep -i 'mysql_root_password' "$CONFIG_FILE" | cut -d'=' -f2 | xargs)

if [ "$EUID" -ne 0 ]; then
  echo "Please run as root"
  exit 1
fi

if [ ! -f "$CONFIG_FILE" ]; then
  echo "Config file not found. Exiting."
  exit 1
fi

source "$CONFIG_FILE"

if [ -z "$MYSQL_ROOT_PASS" ]; then
    echo "MySQL root password not found in $CONFIG_FILE"
    exit 1
fi

log_output() {
    local message="$1"
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $message" >> "$LOG_FILE"
}

perform_sql_tuning() {
    log_output "Starting SQL tuning and optimization..."
    echo -e "\e[0;33m 1. MySQL Version \e[0m"
    mysql -u root -p"$MYSQL_ROOT_PASS" -e "SELECT VERSION();" 2>&1 | tee -a "$LOG_FILE"
    log_output "MySQL version checked."
    echo -e "\e[0;33m 2. Optimize All Tables \e[0m"
    mysql -u root -p"$MYSQL_ROOT_PASS" -e "USE information_schema; 
        SELECT CONCAT('OPTIMIZE TABLE ', table_schema, '.', table_name, ';') 
        FROM tables WHERE engine='InnoDB';" | mysql -u root -p"$MYSQL_ROOT_PASS" 2>&1 | tee -a "$LOG_FILE"
    log_output "Optimized all InnoDB tables."
    echo -e "\e[0;33m 3. Analyze All Tables \e[0m"
    mysql -u root -p"$MYSQL_ROOT_PASS" -e "USE information_schema; 
        SELECT CONCAT('ANALYZE TABLE ', table_schema, '.', table_name, ';') 
        FROM tables WHERE engine='InnoDB';" | mysql -u root -p"$MYSQL_ROOT_PASS" 2>&1 | tee -a "$LOG_FILE"
    log_output "Analyzed all InnoDB tables."
    echo -e "\e[0;33m 4. Check Slow Queries \e[0m"
    mysql -u root -p"$MYSQL_ROOT_PASS" -e "SHOW VARIABLES LIKE 'slow_query_log';" 2>&1 | tee -a "$LOG_FILE"
    mysql -u root -p"$MYSQL_ROOT_PASS" -e "SHOW VARIABLES LIKE 'long_query_time';" 2>&1 | tee -a "$LOG_FILE"
    log_output "Checked slow query log settings."
    echo -e "\e[0;33m 5. Kill Long Running Queries \e[0m"
    mysql -u root -p"$MYSQL_ROOT_PASS" -e "SELECT CONCAT('KILL ',id,';') 
        FROM information_schema.PROCESSLIST 
        WHERE TIME > 600;" | mysql -u root -p"$MYSQL_ROOT_PASS" 2>&1 | tee -a "$LOG_FILE"
    log_output "Killed long-running queries."
    echo -e "\e[0;33m 6. Optimize Performance Schema \e[0m"
    mysql -u root -p"$MYSQL_ROOT_PASS" -e "USE performance_schema;
        UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';" 2>&1 | tee -a "$LOG_FILE"
    log_output "Optimized Performance Schema."
    echo -e "\e[0;33m 7. Check Disk Usage by MySQL Data Directory \e[0m"
    du -sh /var/lib/mysql/ 2>&1 | tee -a "$LOG_FILE"
    log_output "Checked disk usage of MySQL data directory."
    echo -e "\e[0;33m 8. Check Indexes \e[0m"
    mysql -u root -p"$MYSQL_ROOT_PASS" -e "SELECT table_schema, table_name, index_name, column_name, seq_in_index 
        FROM information_schema.statistics 
        WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') 
        ORDER BY table_schema, table_name, index_name, seq_in_index;" 2>&1 | tee -a "$LOG_FILE"
    log_output "Checked database indexes."
    echo -e "\e[0;33m 9. General Database Statistics \e[0m"
    mysql -u root -p"$MYSQL_ROOT_PASS" -e "SHOW STATUS;" 2>&1 | tee -a "$LOG_FILE"
    log_output "Checked general database statistics."
    echo -e "\e[0;33m 10. Recommendations \e[0m"
    {
        echo "Recommendations:"
        echo "1. Ensure that slow query logging is enabled and monitor slow queries."
        echo "2. Regularly review and optimize database indexes."
        echo "3. Check for long-running queries and optimize them."
        echo "4. Monitor database performance and adjust configuration parameters as needed."
    } >> "$LOG_FILE"
    log_output "Provided performance recommendations."
    log_output "SQL tuning and optimization completed."
}

perform_sql_tuning

echo
echo "SQL Tuning script completed."
echo "Executed on:"
date