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

Question

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

score 12 · Accepted Answer · answered Aug 09 '12 at 03:35

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:

score 1 · Answer 2 · answered Jan 14 '13 at 10:28

1

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.

answered Jan 14 '13 at 10:28

codewaggle

247
1
4
11

score 0 · Answer 3 · answered Mar 21 '20 at 23:36

In my case. I need have been set --defaults-file="/root/.my.cnf" to /root/.my.cnf in /usr/local/mysql/bin/mysqladmin --defaults-file="/root/.my.cnf" ping and /usr/local/mysql/bin/mysqladmin --defaults-file="/root/.my.cnf" flush-logs

/etc/logrotate.d/mysql

/var/log/mysql/mysql_general_log.log
/var/log/mysql/error.log {
        create 600 mysql mysql
        notifempty
        daily
        rotate 5
        missingok
        compress
    postrotate
    # just if mysqld is really running
    if test -x /usr/local/mysql/bin/mysqladmin && \
        /usr/local/mysql/bin/mysqladmin --defaults-file="/root/.my.cnf" ping &>/dev/null
    then
        /usr/local/mysql/bin/mysqladmin --defaults-file="/root/.my.cnf" flush-logs
    fi
    endscript
}

My /root/.my.cnf have

-rw------- 1 root root 43 Mar 21 20:51 .my.cnf

[mysqladmin]
password= 111
user= root

score 0 · Answer 4 · answered Jan 09 '17 at 08:33

In my case, the block in /etc/logrotate.d/mysql looked a bit different:

postrotate
        test -x /usr/bin/mysqladmin || exit 0

        if [ -f `my_print_defaults --mysqld | grep -oP "pid-file=\K[^$]+"` ]; then
            # If this fails, check debian.conf!
            mysqladmin --defaults-file=/etc/mysql/debian.cnf flush-logs
        fi
endscript

Note the comment: "If this fails, check debian.conf!" and the command having the parameter --defaults-file=/etc/mysql/debian.cnf. This file had the very same [client] section, defining user root with an empty password. So obviously, the very same password used in /root/.my.cnf had to be placed in that file as well. Security-wise, /etc/mysql/debian.cnf is similar to /root/.my.cnf: owned by root:root, and chmodded to 0600.

score 0 · Answer 5 · answered Jan 26 '19 at 11:00

So, In my case, There's a permission problem to debian-sys-maint user, because of, the galera-cluster has the same integrity on each node, although, each node install individually, by the debian user for each one, which, the config file is /etc/mysql/debian.cnf

So in the logrotate file is:

postrotate
    test -x /usr/bin/mysqladmin || exit 0
    if [ -f `my_print_defaults --mysqld | grep -oP "pid-file=\K[^$]+"` ]; then
        # If this fails, check debian.conf!
        mysqladmin --defaults-file=/etc/mysql/debian.cnf --local flush-error-log \
          flush-engine-log flush-general-log flush-slow-log
    fi
endscript

The solution so simple, just, change the password of debian-sys-maint user on one node, and set the password on the '/etc/mysql/debian.cnf' file on every nodes

SET PASSWORD FOR 'debian-sys-maint'@'localhost' = password('YOUR PASSWORD');

I hope, it be useful, as mine.

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

5 Answers5

Linked