How to Terminate Sleeping MySQL Processes with a Linux Bash Script

Here’s an example of a Linux bash script that checks for sleeping MySQL processes and terminates them:

#!/bin/bash

# MySQL connection details
DB_USER="your_username"
DB_PASS="your_password"

# Get the list of sleeping processes from MySQL
PROCESS_LIST=$(mysql -u $DB_USER -p$DB_PASS -Bse "SELECT ID FROM information_schema.processlist WHERE COMMAND = 'Sleep';")

# Iterate over the sleeping processes and kill them
for PROCESS_ID in $PROCESS_LIST
do
    mysql -u $DB_USER -p$DB_PASS -e "KILL $PROCESS_ID;"
done

echo "Sleeping processes terminated."

Make sure to replace "your_username" and "your_password" with your actual MySQL username and password. The script uses the mysql command-line tool to connect to MySQL and retrieve the list of sleeping processes. Then, it iterates over the process list and terminates each sleeping process with the KILL command.

Save the above script into a file, e.g., kill_sleeping_processes.sh. Make the script executable by running chmod +x kill_sleeping_processes.sh. Finally, execute the script using ./kill_sleeping_processes.sh. It will terminate all sleeping MySQL processes.

Please exercise caution when terminating processes. It is recommended to test scripts on non-production environments first and ensure you have appropriate permissions to execute such actions on your MySQL server.