MySQL doesn't logs error to new file after rotating?

Problem solved but I'm writing down for the future reference.

/root/.my.cnf

[mysqladmin]
user            = root
password        = pa$$w0rd

/etc/logrotate.d/mysql

/var/log/mysql-slow.log /var/log/mysqld.log {
    daily
    rotate 7
    dateext
    compress
    missingok
    #notifempty
    sharedscripts
    create 644 mysql mysql
    postrotate
        /usr/bin/mysqladmin flush-logs
    endscript
}

logrotate is working fine when running from the command line:

# logrotate -v -f /etc/logrotate.d/mysql

but it doesn't work when running from cron at 4 A.M. The logs file was rotated but MySQL doesn't logs the error to newly created file:

-rw-r--r-- 1 mysql mysql      0 Aug  7 10:13 /var/log/mysqld.log
-rw-r--r-- 1 mysql mysql     20 Aug  4 04:04 /var/log/mysqld.log-20120804.gz
-rw-r--r-- 1 mysql mysql     20 Aug  5 04:04 /var/log/mysqld.log-20120805.gz
-rw-r--r-- 1 mysql mysql     20 Aug  6 16:28 /var/log/mysqld.log-20120806.gz

In the postrotate, I redirect both stderr and stdout to a log file to see what happens:

postrotate
    /usr/bin/mysqladmin flush-logs > /var/log/mysqladmin.flush-logs 2>&1
endscript

What I get is:

/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

It sounds like mysqladmin doesn't read /root/.my.cnf during logrotate.

So, try this:

postrotate
    env HOME=/root/ /usr/bin/mysqladmin flush-logs > /var/log/mysqladmin.flush-logs 2>&1
endscript

Source:

http://bugs.mysql.com/bug.php?id=51005
http://scribble.scran.ac.uk/svene/weblog/2893.html

I had a similar problem.

I didn't restart MySQL after adding /root/.my.cnf, so the postrotate flush command wasn't run.

Once I restarted MySQL it read the root my.cnf file and worked as expected.

MySQL doesn't logs error to new file after rotating?

Related

Recent Posts