3

We have a requirement that we need to keep each binlog files(like mysql-bin-changelog.025249, mysql-bin-changelog.025250, mysql-bin-changelog.025251....) of MYSQL RDS to minimum 24 hrs.

However, i see that binlog files are getting purged/deleted after 10-12 mins.

Below is the current setup in my RDS parameter group:

  1. binlog retention hours is set to 24 hrs.
  2. max_binlog_size is set to 134217728.
  3. binlog_expire_logs_seconds is set to 2592000.

Also, my MYSQL RDS database is heavily used and has 400+ tables.

Please let me know which parameter i need to change, so that each bin log should be retained for 24 hrs.

Rick James
  • 80,479
  • 5
  • 52
  • 119
Sumant Kumar
  • 83
  • 7

2 Answers2

2
binlog_expire_logs_seconds = 86400

expire_log_days = 1

It won't hurt to change them both. The latter is the "old" setting; the former (seconds) is new. It is as if they want to change from one to the other, but are not yet forcing the change.

However, note that the purging won't happen exactly at that time; it will 'guarantee' that at least 24 hours is kept.

It looks like the current setting is for 3 days.

The max_binlog_size = 128M gives a clue of when it will think about purging. If it takes a week to fill up that much, it won't purge nearly as often. You could lower it (if Amazon lets you). The size seems not to affect anything more than trivially. (The old default was 1G; I saw no substantive change when that shifted.)

Rick James
  • 80,479
  • 5
  • 52
  • 119
1

Amazon RDS does not allow you to manipulate binary logs the way you normally would on a given standalone server or a VM.

You need to run two stored procedures

To emulate setting the expire_logs_days to 7 days, call this:

mysql> CALL mysql.rds_set_configuration('binlog retention hours', 168);

In your case, call this to set it to 1 day:

mysql> CALL mysql.rds_set_configuration('binlog retention hours',24);

Before and after setting it, run this to see the set value:

mysql> CALL mysql.rds_show_configuration;

I mentioned this before (Sep 05, 2018 : enabling BINARY LOG on AWS RDS MySQL )

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536