MYSQL SERVER LOG MAINTENANCE

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.

Prerequisites:

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
daily
minsize 1M
notifempty
missingok
compress
postrotate
# just if mysqld is really running
if test -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 postrotate script only in the last rotation code block.
Automation:
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.
All Set !!