1

I work with CentOS5, and I have the following version of mysql :

mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (i686) using readline 5.1

I want to rotate the logs of mysql using logrotate.

Let's consider I only want to rotate the file /var/lib/mysql/mysql-log.log for the example. I describe below the procedure I have followed, the logrotate behaviour I have noted, and then I give the content of my config files.

I followed these steps :

  1. I added a file called mysql (see the content below) in /etc/logrotate.d, with 660 rights (the file belongs to root)
  2. I tried to force rotation with the command "logrotate -dv --force /etc/logrotate.conf"

The behaviour I noted :

  • The messages produced by the command logrotate seemed to show me that /var/lib/mysql/mysql-log.log had been rotated
  • Actually, there was no "mysql-log.*" in /var/lib/mysql/, only the original file /var/lib/mysql/mysql-log.log, as big as before executing the command ...

I give you now my onfig files, and the extract of the logs produced my the command logrotate that concerns mysql.

/etc/logrotate.conf

# see "man logrotate" for details

# rotate log files weekly

weekly

# keep 4 weeks worth of backlogs

rotate 4

# create new (empty) log files after rotating old ones

create

# uncomment this if you want your log files compressed

#compress

# RPM packages drop log rotation information into this directory

include /etc/logrotate.d

# no packages own wtmp -- we'll rotate them here

/var/log/wtmp {

    monthly

    minsize 1M

    create 0664 root utmp

    rotate 1

}

# system-specific logs may be also be configured here.

/etc/logrotate.d/mysql

/var/lib/mysql/mysql-log.log {

    rotate 4

    daily

    nocompress

    missingok

    create 660 mysql mysql

    postrotate

      if ls /proc/`cat /var/run/mysqld/mysqld.pid` > /dev/null

      then

        /usr/bin/mysqladmin -pMotDePasseSqlRoot flush-logs

      fi

    endscript

}

And here is the logrotate logs I noted concerning mysql :

reading config file mysql
reading config info for /var/lib/mysql/mysql-log.log 
...
rotating pattern: /var/lib/mysql/mysql-log.log  forced from command line (4 rotations)
empty log files are rotated, old logs are removed
considering log /var/lib/mysql/mysql-log.log
  log needs rotating
rotating log /var/lib/mysql/mysql-log.log, log->rotateCount is 4
renaming /var/lib/mysql/mysql-log.log.4 to /var/lib/mysql/mysql-log.log.5 (rotatecount 4, logstart 1, i 4), 
renaming /var/lib/mysql/mysql-log.log.3 to /var/lib/mysql/mysql-log.log.4 (rotatecount 4, logstart 1, i 3), 
renaming /var/lib/mysql/mysql-log.log.2 to /var/lib/mysql/mysql-log.log.3 (rotatecount 4, logstart 1, i 2), 
renaming /var/lib/mysql/mysql-log.log.1 to /var/lib/mysql/mysql-log.log.2 (rotatecount 4, logstart 1, i 1), 
renaming /var/lib/mysql/mysql-log.log.0 to /var/lib/mysql/mysql-log.log.1 (rotatecount 4, logstart 1, i 0), 
renaming /var/lib/mysql/mysql-log.log to /var/lib/mysql/mysql-log.log.1
creating new log mode = 0660 uid = 27 gid = 27
running postrotate script
running script with arg /var/lib/mysql/mysql-log.log : "
      if ls /proc/`cat /var/run/mysqld/mysqld.pid` > /dev/null
      then
        /usr/bin/mysqladmin -pMotDePasseSqlRoot flush-logs
      fi
"
removing old log /var/lib/mysql/mysql-log.log.5

Do you have any idea why logrotates reports about an action it doesn't actuallay do ? Did I miss a step before or after defining my file /etc/logrotated.d/mysql ?

Thanks for your help !

Sylvain

3 Answers 3

7

Threre is no rotation, because you are using -d option. From manual:

-d Turns on debug mode and implies -v. In debug mode, no changes will be made to the logs or to the logrotate state file.

I have two suggestions:

  • Upgrade OS to x86_64 if possible, because you have 2,5GB memory limit per process
  • Don't use -pPassword parameter with mysql for security reasons, it's better to use --defaults-extra-file=path_to_extra, which is readable only by root. When somebody, who has access to server execute ps -ef | grep mysql, it will see root password for database.

Here is my logrotate /etc/logrotate.d/mysql script for mysql on CentOS 5 (mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1):

# If the root user has a password you have to create a
# /.my.cnf configuration file with the following
# content:
#
# [mysqladmin]
# password = <secret>
# user = <username>
#
# where "<secret>" is the password and <username> is user.
#
# ATTENTION: This /.my.cnf should be readable ONLY
# for root !
/var/log/mysqld.log {
        create 640 mysql mysql
        notifempty
        missingok
        postrotate
                # just if mysqld is really running
                if test -x /usr/bin/mysqladmin && \
                   /usr/bin/mysqladmin --defaults-extra-file=/root/mysql/logrotate.cnf ping &>/dev/null
                then
                   /usr/bin/mysqladmin --defaults-extra-file=/root/mysql/logrotate.cnf flush-logs
                fi
                /usr/bin/chcon -u system_u -r object_r -t mysqld_log_t /var/log/mysqld.log
        endscript
}
1
  • Thanks for this! How would I need to modify this to also rotate a separate slow queries log file?
    – Luke
    Oct 7, 2012 at 8:44
0

That works fine with --defaults-extra-file=/root/mysql/logrotate.cnf option, I this in :

[client]
user     = root
password = TheRootPassword

After removing -dv option from my initial command I could test the rotation.

0

I've been unable to determine fer certain whether a -HUP to the daemon will cause it to flush and reopen its logs like it should.

Here's another reference (and my attempt to clarify, which another URL to a page suggesting a simple HUP will work): http://www.mysqlperformanceblog.com/2007/12/09/be-careful-rotating-mysql-logs/comment-page-1/#comment-1043963

YMMV since I'm not a mysql guy, but if it's correct, your logrotate could just HUP the process like it does for all the other polite apps.

2
  • You're not sure you have an answer. So you should have left this as a comment.
    – itsbruce
    Oct 24, 2012 at 22:07
  • @itsbruce - the op cannot leave comments as they don't have sufficient privileges on the site. Oct 24, 2012 at 22:52

You must log in to answer this question.