THERE4.IO Craig Davis

Software

Enable MySQL logging for Debugging at Runtime

Tail the MySQL query log

Starting with MySQL 5.1 you can enable and disable logs at runtime. This allows you to tail the general and slow query log from the command line and keep an eye on things. This can be especially useful when running with an ORM and you want to see the final queries as executed.

Enable Logging

This approach does not require a server restart. Login to MySQL as the root user via mysql -u root -p and then issue the following:

SET GLOBAL general_log = 'ON';
SET GLOBAL slow_query_log = 'ON';

To disable logs, login to mysql client again and then issue:

SET GLOBAL general_log = 'OFF';
SET GLOBAL slow_query_log = 'OFF';

Watching the Log File

You can use either tail -f or less -F to follow the log as it’s being written to. In order to confirm the location of the log, you can run this in MySQL:

show variables like '%log%';

And then you can run something like: tail -f /var/log/syslog

Mytail : MySQL Logging Made Easy

I’ve wrapped up this into an easy to use command. This bash script mytail will enable the logs, tail them, and then when you exit with ctrl+c will disable the logs once again.

In addition, if you have a .grc.sql.conf file and grc installed from garabik/grc the log will be colorized.

#!/bin/bash
# mytail: Enable and tail the MySQL general log

me=$(basename "$0")
requirements="tail"
colorizer="~/.grc.sql.conf"
args=$(echo "$@" | sed "s/ \-\- .*//g")

_getarg(){
  local arg_exist=$(echo "$args" | grep -e "-$1" && echo 1)
  local lastarg=""
  if [ "$arg_exist" ]; then
    for arg in $args; do
      local match=$(echo "$arg" | grep "\-$1" && echo 1)
      if [ "$match" ]; then
        local arg_has_value=$(echo "$arg" | grep -e "-$1[=| ][A-Za-z0-9]" && echo 1)
        if [ ! "$arg_has_value" ]; then echo "1"; return 0; else
          local assign=$(echo "$arg" | sed "s/.*=//g" 2>/dev/null)
          if [ ${#assign} == 0 ]; then echo "$lastarg"; return 0;
          else echo "$assign"; return 0; fi
        fi
        lastarg="$arg"
      fi
    done
  fi
  return 1
}

mysql_log(){
  dbuser=$(_getarg "user")
  dbpass=$(_getarg "password")

  if [ -z $dbuser ]; then
    echo "Enable MySQL general log and tail output."
    echo "usage: mytail --user=root --password=somepassword"
    exit 1
  fi

  if [ -z $dbpass ]; then
    read -s -p "Enter password: " dbpass
  fi

  mysql -u$dbuser -p$dbpass -e"SET GLOBAL general_log = 'ON';" || exit 1
  loginfo=$(mysql -u$dbuser -p$dbpass -e"show variables like 'general\_log\_file';")
  loginfo=${loginfo:37}
  trap _mysql_log_shutdown SIGINT

  if [ ! -f $colorizer ]; then
    sudo tail -f $loginfo
  else
    sudo grc -c $colorizer tail -f $loginfo
  fi
}

_mysql_log_shutdown()
{
  mysql -u$dbuser -p$dbpass -e"SET GLOBAL general_log = 'OFF';"
  return $?
}

checkrequirements(){
  for req in $requirements; do
    hash "$req" 2>&-
    if [ $? == 1 ]; then echo "You need to install '$req'"; exit; fi
  done
}

checkrequirements
mysql_log
exit 0

Return to top