A MySQL to BigQuery job tends to start as a nightly mysqldump, a file in a bucket and a bq load. It works for a while. Then an order status changes at lunch, the afternoon dashboard still shows the old one, and a customer who was deleted last week is still being counted.
The usual ways to get MySQL rows into BigQuery:
- Datastream for MySQL: Google's managed change data capture (CDC) service. It reads the binlog and writes native BigQuery datasets, near real time by Google's description, with nothing for you to run. BigQuery is the only reader of what it writes.
- Debezium, Kafka and a BigQuery sink connector: changes arrive in seconds, and you operate Kafka, Kafka Connect, Debezium and one sink per table.
- Export, then
bq load: a script, a scheduler and a staging bucket. Full reloads only, so updates and deletes never show up as changes. - Managed extract, load, transform (ELT) tools such as Fivetran, Estuary or Airbyte Cloud: nothing to host, freshness in minutes depending on the plan, and a second copy in BigQuery that you pay to move and store.
This post walks a fifth path. OLake Go, an open source (Apache 2.0) replication tool, reads the MySQL binary log and commits the rows as Apache Iceberg tables into BigLake, Google Cloud's Iceberg representational state transfer (REST) catalog. The files sit in your own Cloud Storage bucket, and BigQuery queries them by a four-part name with no dataset to create and no external table to declare.
I ran this against MySQL 8.4 in Docker and a bucket in us-central1, with OLake Go v1.1.0. The snapshot of 48 records took 37 seconds, and a change sync of 12 records took 55 seconds. All numbers below come from that run.
What the MySQL to BigQuery pipeline looks like
- Source: a MySQL database
shopwithcustomersandorders, and the binlog on inROWformat - Pipeline: OLake UI from its Docker Compose install, one job in Full Refresh + CDC mode
- Catalog and storage: a BigLake catalog named after a Cloud Storage bucket, holding Parquet data files and positional delete files
- Readers: BigQuery first, then DuckDB 1.5.5 and Spark 4.0.1 with Iceberg 1.10.0 against the same tables
You need: Docker, a Google Cloud project where you can enable an API and create a service account, and about 30 minutes.
Step 1: MySQL with the binlog on
For a local test, start MySQL 8.4 with the four binlog settings on the command line:
docker run -d --name mysql_cdc -p 3306:3306 \ -e MYSQL_ROOT_PASSWORD=<root password> -e MYSQL_DATABASE=shop \ mysql:8.4 \ --server-id=1 --log-bin=mysql-bin --binlog-format=ROW \ --binlog-row-metadata=FULL --binlog-expire-logs-seconds=604800 On a server you already have, the same lines go in my.cnf followed by a restart. On Cloud SQL, RDS, Aurora or Azure they are flags or a parameter group. On Cloud SQL, point-in-time recovery is what turns the binlog on.
Create the tables, or skip this and use your own:
CREATE TABLE customers ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), email VARCHAR(200), city VARCHAR(100), tier VARCHAR(20), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, product VARCHAR(100), amount DECIMAL(10,2), status VARCHAR(20), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(id)); My run held 8 customers and 40 orders. Two of the customers, Liam Carter and Zara Ali, get updated in step 6, so add rows with those names if you want to replay my changes exactly.
Then the replication user and fresh statistics:
CREATE USER IF NOT EXISTS 'olake'@'%' IDENTIFIED BY '<password>'; GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'olake'@'%'; FLUSH PRIVILEGES; ANALYZE TABLE customers, orders; SHOW VARIABLES WHERE Variable_name IN ('log_bin', 'binlog_format', 'binlog_row_metadata', 'server_id'); SHOW GRANTS FOR 'olake'@'%'; ⚠️
ANALYZE TABLEis not optional on a fresh table. OLake Go splits the snapshot into parallel chunks from the row estimates ininformation_schema, and a table that was just loaded has none.
Seven days of binlog (604800 seconds) is the replay window. A job paused longer than that cannot find its saved position and needs a new full snapshot.
Step 2: The BigLake catalog on Google Cloud
There is no catalog to create by hand. BigLake makes one named after the bucket the first time OLake Go connects. You prepare four things in the console:
- APIs and services: enable the BigLake API.
- Cloud Storage: create a bucket in the region where BigQuery will run the queries. The bucket name becomes the catalog name in SQL, so keep it short.
- IAM and Admin, Service Accounts: create one for OLake Go, then grant BigLake Editor on the project and Storage Object User on the bucket. IAM stands for Identity and Access Management.
- Keys tab: Add key, Create new key, JSON. The file downloads once.
The two roles split the work. BigLake Editor lets the writer create namespaces and commit tables, and Storage Object User lets it write Parquet and metadata into the bucket.
Step 3: Start OLake UI and add MySQL
curl -sSL https://raw.githubusercontent.com/datazip-inc/olake-ui/master/docker-compose-v1.yml | docker compose -f - up -d Open http://localhost:8000 and log in with admin / password.
Go to Sources, Create Source, MySQL. Leave Enable CDC on and the Initial Wait Time at its default. For connection details I used host host.docker.internal (the OLake containers reach MySQL through it), database shop, user olake, and SSL off.
Set up source tests the connection and then checks binlog_access, log_bin, binlog_format and binlog_row_metadata. A failing check is shown with its current and recommended value, so a wrong setting surfaces here and not halfway through a sync.
Step 4: The BigLake destination
Go to Destinations, Create Destination, Apache Iceberg, and pick BigLake as the catalog type.
| Field | Value |
|---|---|
| REST Catalog URI | https://biglake.googleapis.com/iceberg/v1/restcatalog |
| BigLake Catalog Path | gs://<bucket>, bucket root only |
| Authentication Type | GCP |
| GCP Service Account JSON | the whole key file from step 2 |
| Catalog Name | olake_iceberg |
| GCP Project ID | your project ID |
| Enable Arrow Writes | off |
Click Create. The test calls the endpoint, creates the catalog on first contact, and writes and drops a test table. After that the catalog shows up under BigLake, Catalogs in the console.
⚠️ Leave the catalog in its default end-user credential mode. OLake Go 1.1.0 does not send the access delegation header that vended-credentials mode requires, and the test fails.
Step 5: The job and the first sync
Jobs, Create Job. Name it mysql_to_bigquery, pick a schedule, and select the source and destination. Under Advanced Settings, set Target Query Engines to Google BigQuery, Apache Spark and DuckDB.
That list decides the delete format. OLake Go offers only formats all three engines read, equality and positional deletes, and I kept Positional Deletes. Deletion vectors are left out because BigQuery reads them only in preview.
On the Streams page, tick both tables, keep Full Refresh + CDC and Upsert, then Create Job and Sync Now.
The first run copied 48 records (2.10 KB) in 37 seconds, container start included. The namespace is named after the job and the database, so it became mysql_to_bigquery_shop.
Step 6: Query in BigQuery, change rows, query again
In the BigQuery SQL editor, set the query location to the bucket's region, then use the four-part name:
SELECT id, name, city, tier FROM `<project-id>.<bucket>.mysql_to_bigquery_shop.customers` ORDER BY id; SELECT status, count(*) AS n, round(sum(amount), 2) AS total FROM `<project-id>.<bucket>.mysql_to_bigquery_shop.orders` GROUP BY status ORDER BY status; No CREATE EXTERNAL TABLE, no metadata refresh. The namespace is the dataset and the bucket is the catalog.
Now make 12 changes in MySQL:
INSERT INTO customers (name, email, city, tier) VALUES ('Maya Patel', 'maya@example.com', 'Pune', 'silver'), ('Lucas Silva', 'lucas@example.com', 'São Paulo', 'bronze'); INSERT INTO orders (customer_id, product, amount, status) VALUES (9, 'Laptop', 1299.00, 'placed'), (9, 'Mouse', 25.50, 'placed'), (10, 'Monitor', 349.99, 'placed'), (3, 'Keyboard', 89.00, 'placed'), (7, 'Webcam', 79.90, 'placed'); UPDATE customers SET tier = 'gold' WHERE name = 'Liam Carter'; UPDATE customers SET city = 'Abu Dhabi' WHERE name = 'Zara Ali'; UPDATE orders SET status = 'delivered' WHERE id IN (1, 4, 7); Sync Now again. The second run read 12 records (563 bytes) from the binlog in 55 seconds.
For customers the commit report in the sync log reads addedDataFiles=1, addedRecords=4, addedPositionalDeleteFiles=1, addedPositionalDeletes=2. Four rows went in (2 inserts plus the new versions of 2 updates), and one small delete file retired the 2 old versions. orders got 8 rows and 3 deletes.
Rerun the SELECT. Liam Carter is gold, Zara Ali lives in Abu Dhabi, the two new customers are there, and there is still one row per key.
Every table also carries _olake_id, _op_type, _olake_timestamp and _cdc_timestamp. _op_type is r for snapshot rows, c for inserts, u for updates and d for deletes.
Read the same tables from DuckDB
DuckDB attaches BigLake with a Google access token. In end-user mode it also needs its own storage credentials: a hash-based message authentication code (HMAC) key for the same service account, from Cloud Storage, Settings, Interoperability.
INSTALL iceberg; LOAD iceberg; INSTALL httpfs; LOAD httpfs; -- gcloud auth print-access-token, or a token minted for the service account CREATE SECRET biglake_catalog ( TYPE iceberg, TOKEN '<access token>', EXTRA_HTTP_HEADERS MAP {'x-goog-user-project': '<project-id>'} ); CREATE SECRET biglake_files (TYPE gcs, KEY_ID '<hmac access id>', SECRET '<hmac secret>'); ATTACH 'gs://<bucket>' AS biglake ( TYPE iceberg, ENDPOINT 'https://biglake.googleapis.com/iceberg/v1/restcatalog', SECRET biglake_catalog ); SELECT id, name, city, tier FROM biglake.mysql_to_bigquery_shop.customers ORDER BY id; DuckDB 1.5.5 returned the same 10 customers, and Spark 4.0.1 did too. The orders roll-up matched MySQL to the cent in all three engines: 16 delivered for 8659.68, 18 placed for 10522.26 and 11 shipped for 6397.25.
Tips from the run
-
decimalarrives asFLOAT64. OLake Go stores decimals as doubles. The sums still matched, but round at query time when exact decimal math matters. -
datetimeis read as UTC and shows up asTIMESTAMP.json,enumandtimearrive asSTRING. - Tables need a primary key. Without one, inserts replicate but updates and deletes have no row to match.
- Watch
metadata.json. BigLake rejects one above 1 MB, which a busy table reaches after a few thousand syncs. Expire snapshots and compact on a schedule. - Catalog tables are read from Parquet on every query. When a dashboard needs native BigQuery speed, load the hot slice into a native table on a schedule.
How the MySQL to BigQuery options compare
| Method | Freshness | Updates and deletes | Where the data lands | What you run |
|---|---|---|---|---|
| Datastream | near real time | yes | native BigQuery datasets | nothing; a stream in the console |
| Debezium, Kafka, BigQuery sink | seconds | yes, in upsert mode | Kafka, then BigQuery tables | Kafka, Connect, Debezium, sinks |
Export plus bq load | hours to daily | no | native BigQuery tables | a script and a scheduler |
| Managed ELT | minutes, by plan | varies | a second copy in BigQuery | nothing; per row or per GB pricing |
| OLake Go to BigLake | CDC, schedule down to one minute | yes, as row-level deletes | open tables BigQuery, Spark and DuckDB read | one Docker Compose stack |
When BigQuery is the only reader and a managed Google service is what you want, Datastream is the shorter route. Writing open tables once pays off when Spark, DuckDB or a notebook needs the same rows, or when you want the files in your own bucket.
FAQ
What happens when someone adds a column in MySQL?
The next sync adds it to the table and commits the new schema to BigLake in the same commit, so BigQuery shows it on the next query. Widening changes such as int to bigint are applied. A narrowing or incompatible change fails the sync instead of corrupting the table.
Can the dataset have a different name than mysql_to_bigquery_shop?
Yes. The Iceberg DB field at the top of the Streams page sets the namespace before the first sync. By default it combines the job name and the source database.
Why not deletion vectors, if BigQuery can read them?
BigQuery reads deletion vectors only in preview, with a cap of 100,000 entries per query. Positional deletes are fully supported, and DuckDB and Spark read them as well. That is why the job resolves to positional deletes with these three engines.
What does the job cost to keep running?
Storage is Cloud Storage rates for the Parquet and delete files, and the catalog has no per-table charge. BigQuery bills queries by bytes scanned on demand or by slot time on an edition. OLake Go runs on whatever machine hosts Docker.
Where to go from here
- Swap the Docker MySQL for your real server. Only how the binlog flags are set changes.
- Put compaction and snapshot expiry on a schedule with Spark's
rewrite_data_filesandexpire_snapshotsthrough the same catalog. - The full guide, with the CLI configs, the Spark connection and the data type table, is on olake.io.
If one of these steps behaves differently for you, leave a comment with the error and I will update the post.










