MySQL to BigQuery: How to Move Your Data
Move MySQL into BigQuery with Airbyte. Why RDS binlog retention defaults to zero and breaks CDC, the grants you need, and partition filtering to control cost.
Moving MySQL into BigQuery takes the database running your application and puts a copy somewhere analysts can work without touching production. MySQL is excellent at serving requests and poor at aggregating millions of rows while doing so.
Change data capture is the reason to use this connector rather than a nightly export, and it depends on a setting that is effectively disabled by default on managed MySQL. Getting that wrong means CDC works until the first pause and then stops permanently.
MySQL to BigQuery at a glance:
Why move data from MySQL to BigQuery?
Two situations account for most of these pipelines.
The first is protecting the application. Analytical queries against a transactional MySQL instance compete with the requests your users are making, and a read replica still ties reporting to a row-oriented engine that was never built for aggregation.
The second is joining operational data to everything already in BigQuery. Advertising, web analytics and product data frequently live there, and putting your application records beside them turns questions that spanned two systems into a single query.
What do you need before you start?
Most of this is work a database administrator does, and the first item is the one that fails later rather than immediately:
Binary logging enabled with a sensible retention period. CDC reads the binlog, so it must be on with row-based format. On Amazon RDS the retention parameter defaults to zero, and Airbyte recommends setting it to 168 hours. The MySQL source documentation covers the settings.
A dedicated user with three grants. SELECT on the tables you replicate, plus REPLICATION CLIENT and REPLICATION SLAVE, which are what allow the connector to read the binary log rather than only query tables.
An honest look at your storage engines. The initial snapshot can lock MyISAM tables, which on a production database is disruptive. InnoDB is unaffected, and knowing which you have decides whether the first sync needs a maintenance window.
A BigQuery service account and a decided dataset location. With the BigQuery User and BigQuery Data Editor roles. Queries can only reference datasets in the same physical location and that cannot be changed after creation, so match it to whatever you intend to join against.
If your organisation restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list on the database side before you begin.
How do you build a MySQL to BigQuery pipeline in Airbyte?
Step 1: Set binlog retention before anything else
Check the retention setting and raise it to seven days, then create the replication user with its three grants. Doing this first matters because the consequence of skipping it does not appear during setup, when everything works, but weeks later after the first interruption.
Step 2: Configure the MySQL source
Click Sources in the left navigation, then New Source, and select MySQL, following adding a source. Supply the host, port, database and credentials, then choose change data capture as the replication method rather than leaving it on the cursor-based default.
Step 3: Configure the BigQuery destination
Click Destinations, then New Destination, and select BigQuery, following adding a destination. Supply the project, dataset and service account key, then pick a loading method. Cloud Storage staging is worth considering for the initial snapshot of a production database.
Step 4: Choose sync modes and deduplicate
Click Connections, then New connection, select your tables and a sync mode each. CDC uses at least once delivery, so a change record can arrive more than once and a deduplicating mode keeps that out of your tables.
Deleted rows arrive carrying a deletion marker rather than vanishing, so append and deduped mirrors current MySQL state while plain append preserves every version.
Why does CDC break after a pause?
Because the connector resumes from a position in the binary log, and if MySQL has already purged the log containing that position, there is nothing to resume from. The only recovery is a fresh snapshot of the whole table, which on a production database is an unwelcome surprise.
On Amazon RDS this is close to inevitable unless you intervene, because the binlog retention hours parameter defaults to zero, which tells MySQL to purge logs as soon as it can. Everything works perfectly while syncs run continuously and fails the first time one does not.
Airbyte recommends 168 hours, which is seven days, and that is enough to survive a long weekend outage or a connector upgrade. Set it before the pipeline is relied upon rather than after, because the moment you need the retention is the moment it is too late to add it.
How do you keep query costs sensible?
By filtering on the partitioning column, because BigQuery bills for the bytes a query scans and a replicated production database accumulates indefinitely. Output tables are partitioned daily on the extraction timestamp and clustered on that column plus the primary keys.
That convention needs stating explicitly, because the people querying this data are used to MySQL, where scanning a large table cost time rather than money. The habit of narrowing a query for cost reasons does not transfer, and nobody acquires it by accident.
If you chose plain append to keep every version of every row, this matters more again, since the table holds the full history of changes rather than current state. Establish the convention while there is one query to change rather than a migrated set of reports.
Frequently asked questions
Why did CDC stop working after a quiet period?
The binary log containing the resume position was purged. On RDS the retention parameter defaults to zero, so set it to 168 hours before relying on the pipeline.
Which permissions does the replication user need?
SELECT on the tables you replicate, plus REPLICATION CLIENT and REPLICATION SLAVE, which allow reading the binary log rather than only querying tables.
Will the first sync affect my application?
Possibly, if you have MyISAM tables, since the initial snapshot can lock them. InnoDB is unaffected, so check your storage engines before scheduling.
How do I keep BigQuery costs down?
Filter on the extraction timestamp, which is the partitioning column. Tell analysts explicitly, since MySQL's cost model does not train that habit.
Can I do this without writing code?
The Airbyte setup is entirely UI-driven. The MySQL side needs a DBA to set parameters and grants, which is configuration rather than code.
Get your MySQL data into BigQuery
Raise binlog retention to seven days before anything else, because on RDS the default guarantees CDC breaks at the first interruption. Create the dataset in the right location since that is permanent, check your storage engines before the first snapshot, and teach analysts to filter on the partitioning column.
Airbyte's connector catalog includes 700+ pre-built connectors, so operational data can sit beside the commercial sources that explain it. For the same source in another warehouse, see MySQL to Snowflake, and for another relational source in BigQuery, Microsoft SQL Server to BigQuery.
Integrate with 700+ apps using Airbyte
Move data from 700+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.