Comments on: Log rotate and the (deleted) MySQL log file mystery

By: Arvi

Arvi — Tue, 18 Jul 2017 08:11:19 +0000

I had same issue on mysql 5.0 when I executed logrotate through crontab (Worked perfectly on mysql5.5 though) and got my script fixed by passing HOME in cron itself.
But even after executing flush-logs successfully, mysql is not able to write into –err-log file and lsof shows the (deleted) files are still there.
Is there a way I can get rid of these (deleted) error file(there are 2 FD for deleted error file) without stopping mysql.
Thanks

By: yves guerin

yves guerin — Tue, 12 Jul 2016 12:43:18 +0000

for the user/password use mysqladmin –defaults-file=/etc/mysql/debian.cnf for ubuntu 14.04 so no need to set HOME

By: Anton

Anton — Fri, 19 Feb 2016 07:58:40 +0000

golden post!

By: Daniel Guzmán Burgos

Daniel Guzmán Burgos — Mon, 31 Aug 2015 16:31:14 +0000

Burn:

Logrotate is not a Percona tool nor it comes with Percona rpms. Logrotate is a tool created by RedHat that comes with several Linux distros.

However, Percona Server does it come with a feature to manage Slow Query Log Rotation and Expiration in case you don’t want to/can’t use Logrotate: https://www.percona.com/doc/percona-server/5.6/flexibility/slowlog_rotation.html

By: Burn

Burn — Tue, 25 Aug 2015 06:34:59 +0000

So if you’re aware of the problem, why is it that Percona rpms still have this faulty logrotate?

By: danblack

danblack — Wed, 19 Nov 2014 07:17:47 +0000

mysqladmin with the wrong credentials will still return true which is why its normally not required to set HOME for it.

If your ~/.my.cnf contains any setting of non-default location of socket or port then mysqladmin will also need HOME=/root/ set.

By: nate

nate — Fri, 14 Nov 2014 01:22:47 +0000

a simpler solution may just be use copytruncate option in logrotate, I use this in a lot of cases where the apps don’t have built in log rotation. slow query log and mysql logs don’t get appended to all that much in a lot of cases(my logs are still small and go back a year). Sure there is some tiny risk of losing a bit of data during the copytruncate but in the past what 12 years of me using this I’ve never had a complaint that someone lost data in any log file.

not everyone’s db is the same of course but for the past year on my main prod database slow query log is 1.6G and error.log is 22k (all of this data feeds into splunk as well, pretty rare to reference the log files directly).

By: Fernando Ipar

Fernando Ipar — Wed, 12 Nov 2014 20:54:18 +0000

Sending SIGHUP involves more things than just flushing logs, as explained here http://bugs.mysql.com/bug.php?id=65481, so if you just want to flush logs, the ideal thing is to use the CLI with the right credentials.

By: Raghavendra Prabhu

Raghavendra Prabhu — Wed, 12 Nov 2014 18:40:33 +0000

Instead of mysql -e ‘………’ (which can also fail if credentials are elsewhere or in some other root or few other things), sending a COM_DEBUG (essentially a mysqladmin debug) with SIGHUP (like in other web servers etc.) should also do. Similar thing is done in init scripts and/or packaging post-installs where requirement of credentials to do non-SQL operations (like logrotate) is not required, and cannot be expected to be present at known locations/