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

Last updated: 2020-12-21

I have an Amazon Aurora MySQL DB cluster. I would like to increase the binlog retention to increase the performance of binlog extraction. How can I do this?

Short description

You can increase the availability of your Amazon Aurora MySQL DB cluster's binlogs by increasing the binlog retention period of the DB cluster. This also stops binlog files on your master DB cluster being consumed by the cross-Region replica cluster or binlog replication to external mysql-compatible databases.

Note: Enabling binlog on your Aurora MySQL DB cluster has the following performance effects:

  • Causes additional write overhead, so enable only when necessary
  • Increases engine start up time at reboot due to binlog recovery process

Enable binary logging in your Aurora MySQL DB cluster in the following circumstances:

  • For Aurora cross-Region read replica
  • For Aurora manual replication to an external mysql-compatible database

Note: Aurora MySQL does not use binlogs for intra-cluster replication. Aurora MySQL global databases do not use binlogs.

Resolution

Enable binary logging in Aurora MySQL DB cluster

First, enable binary logging on your Aurora MySQL DB cluster:

  1. Open the Amazon Relational Database Service (Amazon RDS) console.

  2. In the navigation pane, choose Parameter groups.

    Note: If you are using the default Aurora DB cluster parameter group, then create a new DB cluster parameter group. For Type, choose DB Cluster Parameter Group.

  3. Choose the DB custom cluster parameter group, choose Parameter group actions, and then choose Edit.
  4. Change the value for the binlog_format parameter, for example to ROW, Statement, or MIXED.
  5. Choose Save changes.

For more information, see How do I enable binary logging for my Amazon Aurora MySQL cluster?

Increase the binlog retention in Aurora MySQL DB cluster

Increase the binlog retention in your DB cluster to make sure that the binlog files on your replication source are retained until the changes have been applied to the replica. You don't have access to the binlog files for an Aurora MySQL DB cluster. Choose a time frame to retain the binlog files before they are deleted. This must be long enough to make sure that changes are applied to your replica before they are deleted.

Use the mysql_rds_set_configuration procedure to increase the binlog retention of the DB cluster. Run the following command and sample parameters on the writer instance to retain the binlog files for 7 days:

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

Note: The maximum value for binlog retention hours is 2160 (90 days). If you enter a higher value, then 2160 will be used.