How to do MySQL Server Log Maintenance

MySQL SERVER LOG MAINTENANCE

 

As a part of 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_daysmysql 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.

Prerequisites:

USER and Privileges:

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

Secure user credentials using mysql_config_editor:

1 2 3

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

/PATH/log/mysqld.log /PATH/log/slow-query.log /PATH/log/general-query.log { create 640 mysql mysql rotate 5 daily minsize 1M notifempty missingok compress postrotate # just if mysqld is really running iftest-x /usr/bin/mysqladmin /usr/bin/mysqladmin--login-path=logadmin_client ping>/dev/null then /usr/bin/mysqladmin--login-path=logadmin_client flush-logs fi endscript }

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 postrotatescript only in the last rotation code block.

Automation:

Just add this script into crontab entry:

1	00 03 * * * /usr/sbin/logrotate-s /PATH/log/logrotate.status /PATH/monitor/mysql-log-rotate.sh > /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.

Please comment for any suggestion, concerns.