A company is migrating an on-premises self-managed MySQL 8.0 database to an Amazon Aurora … — AWS Certified Solutions Architect - Professional

A company is migrating an on-premises self-managed MySQL $8.0$ database to an Amazon Aurora PostgreSQL-Compatible Edition DB cluster. The source database contains critical application data and tables with binary files stored in `LONGBLOB` columns, with the largest record sized at $8\text{ MB}$ . A solutions architect uses the AWS Schema Conversion Tool (AWS SCT) to convert the database schema and custom functions, which are then successfully applied to the target Aurora DB cluster. The architect then configures an AWS Database Migration Service (AWS DMS) task with Full Load and Ongoing Replication (Change Data Capture) enabled to migrate the data. Shortly after the task starts, the full load completes successfully, but the task immediately fails when entering the ongoing replication phase. The task logs indicate that AWS DMS is unable to read the transaction logs from the source database. Which of the following configurations should the solutions architect apply to resolve this failure and optimize the transfer of the `LONGBLOB` data with minimal downtime?

A
Configure the source MySQL database parameters by setting `binlog_format` to `STATEMENT` and `binlog_row_image` to `MINIMAL`, and restart the MySQL service. For the AWS DMS task, set the LOB support configuration to Full LOB mode to dynamically handle the large payloads.
Configure the source MySQL database parameters by setting `binlog_format` to `ROW` and `binlog_row_image` to `FULL`, and restart the MySQL service. For the AWS DMS task, set the LOB support configuration to Limited LOB mode and specify a Max LOB size of $8192\text{ KB}$ .Answer
C
Configure the source MySQL database parameters by setting `binlog_format` to `ROW` and `binlog_row_image` to `FULL`, and restart the MySQL service. For the AWS DMS task, set the LOB support configuration to Limited LOB mode and keep the default Max LOB size of $32\text{ KB}$ .
D
Configure the source MySQL database parameters by setting `binlog_format` to `ROW` and `binlog_row_image` to `FULL`. Migrate the database using AWS Application Migration Service (MGN) to block-replicate the MySQL server to Amazon EC2, then perform an in-place conversion to Aurora PostgreSQL using AWS SCT.

Answer

Configure the source MySQL parameters to ROW format and FULL row image, restart the service, and set the AWS DMS task to Limited LOB mode with a Max LOB size of 8192 KB.

To perform ongoing replication (CDC) from a MySQL source, AWS DMS requires binary logging to be enabled with `binlog_format` set to `ROW` and `binlog_row_image` set to `FULL`. Since the source database contains LOB columns up to

8\text{ MB}

, using Limited LOB mode with a Max LOB size of

8192\text{ KB}

(

8\text{ MB}

) is the most performant choice because it allocates memory up to the specified size and prevents truncation of LOBs. Full LOB mode would incur significant performance overhead by fetching LOBs in separate queries.

Step-by-Step Solution

Configure binary logging on the source MySQL database.

Setting `binlog_format = ROW` and `binlog_row_image = FULL` in the MySQL configuration file ensures that the transaction changes are logged at the row level with all column values.

AWS DMS CDC requires row-level binary logs to capture and replicate ongoing transactions from MySQL source databases.

Restart the MySQL database service to apply the configuration changes.

The MySQL instance begins generating binary logs in the correct format.

These parameter changes are static in MySQL and require a service restart to take effect.

Configure LOB settings in the AWS DMS task.

Setting Limited LOB mode with a Max LOB size of `

8192\text{ KB}

` (

8\text{ MB}

) allows DMS to allocate adequate memory buffers for the `LONGBLOB` data without truncating the records.

Limited LOB mode is faster than Full LOB mode, but it truncates any LOB data exceeding the configured threshold. Setting the threshold to the maximum known LOB size avoids truncation while maximizing migration speed.

Key Concept

Replication prerequisites and LOB optimization settings for AWS DMS CDC tasks during database migration.

Question

Answer

Step-by-Step Solution

Key Concept

Common Mistakes

Topics

More questions from this topic