I am trying to change the binary log rotation from days to hours. By default there is an option only for days in the MySQL config file: expire_logs_days.
Is there any way to change the binary log rotation from days to hours or even minutes?
I am running MySQL version 5.7.23 on Ubuntu 16.04.
Only a days option exists.
binlog_row_image=minimal might help save some space.
Getting more storage is a better long term solution.
The only way to get binlog rotation in hours is to script it
Here is the code
#!/bin/bash
HRS=16
mysql -uroot -prootpass -e"PURGE BINARY LOGS BEFORE NOW() - INTERVAL ${HRS} HOUR"
Put this code in a script called /root/mybinlog_rotate.sh
Make sure you run chmod +x /root/mybinlog_rotate.sh
Add that script in the crontab to rotate every 30 min
0,30 * * * * /root/mybinlog_rotate.sh
This is a little heavy handed but it can be done
I suggested this months ago : Maximum overall size of MariaDB binary logs
If, by "rotation", you mean creating a new binlog, then decrease the value of max_binlog_size. It defaults to 1G or 100M (depending on the version). 10M would have it creating smaller new files more frequently.
If you mean purging old binlogs, then set expire_log_days (in old versions) to a small number, such as 1. Then binlogs older than 1 day will be deleted at some point.
In MySQL 8.0, it is binlog_expire_logs_seconds:
Added the binlog_expire_logs_seconds system variable, which sets an interval in seconds for purging of the binary log. The effects of this variable and expire_logs_days are cumulative, making it possible to set a period such as 1.5 days. To completely disable automatic binary log purging, set both variables equal to 0, which is the default value for both of them. (Bug #71697, Bug #18260088)
It is OK to change both the size and the expire. After changing the config file, restart the server.
