How can I increase the binlog retention in my Aurora MySQL-Compatible DB cluster?

3 minute read
0

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

  1. Open the Amazon Relational Database Service (Amazon RDS) console.
  2. 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.
  3. Choose the DB custom cluster parameter group. Then, choose Parameter group actions.
  4. Choose Edit.
  5. Change the value for the binlog_format parameter. For example: ROW, Statement, or MIXED.
  6. 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

AWS OFFICIAL
AWS OFFICIALUpdated 8 months ago
2 Comments

For Aurora MySQL 5.7-compatible, the maximum value for binlog retention hours is 168 (7 days). If you enter a higher value, then 168 is used by default. -> This has been fixed in the versions greater than 2.11 in case of Aurora MySQL 5.7-compatible editions. Is that correct?

AWS
replied 10 months ago

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
MODERATOR
replied 10 months ago