How to Connect MySQL to BigQuery : 3 Methods for 2026

Migrating data from MySQL to BigQuery is a common requirement for teams that need faster analytics and reporting. MySQL is built for transactional workloads, but it does not perform well for large analytical queries. 

Moving your data into BigQuery helps you run queries at scale, work with bigger datasets, and reduce load on your MySQL database.

This guide explains how to set up MySQL to BigQuery integration using three easy methods.

• The first method uses an automated pipeline with almost no manual work. 
• The second method walks through a manual ETL process
• The third method covers Google Cloud’s native BQ DTS service for scheduled batch transfers.

Here is a quick comparison to help you choose the right method:

Category	Hevo Data (Automated Pipeline)	Manual ETL (Custom Scripts + GCS + Load Jobs)	Google Cloud Native (BQ DTS)
Best For	No-code, reliable MySQL to BigQuery integration	One-time or low-frequency transfers	Google Cloud users needing scheduled batch transfers
Setup Effort	Very Low	High	Moderate
Data Sync Frequency	Continuous or batch, as per need	Manual / Scheduled	Scheduled (Daily/Hourly/Custom)
Maintenance	Fully Managed	High	Moderate
Best Use Case	Teams needing simple, automated, and scalable pipelines without engineering effort	Engineering teams can manage extraction, transformation, and loading manually	Teams that prefer Google-native tools

What Data Can You Migrate to BigQuery from MySQL?

MySQL stores data in a relational, table-based format, and all of this structured data can be moved into BigQuery. Since both platforms use tables and SQL, most MySQL datasets transfer smoothly, including:

• Transactional tables
• Analytics tables
• Lookup and dimension tables
• Historical or archived datasets
• Any structured data stored in rows and columns

BigQuery supports almost all MySQL data types, although some require conversion. Fields like integers, strings, timestamps, and numeric types map directly, while types such as ENUM or SET must be transformed into compatible formats.

Once migrated, the data is stored as managed BigQuery tables, secured inside Google’s warehouse, and ready for advanced analytics, BI, or machine learning workloads.

How to Connect MySQL to BigQuery: 3 Different Methods

There are multiple ways to move data from MySQL into Google BigQuery, depending on how frequently your data changes, your team’s technical skills, and how much maintenance you want to handle. Choosing the right method depends on balancing setup effort, reliability, and long-term scalability.

Let us look at three practical approaches:

Method 1: Automated Pipeline (Hevo Data)

Hevo Data offers a fully managed, no-code ELT platform that moves data from MySQL to BigQuery in just a few clicks. It removes the hassles of traditional tools, such as writing complex Python scripts and managing multiple ETL tools. You simply configure your source and destination once, and Hevo handles extraction, loading, retries, and monitoring automatically.

Method 2: Manual ETL Scripts

A hands-on process that involves writing custom extraction and transformation scripts, uploading data to Google Cloud Storage, and loading it into BigQuery. This is suitable for one-time migrations or infrequent updates where automation is not required.

Method 3: Google Cloud Native (BQ DTS)

A built-in Google Cloud option that schedules batch transfers from MySQL to BigQuery using BigQuery Data Transfer Service.

Let’s explore each method in detail so you can choose the approach that aligns best with your data workflow and migration needs.

Method 1: Using Automated No-Code Pipeline (Hevo Data) 

Hevo provides a fully managed, no-code pipeline that moves data from MySQL to BigQuery in just a few clicks. There is no scripting, no infrastructure to manage, and no need to worry about schema changes or pipeline failures. You simply configure your source and destination once, and Hevo handles extraction, loading, retries, and monitoring automatically.

Step 1: Connect and Configure Your MySQL Database

• Click Pipelines in the Navigation Bar
• Click + Create
• Select MySQL as the source
• Enter your connection settings (host, port, database, credentials)

What you need: You will need your MySQL Host IP, Port (usually 3306), Database Name, and User Credentials. You must also ensure that Binary Logging is enabled if you wish to capture real-time changes (CDC).
How to set up: Hevo will test the connection to ensure it can reach your database. Once verified, you can choose between Table or Custom Query loading modes to define exactly what data is pulled.

Step 2: Choose BigQuery as the Destination

• Click Destinations
• Click + Create
• Select Google BigQuery
• Enter your BigQuery project, dataset, and authentication details

What you need: You will need your BigQuery Project ID and a Service Account Key (JSON file) with the necessary permissions to write data. You will also have to specify a Dataset where your MySQL tables will reside.
How to ensure post-setup visibility: Once the pipeline is live, you can monitor the Activity Feed to see records moving in real-time. The Schema Mapper will show you the automated mapping between MySQL data types and BigQuery’s column structures, giving you full control over how data is represented.

It is that simple. While you relax, Hevo will fetch the data and send it to your destination Warehouse.

“Hevo Data makes setting up and maintaining data pipelines extremely simple. The no-code interface, wide range of connectors, and automated schema mapping reduce the effort of integrating multiple data sources into a central warehouse. Its real-time replication capability ensures that analytics teams always have fresh data available without complex engineering setups.”

– Ravi Shankar S.

  Full stack developer

Key advantages

• Fast, no-code setup: Connect MySQL and BigQuery in minutes without building or maintaining scripts, servers, or complex cron jobs.
• Automatic error handling & schema management: Built-in retries, auto-healing, and smooth handling of schema changes (like new columns in MySQL) prevent data gaps or broken pipelines.
• Fully managed and low maintenance: Hevo handles infrastructure, scaling, and security updates, so your team doesn’t spend engineering hours on ETL maintenance.
• Real-Time CDC (Change Data Capture): Hevo tracks changes in your MySQL binary logs, so that your BigQuery warehouse is updated instantly whenever a record is created, updated, or deleted.
• Transparent data lineage: With detailed monitoring dashboards, you get complete visibility into pipeline health, latency, and data volume, so it’s super easy to track your data’s journey.

Limitations

Hevo is a powerful set-and-forget solution. However, as a user, you must know that the throughput of the source MySQL server can limit the initial sync of extremely large databases (multi-terabyte scale). Additionally, you also have to check that your firewall or VPC settings are configured to whitelist Hevo’s IP addresses to allow the connection. 

Method 2: Manual ETL Process to Connect MySQL to BigQuery

If you’re going with a manual approach to MySQL–BigQuery integration, you’ll need to use a traditional method using custom-built Extract, Transform, and Load (ETL) scripts. Engineering teams favour this since it gives them granular control over every byte of data being moved. It’s also ideal for those working within highly specialized environments where off-the-shelf connectors might not support specific legacy data formats.

This method is most effective for one-time data migrations or infrequent batch uploads where the overhead of a managed service isn’t yet justified. However, it requires a deep understanding of both MySQL’s export capabilities and BigQuery’s ingestion APIs to ensure data integrity is maintained throughout the journey.

Step-by-step

There are two ways to implement manual ETL:

1. Full Dump and Load
2. Incremental Dump and Load

Below is a clear walkthrough of how each approach works.

1. Full Dump and Load

A full dump and load extracts the entire MySQL table and replaces (or overwrites) the corresponding table in BigQuery. This is usually done during initial migrations or for smaller tables that can be fully reloaded without performance concerns.

The high-level steps to be followed to replicate MySQL to BigQuery are:

• Step 1: Extract Data from MySQL

What you need: You require SSH or direct access to the MySQL server, a user with SELECT and FILE privileges, and enough local disk space to hold the exported files.
How to execute: Usually, developers use the mysqldump utility or a SELECT … INTO OUTFILE command. At this stage, you must also manually handle data cleaning to ensure date formats and special characters are compatible with BigQuery’s requirements.

• Step 2: Clean and Transform the Data
• Step 3: Upload to Google Cloud Storage(GCS)
• Step 4: Upload to the BigQuery Table from GCS

What you need: Access to the Google Cloud CLI, a GCP Service Account with BigQuery Data Editor roles, and a predefined schema in BigQuery that matches your export file.
How to ensure post-setup visibility: After the load command is executed, you must manually check the BigQuery Job History to see if the upload succeeded. Unlike automated tools, you don’t have a visual dashboard to monitor row counts or latency; you can confirm success via manual SQL counts or custom logging scripts.

Step 1: Extract Data from MySQL

You can extract data in several ways:

Option 1: Using SELECT … INTO OUTFILE

Exports table data directly into a CSV file.

SELECT *

INTO OUTFILE 'filename.csv'

CHARACTER SET 'utf8'

FIELDS TERMINATED BY '\t'

FROM your_table;

Option 2: Using mysqldump

Creates a logical backup of a table or database.

mysqldump -u <username> -h <host> -p db_name table_name > table_name.sql

You must convert the SQL output into CSV using a script or a conversion tool.

Option 3: Using SQL Query + CLI Utilities

Run a SQL query and redirect the output to a text file, then clean it with tools like sed or awk.

mysql -B -u user -h mysql_host -e "select * from table_name;" > table_name.txt

Step 2: Clean and Transform the Data

Before loading into BigQuery, ensure:

• Data is UTF-8 encoded
• Column types match BigQuery equivalents
• Dates follow the YYYY-MM-DD format
• Timestamps use colon-separated time
• Text fields are properly quoted
• Unsupported types (ENUM, SET) are mapped to compatible formats

MySQL Data Type	BigQuery Data Type
TINYINT	INT64
SMALLINT	INT64
MEDIUMINT	INT64
INT	INT64
BIGINT	INT64
DECIMAL	NUMERIC
FLOAT	FLOAT64
DOUBLE	FLOAT64
BIT	BOOL
CHAR	STRING
VARCHAR	STRING
BINARY	BYTES
VARBINARY	BYTES
TINYTEXT	STRING
TEXT	STRING
MEDIUMTEXT	STRING
LONGTEXT	STRING
ENUM	No type for ENUM.Must use any type which can represent values in ENUM
SET	No type for SET.Must use any type which can represent values in SET
DATE	DATE
TIME	TIME
DATETIME	DATETIME
TIMESTAMP	TIMESTAMP

Step 3: Upload Files to Google Cloud Storage (GCS)

You can upload files using:

Option 1: Upload Files Using gsutil (Command Line Tool)

gsutil is part of the Google Cloud SDK and is the most efficient method for CLI-based uploads.

• Upload a single file: gsutil cp table_data.csv gs://your-bucket/path/
• Upload an entire directory: gsutil cp -r /local/folder/ gs://your-bucket/path/
• Upload from Amazon S3 to GCS directly: gsutil cp -r s3://source-bucket/path gs://your-gcs-bucket/path

Option 2: Upload Using Storage Transfer Service (Automated Transfers)

The Storage Transfer Service is useful when:

• Your MySQL exports are stored in S3 or another cloud provider
• You want scheduled automatic transfers
• You need periodic syncing between storage systems

Option 3: Upload Using the Google Cloud Console (Web UI)

If your files are stored locally on your machine and you prefer a visual interface, the GCP Console provides a simple alternative the command line.

Steps:

1. In the GCP Console, go to Storage → Browser

2. Select your bucket

3. Click Upload Files

4. Choose your CSV or other extracted files

5. Wait for the upload to complete

This method is best for ad-hoc or one-time file uploads that do not require automation.

Step 4: Load Data from GCS into BigQuery

Option 1: Load Data Using the bq Command-Line Tool (Most Common Method)

The bq load command gives you full control over schema, file formats, and load behavior.

General syntax:

bq --location=[LOCATION] load \

--source_format=CSV \

[DATASET].[TABLE] \

gs://bucket/path/to/file.csv \

schema.json

Option 2: Load Using the BigQuery Web Console (UI Method)

The BigQuery Console provides an intuitive, step-by-step interface for loading data manually.

Steps:

1. Go to BigQuery in the Google Cloud Console
2. Select your project and dataset
3. Click Create Table
4. Choose Google Cloud Storage as the source
5. Paste the GCS file path (gs://bucket/file.csv)
6. Configure format (CSV)
7. Choose schema detection or upload a schema file
8. Select write mode:
   • Write if empty
   • Append
   • Overwrite
9. Click Create Table

Option 3: Load Data programmatically using BigQuery API

BigQuery provides REST APIs and client libraries (Python, Java, Go, Node.js) for automated loads, especially in production pipelines or CI/CD systems.

A simplified Python example:

from google.cloud import bigquery

client = bigquery.Client()

table_id = "project.dataset.table"

job_config = bigquery.LoadJobConfig(

    source_format=bigquery.SourceFormat.CSV,

    autodetect=True,

)

uri = "gs://my-bucket/orders.csv"

load_job = client.load_table_from_uri(uri, table_id, job_config=job_config)

load_job.result()

2. Incremental Dump and Load

When you need to refresh BigQuery with only new or updated rows, an incremental load is more efficient than reloading entire tables.

Step 1: Extract Incremental Records

Use a timestamp column (updated_at, last_modified, etc.) to pull only changed rows.

mysql -B -u user -h mysql_host \

-e "select * from table_name where updated_at > '#last_run_ts#';" \

> table_name_delta.csv

Step 2: Merge Data in BigQuery

Load the extracted data into a staging table, then update the main table.

Option 1: Update existing rows and insert new ones

UPDATE dataset.final_table t

SET t.value = s.value

FROM dataset.staging_table s

WHERE t.id = s.id;

INSERT INTO dataset.final_table (id, value)

SELECT id, value

FROM dataset.staging_table

WHERE id NOT IN (SELECT id FROM dataset.final_table);

Option 2: Delete and reload

DELETE FROM dataset.final_table

WHERE id IN (SELECT id FROM dataset.staging_table);

INSERT INTO dataset.final_table

SELECT * FROM dataset.staging_table;

Key advantages

• Granular control: You have total authority over the extraction logic to filter rows or transform data types exactly how you want before they leave the source.
• No additional licensing costs: You use open-source tools and custom code, avoiding third-party subscription fees, making it a cost-effective choice for small, one-off tasks.
• Flexible for specialized migrations: This method is highly adaptable for unique environments where you might need to combine data from multiple files or unconventional sources into a single export.
• No vendor lock-in: Since the scripts are written in-house (typically in Python or Bash), the team maintains full ownership of the intellectual property and logic behind the data movement.
• Direct interaction with APIs: Developers gain a deep understanding of the BigQuery API, which can be useful for building other custom internal tools or integrations.

Limitations

The manual method can be fragile. Any change to the MySQL schema, such as adding a column or changing a data type, will immediately break the script and lead to data gaps. Additionally, building strong error-handling, retries, and real-time monitoring from scratch can be time-consuming.

Method 3: Google Cloud Native – Using BigQuery Data Transfer Service (BQ DTS)

Overview

The BigQuery Data Transfer Service (BQ DTS) is Google Cloud’s native, fully managed solution for automating data movement from various sources, including MySQL, directly into BigQuery. If your organization is already part of the Google Cloud ecosystem, DTS provides a high-trust environment where data never leaves the GCP backbone, ensuring compliance and simplified security management.

For teams that prioritize stability and integrated monitoring over extreme transformation flexibility, it’s a helpful approach. The Google-native service scales with your BigQuery infrastructure and uses Cloud Storage as a managed staging area. It also schedules recurring transfers, making it a trusted choice for keeping analytical dashboards updated with the latest transactional data from your MySQL instances.

Below is the step-by-step procedure to set up MySQL to BigQuery using BQ DTS.

Step 1: Prepare Your MySQL Source

BQ DTS supports:

• Cloud SQL for MySQL
• On-premises MySQL
• MySQL hosted on AWS, Azure, or other cloud providers

Before enabling transfers, ensure:

• Binary logging is enabled (binlog_format = ROW)
• The MySQL user has the required read permissions
• Network access is configured (public IP, VPC peering, or Cloud VPN/Interconnect)
  

What you need: Enable Binary Logging (binlog_format = ROW) on your MySQL server and create a dedicated user with SELECT, RELOAD, and SHOW DATABASES permissions. On the GCP side, you need to enable the BigQuery Data Transfer API and the Cloud Storage API.
How to set up: You will need to configure your firewall or Cloud VPN to allow Google’s internal service accounts to ping your database.

Step 2: Enable Required APIs in Google Cloud

In the Google Cloud Console, enable the following services:

• BigQuery Data Transfer API
• Cloud Storage API (used for staging intermediate files)
• BigQuery API

This ensures DTS can extract and stage data from MySQL before loading it into BigQuery.

What you need: You will input your MySQL connection string (Host, Port), database credentials, and the ID of the target BigQuery dataset. You must also specify the GCS bucket that will act as the temporary staging area.
How to ensure post-setup visibility: Once the transfer is saved, it appears in the Transfers Dashboard. Here, you can monitor the Run History to see a timeline of past executions. If a transfer fails, DTS provides detailed logs within the console, showing exactly which step encountered an error.

Step 3: Create a BigQuery Dataset

BQ DTS requires a target dataset where transferred tables will be created.

1. Go to BigQuery Console
2. Click + Create Dataset
3. Select:
   • Location (must match GCS bucket region)
   • Default table expiration (optional)
4. Click Create

Step 4: Configure the Transfer

1. In the BigQuery Console, navigate to Transfers
2. Click + Create Transfer
3. Choose MySQL as the data source
4. Configure:
   • Connection details (host, port, username, SSL options)
   • Authentication method (password or Cloud SQL connection)
   • Tables to include or exclude
   • Data refresh window (how many previous days to reload)

Step 5: Choose the Transfer Schedule

BQ DTS supports:

• Daily, Hourly, or Custom scheduling
• On-demand manual runs

Set your preferred schedule based on how frequently MySQL data changes.

Step 6: Establish Connectivity

Depending on your environment:

• Cloud SQL MySQL: Direct connection
• External MySQL (AWS/Azure/on-prem):
  • Use a public IP with allowedlist
  • OR configure private networking via Cloud VPN / Interconnect

BQ DTS validates the credentials and network accessibility before creating the transfer.

Step 7: Start the Initial Load

Once you save the configuration, DTS automatically:

1. Extracts data from MySQL
2. Stages it in GCS (managed internally)
3. Loads it into BigQuery as new tables

Tables are created with schema mappings based on column definitions in MySQL.

Step 8: Monitor and Manage Transfers

BQ DTS provides built-in monitoring tools:

• Transfer history
• Failed vs successful runs
• Execution logs
• Alerts for network or schema issues

You can view logs in BigQuery, Cloud Logging, or set up email alerts.

Key advantages

• Fully managed by Google Cloud: Since it’s a native service, you don’t need to manage external servers or third-party platforms; all billing and support are consolidated within your existing GCP account.
• Smooth integration with GCP security: DTS uses Google Identity and Access Management (IAM) so that you can control who can create or view transfers using the same roles you use for the rest of your cloud infrastructure.
• Built-in scheduling and monitoring: You can easily set transfers to run hourly, daily, or on a custom cron schedule, with integrated alerting via Cloud Pub/Sub or email for failed jobs.
• Automatic schema discovery: DTS automatically maps MySQL data types to BigQuery’s column structures, reducing your manual effort to recreate table schemas in the warehouse.
• Serverless execution: You don’t have to worry about managing compute power for the ETL process; Google handles the scaling and resource allocation behind the scenes.

Limitations

The BigQuery Data Transfer Service is usually designed for batch processing, meaning not suitable for use cases needing sub-minute real-time synchronization. Additionally, you get very limited transformation capabilities; if your data needs cleaning or reformatting before it reaches BigQuery, you will need to pair DTS with a tool like Dataform or write post-load SQL scripts.

What are the Advantages of Connecting MySQL To BigQuery?

Syncing MySQL data into BigQuery helps teams keep operational workloads in MySQL while unlocking fast, scalable analytics in Google Cloud. This setup ensures smoother reporting, better performance, and stronger data governance.

1. Faster Analytics on Large Datasets

BigQuery is built for high-speed analytical queries. It handles large datasets far more efficiently than MySQL, without affecting production performance.

2. Consistent, Unified Source of Truth

Sending MySQL data to BigQuery keeps dashboards, reports, and models fully aligned. Analysts avoid manual exports and always work with up-to-date, consistent data.

3. Better Security and Access Control

Teams can analyze data in BigQuery without exposing or overloading the operational MySQL database, reducing risk and improving governance.

4. Optimized OLAP Capabilities

MySQL is designed for transactions, while BigQuery is designed for analytics. BigQuery’s serverless, columnar, and distributed architecture enables deeper insights and complex queries at scale.

Conclusion

Connecting MySQL to BigQuery unlocks faster analytics, reduces load on your transactional systems, and creates a single source of truth for your teams. While manual ETL and BQ DTS can work for specific use cases, they often require engineering time, scripting, and ongoing monitoring.

Hevo removes all of this complexity. Its automated MySQL to BigQuery pipeline handles extraction, schema changes, retries, and monitoring with zero maintenance. For teams that want a dependable, scalable, and transparent solution, Hevo is the most practical choice. 

Try it free and experience the difference.

FAQ MySQL to BigQuery