Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
branch: master
Fetching contributors…

Cannot retrieve contributors at this time

executable file 270 lines (219 sloc) 9.164 kb
:
# ------------------------------------------------------------------
# Enable slow-query logging for all queries,
# write into a datestamped log file,
# let that run for a specific length of time,
# then restore the previous slow-query log settings.
#
# Copyright 2012 Bill Karwin.
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <http://www.gnu.org/licenses/>.
#
# ------------------------------------------------------------
: ${GETSLOW_CONFIG_FILE:="$HOME/.my.cnf"}
: ${GETSLOW_SLEEP_TIME:="5"}
: ${GETSLOW_LOG_FILE:=""}
: ${GETSLOW_LONG_QUERY_TIME:="0"}
: ${GETSLOW_VERBOSE:="0"}
parse_options() {
options=`getopt c:f:l:s:v $*`
if [ $? -ne 0 ] ; then
echo "$0: unrecognized options." 2>&1
exit 1
fi
eval set -- $options
while [ $# -gt 0 ]
do
case $1 in
-c) GETSLOW_CONFIG_FILE="$2" ; shift ;;
-f) GETSLOW_LOG_FILE="$2" ; shift ;;
-l) GETSLOW_LONG_QUERY_TIME="$2" ; shift ;;
-s) GETSLOW_SLEEP_TIME="$2" ;;
-v) GETSLOW_VERBOSE=1 ;;
(--) shift ; break ;;
(-*) echo "$0: error - unrecognized option $1" 1>&2 ; exit 1 ;;
(*) break ;;
esac
shift
done
mysql=`which mysql`
mysqladmin=`which mysqladmin`
if [ -e $GETSLOW_CONFIG_FILE ] ; then
mysql="$mysql --defaults-extra-file=$GETSLOW_CONFIG_FILE"
mysqladmin="$mysqladmin --defaults-extra-file=$GETSLOW_CONFIG_FILE"
fi
}
# ------------------------------------------------------------
version_check() {
version_full=`$mysql -B -N -e "SELECT @@version"`
if [ $? -ne 0 ] ; then
echo "Cannot connect to MySQL Server"
exit 1
fi
eval `echo "$version_full" | perl -wne '
my @v = split(/[.-]/);
print "version_major=",$v[0],"\n";
print "version_minor=",$v[1],"\n";
print "version_point=",$v[2],"\n";
print "version=",join(".",@v[0..2]),"\n";'`
[ $GETSLOW_VERBOSE -ne 0 ] && echo "Detected version=$version"
if [ $version_major -lt 5 -o \( $version_major -eq 5 -a $version_minor -eq 0 \) ] ; then
echo "This tool does not support MySQL version 5.0 or older. " 1>&2
exit 1
fi
version_comment=`$mysql -B -N -e "SELECT @@version_comment"`
PERCONA=`echo "$version_comment" | grep -ci "percona"`
MARIADB=`echo "$version_comment" | grep -ci "mariadb"`
if [ $PERCONA -ne 0 ] ; then
[ $GETSLOW_VERBOSE -ne 0 ] && echo "Detected Percona Server"
: ${GETSLOW_GLOBAL_CONTROL:="all"}
: ${GETSLOW_LOG_FILTER:=""}
: ${GETSLOW_RATE_LIMIT:=""}
: ${GETSLOW_RATE_TYPE:=""}
: ${GETSLOW_SP_STATEMENTS:=""}
: ${GETSLOW_TIMESTAMP_ALWAYS:=""}
: ${GETSLOW_TIMESTAMP_PRECISION:=""}
if [ $version_major -eq 5 -a $version_minor -eq 5 -a $version_point -lt 10 ] ; then
opt_slow_query_log_timestamp_precision="slow_query_log_microseconds_timestamp"
opt_slow_query_log_timestamp_always="log_slow_timestamp_every"
opt_slow_query_log_use_global_control="use_global_log_slow_control"
else
opt_slow_query_log_timestamp_precision="slow_query_log_timestamp_precision"
opt_slow_query_log_timestamp_always="slow_query_log_timestamp_always"
opt_slow_query_log_use_global_control="slow_query_log_use_global_control"
fi
if [ $version_major -eq 5 -a $version_minor -lt 5 ] ; then
opt_log_slow_rate_type=""
: ${GETSLOW_VERBOSITY:="microtime,query_plan,innodb"}
else
opt_log_slow_rate_type="log_slow_rate_type"
: ${GETSLOW_VERBOSITY:="microtime,query_plan,innodb,profiling"}
fi
fi
if [ $MARIADB -ne 0 ] ; then
[ $GETSLOW_VERBOSE -ne 0 ] && echo "Detected MariaDB"
: ${GETSLOW_VERBOSITY:="query_plan,innodb"}
fi
}
# ------------------------------------------------------------
discover_all() {
discover_one "slow_query_log"
discover_one "slow_query_log_file"
discover_one "long_query_time"
if [ $PERCONA -ne 0 -o $MARIADB -ne 0 ] ; then
[ -z "$GETSLOW_VERBOSITY" ] || discover_one "log_slow_verbosity"
[ -z "$GETSLOW_LOG_FILTER" ] || discover_one "log_slow_filter"
[ -z "$GETSLOW_RATE_LIMIT" ] || discover_one "log_slow_rate_limit"
fi
if [ $PERCONA -ne 0 ] ; then
[ -z "$GETSLOW_GLOBAL_CONTROL" ] || discover_one "$opt_slow_query_log_use_global_control"
[ -z "$GETSLOW_RATE_TYPE" -o -z "$opt_log_slow_rate_type" ] || discover_one "$opt_log_slow_rate_type"
[ -z "$GETSLOW_SP_STATEMENTS" ] || discover_one "log_slow_sp_statements"
[ -z "$GETSLOW_TIMESTAMP_ALWAYS" ] || discover_one "$opt_slow_query_log_timestamp_always"
[ -z "$GETSLOW_TIMESTAMP_PRECISION" ] || discover_one "$opt_slow_query_log_timestamp_precision"
fi
if [ $MARIADB -ne 0 ] ; then
: ${MARIADB}
fi
}
# ------------------------------------------------------------
discover_one() {
variable="$1"
value=`$mysql -s -N -e "SELECT @@$variable"`
error_check "$?" "Discovering $variable"
eval "$variable='$value'"
}
# ------------------------------------------------------------
set_all() {
start_time=`date +%Y%m%d%H%M%S`
: ${GETSLOW_LOG_FILE:="${slow_query_log_file}-full-${start_time}"}
set_one "slow_query_log_file" "'$GETSLOW_LOG_FILE'"
set_one "long_query_time" "$GETSLOW_LONG_QUERY_TIME"
if [ $PERCONA -ne 0 ] ; then
[ -z "$GETSLOW_GLOBAL_CONTROL" ] || set_one "$opt_slow_query_log_use_global_control" "'$GETSLOW_GLOBAL_CONTROL'"
[ -z "$GETSLOW_RATE_TYPE" -o -z "$opt_log_slow_rate_type" ] || set_one "log_slow-rate_type" "'$GETSLOW_RATE_TYPE'"
[ -z "$GETSLOW_SP_STATEMENTS" ] || set_one "log_slow_sp_statements" "$GETSLOW_SP_STATEMENTS"
[ -z "$GETSLOW_TIMESTAMP_ALWAYS" ] || set_one "$opt_slow_query_log_timestamp_always" "$GETSLOW_TIMESTAMP_ALWAYS"
[ -z "$GETSLOW_TIMESTAMP_PRECISION" ] || set_one "$opt_slow_query_log_timestamp_precision" "'$GETSLOW_TIMESTAMP_PRECISION'"
fi
if [ $PERCONA -ne 0 -o $MARIADB -ne 0 ] ; then
[ -z "$GETSLOW_VERBOSITY" ] || set_one "log_slow_verbosity" "'$GETSLOW_VERBOSITY'"
[ -z "$GETSLOW_LOG_FILTER" ] || set_one "log_slow_filter" "'$GETSLOW_LOG_FILTER'"
[ -z "$GETSLOW_RATE_LIMIT" ] || set_one "log_slow_rate_limit" "$GETSLOW_RATE_LIMIT"
fi
if [ $MARIADB -ne 0 ] ; then
: ${MARIADB}
fi
set_one "slow_query_log" "0" "Disabling slow_query_log"
set_one "slow_query_log" "1" "Enabling slow_query_log"
}
# ------------------------------------------------------------
set_one() {
variable="$1"
value="$2"
message="${3:-Setting $variable=$value}"
$mysql -e "SET GLOBAL $variable=$value"
error_check "$?" "$message"
}
# ------------------------------------------------------------
restore_all() {
restore_one "slow_query_log_file" "'$slow_query_log_file'"
restore_one "long_query_time" "$long_query_time"
if [ $PERCONA -ne 0 ] ; then
[ -z "$GETSLOW_GLOBAL_CONTROL" ] || restore_one "slow_query_log_use_global_control" "'$slow_query_log_use_global_control'"
[ -z "$GETSLOW_RATE_TYPE" ] || restore_one "log_slow_rate_type" "'$log_slow_rate_type'"
[ -z "$GETSLOW_SP_STATEMENTS" ] || restore_one "log_slow_sp_statements" "$log_slow_sp_statements"
[ -z "$GETSLOW_TIMESTAMP_ALWAYS" ] || restore_one "slow_query_log_timestamp_always" "$slow_query_log_timestamp_always"
[ -z "$GETSLOW_TIMESTAMP_PRECISION" ] || restore_one "slow_query_log_timestamp_precision" "$slow_query_log_timestamp_precision"
fi
if [ $PERCONA -ne 0 -o $MARIADB -ne 0 ] ; then
[ -z "$GETSLOW_VERBOSITY" ] || restore_one "log_slow_verbosity" "'$log_slow_verbosity'"
[ -z "$GETSLOW_LOG_FILTER" ] || restore_one "log_slow_filter" "'$log_slow_filter'"
[ -z "$GETSLOW_RATE_LIMIT" ] || restore_one "log_slow_rate_limit" "$log_slow_rate_limit"
fi
if [ $MARIADB -ne 0 ] ; then
: ${MARIADB}
fi
set_one "slow_query_log" "0" "Disabling slow_query_log"
restore_one "slow_query_log" "$slow_query_log"
}
# ------------------------------------------------------------
restore_one() {
variable="$1"
value="$2"
set_one "$variable" "$value" "Restoring $variable=$value"
}
# ------------------------------------------------------------
error_check() {
status=$1
message=$2
if [ $status -ne 0 ] ; then
echo "Error occurred while trying: $message" >&2
exit 1
else
[ $GETSLOW_VERBOSE -ne 0 ] && echo "$message"
fi
}
# ------------------------------------------------------------
parse_options "$@"
version_check
discover_all
trap restore_all EXIT INT QUIT TERM ERR
set_all
[ $GETSLOW_VERBOSE -ne 0 ] && echo -n "Sleeping $GETSLOW_SLEEP_TIME seconds... "
sleep $GETSLOW_SLEEP_TIME
[ $GETSLOW_VERBOSE -ne 0 ] && echo "done."
# Let the trap restore all the settings on EXIT.
exit 0
# ------------------------------------------------------------
Jump to Line
Something went wrong with that request. Please try again.