MySQL to Snowflake: How to Replicate Your Data

Replicating MySQL to Snowflake with change data capture is a well-trodden pipeline with one failure mode that accounts for most of the pain people report, and it is not a bug. It is a race between how long your initial snapshot takes and how long MySQL keeps its binary logs.

Almost everything else about this connector is well behaved. It is one of Airbyte's certified sources, it captures deletes, it scales to tables in the terabyte range, and it does not need a well-maintained updated_at column to work. But if you configure retention carelessly, none of that helps, because the connection will spend its life restarting from a full snapshot it never gets to finish. Get retention right first and the rest of the setup is ordinary.

How does Airbyte actually read MySQL?

Change data capture is the default replication method on new MySQL connectors, and it runs in two distinct phases that behave quite differently. The first is an initial consistent snapshot of the tables you selected. Airbyte reads them without acquiring table locks, so other clients keep writing normally throughout, and it checkpoints its progress by primary key as it goes. The second phase is the ongoing one: Airbyte reads MySQL's binary log from the position recorded when the snapshot began, picking up inserts, updates and deletes as they were committed.

The handover between those phases is where the design gets interesting. Checkpointing by primary key is useful only while the snapshot is running, because primary keys keep being inserted and updated afterwards. So once the snapshot completes, the connector switches to the binlog position it noted at the start. Everything that changed while the snapshot was running is then replayed from the log, which is what makes the result consistent rather than a smear of different points in time.

There is a second replication method, a cursor-based standard mode that reads a column such as updated_at. Airbyte recommends against it, and the reasons are worth knowing rather than taking on faith. A cursor cannot see deletions at all. It cannot see an update that leaves the cursor column untouched. And it depends on your application maintaining that column correctly on every write path, including the ones written by a contractor in 2019. Standard mode exists for servers that do not expose the binlog and for datasets small enough that correctness questions do not arise. If your server can do CDC, use CDC.

Why does my connection keep running full refreshes?

This is the symptom that brings most people to the troubleshooting docs. Normally the first sync is a full snapshot and every sync after it is incremental. If you are seeing repeated full refreshes instead, open the logs and look for a message saying the saved offset is no longer present on the server and that Airbyte is going to trigger a sync from scratch. That line is the whole diagnosis: MySQL purged the binlog past the point Airbyte needed to resume from, so there was nothing to resume from and it started over.

There are two distinct causes and they want different fixes. The first is a busy database generating binlog faster than your retention window covers the gap between syncs. A database under heavy write load can roll through its retained logs in hours. The fix is to sync more frequently, extend retention, or both, and syncing more frequently is usually the cheaper of the two because retained binlog costs disk.

The second cause is nastier and is specific to large databases. If the initial snapshot itself takes longer than your retention period, then by the time it finishes, the binlog position it recorded at the start has already expired. The connector cannot resume, so it starts the snapshot again, which takes just as long, and fails in exactly the same way. Nothing about that loop looks like an error at a glance, because each individual sync reports as a full refresh rather than a failure. Teams have run it for days before working out what was happening. If your first sync is measured in hours, size retention against that duration and not against your sync interval.

What binlog retention should you actually set?

On a self-managed MySQL server you enable binary logging in the server configuration file, and most cloud providers offer this as a one-click option instead. The commonly recommended retention for an Airbyte pipeline is around ten days, expressed in seconds as 864000. That figure is not arbitrary: it is chosen so that a sync failure, a paused connection or a long weekend does not consume the entire window and force a resnapshot. Pair it with reasonably frequent syncs, because retention and sync frequency solve the same problem from opposite ends.

Amazon RDS needs separate attention, because it does not use the standard MySQL expiry parameter at all. RDS has its own setting called binlog retention hours, and it defaults to zero, which means binary logs are removed immediately. A stock RDS instance therefore cannot sustain CDC in any form until you change it. The change is made through an RDS-specific stored procedure rather than a parameter group, and 24 hours is the usual floor people start from.

Whatever number you land on, budget for the disk. Retained binary logs are real storage on the source instance, and on a write-heavy database ten days of them is not a rounding error. This is the point in the project where the database owner needs to be in the room, because you are asking for a configuration change and a capacity increase on a production system, not just a read-only user.

Which permissions and encryption settings do you need?

Create a dedicated read-only MySQL user with replication permissions rather than reusing an application account. The dedicated user is not ceremony: it means you can revoke Airbyte's access without touching anything else, and it makes the source of a query obvious when someone is looking at the process list wondering what is reading their tables. If you were to run the standard cursor-based method instead, only SELECT is required, which is one of the few genuine advantages that mode has.

On encryption, the connector requires it by default and the connection fails outright if the source will not encrypt. The stricter option additionally verifies that the server presents a valid certificate, and it is the right choice wherever you control the certificate chain. On Airbyte Cloud you will also need to allow inbound traffic from Airbyte's IP ranges, which is a firewall change somebody else usually owns, so raise it early rather than discovering it during your first connection test.

What can break during the initial snapshot?

The snapshot assumes your schema is stable while it runs. If a migration lands halfway through, the behaviour is undefined and the practical outcome is a resync. On a large database that means coordinating with whoever ships migrations before you start, not after, and treating the initial sync as a scheduled window rather than something you kick off on a Friday afternoon.

The no-locking behaviour has one exception worth checking for. Tables using the MyISAM storage engine are still locked during the snapshot, while InnoDB tables are not. Most modern schemas are entirely InnoDB, but legacy databases often retain a few MyISAM tables nobody has migrated, and those are exactly the ones likely to be logging or audit tables that something writes to constantly. Find out before you start rather than during.

The same reasoning extends past the first sync. A newly created table has to be snapshotted before CDC can track it, so adding a table to an existing connection needs a schema refresh rather than just selecting it in the catalogue. For this reason it is worth setting the connection to require manual approval of schema changes rather than propagating them automatically, so a new column or table cannot slip into a running sync without the snapshot it needs.

How does the data land in Snowflake?

The destination side is the straightforward half. Supply a warehouse, a database, a schema and a role with permission to create tables in that schema. As with the source, a dedicated role is worth the two minutes it costs, because warehouse credit consumption then has an obvious owner when somebody asks what has been running.

The decision that matters here is what a deletion should look like. Deleted rows arrive carrying a deletion timestamp in a dedicated metadata column, and in append and deduped mode the final table stops showing records whose most recent entry is a delete. That is the correct default for analytics, because the table mirrors current state in MySQL and nobody has to remember to filter anything.

It is the wrong default if anyone needs to prove a row once existed. Compliance questions, financial reconciliation and incident investigations all tend to ask what the data looked like six months ago rather than what it looks like now. For those cases use plain incremental append, keep every version Airbyte ever saw, and build the current-state view downstream where it is one query rather than an irreversible property of the pipeline. Choose this at connection setup, because switching later means rebuilding the history you did not keep.

Frequently asked questions

Why does my MySQL connection keep running full refreshes?

The binlog Airbyte needed has been purged, so it cannot resume and starts over. Increase binlog retention, sync more frequently, or both. On a very large database, check whether the initial snapshot is simply taking longer than the retention window.

What binlog retention should I set on Amazon RDS?

At least 24 hours, set through the RDS-specific configuration procedure rather than a parameter group. The default is zero, which purges binary logs immediately and makes CDC impossible.

Does the initial snapshot lock my tables?

Not for InnoDB tables, where writes from other clients continue normally. Tables using the MyISAM engine are locked, so check whether your schema still contains any.

Can I run migrations while the initial sync is going?

No. The snapshot assumes the schema is stable while it runs. Coordinate with whoever deploys migrations before starting a large initial sync.

Should I ever use the standard replication method instead of CDC?

Only if your server does not expose the binlog, or the dataset is small and you just want a snapshot. Cursor-based replication cannot capture deletes and misses updates that leave the cursor column unchanged.

Get your MySQL data into Snowflake

Almost everything that goes wrong with this pipeline traces back to binlog retention being shorter than the work Airbyte needs to do. Set retention generously before the first sync, especially on RDS where the default is zero, freeze schema changes during the initial snapshot, and read the logs for the offset message rather than assuming a repeated full refresh is normal behaviour.

The rest is a decision you make once and live with. Choose your deletion semantics deliberately at setup, use a dedicated user and role at both ends, and treat the initial sync as a coordinated event rather than a button press.

For the equivalent decisions on the other major open source database, see PostgreSQL to Databricks, which covers hard versus soft deletes in more depth. If Snowflake sits alongside another warehouse, BigQuery to Snowflake covers moving between them.

Start syncing now →