I couldn’t find a mysql slow query log parser any where on the web that I could utilize, so I wrote one in bash. This script will parse a MySQL Slow Query Log with the following parameters:
–current mysql slow query log path –query time
Here is a CLI example:
path_to_this_mysql_slow_query_parser.sh /path_to_your/mysql-sloq-query-log 30
This will print all slow queries that took 30 seconds or longer ~/Desktop/MSQP_ouput.txt
#!/bin/bash
# Galen Sprague 2009
# This script will parse a MySQL Slow Query Log with the following parameters.
# “–current mysql slow query log path –query time”
# CLI example:
# path_to_this_mysql_slow_query_parser.sh /path_to_your/mysql-sloq-query-log 30
# This would print all slow queries that took 30 seconds and higher to ~/Desktop/MSQP_ouput.txt
#count each line of the mysql slow query log
let COUNTER=0
#counter for each slow query
let SLOWQUERYCOUNTER=0
#read each line of the mysql slow query log
cat $1 | while read LINE
do
# Count each LINE
let COUNTER=COUNTER+1
#While reading each line see if we are on a line that contians “# Time: “
if [[ $LINE == *'# Time: '* ]]
then
ACTUALTIMEQUERYWASRUN=$LINE
TIMEALREADYPRINTED=’false’
fi
#While reading each line see if we are on a line that contians “# Query_time: “
if [[ $LINE == *'# Query_time: '* ]]
then
# Get the query_time number on this line
# Example: “# Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 85102″
# by parsing every thing from the right of “time:”
LINE=${LINE#*time:}
# and by parsing everything from the left of “Lock” giving use the query time number
LINE=${LINE%% Lock*}
if (( $LINE >= $2 ))
then
# GETLINESTART = Go back a line from the query time to collect the user data
let GETLINESTART=COUNTER-1
# For CLI ouput
let SLOWQUERYCOUNTER=SLOWQUERYCOUNTER+1
# To show progress in CLI
echo $SLOWQUERYCOUNTER”. High Query_time on line: ” $COUNTER ” printing to file…”
# print the time for these queries only once
if [[ $TIMEALREADYPRINTED == 'false' ]]
then
# echo time the query was run
echo $ACTUALTIMEQUERYWASRUN >> ~/Desktop/MSQP_output.txt
TIMEALREADYPRINTED=’true’
fi
#Print Query info
sed -n -e “$GETLINESTART,/;/p” $1 >> ~/Desktop/MSQP_output.txt
#if there is a use database statement first then add the following select statement
let USEDBLINE=COUNTER+1
CHECKFORUSEDB=`sed -n -e “$USEDBLINE p” $1`
if [[ $CHECKFORUSEDB == *'use '* ]]
then
echo “We are in the USEDB if…”
let SELECTLINE=USEDBLINE+1
sed -n -e “$SELECTLINE p” $1 >> ~/Desktop/MSQP_output.txt
let SELECTLINE=0
fi
let USEDBLINE=0
# echo line feed to separate queries
echo >&2 >> ~/Desktop/MSQP_output.txt
fi
fi
done
#!/bin/bash
#count each line of the mysql slow query log
let COUNTER=0
#counter for each slow query
let SLOWQUERYCOUNTER=0
#read each line of the mysql slow query log
cat $1 | while read LINE
do
# Count each LINE
let COUNTER=COUNTER+1
#While reading each line see if we are on a line that contians “# Time: “
if [[ $LINE == *'# Time: '* ]]
then
ACTUALTIMEQUERYWASRUN=$LINE
TIMEALREADYPRINTED=’false’
fi
#While reading each line see if we are on a line that contians “# Query_time: “
if [[ $LINE == *'# Query_time: '* ]]
then
# Get the query_time number on this line
# Example: “# Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 85102″
# by parsing every thing from the right of “time:”
LINE=${LINE#*time:}
# and by parsing everything from the left of “Lock” giving use the query time number
LINE=${LINE%% Lock*}
if (( $LINE >= $2 ))
then
# GETLINESTART = Go back a line from the query time to collect the user data
let GETLINESTART=COUNTER-1
# For CLI ouput
let SLOWQUERYCOUNTER=SLOWQUERYCOUNTER+1
# To show progress in CLI
echo $SLOWQUERYCOUNTER”. High Query_time on line: ” $COUNTER ” printing to file…”
# print the time for these queries only once
if [[ $TIMEALREADYPRINTED == 'false' ]]
then
# echo time the query was run
echo $ACTUALTIMEQUERYWASRUN >> ~/Desktop/MSQP_output.txt
TIMEALREADYPRINTED=’true’
fi
#Print Query info
sed -n -e “$GETLINESTART,/;/p” $1 >> ~/Desktop/MSQP_output.txt
#if there is a use database statement first then add the following select statement
let USEDBLINE=COUNTER+1
CHECKFORUSEDB=`sed -n -e “$USEDBLINE p” $1`
if [[ $CHECKFORUSEDB == *'use '* ]]
then
#echo “We are in the USEDB if…”
let SELECTLINE=USEDBLINE+1
#sed -n -e “$SELECTLINE p” $1 >> ~/Desktop/MSQP_output.txt
GETSELECTSTATEMENT=`sed -n -e “$SELECTLINE p” $1`
if [[ $GETSELECTSTATEMENT == 'SELECT ' ]]
then
#there are multiple lines for this select statement
sed -n -e “$SELECTLINE,/;/p” $1 >> ~/Desktop/MSQP_output.txt
else
#there is just one line
sed -n -e “$SELECTLINE p” $1 >> ~/Desktop/MSQP_output.txt
fi
let SELECTLINE=0
fi
let USEDBLINE=0
# echo line feed to separate queries
echo >&2 >> ~/Desktop/MSQP_output.txt
fi
fi
done
Enjoy!
