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

Improve this question

3 Answers 3

Reset to default
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 }
Improve this answer
1
  • Thanks for this! How would I need to modify this to also rotate a separate slow queries log file? Commented 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.

Improve this answer
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.

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

You must log in to answer this question.