How to purge the old logs in mysql? Rotate the logs in MySQL.
Logrotate: /etc/logrotate.conf
– Linux utility
– Rotate the logs based on the rules
– Used to maintain the logs
* /etc/logrotate.d/ — File to input the parameters
* /var/lib/logrotate/status — check status of log rotate
* /etc/logrotate.conf
1. Login into MySQL to check log expire time. We can change this time accordingly.
mysql> show global variables like 'binlog_expire_logs_seconds'; +----------------------------+---------+ | Variable_name | Value | +----------------------------+---------+ | binlog_expire_logs_seconds | 2592000 | +----------------------------+---------+ 1 row in set (0.00 sec) mysql>
2. Manually purge the bin logs by the
mysql> purge binary logs to 'binlog.000006'; Query OK, 0 rows affected (0.02 sec) mysql> show binary logs; +---------------+-----------+-----------+ | Log_name | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000006 | 180 | No | | binlog.000007 | 180 | No | | binlog.000008 | 180 | No | | binlog.000009 | 180 | No | | binlog.000010 | 201 | No | | binlog.000011 | 201 | No | | binlog.000012 | 1114 | No | +---------------+-----------+-----------+ 7 rows in set (0.00 sec)
3. Schedule the cron job to purge the old logs.
cd /u01/mysql/logs
mkdir archive_error_log
root@Server:/etc/logrotate.d# vi mysql-rotate-logs
root@Server:/etc/logrotate.d# cat mysql-rotate-logs
/u01/mysql/logs/error.log {
create 600 mysql mysql
notifempty
daily
rotate 365
dateext
missingok
compress
copytruncate
olddir /u01/mysql/logs/archive_error_log
}
root@Server:/etc/logrotate.d# logrotate -f mysql-rotate-logs
root@Server:/etc/logrotate.d# cd /u01/mysql/logs/archive_error_log
root@Server:/u01/mysql/logs/archive_error_log# ls -lrth
total 4.0K
-rw-r----- 1 mysql mysql 1.4K Mar 8 22:12 error.log-20240308.gz
root@Server:/u01/mysql/logs/archive_error_log#
**Schedule this command “logrotate -f mysql-rotate-logs” in crontab as per requirement.