Logrotate is a very useful utility for rotating, compression, removal, and mailing of log files. Each log file may be handled daily, weekly, monthly, or when it grows too large. When logrotate will be used is specified by cron job. For specific functions of logrotate see man logrotate page.

We assumed that you have already installed mysql-server and logrotate package on system.

I prefer to put configuration files for logrotate in /etc/logrotate.d/ directory. In this case we will do this for mysql service. But to understand why I am using logrotate for specific log files we will first take a look to /etc/my.cnf file (acctually just for few lines)

general_log_file=/var/log/mysql.log
...
log-error=/var/log/mysqld.log

As we can see I am using /var/log/mysql.log and /var/log/mysqld.log for mysql logging stuff. First of all we have to create file /root/.my.cnf which had to be readable only by the root user. In this case /root/.my.cnf is with this content

[root@ mysql1 ~]# cat /root/.my.cnf
[mysqladmin]
user=root
password=pillow Password for root MySQL user

Now, take a look to /etc/logrotate.d/mysqld file

[root@mysql1 ~]# cat /etc/logrotate.d/mysqld
/var/log/mysqld.log {
create 640 mysql mysql After rotation create file with 640 permissions and owned by mysql user
notifempty Do not rotate the log if it is empty
weekly Log files are rotated if the current weekday
rotate 4 Log files are rotated count 4 times before being removed
dateext Archive old versions of log files adding a daily extension like YYYYMMDD
missingok If the log file is missing, go on to the next one without issuing an error message
compress Old versions of log files are compressed with gzip
postrotate
if test -x /usr/bin/mysqladmin && \
/usr/bin/mysqladmin ping &>/dev/null
then
/usr/bin/mysqladmin flush-logs Flush all MySQL logs
fi
endscript
}

/var/log/mysql.log {
create 644 mysql mysql
notifempty
daily
rotate 4
dateext
missingok
compress
}

If you want to run this logrotate immediately you should run

[root@mysql1 ~]# logrotate -f /etc/logrotate.d/mysqld