Amazon web services – understanding DMS cloudwatch logs

Step 1: Understand the CloudWatch Logs

From the CloudWatch logs, we can see several entries related to different stages of the replication task. One critical entry is:

2024-05-24T07:42:20.000Z [TASK_MANAGER ]E: Task '222222222222222EEEEE' encountered a fatal error (repository.c:5580)

This indicates a fatal error in the replication task, but it doesn’t provide much detail. To diagnose further, you should look for entries with [E: which denote errors.

Step 2: Check MySQL Binary Logs

One common issue with DMS replication tasks is the absence of binary logs on the MySQL source database. Binary logs are necessary for change data capture (CDC) in DMS.

Enabling Binary Logs on MySQL

To enable binary logs on your MySQL database, you need to edit the MySQL configuration file (my.cnf or my.ini depending on your OS) and add the following lines:

[mysqld]
log-bin=mysql-bin
server-id=1
binlog_format=ROW

• log-bin=mysql-bin: Enables binary logging.
• server-id=1: Sets a unique server ID.
• binlog_format=ROW: Sets the binary log format to row-based logging, which is necessary for DMS.

After making these changes, restart your MySQL server.

Step 3: Verify the Configuration

To verify that binary logging is enabled, run the following SQL command in your MySQL client:

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'server_id';

This should return:

• log_bin: ON
• binlog_format: ROW
• server_id: 1 (or any unique number)

Step 4: Create the DMS Replication Task with Terraform

Below is an example of how you might set up a DMS replication task using Terraform. Ensure that the MySQL endpoint is correctly configured with the necessary permissions.

Example Terraform Configuration

provider "aws" {
  region = "us-west-2"
}

resource "aws_dms_endpoint" "source" {
  endpoint_id          = "source-endpoint"
  endpoint_type        = "source"
  engine_name          = "mysql"
  username             = "your-username"
  password             = "your-password"
  server_name          = "your-mysql-server"
  port                 = 3306
  database_name        = "your-database"

  extra_connection_attributes = "initstmt=SET SESSION sql_mode='ANSI_QUOTES'"
}

resource "aws_dms_endpoint" "target" {
  endpoint_id          = "target-endpoint"
  endpoint_type        = "target"
  engine_name          = "s3"
  service_access_role  = "arn:aws:iam::123456789012:role/DMSAccessForEndpoint"
  bucket_name          = "your-bucket"
  bucket_folder        = "your-folder"
}

resource "aws_dms_replication_task" "task" {
  replication_task_id          = "task-id"
  migration_type               = "full-load-and-cdc"
  table_mappings               = file("table-mappings.json")
  replication_task_settings    = file("task-settings.json")
  source_endpoint_arn          = aws_dms_endpoint.source.endpoint_arn
  target_endpoint_arn          = aws_dms_endpoint.target.endpoint_arn
  replication_instance_arn     = aws_dms_replication_instance.instance.arn
}

Example table-mappings.json

{
    "rules": [
        {
            "rule-type": "selection",
            "rule-id": "1",
            "rule-name": "1",
            "object-locator": {
                "schema-name": "ti-lv",
                "table-name": "application"
            },
            "rule-action": "include"
        }
    ]
}

Example task-settings.json

{
  "TargetMetadata": {
    "TargetSchema": "",
    "SupportLobs": true,
    "FullLobMode": false,
    "LobChunkSize": 0,
    "LimitedSizeLobMode": true,
    "LobMaxSize": 512,
    "InlineLobMaxSize": 0,
    "LoadMaxFileSize": 0,
    "ParallelLoadThreads": 0,
    "ParallelLoadBufferSize": 0,
    "BatchApplyEnabled": false,
    "TaskRecoveryTableEnabled": false,
    "ParallelLoadQueuesPerThread": 0,
    "ParallelApplyThreads": 0,
    "ParallelApplyBufferSize": 0,
    "ParallelApplyQueuesPerThread": 0
  },
  "FullLoadSettings": {
    "TargetTablePrepMode": "DROP_AND_CREATE",
    "CreatePkAfterFullLoad": false,
    "StopTaskCachedChangesApplied": false,
    "StopTaskCachedChangesNotApplied": false,
    "MaxFullLoadSubTasks": 2,
    "TransactionConsistencyTimeout": 1200,
    "CommitRate": 20000
  },
  "Logging": {
    "EnableLogging": true,
    "EnableLogContext": false,
    "LogComponents": [
      {
        "Id": "TRANSFORMATION",
        "Severity": "LOGGER_SEVERITY_DEFAULT"
      },
      {
        "Id": "SOURCE_UNLOAD",
        "Severity": "LOGGER_SEVERITY_DEBUG"
      },
      {
        "Id": "IO",
        "Severity": "LOGGER_SEVERITY_DEFAULT"
      },
      {
        "Id": "TARGET_LOAD",
        "Severity": "LOGGER_SEVERITY_DEFAULT"
      },
      {
        "Id": "PERFORMANCE",
        "Severity": "LOGGER_SEVERITY_DEFAULT"
      },
      {
        "Id": "SOURCE_CAPTURE",
        "Severity": "LOGGER_SEVERITY_DEBUG"
      },
      {
        "Id": "SORTER",
        "Severity": "LOGGER_SEVERITY_DEFAULT"
      },
      {
        "Id": "REST_SERVER",
        "Severity": "LOGGER_SEVERITY_DEFAULT"
      },
      {
        "Id": "VALIDATOR_EXT",
        "Severity": "LOGGER_SEVERITY_DEFAULT"
      },
      {
        "Id": "TARGET_APPLY",
        "Severity": "LOGGER_SEVERITY_DEBUG"
      },
      {
        "Id": "TASK_MANAGER",
        "Severity": "LOGGER_SEVERITY_DEFAULT"
      },
      {
        "Id": "TABLES_MANAGER",
        "Severity": "LOGGER_SEVERITY_DEFAULT"
      },
      {
        "Id": "METADATA_MANAGER",
        "Severity": "LOGGER_SEVERITY_DEFAULT"
      },
      {
        "Id": "FILE_FACTORY",
        "Severity": "LOGGER_SEVERITY_DEFAULT"
      },
      {
        "Id": "COMMON",
        "Severity": "LOGGER_SEVERITY_DEFAULT"
      },
      {
        "Id": "ADDONS",
        "Severity": "LOGGER_SEVERITY_DEFAULT"
      },
      {
        "Id": "DATA_STRUCTURE",
        "Severity": "LOGGER_SEVERITY_DEFAULT"
      },
      {
        "Id": "COMMUNICATION",
        "Severity": "LOGGER_SEVERITY_DEFAULT"
      },
      {
        "Id": "FILE_TRANSFER",
        "Severity": "LOGGER_SEVERITY_DEFAULT"
      }
    ]
  },
  "ControlTablesSettings": {
    "FullLoadExceptionTableEnabled": false,
    "ControlSchema": "dms",
    "HistoryTimeslotInMinutes": 5,
    "HistoryTableEnabled": true,
    "SuspendedTablesTableEnabled": true,
    "StatusTableEnabled": true
  },
  "StreamBufferSettings": {
    "StreamBufferCount": 3,
    "StreamBufferSizeInMB": 8,
    "CtrlStreamBufferSizeInMB": 5
  },
  "ChangeProcessingDdlHandlingPolicy": {
    "HandleSourceTableDropped": true,
    "HandleSourceTableTruncated": true,
    "HandleSourceTableAltered": true
  },
  "ErrorBehavior": {
    "EventErrorPolicy": "IGNORE",
    "DataErrorPolicy": "LOG_ERROR",
    "DataTruncationErrorPolicy": "LOG_ERROR",
    "DataErrorEscalationPolicy": "SUSPEND_TABLE",
    "DataErrorEscalationCount": 0,
    "TableErrorPolicy": "SUSPEND_TABLE",
    "TableErrorEscalationPolicy": "STOP_TASK",
    "TableErrorEscalationCount": 0,
    "RecoverableErrorCount": -1,
    "RecoverableErrorInterval": 5,
    "RecoverableErrorThrottling": true,
    "RecoverableErrorThrottlingMax": 1800,
    "RecoverableErrorStopRetryAfterThrottlingMax": true,
    "ApplyErrorDeletePolicy": "IGNORE_RECORD",
    "ApplyErrorInsertPolicy": "LOG_ERROR",
    "ApplyErrorUpdatePolicy": "LOG_ERROR",
    "ApplyErrorEscalationPolicy": "LOG_ERROR",
    "ApplyErrorEscalationCount": 0,
    "ApplyErrorFailOnTruncationDdl": false,
    "FullLoadIgnoreConflicts": true,
    "FailOnTransactionConsistencyBreached": false,
    "FailOnNoTablesCaptured": false
  },
  "FailTaskWhenCleanTaskResourceFailed": false,
  "TTSettings":

 {
    "EnableTT": false,
    "TTRecordSettings": null,
    "TTS3Settings": null
  },
  "ChangeProcessingTuning": {
    "BatchApplyPreserveTransaction": true,
    "BatchApplyTimeoutMin": 1,
    "BatchApplyTimeoutMax": 30,
    "BatchApplyMemoryLimit": 500,
    "BatchSplitSize": 0,
    "MinTransactionSize": 1000,
    "CommitTimeout": 1,
    "MemoryLimitTotal": 1024,
    "MemoryKeepTime": 60,
    "StatementCacheSize": 50
  },
  "PostProcessingRules": null,
  "CharacterSetSettings": null,
  "LoopbackPreventionSettings": null,
  "BeforeImageSettings": null
}

Step 5: Monitor CloudWatch Logs

After ensuring that the MySQL binary logs are enabled and the DMS replication task is correctly configured, monitor the CloudWatch logs for any errors. Look specifically for entries with [E: to diagnose issues.

Step 6: Additional Diagnostics

If issues persist, you can enable more detailed logging in the DMS task settings. Set the logging level to DEBUG for more components if needed. This will provide more granular details in CloudWatch logs.

Conclusion

By following these steps, you should be able to diagnose and resolve issues with your DMS replication task. Ensure that the MySQL binary logs are enabled, and carefully monitor the CloudWatch logs for detailed error messages. If you need further assistance, consider posting detailed log entries on forums or contacting AWS support.