Managing MySQL Server Logs: Rotate, Compress, Retain & Delete
Tags: mysql, sql, database, management
Published: 2019-05-13 20:14:10

---
title: Managing MySQL Server Logs: Rotate, Compress, Retain & Delete
published: true
description: How to manage MySQL server error logs, general logs and slow query logs. Automatically archive, compress, delete and retain to control disk space use: ScaleGrid Blog.
tags: MySQL, SQL, Database, Management,
canonical_url: https://scalegrid.io/blog/managing-mysql-server-logs-rotate-compress-retain-delete/
---
<link rel="canonical" href="https://scalegrid.io/blog/managing-mysql-server-logs-rotate-compress-retain-delete/" />

<a style="color: #2da964;" href="https://scalegrid.io/blog/managing-mysql-server-logs-rotate-compress-retain-delete/"><img src="https://scalegrid.io/blog/wp-content/uploads/2019/04/Managing-MySQL-Server-Logs-Rotate-Compress-Retain-Delete-ScaleGrid-Blog.jpg" width="100%" height="auto"></a>
<p style="text-align: justify;">MySQL Server generates several <a style="color: #2da964;" href="https://dev.mysql.com/doc/refman/5.7/en/server-logs.html">logs</a> that can help you monitor the activities of the server. However, once these logs are enabled, they can grow in size and start taking up too much disk space. This is why it’s important to have an automated way of archiving and preserving MySQL log files for a certain duration, as well as deleting the old ones. In this blog post, we describe some best practices for setting up and <a style="color: #2da964;" href="https://scalegrid.io/blog/managing-mysql-server-logs-rotate-compress-retain-delete/">managing MySQL error logs, general logs and slow query logs</a> for your <a style="color: #2da964;" href="https://scalegrid.io/mysql.html">MySQL deployments</a>.</p>

<h2>Setting Up MySQL Server Logging</h2>
Let’s look at how to setup the following 3 types of logs:
<h3>Error Log</h3>
<p style="text-align: justify;">Logs all the problems encountered during starting, running, or stopping mysqld. This log can be enabled by having the following option in /etc/my.cnf file:</p>

<ul>
	<li>log_error=/var/log/mysql/mysqld.log</li>
</ul>
<h3>General Query Log</h3>
<p style="text-align: justify;">Logs established client connections and statements received from clients. This log can be enabled by having the following option in /etc/my.cnf file:</p>

<ul>
	<li>general_log=ON</li>
	<li>general_log_file=/var/log/mysql/general.log</li>
</ul>
<h3>Slow Query Log</h3>
<p style="text-align: justify;">Logs queries that took more than long_query_time seconds to execute. This log can be enabled by the following option in /etc/my.cnf file:</p>

<ul>
	<li>slow_query_log=ON</li>
	<li>slow_query_log_file=/var/log/mysql/mysql-slowquery.log</li>
</ul>
<h2>Setting Up Criteria For Log Rotation</h2>
<p style="text-align: justify; padding-bottom: 15px;">As an example, let’s have some criteria for managing general MySQL query logs. We can come up with a suitable set of criteria for log management by asking the following questions:</p>
<p style="text-align: justify;"><strong>Q: What is the maximum size that the log file can grow?</strong></p>
<p style="text-align: justify; padding-bottom: 15px;">A: Let’s say it can grow up to 300 MB after which it needs to be rotated and compressed.</p>
<p style="text-align: justify;"><strong>Q: What is the frequency that you want the log file to be rotated?</strong></p>
<p style="text-align: justify; padding-bottom: 15px;">A: We can say that we want logs to be rotated on a daily basis.</p>
<p style="text-align: justify;"><strong>Q: How many old log files you want to retain?</strong></p>
<p style="text-align: justify; padding-bottom: 15px;">A: We would like to retain the last 30 log files.</p>
<p style="text-align: justify;">Based on the above criteria, the overall disk space required for general query log management is about 1.2 GB. Assuming a 90% compression ratio - we will have 30 compressed log files of size 30 MB each and a live log file of about 300 MB.</p>

<h2>Managing The Logs Using Linux logrotate Utility</h2>
<p style="text-align: justify;"><a style="color: #2da964;" href="https://linux.die.net/man/8/logrotate">logrotate</a> is a Linux utility that helps with the efficient administration of log files and provides options for automatic rotation, compression, and removal of log files. The criteria established above can be configured for logrotate utility by creating a configuration file in the /etc/logrotate.d folder.</p>
Let’s call this configuration file mysqlgeneral and the contents of the file will be:
<pre class="wp-block-code" style="color: #23282d;"><code>/var/log/mysql/general.log{
        compress
        dateext
        maxsize 300M
        copytruncate
        maxage 365
        dateformat -%Y%m%d%s
        daily
        rotate 30
        notifempty
}</code></pre>
<p style="text-align: justify;">With the above options for logrotate, the general query logs get rotated either on a daily basis or when the log file size exceeds 300 MB. The old logs are compressed and 30 such files will be preserved. Log rotation will be skipped if the log file is empty due to the setting ‘notifempty’.</p>
<p style="text-align: justify;">The ‘copytruncate’ option is to ensure that current log file is never deleted during rotation and only its contents get truncated. This is important since some applications expect that the log file is always available and it’s not possible to delete the log without stopping the application first.</p>
<p style="text-align: justify;">Now that the log rotation configuration is set for the general query log, the logrotate utility has to be run so that the above configuration is executed. This is typically done through a cron job. We can set this to be running every hour by placing the logrotate script in /etc/cron.hourly directory:</p>

<pre class="wp-block-code" style="color: #23282d;"><code>#!/bin/sh

/usr/sbin/logrotate /etc/logrotate.conf
EXITVALUE=$?
if [ $EXITVALUE != 0 ]; then
    /usr/bin/logger -t logrotate "ALERT exited abnormally with [$EXITVALUE]"
fi
exit 0
</code></pre>
<p style="text-align: justify; padding-bottom: 10px;">So, with a few simple steps, we have set up log rotation for MySQL general logs based on our criteria. The same approach can be followed for MySQL error logs and slow query logs as well. Check out these other posts to learn more about optimizing your MySQL deployments:</p>

<ul>
	<li style="padding-bottom: 10px;"><a style="color: #2da964;" href="https://scalegrid.io/blog/calculating-innodb-buffer-pool-size-for-your-mysql-server/">Calculating InnoDB Buffer Pool Size for your MySQL Server</a></li>
	<li style="padding-bottom: 10px;"><a style="color: #2da964;" href="https://scalegrid.io/blog/configuring-and-managing-ssl-on-your-mysql-server/">MySQL Tutorial – Configuring and Managing SSL On Your MySQL Server</a></li>
	<li style="padding-bottom: 20px;"><a style="color: #2da964;" href="https://scalegrid.io/blog/mysql-high-availability-framework-explained-part-1/">MySQL High Availability Framework Explained – Part I: Introduction</a></li>
</ul>