How to troubleshoot binary logging errors that I received when using AWS DMS with Aurora MySQL as the source?
Last updated: 2019-10-01
I have an Amazon Aurora DB instance that is running MySQL and binary logging is enabled. I'm using the Aurora DB instance as the source for an AWS Database Migration Service (AWS DMS) task, and I received an error. How do I troubleshoot and resolve this error?
You must enable binary logging on the source Aurora MySQL DB writer instance to use change data capture (CDC) with an AWS DMS task that is either FULL LOAD AND CDC or CDC only. You must use the writer instance because read replicas aren't supported as a source for CDC operations. For more information, see the Limitations on Using a MySQL Database as a Source for AWS DMS.
If binary logging isn't enabled or you're connected to the reader instance, then you see a log entry similar to the following:
|[SOURCE_CAPTURE ]I: System var 'log_bin' = 'OFF'|
|[SOURCE_CAPTURE ]E: Error Code  : Binary Logging must be enabled for MySQL server  (mysql_endpoint_capture.c:366)|
If you're connected to the reader instance, first 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 points to the current writer of the cluster at all times.
Then, connect to the source Aurora cluster writer node by using the cluster endpoint to confirm that binary logging is enabled:
mysql> show global variables like "log_bin"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | OFF | +---------------+-------+
If the log_bin parameter is set to OFF, then check the Aurora cluster's cluster parameter group to confirm that the binlog_format parameter is set to ROW. If binlog_format isn't set to ROW, modify the parameter to enable binary logging for Aurora for MySQL.
Note: This is a static parameter, so you must reboot your Aurora instance for this change to take effect.
After you set the binlog_format parameter to ROW, confirm that binary logging is enabled by connecting to your Aurora instance:
mysql> show global variables like "log_bin"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+
After you enable binary logging and confirm that you're using the cluster writer endpoint with AWS DMS, restart your task.