MySQL to Snowflake: Binlog CDC, Replication & Data Ingestion Guide

MySQL tables reach Snowflake in three basic ways: mysql client dumps loaded with COPY INTO, scheduled updated_at queries, or change data capture (CDC) that reads the binary log (binlog) like a replica. For a production database CDC usually fits best: it reads each committed insert, update and delete as a row event instead of re-querying tables, so it also sees hard deletes.

At the source, MySQL keeps binlog files only for a retention period each managed host sets differently, and a pipeline that falls behind it must re-read every table. In Snowflake, DECIMAL loaded as FLOAT keeps about 15 digits, and every load resumes a billed warehouse. Estuary runs this route as a managed service: it reads the MySQL binlog into a durable collection per table and loads Snowflake at an interval you choose.

Choosing a MySQL to Snowflake method

Openflow is Snowflake's current MySQL connector; the older native-app connector stays in preview, with general availability off the roadmap. Most of what follows assumes a binlog reader.

How MySQL to Snowflake binlog CDC works

MySQL sends a CDC pipeline the same row events a replica would apply. Four details matter:

• binlog_format = ROW logs each changed row. STATEMENT, and MIXED by default, log SQL text that a CDC reader can't turn back into rows.
• binlog_row_image = FULL (default) logs every column; MINIMAL logs only the key before a change and changed columns after.
• binlog_row_metadata = FULL adds column names, ENUM/SET labels and key columns. At the default, MINIMAL, the reader tracks structure by parsing DDL, so DDL missing from the binlog leaves it wrong.
• The position is a file and byte offset, valid only on the server that wrote it, or a GTID, valid across the topology.

A new pipeline copies existing rows with SELECT queries and replays binlog events from where the copy began, so a row changed during the copy isn't lost or applied out of order. In Snowflake, changes are staged and merged by key into current-state tables, or appended as rows (Snowpipe Streaming, the Kafka connector).

MySQL requirements

Settings differ by host:

On self-hosted MySQL 8.x, check the settings and create a least-privilege user:

sql
SELECT @@log_bin, @@binlog_format, @@binlog_row_image, @@binlog_row_metadata,
       @@binlog_expire_logs_seconds, @@time_zone;
SET PERSIST binlog_row_metadata = 'FULL';
CREATE USER 'cdc_user'@'%' IDENTIFIED BY '...';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'cdc_user'@'%';
GRANT SELECT ON shop.* TO 'cdc_user'@'%';  -- schema or table level, not column level

Three rules prevent most incidents:

1. Size retention for your longest outage plus catch-up time, with seven days as a floor (the RDS for MySQL maximum).
2. Give every table a primary key or a NOT NULL unique index. Snowflake MERGE matches on it.
3. Name the time zone.DATETIME stores no zone and time_zone = SYSTEM names none, so a reader must be told which zone to convert from. Set the server's zone (IANA names need the time zone tables), or tell the pipeline.

Snowflake requirements

On the Snowflake side, create a database, schema and warehouse plus a dedicated role and service user, and give that user a key pair: Snowflake is disallowing passwords for service users.

• AUTO_SUSPEND = 60 keeps idle time short; each resume is still billed for at least 60 seconds, then per second.
• Choose the timestamp type deliberately; an account-level TIMESTAMP_TYPE_MAPPING must match what the pipeline writes.
• Don't enable QUOTED_IDENTIFIERS_IGNORE_CASE for the pipeline user; MySQL columns named after Snowflake reserved words are quoted.

Setting up MySQL to Snowflake replication

1. Turn on row-based logging with full row images and metadata, and confirm with the query above.
2. Set binlog retention for your worst outage, with your host's setting.
3. Create the replication user and let the pipeline reach the MySQL port: allowlisted IPs, an SSH bastion, or private networking.
4. Prepare Snowflake as described above.
5. Pick merge or append per table: MERGE for tables read as current state, append for event tables.
6. Load existing data. Retention must cover the whole backfill too; prioritise tables and skip unneeded history.
7. Start streaming the binlog, with a Snowflake load interval matched to how often tables are read.
8. Validate. Compare row counts and the exact SUM of each DECIMAL column. UPDATE and DELETE a test row and add a column, then check each in Snowflake after one load interval. Pause the pipeline, run FLUSH BINARY LOGS to start a new binlog file, and confirm it resumes from its saved position.

How updates, deletes and schema changes reach Snowflake

• Updates merge by key, or append as a new version. A primary-key update usually arrives as a delete of the old key plus an insert of the new one.
• Deletes carry only the row's before image; pipelines remove the Snowflake row or flag it, as Openflow's _SNOWFLAKE_DELETED does.
• ON DELETE CASCADE child deletes run inside InnoDB on MySQL 8.0 and 8.4 and aren't logged, so binlog CDC never sees them; 9.6 and later log them as row events unless innodb_native_foreign_keys is on.
• TRUNCATE TABLE is always logged as a statement, so binlog CDC tools typically ignore it. Reload if Snowflake must match.
• New columns appear as nullable columns; earlier rows stay NULL until re-loaded.
• Type changes that widen, such as INT to BIGINT, pass through or migrate in place; incompatible ones usually force a table rebuild.
• ALTER TABLE … DROP COLUMN stops new values for that Snowflake column; Openflow also renames it with a __SNOWFLAKE_DELETED suffix.

MySQL to Snowflake data type mapping

DECIMAL precision

A DECIMAL(18,4) amount has more digits than a 64-bit double holds (about 15), so a pipeline mapping it to Snowflake FLOAT instead of NUMBER(p,s) rounds it and sums stop matching MySQL. Declare NUMBER(p,s) with the MySQL precision and scale up to 38 digits, and VARCHAR above that. Casting the FLOAT column afterwards can't bring back rounded digits; reload the table.

Performance, latency and Snowflake cost

The binlog stream never stops, but a Snowflake table is only as fresh as its last load. Cost tracks how often loads wake the warehouse and how many micro-partitions each MERGE touches.

• Load less often. Each warehouse resume bills at least 60 seconds, so a load every minute keeps it billed around the clock.
• Merge each key once per load. A MySQL counter bumped 500 times between loads should become one merged row, not 500.
• Keep keys in write order. A MERGE bounded by the load's key range lets Snowflake skip micro-partitions: new AUTO_INCREMENT rows sit in a narrow range, random UUIDs span the table.
• Append event tables. Snowpipe Streaming inserts without reading the table, but updates become extra rows, so current state needs a view, which costs compute.

Production considerations

• Retries and recovery. A restarted pipeline resumes from the binlog file and offset it last committed, if MySQL still retains that file. Replays are harmless in merged tables but duplicate appended rows unless position and write commit atomically.
• Source impact. Streaming adds one replication connection; backfills add key-range scans; batch methods re-query every run.
• Monitoring. Alert on lag against binlog retention (SHOW BINARY LOGS lists the files MySQL still holds), on free storage, and on time since the last Snowflake load.

Common problems and troubleshooting

Binlog purged before the pipeline caught up

The pipeline can't resume and reports ERROR 1236 with "Could not find first log file name". The purged changes are gone. Compare the pipeline's saved file with the oldest in SHOW BINARY LOGS.

Recovery, cheapest first:

1. Large tables are append-only or have reliable change timestamps: restart at the current position and re-read only rows newer than the outage there (id > … or updated_at > …), the rest in full.
2. Otherwise: restart at the current position and re-read every table, merging into Snowflake without truncating so it stays queryable.

A re-read can't show rows deleted in the gap: reconcile keys or reload those tables, then raise retention.

Position lost after a failover, restore or replica rebuild

A file-and-offset position means nothing on another server, so after a promotion, snapshot restore or replica rebuild the pipeline fails with ERROR 1236, sometimes citing a position past the end of the file. For a planned switch, pause writes, let the pipeline catch up, switch, then restart at the new server's current position without re-reading tables. Treat an unplanned one like a purged binlog; tools that track GTIDs can follow a failover.

Pipeline falling behind the binlog

If the reader is current but Snowflake is stale, look at the load interval or merges. Real binlog lag usually comes from huge transactions (under FULL, a bulk UPDATE logs two images per row) or a competing backfill; an event over max_allowed_packet stops replication until you raise it.

How Estuary's MySQL to Snowflake pipeline works

Estuary's MySQL CDC capture reads the binlog while backfilling each table in key-ordered chunks of 50,000 rows, and its Snowflake materialization combines changes to each key between syncs, stages them in an internal Snowflake stage, then runs COPY INTO, or MERGE when some keys already exist.

• Retention: at startup the capture requires log_bin, ROW format, the REPLICATION CLIENT grant and 168 hours of retention. If its user can read mysql.rds_configuration, an RDS binlog retention hours of NULL fails with "binlog retention period is too short". Otherwise it reads binlog_expire_logs_seconds, then expire_logs_days, counting zero in both as no expiry, though Azure's default 0 purges promptly. It can't see Cloud SQL's transaction log retention.
• Types:DECIMAL arrives as an exact string with format: number, which lands as FLOAT by default. Set castToString: true on the field to keep the exact digits as TEXT, then cast to NUMBER(p,s) in a view; a DDL override alone still converts through FLOAT. TINYINT(1) stays an integer unless Estuary support sets a feature flag. Binary lands as BINARY, zero dates as 0001-01-01, and TIME as TEXT. DATETIME is converted to UTC using time_zone or the capture's Timezone.
• Keyless tables use a NOT NULL unique index if there is one. Otherwise the collection is keyed by binlog position, which is unique to each change, so Snowflake gets a row per change rather than current state.
• Schema changes:ALTER TABLE is applied from the binlog. DROP TABLE and RENAME TABLE deactivate the binding, and a recreated table is backfilled again. TRUNCATE is ignored.

Implementing MySQL to Snowflake with Estuary

These steps adapt Estuary's general data flow guide to this route.

1. Complete the prerequisites in MySQL requirements and Snowflake requirements on MySQL 8.0 or later, with at least 7 days of binlog retention (setup by host). In Snowflake, run the connector's setup script and register a public key for key-pair authentication; password auth is deprecated.
2. Create the MySQL capture. On the Sources page, click New Capture, choose MySQL, name the capture, and fill in the endpoint properties:
   • Server Address: host:port. Allowlist Estuary's IP addresses, or use Network Tunnel for an SSH tunnel. Private and BYOC deployments can use private networking.
   • Login Username: defaults to flow_capture; with the SQL above, enter cdc_user.
   • Authentication: Password (32 characters at most), or AWS IAM, Google Cloud IAM or Azure IAM (IAM authentication).
   • Timezone: only if time_zone is SYSTEM and tables have DATETIME columns.
   • Advanced Options: leave Skip Binlog Retention Sanity Check off; set SSL Mode to verify_identity to check the server certificate (TLS).

3. Select tables. Click Next. Estuary lists one binding per discovered table under Output Collections.
   • Remove tables you don't want; each binding writes its own collection.
   • Raise Backfill Priority to load key tables first. Leave Backfill Mode at its default; keyless tables get Without Primary Key automatically.
   • Keep Automatically keep schemas up to date on (see schema evolution).

Click Save and publish. The backfill starts, with binlog capture running alongside it.

4. Create the Snowflake materialization. On the capture's details page, click Materialize, choose Snowflake, and fill in the endpoint properties:
   • Host (Account URL): orgname-accountname.snowflakecomputing.com, no protocol.
   • Database, Schema, Warehouse and Role from the setup script.
   • Snowflake Timestamp Type: the type you chose above (timestamp mapping).
   • Authentication: Private Key (JWT), with User and Private Key.
   • Hard Delete: on to delete rows; off keeps soft deletes (_meta/op = 'd').

5. Choose the sync schedule. Under Sync Schedule, Sync Frequency defaults to 30 minutes. Shorter intervals give fresher tables and more warehouse resumes. For business-hours freshness, set Timezone, Fast Sync Start Time, Fast Sync Stop Time and optionally Fast Sync Enabled Days; outside that window, syncs run every 4 hours. See sync schedules.

6. Map collections to tables. Click Next. For the naming convention, Match source structure turns each MySQL database into a Snowflake schema. Each binding under Source Collections has:
   • The table name and Alternative Schema, to override names.
   • Delta Updates, for append-only tables; with key-pair auth they use Snowpipe Streaming, still on the sync schedule.
   • Clustering Keys, for large, selectively filtered tables; clustering bills its own credits.

Before publishing, add castToString: true for each DECIMAL field under the binding's fields.include in the specification (custom column types).

7. Publish. Click Save and publish. Estuary creates the Snowflake tables and loads the backfill without waiting for the schedule.
8. Verify that data is landing.
   • On the capture details page, Connector Status shows backfill progress and the Shard Information card reads PRIMARY; open a collection from the Collections list to see its Data Preview.
   • The materialization details page charts Data Read.
   • In Snowflake, query a target table, then run the checks from Setting up MySQL to Snowflake replication.

How Estuary handles binlog loss, re-syncs and extra destinations

Backfill SELECT results and binlog row events for a MySQL table both land in one collection, a log of JSON files in your cloud storage bucket. Snowflake loads from that log, so rebuilding a table or adding a second destination never queries MySQL; capture-side events, such as a purged binlog or a recreated table, do.

One collection for the backfill and the binlog stream

DECIMAL values sit in the collection as the exact strings MySQL returned, so after you add castToString, a materialization backfill restores exact values from the collection alone. Each MERGE or COPY INTO is saved in the materialization's checkpoint before it runs, so a crash re-runs it from the same staged files.

What a re-backfill does and doesn't avoid

The limits: replay depends on retention (trial storage keeps about 20 days; use your own bucket for full history). A binlog gap means reading every table, because the capture keeps one position for all. Backfills emit no deletes, so rows deleted in the gap stay in Snowflake, and delta-update tables get duplicates from incremental backfills.

Schema evolution

ALTER TABLE changes are read from the binlog, and with Automatically keep schemas up to date on, an added MySQL column appears in Snowflake as a nullable column. Some widening changes, such as integer to text, migrate in place; other incompatible changes rebuild the table from the collection unless onIncompatibleSchemaChange says to abort or disable.

When another approach may make more sense

• Snowflake-only, native tooling: the Openflow Connector for MySQL reads the binlog from MySQL 8.0 and 8.4 (Aurora version 3 only) on a Snowflake-hosted or AWS-hosted Openflow runtime. It uses password auth only, needs a primary key, NOT NULL unique index or logical key, marks deletes in _SNOWFLAKE_DELETED and keeps journal tables indefinitely; re-adding a failed table re-snapshots it.
• A Debezium MySQL connector already feeds Kafka: load the topics with the Snowflake Kafka connector. It inserts rows, so you build the dedup/MERGE layer yourself.
• A one-off move, or a few small tables refreshed nightly:mysql --batch exports with COPY INTO, or an updated_at query, need no binlog settings. Each run rereads or requeries every table, and deleted rows never arrive.
• No binlog access (some managed tiers, or a replica without binary logging) or you need views and custom SQL: use query-based batch capture, which mostly can't see deletes. Estuary's MySQL Batch connector polls tables, views or custom SQL, every 24 hours by default.

Related resources

• Estuary MySQL CDC connector, Amazon RDS for MySQL, Google Cloud SQL for MySQL, MySQL Batch
• Estuary Snowflake materialization, sync schedules, backfilling data, schema evolution, custom column types
• MySQL: binary logging options, InnoDB and replication
• Hosts: RDS binlog retention, Aurora binlog retention, Cloud SQL transaction log retention, Azure Flexible Server parameters
• Snowflake: Openflow Connector for MySQL, numeric types, key-pair authentication

To try the route, create a MySQL capture in the Estuary dashboard and follow the data flow guide. Pricing: estuary.dev/pricing.