Rotating MySQL slow query log

PDF

On MySQL servers with slow query log enabled the log file might grow excessively when having a high volume of slow queries, the same might happen with the general mysql log file.
Simply deleting the log file might cause downtime while it might take a long time to perform the deletion of the log file (depends on the log file size and filesystem)
There is safeway way to rotate the slow query log files without stopping the mysqld daemon or blocking MySql during a truncate.

/var/log/mysql/mysql_slow_query.log {
    nocompress
    create 660 mysql mysql
    size 1G
    dateext
    missingok
    notifempty
    sharedscripts
    postrotate
       /usr/local/bin/mysql -e 'select @@global.long_query_time into @lqt_save; set global long_query_time=2000; select sleep(2); FLUSH LOGS; select sleep(2); set global long_query_time=@lqt_save;'
    endscript
    rotate 150

Logrotate will run the postrotate script. Retreive the current long query time and store it into lqt_save, set the global long query time to 2000, wait for the buffers to be flushed, close and reopen the log files (FLUSH LOGS) and set the long query time back to its configured time (set global long_query_time=@lqt_save)

Tags:
 

Please login first before adding a comment.

Search






You are here: Home Howtos and FAQs MySql Rotating MySQL slow query log