I have an Amazon Aurora MySQL-Compatible Edition DB cluster. I want to increase the binlog retention to increase the performance of binlog extraction.
Short description
To increase the availability of your Aurora MySQL-Compatible DB cluster's binlogs, increase the binlog retention period of the DB cluster.
Note: Turning on binlog on your Aurora MySQL-Compatible DB cluster has the following performance effects:
- Because this causes additional write overhead, turn it on only when necessary.
- Because of the binlog recovery process, there is increased engine start-up time at reboot.
As a best practice, turn on binary logging in your Aurora MySQL-Compatible DB cluster in the following circumstances:
- For an Aurora read replica across AWS Regions
- For Aurora manual replication to an external MySQL-compatible database
Note: Aurora MySQL-Compatible doesn't use binlogs for intra-cluster replication. Aurora MySQL-Compatible global databases don't use binlogs.
Resolution
Turn on binary logging in Aurora MySQL-Compatible DB cluster
- Open the Amazon Relational Database Service (Amazon RDS) console.
- In the navigation pane, choose Parameter groups.
Note: If you're using the default Aurora DB cluster parameter group, also create a new DB cluster parameter group. Then, for Type, choose DB Cluster Parameter Group.
- Choose the DB custom cluster parameter group. Then, choose Parameter group actions.
- Choose Edit.
- Change the value for the binlog_format parameter. For example: ROW, Statement, or MIXED.
- Choose Save changes.
For more information, see How do I turn on binary logging for my Aurora MySQL-Compatible cluster?
Increase the binlog retention in Aurora MySQL-Compatible DB cluster
Verify that your replication source retains its binlog files until the changes apply to the replica.
Note: Make sure that you choose a time frame to retain the binlog files before they're deleted. The retention time frame must be long enough to make sure that changes apply to your replica before they're deleted.
To increase the binlog retention of the DB cluster, use the mysql_rds_set_configuration procedure. You can run the following command and example parameters on the writer instance to retain the binlog files for seven days:
CALL mysql.rds_set_configuration('binlog retention hours', 168);
For Aurora MySQL-Compatible (with MySQL 5.7 compatibility) versions that are earlier than 2.11, the maximum value for binlog retention hours is 168 (7 days). Therefore, you must enter this value as a number between 1 and 168, or enter it as NULL.
For versions 2.11.0 and later, the maximum binlog retention hours value is 2160 (90 days). If the value that you enter for binlog retention hours is greater than 2160, then the value defaults to 2160.
Related information
Retain binary logs on the replication source until no longer needed