How do I turn on binary logging for my Amazon Aurora MySQL-Compatible cluster?

Short description

By default, Aurora MySQL-Compatible has binary logging turned off. To check if binary logging is currently turned off on your DB instance, run the following command:

mysql> show variables like 'log_bin';
+----------------+------------+
| Variable_name  | Value      |
+----------------+------------+
| log_bin        | OFF        |
+----------------+------------+

To replicate to an external MySQL-compatible database, or to create a cross-Region replica, you must turn on binary logging for your DB cluster.

Note: When binary logging is turned on for Aurora, the recovery time after a crash can be longer. This is because a full binary log recovery is performed on the writer instance. The duration of crash recovery depends on the amount of data that's logged in the binary logs, based on the format of binlog_format. The duration also depends on your workload.

Resolution

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.

3.    For Type, choose DB Cluster Parameter Group.

4.    Select the DB custom cluster parameter group, choose Parameter group actions, and then choose Edit.

5.    Change the value for the binlog_format parameter, for example to ROW, Statement, or MIXED. It's a best practice to use MIXED unless you require a specific binlog format.

6.    Choose Save changes.

Note: Setting the binary logging format to ROW can result in very large binary log files. Large binary log files reduce the amount of storage available for a DB cluster. They can also increase the amount of time to perform a restore of a DB cluster.

Also, be aware that binlog_format is a static parameter. You must reboot the writer DB instance in your cluster for the change to take effect. This restart is required even if you already have a custom DB cluster parameter group.

If you created a new DB cluster parameter group in step 2, then attach the parameter group to your DB cluster:

1.    Open the Amazon RDS console.

2.    In the navigation pane, under Clusters, choose Modify.

3.    Update the DB Cluster Parameter Group to the new DB cluster parameter group, and then choose Apply immediately.

4.    Choose Continue, and then choose Modify cluster.

Note: After changing the DB cluster parameter group associated with a DB cluster, you must reboot the primary DB instance in the cluster to apply changes.

To confirm that binary logging is now turned on on your DB cluster, run the command to verify binary logging again:

mysql> show variables like 'log_bin';
+----------------+------------+
| Variable_name  | Value      |
+----------------+------------+
| log_bin        | ON         |
+----------------+------------+

mysql> show variables like 'binlog_format';
+----------------+------------+
| Variable_name  | Value      |
+----------------+------------+
| binlog_format  | STATEMENT  |
+----------------+------------+

---

Related information

Managing an Amazon Aurora DB cluster

How do I identify which Amazon RDS DB parameters are in custom parameter groups and which are in default parameter groups?