MySQL to Amazon Redshift: How to Move Your Data
Move MySQL into Amazon Redshift with Airbyte. Why RDS binlog retention breaks CDC, mandatory S3 staging, and choosing between current state and full history.
Moving MySQL into Amazon Redshift takes an application database that is poor at analytical queries and puts its contents somewhere built for them. Running a reporting query against the MySQL instance serving your product is how you make the product slow, and it is why this pipeline exists.
This guide covers the managed path with Airbyte. Two things carry the work: a MySQL binlog retention setting that on managed services defaults to a value that breaks change data capture, and the fact that Redshift cannot be loaded without a staging bucket.
MySQL to Amazon Redshift at a glance:
Why move data from MySQL to Amazon Redshift?
Two situations account for most of these pipelines.
The first is protecting the application. Analytical queries against a transactional MySQL instance compete with the requests your users are making, and the usual workaround, a read replica, still ties your reporting to a row-oriented engine that was never built for aggregation.
The second is joining. Your application database holds one part of the picture, and marketing, support and finance data lives elsewhere. A warehouse is where those meet, and if your analytical estate is already on AWS then Redshift is the path of least resistance.
What do you need before you start?
One of these is a setting most people never think about, and it decides whether CDC works at all:
Binary logging enabled, with a real retention period. On Amazon RDS the binlog retention hours parameter defaults to zero, meaning logs are purged as soon as MySQL no longer needs them. Airbyte recommends setting it to 168 hours, which is seven days, so the connector has room to resume after an outage.
A replication user with the right grants. Select, plus replication client and replication slave for change data capture. Create it specifically for this pipeline rather than reusing an application account.
An S3 staging bucket and IAM permissions. The Redshift destination writes data to S3 as files with a manifest, then issues a COPY command, which is Redshift's own recommended approach. You need put, get, delete and list on the bucket, in the same region as the cluster.
Redshift entities created in advance. A database, a schema and a user able to write data and manage staging operations. Airbyte writes into two schemas, the one you nominate and a raw schema it manages, so tell whoever reviews your Redshift objects that the second one is expected.
Note also that an SSH tunnel covers the SQL connection only. Staging traffic reaches S3 over HTTPS regardless, so a network design assuming everything passes through a bastion is wrong.
How do you build a MySQL to Redshift pipeline in Airbyte?
Step 1: Fix binlog retention before anything else
Check the retention setting on your MySQL instance and raise it if it is at the default. This is the single most common reason a MySQL CDC pipeline works for a week and then fails, because the logs the connector needs to resume from have already been purged. Doing it now costs five minutes.
Step 2: Configure the MySQL source
Click Sources in the left navigation, then New Source, and select MySQL, following adding a source. Supply the host, port, database and the replication user's credentials, and choose change data capture as the replication method. Note that the initial snapshot can lock tables on MyISAM, so schedule the first sync when that will not hurt.
Step 3: Configure the Redshift destination
Click Destinations, then New Destination, and select Redshift, following adding a destination. Supply the cluster host, database, schema and credentials, along with your S3 bucket and IAM key. Staging files are removed after a successful COPY by default, and can be retained if you need them for debugging.
Step 4: Create the connection and choose sync modes
Click Connections, then New connection, select your tables and a sync mode per stream. With CDC, deleted rows arrive flagged rather than vanishing, so append and deduped mirrors current MySQL state while plain append preserves every version for anyone who wants history.
Keep the schema stable during the initial snapshot, since a change to a table mid-snapshot can produce a failure that is easier to avoid than to diagnose.
Why does CDC break after a week?
Because the binary log the connector reads from has been purged. MySQL keeps binlogs for its own replication purposes and discards them once they are no longer needed, and on Amazon RDS the retention parameter defaults to zero, which means exactly that.
A working pipeline hides this, because the connector keeps up and never needs old logs. Then something pauses it, a failure over a weekend or a maintenance window, and when it resumes the position it wants no longer exists. At that point the only remedy is a fresh snapshot, which on a large table is not trivial.
Seven days of retention gives the pipeline room to survive a weekend outage and gives you room to notice. It is also worth alerting on sync failures rather than relying on somebody checking, because the cost of a missed failure here is a full re-snapshot rather than a late table.
Why is S3 staging not optional?
Because it is how Redshift is designed to be loaded. The destination writes data to S3 as files with a manifest, then issues a COPY command pointing at them, which is Redshift's own recommended path for bulk loading rather than something Airbyte invented. There is no direct insert alternative.
That has practical consequences worth planning for. You need a bucket in the same region as your cluster to avoid cross-region transfer, IAM credentials with the four permissions on it, and a decision about whether staging files are purged after each successful load. Purging is the default and retaining them is occasionally useful when debugging.
It also affects your network design. An SSH tunnel covers the SQL connection to the cluster and not the staging traffic, which reaches S3 over HTTPS regardless. If your security model assumes all traffic passes through a bastion, that assumption needs revisiting before somebody discovers it in a review.
Frequently asked questions
Why did my MySQL CDC pipeline stop working?
Most likely the binary logs were purged before the connector could resume. On RDS the retention parameter defaults to zero, so set it to around seven days.
Do I need an S3 bucket for this?
Yes. The Redshift destination stages data in S3 and loads it with COPY, which is Redshift's recommended approach. There is no direct insert path.
Are deletions in MySQL reflected in Redshift?
Yes, with change data capture. Deleted rows arrive carrying a deletion marker rather than vanishing, so choose your sync mode according to whether you want current state or full history.
Why is there an extra schema in my Redshift cluster?
Airbyte writes to your target schema and to a raw data schema it manages to improve reliability. Both are expected.
Can I do this without writing code?
The Airbyte setup is entirely UI-driven. The MySQL side needs a parameter change and a user with the right grants, which is configuration rather than code.
Get your MySQL data into Amazon Redshift
Raise binlog retention before you build anything, because that is what makes the pipeline survivable. Then set up the staging bucket in the right region, expect the second schema, decide per table whether you want current state or history, and alert on failures rather than trusting silence.
Airbyte's connector catalog includes 600+ pre-built connectors, so application data can sit beside marketing and finance sources in one warehouse. For the same source in another warehouse, see MySQL to Snowflake, and for another source landing in Redshift, ClickHouse to Amazon Redshift.
Integrate with 700+ apps using Airbyte
Move data from 700+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.