MySQL Query log not getting purged

Question

I had enabled MySQL Query Log for checking which queries are getting executed from our application at certain time interval.

I wanted Logs to be refreshed for 3 days once, So I had enabled Expiry Log days as 3 days.

But when I saw the log file, the log was there from the date of enabled the Query Log which was more than a week.

Due to this the log file got increased to more than 2 GB file which was not able to get the information from it.

Is there any option to enable so that Query Log file will be refreshed/purged after some days.

I am using MySQL 5.0.24 server.

Answer 1

expire_logs_days is responsible for rotating binary logs only.

You may need to zap the error log yourself.

Try doing this (Example : /var/log/mysqld.log is the error log)

Step 01) Create a script to copy the error log and gzip it, naming it by date and time (call the script /root/rotate_mysqld_log.sh)

cd /var/log
DT=`date +"%Y%m%d_%H%M%S"`
BACKUP_ERROR_LOG=mysqld_${DT}.log.gz
cat mysqld.log | gzip > ${BACKUP_ERROR_LOG}
echo -n > mysqld.log

Step 02) make /root/rotate_mysqld_log.sh executable

chmod +x /root/rotate_mysqld_log.sh

Step 03) Add the script to the crontab (Rotate at midnight)

0 0 * * * /root/rotate_mysqld_log.sh

Give it a Try !!!