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 shop with customers and orders, and the binlog on in ROW format
  • 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 TABLE is not optional on a fresh table. OLake Go splits the snapshot into parallel chunks from the row estimates in information_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:

  1. APIs and services: enable the BigLake API.
  2. 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.
  3. 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.
  4. Keys tab: Add key, Create new key, JSON. The file downloads once.

Google Cloud console IAM Grant access panel with the OLake service account as principal and the BigLake Editor role picked

Google Cloud console Create private key dialog for the OLake service account with JSON chosen as the key type

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.

OLake UI MySQL source form with Enable CDC on, initial wait time 10, host host.docker.internal, database shop and user olake

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

OLake UI Iceberg destination form with catalog type Big Lake, the BigLake REST endpoint, a gs bucket as catalog path, GCP authentication and a project ID

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.

OLake UI Streams page with customers and orders ticked, Full Refresh + CDC, Upsert and Positional Deletes on Iceberg format version V2

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.

OLake UI job history for mysql_to_bigquery with two completed syncs, 37 seconds and then 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.

OLake UI sync log filtered to the Iceberg commit report, showing one data file and one positional delete file for the change sync

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.

BigQuery SQL editor running the customers query on the BigLake catalog and returning 10 rows with id, name, city and tier

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.

Cloud Storage Interoperability settings with the Select a service account dialog and the OLake service account chosen for a new key

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

  • decimal arrives as FLOAT64. OLake Go stores decimals as doubles. The sums still matched, but round at query time when exact decimal math matters.
  • datetime is read as UTC and shows up as TIMESTAMP. json, enum and time arrive as STRING.
  • 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_files and expire_snapshots through 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.