MySQL LogRotate script

Did you ever try to use log rotate facility in Linux with MySQL? There is no need to script one for this purpose, it is already installed. From MySQL spec file, it looks for logrotate.d folder:

    # Ensure that needed directories exists 
    install -d $RBR%{_sysconfdir}/{logrotate.d,init.d}

As well as there is dedicated mysql-log-rotate.sh script for installing logrotate script. The script path is: /mysql-5.6.24/support-files/mysql-log-rotate.sh Again from spec file:

    # Install logrotate and autostart 
    install -m 644 $MBD/release/support-files/mysql-log-rotate $RBR%{_sysconfdir}/logrotate.d/mysql

After installing there will be mysql script in /etc/logrottate.d/.

   # The log file name and location can be set in
   # /etc/my.cnf by setting the "log-error" option
   # in either [mysqld] or [mysqld_safe] section as
   # follows:
   #
   # [mysqld]
   # log-error=/var/lib/mysql/mysqld.log
   #
   # In case the root user has a password, then you
   # have to create a /root/.my.cnf configuration file
   # with the following content:
   #
   # [mysqladmin]
   # password = <secret> 
   # user= root
   #
   # where "<secret>" is the password. 
   #
   # ATTENTION: The /root/.my.cnf file should be readable
   # _ONLY_ by root !

   /var/lib/mysql/mysqld.log {
         # create 600 mysql mysql
         notifempty
         daily
         rotate 5
          missingok
         compress
    postrotate
         # just if mysqld is really running
         if test -x /usr/bin/mysqladmin && 
          /usr/bin/mysqladmin ping &>/dev/null
       then
          /usr/bin/mysqladmin flush-logs
       fi
   endscript
  }

There is 2 simple and critical problem with this script. First of all the name of error log file. If MySQL started from default my.cnf file, path for error log file will be: /var/log/mysqld.log

     [mysqld_safe] 
     log-error=/var/log/mysqld.log 
     pid-file=/var/run/mysqld/mysqld.pid

But in script it is defined as: /var/lib/mysql/mysqld.log Even if you comment out default settings in my.cnf file, MySQL by default will create error log file as follows: datadir + host_name.err = /var/lib/mysql/host_name.err Again there will be no such error log file defined in log rotate script. Second problem is usage of mysqladmin. Script is calling mysqladmin without any password. We assume that there is no such MySQL instance without user, at least in production environment:

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

     [root@centos7 ~]# /usr/bin/mysqladmin flush-logs /usr/bin/mysqladmin: connect to server at  'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' 

From now we know that, after installing MySQL there will be non-working log rotate script, which will fail due to explanations above. Related report is: #73949

Author: Shahriyar Rzayev

Azerbaijan MySQL User Group leader.

1 thought on “MySQL LogRotate script”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s