0

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.

1

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

0

Only a days option exists.

binlog_row_image=minimal might help save some space.

Getting more storage is a better long term solution.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.