rotating mysql slow query log

Question

My server's MySQL slow query log is growing day by day (37 MB now) so I want to rotate it. If I move current log file to another folder then will MySQL automatically create another log file? Think of it like I am deleting current log file, so will MySQL automatically create a new one when another slow query comes?

Thanks

score 8 · Answer 1 · edited Nov 10 '16 at 11:34

you simply cant use logrotate to do that , you will have to first change the file name in my.cnf and than do what ever wants to do.reload the mysql.

if you want the logrotate way , you will have to disable the slow query log for that time.

The logrotate thing was suggested by percona team and works for me.

/var/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
}

score 1 · Accepted Answer · answered Jan 05 '11 at 07:17

1

You can use logrotate script to periodically rotate MySQL logs, and possibly keep a limited number of previous logs (to save space). You can configure it with any schedule you like.

Personally, I found it easy to configure using Webmin GUI

answered Jan 05 '11 at 07:17

usr-local-ΕΨΗΕΛΩΝ

26,101
30
154
305

I have installed webmind on CentOS but I can't open it. It says webmin service is running and I can access it at https://www.myserver.com:10000/ but when I open it in browser, nothing happens. I have tried with and without https. SSL is installed on server. – Ali Jan 05 '11 at 07:37
You should then first work to fix the webmin problem, maybe asking on superuser.com. Or, maybe, you should ask **how to set** a logrotate entry ;) – usr-local-ΕΨΗΕΛΩΝ Jan 05 '11 at 07:40
Ok I managed to run webmin. And I have also added MySQL in log rotation let's see what happens. – Ali Jan 14 '11 at 07:37
Ok I checked and found out that MySQL is not writing log files now. Although logs are being rotated and new log files are being created with same permissions and owner but these files are empty which means MySQL is not writing due to some problem. What could be that problem be? – Ali Jan 17 '11 at 06:24
1

I'm pretty sure you can't just move the old file out and replace it with an empty file. I think you need either to restart MySQL or use the FLUSH LOGS command, see here http://stanley-huang.blogspot.co.uk/2010/03/level-2-rotate-mysql-general-log-and.html – liquorvicar Sep 26 '12 at 18:06

score -2 · Answer 3 · answered Jan 05 '11 at 07:32

-2

You can setup a daily cron which will be ziping and emptying the logfile:

zip /tmp/$(date +%Y-%m-%d)-slow-query-log.zip /var/lib/mysql/slow-queries.log
cat /dev/null > /var/lib/mysql/slow-queries.log

Of course, if your slow query log has so much content logged every day you (or someone else) ought to investigate and fix the bad queries :)

answered Jan 05 '11 at 07:32

cherouvim

31,725
15
104
153

3

This is generally unwise. See http://www.mysqlperformanceblog.com/2013/04/18/rotating-mysql-slow-logs-safely/ and http://dev.mysql.com/doc/refman/5.1/en/log-file-maintenance.html. – Neek Jul 29 '13 at 03:46

rotating mysql slow query log

3 Answers3

Linked