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_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
USER and Privileges:
Secure user credentials using mysql_config_editor:
NOTE: It will store user credentials info into .mylogin.cnf (This conf file will be get created under current OS user home directory)
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.
Just add this script into crontab entry:
- You can set rotation on the basis of SIZE, TIME or both. Explore logrotate option for more options.
-s /PATH/log/logrotate.statusfile 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.
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.