Log file rotation problem

aliweb · 01-17-2011, 12:34 AM

Hi,

I am on a CentOS 64 bit and used webmin to rotate MySQL slow query log. Now the thing is although logs are being rotated daily but the new log files being created are empty which means MySQL is unable to write them due to some reason. I have checked using "ls -l" that both permission and owner of files is same but still MySQL is not writing logs. What could be the reason?

Thanks

bathory · 01-17-2011, 02:02 AM

Hi,

This usually means that mysqld was not restarted after the logs rotation, so the server still writes its logs in the old log file.

Regards

Andy Alt · 01-17-2011, 02:59 AM

What bathory said.

As I understand it, if the log file is emptied by another program while mysql is running, mysql doesn't know about it, so gets confused.

In a few logrotate scripts, the daemon is restarted. Here's an example from my httpd logrotate script. (DO NOT copy and paste)

Code:

/var/log/httpd/*_log {
  rotate 10
  notifempty
  size=5M
  compress
  delaycompress
  sharedscripts
  postrotate
    /etc/rc.d/rc.httpd restart
  endscript
}

aliweb · 01-17-2011, 03:47 AM

But shouldn't MySQL do it without restarting? I mean what if my site is heavy traffic and I want daily log rotation. So that means I will have to restart it daily!?

aliweb · 01-17-2011, 03:59 AM

Ok I found out that you have to use "flush-logs" command to flush old logs and then MySQL creates a new one.

Quote:

shell> mysqladmin flush-logs

http://dev.mysql.com/doc/refman/5.0/...intenance.html

But why do I have to do it manually? Should webmin be doing this automatically when it runs that script to rotate log file?

bathory · 01-17-2011, 05:37 AM

Quote:

But why do I have to do it manually? Should webmin be doing this automatically when it runs that script to rotate log file?

I'm not using webmin, but I guess you have to configure the module that rotates the logs, to run "mysqladmin flush-logs" after logs rotation

aliweb · 01-17-2011, 06:28 AM

Quote:

Originally Posted by bathory

I'm not using webmin, but I guess you have to configure the module that rotates the logs, to run "mysqladmin flush-logs" after logs rotation

Oh yes there is an option in webmin through which you can run a command after log file is rotated. I completely missed it. Will try it and then see how it goes.

aliweb · 01-21-2011, 12:47 AM

After observing a couple of days I see that the post rotation command option in webmin is not working. It is not running "mysqladmin flush-logs" command automatically after logs are rotated even though I have defined in it. When I run this command manually then it works.

bathory · 01-21-2011, 02:02 AM

Hi,

Maybe you should give the full path to the command, like:

Code:

/usr/bin/mysqladmin flush-logs

or even add "-u root -p<password>"

aliweb · 02-02-2011, 04:55 AM

Ok I tried the following in webmin but none of them works.

Code:

/usr/bin/mysqladmin flush-logs

Code:

/usr/bin/mysqladmin -u root -p<ROOT_PASSWORD> flush-logs

Code:

/usr/bin/mysqladmin -u <DB_USER> -p<DB_PASSWORD> flush-logs

bathory · 02-02-2011, 07:01 AM

Hi,

Have a look here

Regards