Mysql 19-May-2017


As a part database administration, DBA has to take care of sub-components of database like server logs and has to plan for maintenance activity for these components regularly.

MySQL has various types of log i.e binary log, error log, slow query log, general log for different purposes. And after certain time these logs will grow and you will start seeing issues like low disk space, a large number of logs etc.

MySQL allows you to flush logs using flush logs command, next “How to rotate and clean up old MySQL logs? ”

Linux has a utility called “logrotate” , using logrotate we can implement log rotation for MySQL server logs.

Binary logs: This one is critical if you have replication setup, By enabling  expire_logs_days mysql variable you can manage cleanup and flush logs cmd will rotate binary log.

For General and Slow query that’s not the case, “flush log” cmd will flush the content from memory to respective log files, but it will not rotate these logs. logrotate by default configured and managed with OS root user.On a Linux (Red Hat) installation, you can use the mysql-log-rotate script for this. If you installed MySQL from an RPM distribution, this script should have been installed automatically. It kind of sample script for full implementation, let’s create a separate mysql-log-rotate script.


USER and Privileges:

CREATE USER  'logadmin'@'localhost' IDENTIFIED BY 'xyzpwd';
GRANT RELOAD ON *.* TO 'logadmin'@'localhost';

Secure user credentials using mysql_config_editor:

shell> mysql_config_editor set --login-path=logadmin_client --host=localhost --user=monitor --password                                                                                    
Enter password:<enter_mysql_logadmin_user_password>

NOTE: It will store user credentials info into .mylogin.cnf (This conf file will be get created under current OS user home directory)

mysql-log-rotate script

/PATH/log/mysqld.log /PATH/log/slow-query.log /PATH/log/general-query.log {
create 640 mysql mysql
rotate 5
minsize 1M
# just if mysqld is really running
if test -x /usr/bin/mysqladmin
/usr/bin/mysqladmin --login-path=logadmin_client ping >/dev/null
/usr/bin/mysqladmin --login-path=logadmin_client flush-logs
NOTE: Above script will flush logs 3 times since we have 3 logs in one code block.To flush log only at once you can create separate rotate code block for each log and add postrotate script only in the last rotation code block.
00 03 * * * /usr/sbin/logrotate -s /PATH/log/logrotate.status /PATH/monitor/ > /PATH/log/logrotate_cron.log 2>&1

Key points:

  • You can set rotation on the basis of SIZE, TIME or both. Explore logrotate option for more options.
  • -s /PATH/log/logrotate.status file will get create/update with log name and timestamp, Which will get use for next rotation on the basis of filename and timestamp it has.
  • -f, --force 
    Tells logrotate to force the rotation, even if it doesn’t think
    this is necessary. Sometimes this is useful after adding new
    entries to logrotate, or if old log files have been removed by
    hand, as the new files will be created, and logging will continue correctly.

All Set !!