Home / server / Questions / 279401
Accepted
Phanindra
Asked: 2011-06-11 22:37:44 +0800 CST

MySQL Query log not getting purged

  • 772

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.

mysql logging

1 Answers

  1. Best Answer

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

    • 1