jon zobrist (dot) com

You want general query logging in, but don’t want to keep those pesky query log files around?

I generally setup the MySQL user and run this from cron.

Here’s my script to do so, here’s a gzip’d version, and here is a zipped version

#!/bin/bash
#setup a user in mysql with the RELOAD priviledge
#grant RELOAD on *.* to reloader@'localhost' identified by 'PASSWORD';
#flush privileges;

user="reloader"
password="PASSWORD"
LOG="${HOME}/mysqld/mysqld.log"
LOGARCHIVE="${HOME}/logarchive"
NEW_LOG="${LOGARCHIVE}/mysqld-`date +%F-%s`.log"
KEEP_MIN=15
MAX_LOAD="5.0"
RUNFILE="${HOME}/rotate-general-log.pid"

if [ -f ${RUNFILE} ]
 then
  echo "Runfile ${RUNFILE} exists, exiting at `date`"
  ${HOME}/bin/page.sh "Runfile ${RUNFILE} exists, exiting at `date`"
  exit 1
 else
  echo "$$" > ${RUNFILE}
fi

#if [ $(echo "`cut -f1 -d ' ' /proc/loadavg` < ${MAX_LOAD}" | bc) -eq 1 ];
if [ $(echo "`cut -f1 -d ' ' /proc/loadavg` < ${MAX_LOAD}" | bc) -eq 1 ];
 then
  echo "System load less than ${MAX_LOAD}, proceeding [DEBUG]"
else
  echo "log rotate delayed, due to system load > ${MAX_LOAD}"
  ${HOME}/bin/page.sh "log rotate delayed, due to system load > ${MAX_LOAD}"
  /bin/rm ${RUNFILE}
  exit 1
fi

MYSQLD_PID=`pgrep mysqld`

if [ ! "${MYSQLD_PID}" ]
 then
  echo "Mysqld is NOT running, paging and exiting at `date`"
  ${HOME}/bin/page.sh "NO MySQLD on `hostname` at `date`"
  /bin/rm ${RUNFILE}
  exit 1
else
  echo "Mysqld running at `date`, PID ${MYSQLD_PID}, continuing"
fi

if [ -d "${LOGARCHIVE}" ]
 then
  echo "Moving general log at `date`"
  /bin/mv ${LOG} ${NEW_LOG}
  touch ${LOG}
  chmod og-rwx ${LOG}
  echo "Flushing general log at `date`"
  mysqladmin -u ${user} -p --password="${password}" flush-logs
  echo "Done flushing general log at `date`"
  echo "Gzipping ${NEW_LOG} log at `date`"
  gzip ${NEW_LOG}
  echo "Done gzipping ${NEW_LOG} log at `date`"
  echo "Size is `du -sh ${LOGARCHIVE}` pre-clean"
  echo "Cleaning ${LOGARCHIVE}, deleting files older than ${KEEP_MIN} minutes at `date`"
  find ${LOGARCHIVE} -iname "*.gz" -mmin +${KEEP_MIN} -print -exec /bin/rm {} \;
  echo "Done cleaning ${LOGARCHIVE}, at `date`"
  echo "Size is `du -sh ${LOGARCHIVE}` post-clean"
else
 echo "Missing logarchive dir ${LOGARCHIVE}"
  ${HOME}/bin/page.sh "Missing logarchive dir ${LOGARCHIVE}"
 /bin/rm ${RUNFILE}
 exit 1
fi

/bin/rm ${RUNFILE}
echo "Done at `date`"