Databricks is where the joins, the notebooks and the models run. The orders they depend on are written in MySQL, one row at a time, all day long. How those rows get from MySQL to Databricks decides whether a status that flipped this morning is visible this afternoon, or only after tonight's reload.
The common ways to move the data:
-
Dump and load.
mysqldumporSELECT ... INTO OUTFILEwrites files to a cloud bucket, and COPY INTO or Auto Loader picks them up. You keep an export script, a bucket and a Databricks job. Freshness is hours to daily, and a changed or deleted row only arrives through a full reload or a merge you write. - Debezium and Kafka, read with Structured Streaming. Changes show up in seconds. The price is Kafka, Kafka Connect, Debezium and a Databricks job that stays running, plus your own merge logic for updates and deletes.
- Lakeflow Connect. Databricks lists MySQL among its managed change data capture (CDC) connectors. It runs on Databricks serverless compute with nothing to host, and the rows land in streaming tables governed by Unity Catalog.
This post walks through a fourth option. OLake Go, an open source (Apache 2.0) replication tool, reads the MySQL binary log (binlog) directly and commits the rows as Apache Iceberg tables into Unity Catalog. Databricks shows them as managed Iceberg tables, and DuckDB and Spark can query the same files through the same catalog.
I ran every step below against MySQL 8.4 and a Databricks workspace on Google Cloud, with OLake Go v1.1.0. The snapshot of 48 records took 25 seconds, and a change sync of 12 records took 22 seconds. Each figure in this post is from that run.
The MySQL to Databricks pipeline at a glance
-
Source: a MySQL database called
shopwith two tables,customersandorders, and the binlog on inROWformat - Writer: OLake UI from its Docker Compose install, with one job in Full Refresh + CDC mode
- Catalog: Unity Catalog, which keeps the tables as managed Iceberg tables in the catalog's managed storage
- Readers: the Databricks SQL editor on a 2X-Small serverless SQL warehouse, then DuckDB 1.5.5
You need: MySQL 5.7 or later, a Databricks workspace with Unity Catalog, an admin who can turn on external data access, Docker, and about 30 minutes.
Step 1: Prepare MySQL for CDC
OLake Go reads the same stream that MySQL replicas use. The server needs the binlog on with a server_id, the ROW format and FULL row metadata. These lines go in my.cnf, followed by a restart:
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_row_metadata = FULL
binlog_expire_logs_seconds = 604800
Statement-based logging does not carry row images, which is why the format has to be ROW. The last line keeps 7 days of binlog, so a paused job can catch up. On Amazon Relational Database Service (RDS), Aurora, Azure or Cloud SQL, the same settings live in a parameter group or a server setting instead of my.cnf.
To replay my data, create the two tables. Skip this block if you are pointing at tables you already have:
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));
Insert whatever rows you like. Mine held 8 customers and 40 orders, and the statements in step 7 update two customers named Liam Carter and Zara Ali.
Next comes the user OLake Go logs in as, fresh table statistics, and a check of the result:
CREATE USER IF NOT EXISTS 'olake'@'%' IDENTIFIED BY '<password>';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'olake'@'%';
FLUSH PRIVILEGES;
-- Table statistics: the chunked snapshot plans its chunks from them
ANALYZE TABLE customers, orders;
SHOW VARIABLES WHERE Variable_name IN ('log_bin', 'binlog_format', 'binlog_row_metadata', 'server_id');
SHOW GRANTS FOR 'olake'@'%';
⚠️ Do not drop the
ANALYZE TABLEline. The first sync splits each table into parallel chunks using table statistics, and a freshly created or freshly loaded table has none. Without them the sync stops withstats not populated for table[shop.orders]. Please run ANALYZE TABLE to update table statistics.
Step 2: Set up Unity Catalog in Databricks
OLake Go writes through Unity Catalog's Iceberg representational state transfer (REST) endpoint. Databricks allows writes through that endpoint for managed Iceberg tables only (Databricks: access tables from Iceberg clients). The workspace needs four things, set once:
| What | Where | Why |
|---|---|---|
| External data access on the metastore | Catalog, the gear icon, Metastore | Without it the endpoint refuses every client that is not Databricks itself |
| A catalog with managed storage | Catalog, Create catalog | OLake Go creates one schema per job inside it |
EXTERNAL USE SCHEMA for the writing principal |
On the catalog | Unity Catalog checks it before it serves a table to an outside engine |
| A personal access token | Settings, Developer, Access tokens | It goes out as a bearer token on every catalog call |
Then run the grants in the Databricks SQL editor, with your own principal in place of the example:
-- The catalog OLake Go writes into. Its managed storage holds the Iceberg files.
CREATE CATALOG IF NOT EXISTS lakehouse MANAGED LOCATION '<external location URL>/lakehouse';
-- What the writing principal needs: browse and create schemas, then read and write tables in them
GRANT USE CATALOG, CREATE SCHEMA ON CATALOG lakehouse TO `olake@example.com`;
GRANT USE SCHEMA, CREATE TABLE, SELECT, MODIFY ON CATALOG lakehouse TO `olake@example.com`;
GRANT EXTERNAL USE SCHEMA ON CATALOG lakehouse TO `olake@example.com`;
Granting on the catalog means every schema created later inherits the privileges. In my workspace the catalog was called datazip, and the token expired after seven days. The SQL in this post says lakehouse, so put your own catalog name in its place.
⚠️
ALL PRIVILEGESdoes not containEXTERNAL USE SCHEMA, and only the catalog owner can grant it (Databricks: Unity Catalog privileges). Even a catalog owner has to grant it explicitly.
Step 3: Start OLake UI and connect 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.
- Open Sources, click Create Source and pick MySQL as the connector. Name the source and keep the OLake Go version on the latest release.
- Under Change data capture (CDC), leave Enable CDC on. The default Initial Wait Time is fine.
- Under Connection details, enter the host, port, database name, username and password.
- Click Set up source.
My MySQL ran in another container, so the host was host.docker.internal. The database was shop, the user was olake from step 1, and SSL was disabled for the local test.
Set up source tests the connection and then runs four binlog checks before anything is saved: binlog_access, log_bin, binlog_format and binlog_row_metadata. If one fails, the dialog names the setting and shows its recommended value.
Step 4: Add the Unity Catalog destination
Open Destinations and click Create Destination. Pick Apache Iceberg as the connector, Unity as the catalog type and Personal Access Token as the authentication type.
| Field | Value |
|---|---|
| REST Catalog URL | https://<workspace-url>/api/2.1/unity-catalog/iceberg-rest |
| Unity Catalog Name |
lakehouse, the catalog from step 2 |
| Token | the personal access token from step 2 |
Click Create. OLake Go calls the endpoint with the token and saves the destination. A permission error here points back to step 2: external data access is off, the principal lacks EXTERNAL USE SCHEMA, or the catalog name is wrong.
Step 5: Create the job and run the first sync
- Open Jobs and click Create Job. I named mine
mysql_to_databricks. Give it a schedule, either Every Day at a fixed hour or a cron expression. - Select the MySQL source and the Unity destination.
- Under Advanced Settings, set Target Query Engines to Databricks, plus DuckDB and Apache Spark if they will read the tables too.
- Click Next. OLake Go discovers the tables and opens the Streams page.
- Tick
customersandorders. Set the sync mode to Full Refresh + CDC and the ingestion mode to Upsert, and leave the Upsert Type alone. - Click Create Job, then open the job's menu and choose Sync Now.
The third item is the one Databricks cares about. Under Unity Catalog, Databricks reads Iceberg v3 deletion vectors and rejects commits that carry equality deletes. With Databricks in the list, the Upsert Type on both streams was already Deletion Vector, so there was no format for me to pick.
The first sync copied 8 customers and 40 orders (48 records, 2.10 KB) in 25 seconds, container start included. It notes the current binlog position first and then reads each table in parallel chunks, so a row written during the copy is not lost.
The schema name comes from the job and the source database: job mysql_to_databricks on database shop gave mysql_to_databricks_shop. To choose your own, edit the Iceberg DB field at the top of the Streams page before the first sync.
Step 6: Query the tables in Databricks
Nothing has to be registered. The tables are managed Iceberg tables in the catalog, and the SQL editor or a notebook reads them by three-part name:
SHOW TABLES IN lakehouse.mysql_to_databricks_shop;
SELECT id, name, city, tier
FROM lakehouse.mysql_to_databricks_shop.customers
ORDER BY id;
SELECT status, count(*) AS n, round(sum(amount), 2) AS total
FROM lakehouse.mysql_to_databricks_shop.orders
GROUP BY status ORDER BY status;
A 2X-Small serverless SQL warehouse was plenty for these checks. DESCRIBE EXTENDED on either table shows the Iceberg provider and the deletion vectors in its properties.
OLake Go adds four columns to every table: _olake_id (a hash of the primary key), _op_type, _olake_timestamp and _cdc_timestamp. MySQL tables get two more, _cdc_binlog_file_name and _cdc_binlog_file_pos.
Step 7: Change rows in MySQL and sync again
A full copy proves little. These are the 12 changes I made in MySQL after the first sync, 7 inserts and 5 updates:
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', updated_at = NOW() WHERE name = 'Liam Carter';
UPDATE customers SET city = 'Abu Dhabi', updated_at = NOW() WHERE name = 'Zara Ali';
UPDATE orders SET status = 'delivered', updated_at = NOW() WHERE id IN (1, 4, 7);
Choose Sync Now a second time. This sync read 12 records (563 bytes) from the binlog and finished in 22 seconds end to end.
For customers, the sync wrote one new data file holding 4 rows: the 2 inserts and the new versions of the 2 updated rows. One deletion vector, a small bitmap per data file that marks replaced positions, retires the 2 old versions. The commit report in the sync log says the same thing: addedDataFiles=1, addedRecords=4, addedDVs=1 and addedPositionalDeletes=2.
orders has the same shape, with 8 rows and 3 deleted positions, and the equality delete count stays at 0.
Now run the SELECT from step 6 again, without any refresh. Liam Carter is gold, Zara Ali has moved to Abu Dhabi, and the two new customers are in the result.
The _op_type column records how each row arrived: r for the 6 untouched snapshot rows, c for the 2 inserts and u for the 2 updates. A delete is marked d. The orders roll-up matched MySQL to the cent: 16 delivered orders for 8659.68, 18 placed for 10522.26 and 11 shipped for 6397.25.
Schema changes. A column added in MySQL is added to the Iceberg table on the next sync, and the new schema is committed to Unity Catalog. Widening changes such as int to bigint are applied. Narrowing ones fail the sync rather than corrupt the table.
Read the same tables from DuckDB
DuckDB attaches the Unity Catalog catalog as an Iceberg REST catalog with the same token. Unity Catalog hands it short-lived storage credentials per table, so DuckDB needs no cloud keys of its own.
INSTALL iceberg; LOAD iceberg;
INSTALL httpfs; LOAD httpfs;
CREATE SECRET unity (TYPE iceberg, TOKEN '<personal access token>');
ATTACH 'lakehouse' AS uc (
TYPE iceberg,
ENDPOINT 'https://<workspace-url>/api/2.1/unity-catalog/iceberg-rest',
SECRET unity,
ACCESS_DELEGATION_MODE 'vended_credentials'
);
SELECT id, name, city, tier
FROM uc.mysql_to_databricks_shop.customers
ORDER BY id;
DuckDB 1.5.5 returned the same 10 customers and the same orders roll-up as Databricks. Spark 4.0.1 with Iceberg 1.10.0 did too, through the same endpoint. The principal behind the token needs EXTERNAL USE SCHEMA for these readers as well.
Data types worth a look
| MySQL | Databricks shows | Note |
|---|---|---|
int, mediumint, smallint, tinyint
|
INT |
|
bigint |
BIGINT |
|
decimal(p,s) |
DOUBLE |
round at query time when you need exact decimal arithmetic |
float |
FLOAT |
|
datetime, timestamp
|
TIMESTAMP |
Coordinated Universal Time (UTC); datetime has no zone in MySQL and is read as UTC |
enum, json, time, bit(1), text types |
STRING |
time arrives as text |
In my run DESCRIBE showed orders.amount as DOUBLE, and its sums still matched MySQL to the cent.
Tips and limits
-
Keep the binlog longer than your longest pause. MySQL deletes binlog files after
binlog_expire_logs_seconds, and a job paused beyond that needs a fresh snapshot. Seven days of binlog on a busy server can be tens of gigabytes, so size the disk for it. - Tables need a primary key. Without one, inserts replicate, but updates and deletes cannot be matched to a row and are not applied. Add a primary key or a unique index first.
- These are Iceberg tables, not Delta tables. If a pipeline needs native Delta Lake features on these rows, load them into a Delta table downstream.
- Freshness is a schedule, down to one minute, not a continuous stream.
-
A null-path error points at the delete format.
Can not create a Path from a null string [ErrorCode: 2015]is what Unity Catalog returns when a client commits equality delete files. Set Databricks as a target query engine and start that table again from a full refresh.
How the MySQL to Databricks methods compare
| Method | Freshness | Updates and deletes | Where the data lands | What you run |
|---|---|---|---|---|
| Lakeflow Connect MySQL connector | CDC, run by Databricks | yes, through CDC | streaming tables governed by Unity Catalog | nothing to host; Databricks serverless compute |
| Debezium, Kafka and Structured Streaming | seconds | yes, with your own merge logic | Kafka topics, then tables written by a Databricks streaming job | Kafka, Kafka Connect, Debezium and a running Databricks job |
mysqldump or INTO OUTFILE, then COPY INTO or Auto Loader |
hours to daily | no; full reloads or your own merge | tables written by a Databricks job | an export script, a bucket and a Databricks job |
| OLake Go into Unity Catalog (this post) | CDC on a schedule down to one minute | yes, as deletion vectors | Iceberg tables that Databricks, DuckDB and Spark all read | one Docker Compose stack, open source; your storage and your own compute |
Lakeflow Connect is the simpler choice when Databricks is the only reader and you want Databricks to run the pipeline. The route in this post makes sense once a second engine needs the same rows, because the tables are written once and nobody makes a second copy.
FAQ
Does this work with MySQL on RDS, Aurora, Azure or Cloud SQL?
Yes. Hosted MySQL works the same way as a server you run yourself. The only difference is that the binlog settings go in a parameter group or a server setting, not in my.cnf.
What are the _cdc_binlog_file_name and _cdc_binlog_file_pos columns for?
They record where in the binlog each changed row came from. MySQL tables carry them on top of the four columns every OLake Go table has. Leave them out of dashboards, or use them when you want to see which binlog file a change was read from.
Should I change the Initial Wait Time on the source?
Usually not. The default is fine, and 10 seconds was enough for my two-table database. Raise it only if the binlog is large and a later sync times out while finding its saved position.
Who compacts the tables and expires old snapshots?
Unity Catalog does both on managed Iceberg tables when predictive optimization is on (Databricks: Iceberg tables). Check that it is enabled for the catalog you write to. Spark's rewrite_data_files also works through the same Unity Catalog endpoint.
Where to go from here
- Point the source at a real database, and size the binlog volume for the retention you chose.
- Before production, replace the personal access token with a service principal that uses OAuth machine-to-machine credentials. An expired token fails the next sync at its first catalog call.
- The Spark connection, the command line setup and the full type mapping are in the MySQL to Databricks guide on olake.io.
If something behaves differently on your workspace, drop the exact error text in the comments and I will take a look.






Top comments (0)