Mysql thread list

get_threads.sh

#!/bin/sh

SEARCH="tmp"

if [[ $# -eq 1 ]]
then
SEARCH=$1
elif [[ $# -gt 1 ]]
then
echo -ne "ERROR: Use without arguments to search for threads using tmp tables, or specify a single argument to search for in the 'state' field.\n"
exit 0
fi


# Output total number of current threads
echo -ne "\n"
mysql -e "SELECT COUNT(ID) AS 'Total number of MySQL threads' FROM INFORMATION_SCHEMA.PROCESSLIST;" | sed "$!{:a;N;s/\n/: /;ta}";


# Output threads containing CLI argument in state field
echo -ne "\nThreads containing '$SEARCH' in the state field:\n"
mysql -e "SELECT COUNT(ID) AS 'Number of threads' FROM INFORMATION_SCHEMA.PROCESSLIST WHERE state LIKE '%$SEARCH%';" | sed "$!{:a;N;s/\n/: /;ta}";
mysql -A -e "SELECT id, state, time, SUBSTRING(INFO, 0, 20) AS query FROM INFORMATION_SCHEMA.processlist WHERE state LIKE '%$SEARCH%';"

# Output full processlist to file
FILENAME="/home/lalrorg/processlists/mysql_processlist_`date +%F_%H-%M-%S`.txt"

echo -ne "\nCollecting FULL PROCESSLIST...\n"
mysql -A -e 'SHOW FULL PROCESSLIST \G' > $FILENAME && echo -ne "Full processlist written to $FILENAME \n\n"