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?
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.
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.
Retain binary logs on the replication source until they are no longer needed