How do I troubleshoot binary logging errors that I receive when I use AWS DMS with Aurora MySQL-Compatible as the source?

Short description

To use change data capture (CDC) with a full-load and CDC or CDC-only AWS DMS task, turn on binary logging on the source DB writer instance. Use the writer instance because read replicas don't support CDC operations. For more information, see Limitations on using a MySQL database as a source for AWS DMS.

If you haven't turned on binary logging or you connect to the reader instance, then you see a log entry that looks similar to the following:

Messages

[SOURCE_CAPTURE ]I: System var 'log_bin' = 'OFF'

[SOURCE_CAPTURE ]E: Error Code [10001] : Binary Logging must be enabled for MySQL server [1020418] (mysql_endpoint_capture.c:366)

Resolution

If you connect to the reader instance, identify the writer instance, and then connect to the writer instance with AWS DMS. It's a best practice to connect to the cluster endpoint because the cluster endpoint directs to the cluster's current writer at all times.

To confirm whether binary logging is turned on, use the cluster endpoint to connect to the source cluster writer node:

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

If the log_bin parameter is set to OFF, then view the cluster parameter group to check whether the binlog_format parameter is set to ROW. If binlog_format isn't set to ROW, then modify the parameter to turn on binary logging for Aurora MySQL-Compatible.

Note: This is a static parameter, so you must reboot your Aurora MySQL-Compatible instance for this change to take effect.

After you set the binlog_format parameter to ROW, confirm that you turned on binary logging. To do so, connect to your Aurora MySQL-Compatible instance:

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

After you turn on binary logging and confirm that you use the cluster writer endpoint with AWS DMS, restart your task.

Related information

Using a MySQL-compatible database as a source for AWS DMS