Skip to main content

MySQL to Amazon Redshift Data Synchronization | NineData

Use this workflow when you need to move MySQL data into Amazon Redshift for analytics, reporting, or dashboard workloads. NineData Data Replication helps load existing data first and then keep Redshift aligned with new MySQL changes.

This page explains the high-level workflow. For detailed prerequisites, permissions, data type mapping, and precheck items, see MySQL to Redshift.

When to use this workflow​

Use this workflow when you need to:

  • Load MySQL business data into Amazon Redshift for analytics.
  • Keep Redshift updated with incremental changes from MySQL.
  • Reduce the impact of analytical queries on the MySQL production database.
  • Validate the target data before using it for reporting or downstream processing.

Overview​

MySQL and Redshift use different storage engines and table models. Prepare the Redshift target tables before you start the task and make sure the table and column structures match the objects you plan to replicate.

For incremental replication, make sure the MySQL source has Binlog enabled and uses the required Binlog settings. Source-side DDL changes are not synchronized to Redshift in this link, so plan schema changes separately.

Before you begin​

Prepare the following items:

  • Access to the NineData console.
  • A MySQL source data source and an Amazon Redshift target data source.
  • Target tables in Redshift that match the MySQL objects to replicate.
  • A Redshift IAM role that allows NineData to load staged data into Redshift.
  • Required MySQL permissions, including Binlog-related permissions for incremental replication.
  • Network connectivity from NineData to both systems.

Step 1: Add the MySQL and Redshift data sources​

  1. In the NineData console, click Data Source Management > Data Sources, and then click Create Data Source.

    Create a MySQL data source

    Select the Redshift data source type

  2. Follow the page prompts, test the connection, and click Create Data Source.

    Complete data source configuration

  3. Repeat the process until both the MySQL source and Redshift target are available in NineData.

Step 2: Create the replication task​

  1. In the NineData console, click Data Replication > Data Replication, and then click Create Replication.

    Create a replication task

  2. Configure the MySQL source and Redshift target.

  3. Select the replication types that match your task. For a warehouse pipeline, select Full Replication to load existing data and Incremental Replication to keep Redshift updated after the full load.

    Configure MySQL to Redshift replication

  4. Configure the Redshift IAM role and target table mapping.

  5. Run the precheck. Fix any failed item, and then start the task.

  6. Monitor the task until the full load is complete and the incremental delay is within your expected range.

Step 3: Validate the target data​

After the task starts, open the task details page to review replication progress and target status.

  1. Click the replication task ID.

    Open the task details page

  2. If data comparison is enabled for your task, review the Data Comparison tab.

    Review comparison results

  3. Re-run comparison when you need to validate the latest synchronized data.

    Run comparison again

Step 4: Configure alerts​

For a long-running warehouse pipeline, configure alerts for task failure and replication delay.

  1. Open the replication task details page.

    Open the task details page

  2. Click Configure Alerts.

    Configure task alerts

  3. Enter a Policy Name, review the rules, and click Save Configuration.

    Save the alert policy

Result​

After the full load completes, NineData keeps writing incremental MySQL changes to Redshift based on the configured replication task. You can then use Redshift for reporting, analytics, and downstream processing while keeping the production MySQL database focused on transactional workloads.