How can I increase the binlog retention in my Aurora MySQL-CompatibleDB 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. How can I do this?

Short description

You can increase the availability of your Aurora MySQL-Compatible DB cluster's binlogs by increasing the binlog retention period of the DB cluster.

Note: Turning on binlog on your Aurora MySQL-Compatible DB cluster has the following performance effects:

  • Causes additional write overhead (turn it on only when necessary)
  • Increases engine start-up time at reboot because of the binlog recovery process

As a best practice, turn on binary logging in your Aurora MySQL-Compatible 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-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, then create a new DB cluster parameter group. 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

Make sure that the binlog files on your replication source are retained until the changes are applied to the replica.

Note: Make sure that you choose a time frame to retain the binlog files before they are deleted. The retention time frame must be long enough to make sure that changes are applied to your replica before they are deleted.

To increase the binlog retention of the DB cluster, use the mysql_rds_set_configuration procedure. You can 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: 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.
For other Aurora MySQL compatible versions, the maximum value for binlog retention hours is 2160 (90 days). If you enter a higher value, then 2160 is used by default.


Related information

Retain binary logs on the replication source until they are no longer needed

AWS OFFICIAL
AWS OFFICIALUpdated a year 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 5 days ago

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

profile pictureAWS
MODERATOR
replied 4 days ago