MySQL to Redshift Replication: Zero-ETL, DMS, or CDC

Artie is a fully managed real-time streaming platform that continuously replicates database changes into warehouses and lakes. We automate the entire data ingestion lifecycle - from capturing changes to merges, schema evolution, backfills, and observability - and scale to billions of change events per day.

This post is for data engineers and backend developers who run MySQL in production and want near-real-time analytics in Redshift.

TL;DR: MySQL's binary log captures every row-level change in your database. With CDC, you can stream those changes to Redshift in under a minute instead of waiting for nightly batch jobs. This post compares the four common ways to get MySQL data into Redshift (AWS zero-ETL, AWS DMS, batch export, and CDC), then walks through how binlog CDC works, how to set it up, and the Redshift-specific patterns that make it performant.

The Problem: Your Analytics Are Always a Day Behind

Say you're running a meal-kit delivery company - let's call it FreshBowl. Orders land in MySQL. Your ops team needs to see order fulfillment status in Redshift so they can catch warehouse bottlenecks before they cascade into late deliveries.

With batch ETL, here's what happens: an order comes in at 3pm, the ETL job runs at midnight, and the ops team sees it at 6am the next day. By then, the bottleneck has been backing up for 15 hours.

This is the core problem with batch pipelines. Your analytics are always looking at yesterday. Revenue dashboards show last night's close, not this afternoon's spike. Fulfillment metrics lag behind actual warehouse throughput. Your dbt models are technically correct but practically stale.

What you actually want is for FreshBowl's orders table in MySQL to show up in Redshift within a minute of a row changing. That's what change data capture (CDC) gives you.

Migrate MySQL to Redshift Once, or Replicate It Continuously?

Answer that first, because it rules out half the options. A one-time migration or a nightly refresh only needs a batch export. A table that stays in sync needs something that reads the binlog, and AWS zero-ETL, AWS DMS, and CDC tools like Artie all do. The differences are where your MySQL can run and what each one won't handle.

When zero-ETL is enough

If your database runs on RDS for MySQL 8.0 or 8.4 or Aurora MySQL 3.05.2 or later, your warehouse is Redshift Serverless or a provisioned RA3 or RG cluster in the same Region, and every table you need has a primary key, try zero-ETL first. AWS runs the pipeline, it reads the same binlog described below, and there's nothing for you to deploy. Check the version tables linked above for your Region; a few aren't covered. The target also needs case-sensitive identifiers turned on, and a provisioned cluster has to be encrypted.

Where zero-ETL stops

These come from AWS's own limitation lists for RDS and Redshift:

• RDS and Aurora sources only, same Region. MySQL on EC2, on-premises, or in another cloud can't be the source.
• No primary key, no replication. Tables without one aren't replicated to Redshift.
• Schema changes can mean a resync. An ALTER TABLE on the source can trigger a table resynchronization, and the table can't be queried until it finishes. Initial seeding and resyncs can take 20-25 minutes or more, depending on database size.
• Type gaps. BLOB and spatial columns aren't on the supported type list, and a table with an unsupported type goes out of sync. A value longer than Redshift's 65,535-byte VARCHAR puts its table in a failed state unless you set TRUNCATECOLUMNS.
• InnoDB only, no cascading foreign keys. A table with an ON DELETE or ON UPDATE rule using CASCADE, SET NULL, or SET DEFAULT goes into a failed state.
• A read-only copy. Data lands as-is in a read-only database. You can't create tables or views in it, so transformations happen elsewhere in the warehouse.

AWS DMS

DMS reaches MySQL that zero-ETL can't, including self-managed servers on-premises or on EC2, and can run a full load followed by ongoing changes. It writes changes to CSV files, uploads them to S3, and runs COPY into Redshift, so data arrives in batches. The replication instance has to be in the same account and Region as your Redshift cluster.

A few details matter for MySQL sources: DMS needs binlog_format = ROW and binlog_row_image = FULL, it doesn't use GTIDs even when your server has them, and DROP TABLE and RENAME TABLE aren't replicated. On the Redshift side, tables without primary keys get changes applied statement by statement, which AWS warns can add latency, and because Redshift doesn't enforce primary keys, a resumed task can write duplicate rows. We've written up where DMS struggles as a long-running CDC pipeline and what teams use instead.

If you only need occasional lookups on live rows, Redshift federated queries can read RDS or Aurora MySQL in place without copying anything. Part of each query runs on your MySQL instance, though, so it doesn't replace replication.

How to Export MySQL to Redshift With S3 and COPY

For a one-time migration or a nightly refresh, skip the binlog: export to S3, then load with COPY.

1. Export. On RDS for MySQL, export a snapshot to S3: RDS writes Parquet files in the background without affecting the live database's performance. On Aurora MySQL, SELECT ... INTO OUTFILE S3 writes CSV or text files straight from a query. On self-managed MySQL, write files with SELECT ... INTO OUTFILE and upload them with the AWS CLI.
2. Create the target tables in Redshift, with the sort and distribution keys covered below. Check the Parquet type mapping first: snapshot exports write MySQL DATE, TIME, and JSON values as strings.
3. Load with COPY. For a snapshot export, point COPY at the table's folder:

COPY analytics.orders
FROM 's3://freshbowl-exports/orders-2026-11-03/freshbowl/freshbowl.orders/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftCopyRole'
FORMAT AS PARQUET;

COPY loads Parquet columns in file order, so the target table needs the same columns in the same order. Each run is a full copy of the table as of the export, and COPY appends, so a nightly refresh has to clear the table first: run TRUNCATE analytics.orders before COPY (Redshift commits a TRUNCATE immediately, so queries briefly see an empty table), or COPY into a staging table and swap the names in one transaction. That's fine for reporting on yesterday, and it's the "always a day behind" problem above if you need today.

How Binlog CDC Actually Works

MySQL's binary log (binlog) is a sequential record of every data change on the server. It exists primarily for replication between MySQL instances, but it's also the foundation for CDC.

What is CDC? Change data capture is a pattern where you read a database's internal change log instead of querying tables directly. You get a stream of "row X was inserted," "row Y was updated," "row Z was deleted" - without polling the source or adding triggers to your application code. Wikipedia has a solid overview.

There are three binlog formats, and only one works for CDC:

ROW format records the actual before-and-after state of each row. If someone updates total from 42.00 to 47.50 on FreshBowl order #1234, the binlog captures both values. This is what CDC needs.

STATEMENT format logs the SQL statement itself (UPDATE orders SET total = 47.50 WHERE id = 1234). Sounds equivalent, but it breaks down with non-deterministic functions, triggers, and stored procedures. You can't reliably reconstruct row state from statements.

MIXED format switches between the two depending on the query. Too unpredictable for CDC.

So the first step is making sure your MySQL instance uses ROW format. If you're on RDS or Aurora, this means editing a parameter group - it's not a my.cnf change you can make directly.

GTIDs (Global Transaction IDs) are the other key piece. Without GTIDs, the CDC reader tracks position by binlog file name and byte offset - which breaks if MySQL rotates logs or you fail over to a replica. GTIDs give every transaction a unique identifier that survives failover. Enable them.

Here's the full architecture, from binlog to Redshift:

MySQLMySQL databaseBinary logCDC pipelineCDC readerChange streamRedshiftStaging table MERGETarget table

And here's the MySQL setup for a self-managed server's my.cnf. On RDS or Aurora, put the binlog_* and GTID settings in the parameter group instead (RDS also needs automated backups turned on for the binlog to exist):

[mysqld]
server_id                = 1
log_bin                  = mysql-bin
binlog_format            = ROW
binlog_row_image         = FULL
gtid_mode                = ON
enforce_gtid_consistency = ON

gtid_mode = ON in my.cnf suits a new server. On a live primary that has replicas, turn GTIDs on in steps instead: enforce_gtid_consistency first, then gtid_mode through OFF_PERMISSIVE and ON_PERMISSIVE to ON, as in MySQL's guide to enabling GTIDs online.

Then give the CDC reader its own user. SELECT covers the initial backfill; the two replication privileges let it read the binlog:

CREATE USER 'cdc_reader'@'%' IDENTIFIED BY '<password>';
GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'cdc_reader'@'%';

Once this is in place, a CDC reader connects as a replication client, reads the binlog stream, and forwards each change event downstream - in our case, to Redshift.

One thing that bites people early: binlog retention. MySQL doesn't keep binlogs forever. If your CDC reader goes offline for longer than the retention period, it loses its place and needs a full re-sync. On RDS for MySQL, the default retention is NULL, which means binlogs are purged as soon as possible; Aurora MySQL cleans them up lazily, usually within a day. Set it to at least 24 hours (72 is safer):

CALL mysql.rds_set_configuration('binlog retention hours', 72);

Landing Data in Redshift

Getting changes out of MySQL is half the problem. The other half is applying them to Redshift efficiently.

The staging + MERGE pattern. Redshift doesn't handle row-level upserts the way a transactional database does. The standard approach is to land a batch of changes into a temporary staging table, then apply inserts, updates, and deletes to the target table in one transaction. Redshift's MERGE takes a single action for matched rows (update or delete), so deletes run as their own statement inside the same transaction.

Here's what that looks like for FreshBowl's orders table, with a staging table that holds the latest change per order and an op column marking deletes:

BEGIN;

DELETE FROM orders
USING orders_staging s
WHERE orders.id = s.id AND s.op = 'd';

MERGE INTO orders
USING (SELECT * FROM orders_staging WHERE op <> 'd') s
ON orders.id = s.id
WHEN MATCHED THEN UPDATE SET user_id = s.user_id, status = s.status, total = s.total, metadata = s.metadata
WHEN NOT MATCHED THEN INSERT (id, user_id, status, total, metadata, created_at)
  VALUES (s.id, s.user_id, s.status, s.total, s.metadata, s.created_at);

COMMIT;

This gives you exactly-once semantics. If a batch retries, the MERGE is idempotent - no duplicate rows.

But MERGE does take a lock on the target table. If you're running analytical queries against the same table that's receiving merges, you can hit contention. Batching changes and merging on a short interval (say, every 30 seconds) keeps the lock windows small. For very high-volume tables, you'll want to think about merge frequency vs. query concurrency.

Sort keys and distribution keys matter a lot in Redshift, and they're worth getting right up front. For FreshBowl's orders table, sorting by created_at makes time-range queries fast - "show me orders from the last hour" only scans the relevant blocks instead of the whole table. Distributing by user_id means queries that filter or join on a user are local to a single node instead of shuffling data across the network.

A good rule of thumb: sort by your most common time filter, distribute by your most common join key. For small lookup tables (like a warehouses dimension with 50 rows), use ALL distribution so every node has a local copy and joins never require redistribution. Redshift's sort key docs go deeper on the trade-offs, and our guide to running Redshift at scale covers the cluster side: RA3 nodes, WLM queues, and concurrency scaling.

JSON columns in MySQL map to Redshift's SUPER type. FreshBowl stores order metadata as JSON - item counts, special instructions, delivery preferences. In Redshift, you query it with path expressions:

SELECT o.id,
       o.metadata.special_instructions,
       o.metadata.delivery_preferences.time_window
FROM orders o
WHERE o.metadata.item_count::int > 3;

One caveat: SUPER has nesting depth limits. If your JSON goes five or more levels deep, flatten it in a dbt model rather than querying the raw SUPER column directly.

Type mappings are mostly intuitive - INT maps to INTEGER, VARCHAR stays VARCHAR, TIMESTAMP stays TIMESTAMP. The non-obvious ones worth knowing:

How Artie Handles This

Everything above - reading the binlog, managing positioning with GTIDs, staging changes, running MERGE, handling type mappings - is what Artie automates end to end.

You don't operate Kafka or Debezium yourself. You don't write MERGE SQL. You don't build retry logic or monitor binlog position. Artie runs the full pipeline as a managed service: MySQL binlog reader, through Kafka, into Redshift via the staging + MERGE pattern described above.

A few things worth calling out that are genuinely hard to build yourself:

Schema evolution. When someone runs ALTER TABLE orders ADD COLUMN priority VARCHAR(20) on MySQL, Artie detects the change and adds the column to Redshift automatically. No pipeline restart, no manual DDL. Renamed or dropped columns are handled per your configuration.

Backfills. The first sync does a parallel backfill of existing data while simultaneously streaming new changes from the binlog. You don't have to choose between "catch up on history" and "start streaming now" - both happen at the same time.

Monitoring. Replication lag, throughput, and error rates are visible in the Artie dashboard. Set up alerts so you know the moment something falls behind.

MySQL that zero-ETL can't reach. Artie reads the binlog from MySQL 5.7 and later wherever it runs: self-hosted, RDS, Aurora, Azure Database for MySQL, or Cloud SQL. It can also read from a read replica that has log_replica_updates on (log_slave_updates before MySQL 8.0.26). A table without a primary key can still replicate if it has a unique index you pick as the key, and gh-ost schema migrations are handled without a pipeline restart.

Honest latency expectations. Typical end-to-end latency is under 60 seconds from MySQL commit to Redshift availability. But be realistic - very large transactions, Redshift Serverless cold starts after idle periods, or catch-up from an outage can add delay. And if your team only checks dashboards once a day, real-time CDC might be more infrastructure than you need. A batch tool syncing every hour works fine for daily reporting. Real-time matters when you're making decisions on data less than an hour old - like FreshBowl's ops team catching a warehouse bottleneck at 3pm instead of discovering it at 6am tomorrow.

Getting started takes three steps:

1. Enable row-based binlog on MySQL and create a replication user
2. Connect your MySQL source and Redshift destination in Artie
3. Select tables and deploy - Artie validates connectivity before anything starts

Start replicating MySQL to Redshift with Artie

FAQ

Further Reading

• MySQL Binary Log documentation - official reference for binlog configuration
• Zero-ETL considerations for Amazon Redshift - AWS's full list of target-side limits
• Redshift MERGE statement - syntax and best practices for upserts
• Redshift SUPER type - querying semi-structured JSON data
• Redshift sort key best practices - choosing the right sort and distribution keys
• Change data capture - the concept behind binlog CDC