Watching or killing long mysql queries #mysql #docker

mysql_watcher.sh

#!/bin/bash

# queries which executes >= THIS VAR will be killed
KILL_TIME=0

# settings for mysql connection
DB_LOGIN_PATH="local"
# You can create login-path like this:
# mysql_config_editor set --login-path=local --host=0.0.0.0 --user=root --password

# Use next two lines if and how you need
# QUERY_USER="USER = 'user_name' AND "
# QUERY_DB="DB = 'db_name' AND "
QUERY="SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, LEFT(INFO,220) \
 FROM INFORMATION_SCHEMA.PROCESSLIST \
 ${QUERY_USER}\
 ${QUERY_DB}\
 WHERE info IS NOT NULL AND TIME >= ${KILL_TIME};"

# get long queries table, skiping head line (first line)
QUERIES_TABLE=`mysql --login-path=${DB_LOGIN_PATH} -e "${QUERY}" | tail -n +2` 

# parse and action.
# iterate over table rows.
while read query_info_row; do
  # iterate over row columns
  while read -r _cid_ _user_ _host_ _db_ _command_ _time_ _state_ _info_
  do
    # echo "${_cid_} ${_user_} ${_host_} ${_db_} ${_command_} ${_time_} ${_state_} ${_info_}"

    if [ ! -z "${_cid_}" ]; then
      echo "Query: ${_info_}"
      echo "executing seconds: ${_time_}"
      echo "by user: '${_user_}'"
      echo "in db: '${_db_}'"
      echo
      echo "You can kill this query by:"
      echo "mysql --login-path=${DB_LOGIN_PATH} -e \"KILL QUERY ${_cid_}\""
      # or kill query right now
      # mysql --login-path=${DB_LOGIN_PATH} -e "KILL QUERY ${_cid_}"
    fi
  done <<< "${query_info_row}"

done <<< "${QUERIES_TABLE}"

exit 0

result.txt

Query: SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, LEFT(INFO,220)  FROM INFORMATION_SCHEMA.PROCESSLIST    WHERE info IS NOT NULL AND TIME >= 0
executing seconds: 0
by user: 'root'
in db: 'NULL'

You can kill this query by:
docker exec mysql mysql -u root -e "KILL QUERY 3514"