Examkin

Question

Difficulty: HardDatabase Migration and Schema Conversion using DMS and SCT

A Solutions Architect is migrating a self-managed, on-premises MySQL 8.0 database to an Amazon Aurora PostgreSQL-Compatible Edition DB cluster. The migration strategy uses the AWS Schema Conversion Tool (SCT) to convert the schema and AWS Database Migration Service (DMS) for ongoing replication (Change Data Capture - CDC) to minimize downtime. After converting the schema and creating the target tables, the architect configures a DMS task with a migration type of 'Full load, ongoing replication'. The task successfully completes the full load phase, but fails shortly after starting the CDC phase. Investigation reveals that binary logging is enabled on the source MySQL server with the format set to ROW, but the replication task cannot process UPDATE operations. Which configuration action on the source MySQL database will resolve this issue?

  1. Set the parameter binlog_row_image to FULL in the MySQL configuration file and restart the MySQL service.Answer
  2. B
    Execute ALTER TABLE statements on the source database to enable supplemental logging for primary keys on all tables.
  3. C
    Set the binlog_format parameter to MIXED in the MySQL configuration file and restart the MySQL service.
  4. D
    Install the AWS DMS Binary Reader agent on the source database host and configure it to read the transaction logs directly.

Answer

Set the parameter binlog_row_image to FULL in the MySQL configuration file and restart the MySQL service.
The correct answer is to configure the source MySQL parameter binlog_row_image to FULL. In heterogeneous migrations (such as MySQL to Aurora PostgreSQL), AWS DMS requires the full database row before and after the modification to apply updates and deletes on the target. If binlog_row_image is set to MINIMAL or NOBLOB, the binlog will only record the changed columns, which causes DMS replication tasks to fail during the change data capture (CDC) phase.

Step-by-Step Solution

1
Analyze the error state of the AWS DMS CDC task.
The task succeeds in the full load phase but fails during CDC when attempting to process UPDATE statements.
The full load reads directly from the tables, whereas CDC reads from the binary logs to reconstruct database transactions.
2
Verify the binary logging configuration on the source MySQL database.
The binlog_format is set to ROW, but the database lacks the full row image configuration.
By default or due to performance tuning, MySQL might log only modified columns (MINIMAL) which is insufficient for heterogeneous target engines like PostgreSQL that need full row details to execute UPDATE commands.
3
Apply the required configuration change to the MySQL parameter file.
Configuring binlog_row_image to FULL ensures the complete row context is written to the binlog for every change.
This allows the DMS task to correctly parse and translate the source updates to target updates on Aurora PostgreSQL.

Key Concept

Configuring source database binary logging requirements (binlog_row_image) for heterogeneous AWS DMS CDC tasks.
Estimated Time:2m 30s
Rate this question

Topics

Database Migration and Schema Conversion using DMS and SCT

More questions from this topic