OS X Methods

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

#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!