#!/bin/bash


USER=
PASSWD=
LOG_FILE=/tmp/mysql_processlist.log


exec_sql() {
  mysql -u${MYSQL_REPL_USER:=$USER} -p${MYSQL_REPL_PASSWORD:=$PASSWD} -h${HOST:-127.0.0.1} -N -e "$1"
}

printf_result() {

  Thread_os_id=$(echo $result | awk '{print $2}')
  Processlist_id=$(echo $result | awk '{print $3}')
  #User=$(echo $result | awk '{print $4}')
  #Host=$(echo $result | awk '{print $5}')
  #Database=$(echo $result | awk '{print $6}')
  #Command=$(echo $result | awk '{print $7}')
  Time=$(echo $result | awk '{print $8}')
  #State=$(echo $result | awk '{print $9}')
  Info=$(echo "$result" | awk '{for (i=9;i<=NF;i++)printf("%s ", $i);print ""}'| sed -e 's/^[ ]*//g' | sed -e 's/[ ]*$//g')
  CPU_used=$(top -b -H -n 1 -u mysql |grep --color -E "\s+${i}\s" | awk '{print $9}')
  MEM_used=$(top -b -H -n 1 -u mysql |grep --color -E "\s+${i}\s" | awk '{print $10}')
  if [ ! -z "$result" -a "$Info" != "NULL" ];then
    printf "%-15s %-15s %-5.2f %-5.2f %-10s %-10s %-20.s\n" $Thread_os_id $Processlist_id $CPU_used $MEM_used $Time "$Info" >> $LOG_FILE
  fi
}

while true
do
  CPU_TPID=$(top -b -H -n 1 -u mysql |sed -n '8,$p' |sort -g -r -k 9 |awk '{print $1}')
  #MEM_TPID=$(top -b -H -n 1 -u mysql |sed -n '8,17'p |sort -g -r -k 10 |awk '{print $2}')
  echo "============$(date "+%Y-%m-%d %T")====================="  >> $LOG_FILE
  
  echo "Top of used CPU:"  >> $LOG_FILE
  #echo "# CPU column shows the percentage of single core cpu usage"  >> $LOG_FILE
  printf "%-15s %-15s %-5s %-5s %-10s %-10s\n" Thread_os_id Processlist_id CPU% MEM% Time Info  >> $LOG_FILE
  printf "%-15s %-15s %-5s %-5s %-10s %-10s\n" ============ ============== ==== ==== ==== ====  >> $LOG_FILE
  for i in $CPU_TPID
  do
    result=$(exec_sql "SELECT a.thread_id,a.thread_os_id,a.processlist_id,b.user,b.host,b.db,b.command,b.time,b.info FROM performance_schema.threads a LEFT JOIN information_schema.processlist b ON a.processlist_id = b.id where a.type = 'FOREGROUND' AND thread_os_id="$i";" 2>/dev/null)
    printf_result
  done
sleep 5
echo -e "\n" >> $LOG_FILE
done

