How do I increase the binary log 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 binary log retention to increase the performance of binary log extraction.

Resolution

Note: Aurora MySQL-Compatible doesn't use binary logs for intra-cluster replication. Also, Aurora MySQL-Compatible global databases don't use binary logs.

Turn on binary logging

Complete the following steps:

  1. Open the Amazon Relational Database Service (Amazon RDS) console.
  2. In the navigation pane, choose Parameter groups.
    Note: If you use the default Aurora DB cluster parameter group, then also create a new DB cluster parameter group. Then, for Type, choose DB cluster parameter group.
  3. Select the DB custom cluster parameter group, and then choose Edit.
  4. Under Modifiable parameters, select binlog_format and change the format. For example, choose ROW, STATEMENT, or MIXED. For more information, see Accessing MySQL binary logs.
  5. Choose Save changes.

When you turn on the binary logging format, it affects your Aurora MySQL-Compatible DB cluster performance in the following ways:

  • Binary logs cause additional write overhead. It's a best practice to turn on binary logging only when necessary.
  • Because of the binary log recover process, binary logs increase the engine start-up time at reboot.

As a best practice, turn on binary logging in your Aurora MySQL-Compatible DB cluster in the following situations:

  • For an Aurora read replica across AWS Regions
  • For Aurora manual replication to an external MySQL-compatible database

For more information, see How do I turn on binary logging for my Aurora MySQL-Compatible cluster?

Increase the binary log retention

Before you delete the binary logs, you must choose a time frame to retain the binary log for. The retention time frame must be long enough for you to verify that Aurora applied the changes to your replica before the deletion.

To increase the binary log retention time frame of the DB cluster, use the mysql_rds_set_configuration procedure. For example, to retain the binary logs for 7 days, run the following command on the write instance:

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

For Aurora MySQL-Compatible versions that are earlier than 2.11 (Aurora with MySQL 5.7 compatibility), the maximum value for binary log retention hours is 168 (7 days). So, you must enter the value as a number that's between 1 and 168, or enter NULL.

For versions 2.11.0 and later, the maximum binary log retention hours value is 2160 (90 days). If the value that you enter for binary log retention hours is greater than 2160, then the value defaults to 2160.

Related information

Replication between Aurora and MySQL or between Aurora and another Aurora DB cluster (binary log replication)

AWS OFFICIALUpdated a month 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
SUPPORT ENGINEER
replied 2 years ago

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

AWS
MODERATOR
replied 2 years ago