MySQL to BigQuery: how to replicate MySQL tables into BigQuery incrementally

How do you connect MySQL to BigQuery?

You connect MySQL to BigQuery by running an extract process that reads rows from MySQL over the standard client protocol and writes them into BigQuery through load jobs or the Storage Write API. BigQuery has no native driver that reads MySQL tables on its own, so something sits in the middle: a managed connector, a scheduled script, or a streaming CDC service.

The plumbing has three parts. First, database access: point the reader at a replica rather than the primary, allowlist the egress IPs or set up a private connection, and create a dedicated MySQL user with SELECT on the tables you replicate, plus REPLICATION SLAVE and REPLICATION CLIENT if you go the binlog route. Second, a Google Cloud service account holding bigquery.dataEditor on the destination dataset, and object write access on a staging bucket if you stage through Cloud Storage. Third, the load logic itself: watermark bookkeeping, a staging table, the MERGE, and retries.

Reading from a replica is the part teams skip and regret. A first backfill of a 400 million row table is a long sequential scan that competes with the queries paying your salary. Point it at a read replica, size the batches, and the backfill becomes boring. The managed path is the MySQL to BigQuery connector, which owns the watermark, the staging table, the merge, and the retry behavior. If your warehouse is Snowflake instead, the same pattern runs through the MySQL to Snowflake connector.

Can you query MySQL directly from BigQuery?

Yes, if the database is Cloud SQL for MySQL. BigQuery federated queries let you create a Cloud SQL connection and call EXTERNAL_QUERY to push a query to the source database and get results back in BigQuery, with no copy at all. For self-managed MySQL on your own servers or on RDS, federation is not available and you replicate instead.

Federation is genuinely useful for small lookup tables and ad hoc checks. It is a poor fit for dashboards, because every query hits the operational database, so concurrency and load land on the system that also takes customer orders. It also inherits the source's limits: joins between a federated table and a large BigQuery table pull data across the boundary each time, and MySQL users configured with caching_sha2_password authentication are not supported. Replicate when the data is read repeatedly, federate when it is read rarely.

How do you replicate MySQL to BigQuery in real time?

Real time means log-based change data capture. A CDC reader consumes the MySQL binary log, which records every committed insert, update, and delete in order, and applies those events to BigQuery within seconds. Google's managed option is Datastream, which supports MySQL 5.6, 5.7, 8.0, and 8.4, with GTID-based replication available on 5.7 and later. Everything else is polling with a shorter interval.

The MySQL side has prerequisites. The binary log must be enabled with binlog_format = ROW and binlog_row_image = FULL, so an update event carries the whole row rather than the changed columns. Retention has to be long enough that your reader can be down for a weekend without losing its place, which usually means days rather than hours. On MySQL 8.0 and later Datastream requires binlog_row_value_options to be empty, and it does not support binary log transaction compression, so leave that off if you plan to stream. Very large tables need care too: Datastream requires a unique, non-nullable index for backfilling tables above 500 million rows, and a stream tops out at 10,000 tables.

Be honest about whether you need it. CDC buys you seconds of latency and correct deletes, and it costs you a new operational surface: a reader that must keep up, binlog retention to monitor, and schema changes that arrive as events you have to interpret. If the consumers are dashboards that refresh hourly, a watermark load is the better trade.

Method	Freshness	Sees deletes	Load on MySQL	Use it when
Full reload	As often as you can afford	Yes, by replacement	High on every run	Small reference tables under a few million rows
Watermark on updated_at	Minutes to an hour	No	Low, indexed range scan	Most operational tables. The default choice
Binlog CDC (Datastream, Debezium)	Seconds	Yes	Low, reads the log not the tables	Low-latency needs and hard deletes you must track
Federated query (Cloud SQL only)	Live, no copy	N/A	Every query hits production	Small lookups and ad hoc checks, never dashboards

How do MySQL data types map to BigQuery types?

Most MySQL types have an obvious BigQuery counterpart: integers become INT64, VARCHAR becomes STRING, DATE stays DATE. The mappings worth deciding on purpose are DECIMAL, TINYINT(1), and the difference between DATETIME and TIMESTAMP.

MySQL type	BigQuery type	Notes
TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT	INT64	Unsigned BIGINT can exceed INT64. Carry those as NUMERIC or STRING if the values run high
TINYINT(1)	BOOL	MySQL has no real boolean, so this is the convention. Confirm the column truly holds 0 and 1 before casting
BIT(1)	BOOL	Wider BIT columns land as INT64 or BYTES
DECIMAL, NUMERIC	NUMERIC, or BIGNUMERIC past 38 digits	The money case. Never let a DECIMAL land in FLOAT64, which rounds cents in ways nobody catches until a reconciliation fails
FLOAT, DOUBLE	FLOAT64	Fine for measurements, wrong for currency
VARCHAR, CHAR, TEXT, LONGTEXT	STRING	BigQuery STRING has no declared length, so MySQL length limits simply disappear
DATE	DATE	Direct match. Watch for the zero date 0000-00-00, which is not a valid DATE and must become NULL
DATETIME	DATETIME, or TIMESTAMP in UTC	DATETIME is wall-clock with no zone. If the app writes local time here, you inherit the ambiguity
TIMESTAMP	TIMESTAMP	MySQL stores this as UTC internally and renders it in the session zone. Read it as UTC and stop thinking about it
TIME	TIME	MySQL TIME allows values beyond 24 hours (it can express a duration). Those will not fit
JSON	JSON or STRING	JSON allows dot-path queries. STRING is safer when the shape is inconsistent and you want raw fidelity
ENUM, SET	STRING	New enum values arrive silently, so validate downstream instead of assuming the set is closed
BLOB, BINARY, VARBINARY	BYTES	Ask whether it belongs in the warehouse at all. Large blobs inflate storage and are rarely queried
GEOMETRY, POINT	GEOGRAPHY, or skip	Spatial types are the common gap. Datastream, for one, replaces spatial columns with NULL

Two of these deserve one more sentence. TINYINT(1) is the classic MySQL ambiguity, because the same declaration is used both for genuine booleans and for small integers like a rating from 1 to 5. Cast blindly and a rating of 3 becomes true. And the zero date is a MySQL quirk with no equivalent anywhere else: legacy schemas are full of 0000-00-00 values that will fail the load, so map them to NULL during extraction rather than discovering them at 2 a.m.

How do you handle deletes when replicating MySQL to BigQuery?

A watermark load cannot see a hard delete. The row is gone, no updated_at changed, and the copy in BigQuery lives on as a ghost that inflates every count. There are three fixes: soft-delete in MySQL with a deleted_at column the watermark already picks up, run binlog CDC which emits delete events, or periodically reconcile primary keys between source and target and remove the orphans.

Soft deletes are the cheapest answer and the one most teams land on. If the application sets deleted_at instead of issuing a DELETE, the deletion is just another update riding the existing pipeline, your BigQuery views filter WHERE deleted_at IS NULL, and you keep the history for free. When you cannot change the application, a weekly key reconciliation on the tables that matter is usually enough: pull the primary keys, compare, delete what no longer exists.

Updates ride a MERGE against a staging table, and the pattern that survives retries is dedupe-then-merge. A single batch can contain several versions of the same row, so collapse the staging set with ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC), keep rank 1, then MERGE on the primary key. That is what makes the load idempotent: replaying a batch after a network timeout produces the same table state instead of duplicates. Retries are normal, not exceptional, so design for them.

One more detail specific to MySQL. If you replicate a table whose primary key is an AUTO_INCREMENT integer and the table has ever been restored from a backup or migrated between shards, verify that keys were not recycled. Merging on a recycled key silently overwrites an unrelated row. When in doubt, generate a stable surrogate key during extraction and merge on that.

How much does it cost to move MySQL data into BigQuery?

BigQuery charges for storage and for the bytes your queries scan, or for slot time on capacity pricing. Loading data through batch load jobs is free, so the replication itself is rarely the line item. What drives the bill is table design, because an unpartitioned table forces every query to read all of it, and a nightly full reload rewrites storage you already had.

Partitioning is the highest-leverage decision on the page. Partition fact tables by a date column analysts actually filter on, order date or event date, or by ingestion time if there is no natural candidate. A dashboard asking for the last 30 days then reads 30 partitions instead of four years of history. Layer clustering on top for the columns that show up in WHERE and JOIN clauses, usually customer ID, tenant ID, or status, and BigQuery prunes blocks inside each partition as well.

Full reloads compound the cost quietly. Every reload writes a fresh copy of every row, which resets the clock on long-term storage discounts and pushes needless churn through the merge. Incremental loads touch only the partitions that actually changed. On the pipeline side our pricing is flat by plan (Starter $49, Growth $149, Scale $399, Enterprise custom) rather than metered per row, so a busy month does not produce a surprise invoice. See pricing, or the fuller breakdown in what data integration actually costs.

What breaks in production, and how do you catch it?

Four failure modes account for most incidents: schema drift when someone adds or retypes a column in MySQL, binlog retention expiring while a CDC reader is behind, timezone drift from mixing DATETIME and TIMESTAMP, and a load that stops succeeding without anyone noticing. The last one does the most damage, because the dashboard keeps rendering yesterday's numbers as though they were today's.

Schema drift is the routine one. A developer adds referral_source to users on a Tuesday and your pipeline either ignores the column or fails hard. Prefer a loader that adds new nullable columns automatically and alerts on incompatible changes, such as an INT becoming VARCHAR, which needs a human decision. Before you retype a column that analysts already build on, it helps to know which dashboards and models depend on it, which is what a column-level lineage map across the warehouse gives you.

Binlog expiry is the CDC-specific trap. MySQL purges binary logs on a retention schedule, so if your reader dies on Friday and the retention is 24 hours, Monday morning you are not behind, you are broken, and the fix is a full re-backfill. Set retention generously, alert on reader lag well before the retention window, and treat a stopped stream as a page rather than a ticket.

For silent stops, alert on absence rather than on errors. A rule like "the last successful run of this table was more than 90 minutes ago" catches the case where the job never started at all, which an error-only alert can never see. Keep a per-run log with row counts and the watermark value you committed, because when the numbers look wrong the first question is always which batch last touched the table.

Once the tables are landing cleanly, the rest of the work is modeling and serving. If you are still deciding where transformation belongs, the tradeoffs are in ETL vs ELT, and the broader pipeline approach lives on the ETL solutions page. The same partitioning and watermark rules carry over if you are also loading Postgres into BigQuery, and the MySQL to Snowflake route covers the same source against a different warehouse. To see the field mapping and schedule against your own tables, open the MySQL to BigQuery integration or start a demo.