How to Use MySQL Database with ETL Processes

The first ETL pipeline I built against MySQL taught me a lesson I now repeat to every junior engineer I mentor: extracting data isn’t the hard part — extracting data without wrecking the performance of the production database it’s coming from is. In this article, I’m covering how I use MySQL as both a source and a destination in ETL (Extract, Transform, Load) processes, the techniques I use to extract efficiently, common transformation patterns, loading strategies, and the operational details that separate a pipeline that works in a demo from one that survives production for years.

Where MySQL Fits in an ETL Pipeline

MySQL typically shows up in ETL work in one of three roles:

1. Source system — the OLTP database powering an application, from which data is extracted for analytics.
2. Staging area — an intermediate database holding raw or lightly transformed data before it’s loaded into a warehouse.
3. Target/destination — less common for large-scale analytics (columnar warehouses like Snowflake, BigQuery, or Redshift usually win there), but very common for smaller reporting databases or operational data stores.

flowchart LR
    subgraph Sources
    A[(MySQL - OLTP App DB)]
    B[(Other APIs/Files)]
    end
    A -->|Extract| C[Staging Layer]
    B -->|Extract| C
    C -->|Transform| D[Transformation Engine]
    D -->|Load| E[(Data Warehouse)]
    D -->|Load| F[(MySQL Reporting DB)]

Extraction Strategies From MySQL

The extraction method I choose depends heavily on data volume and how fresh the data needs to be.

Full Extraction

Simplest approach — extract the entire table every run. I only use this for small, slowly-changing reference tables.

SELECT * FROM product_categories;

Incremental Extraction Using Timestamps

For most operational tables, I extract only rows changed since the last run, using an updated_at column:

SELECT *
FROM orders
WHERE updated_at > :last_extraction_timestamp
ORDER BY updated_at ASC;

This requires every table have a reliable updated_at (and ideally created_at) column maintained automatically:

ALTER TABLE orders
  MODIFY updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

The weakness here: this approach can’t detect hard deletes (a row physically removed leaves no trace to extract). For that, I either use soft deletes (is_deleted flag) or move to CDC.

Change Data Capture (CDC) Using the Binary Log

For low-latency, delete-aware extraction, I use CDC by reading MySQL’s binary log directly rather than polling with SQL queries. Tools like Debezium (built on Kafka Connect) do this by acting like a replica — they connect to MySQL, read the binlog stream, and turn every INSERT/UPDATE/DELETE into a structured event.

sequenceDiagram
    participant App as Application
    participant MySQL as MySQL Source DB
    participant BinLog as Binary Log
    participant Debezium as Debezium Connector
    participant Kafka as Kafka Topic
    participant Consumer as ETL Consumer

    App->>MySQL: INSERT/UPDATE/DELETE
    MySQL->>BinLog: Write change event
    Debezium->>BinLog: Stream events (acts like a replica)
    Debezium->>Kafka: Publish structured change event
    Kafka->>Consumer: Consume and transform

I favor CDC for any pipeline where near-real-time freshness matters, or where I need reliable delete detection, since it reads the actual replication stream rather than repeatedly polling and comparing snapshots.

To enable it, MySQL just needs standard replication prerequisites:

[mysqld]
server-id=100
log_bin=mysql-bin
binlog_format=ROW
binlog_row_image=FULL

And a dedicated CDC user with replication privileges:

CREATE USER 'debezium_user'@'%' IDENTIFIED BY 'CdcP@ss1';
GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT
  ON *.* TO 'debezium_user'@'%';

Extracting Without Hurting Production

Whichever method I use, I always protect the source system:

• I extract from a read replica, never the primary, for anything beyond light queries.
• I use --single-transaction equivalent isolation (consistent snapshot reads) so extraction doesn’t hold locks.
• I chunk large full extracts using the primary key range rather than one giant query:

SELECT * FROM orders WHERE order_id BETWEEN 1 AND 100000;
SELECT * FROM orders WHERE order_id BETWEEN 100001 AND 200000;
-- ...continues in batches

This keeps memory usage bounded on both the extraction tool and MySQL itself, and avoids one enormous long-running transaction that could hold back purge operations on a busy InnoDB table.

Transformation Patterns

Transformation logic sometimes happens in the ETL tool (Python/Spark/dbt), and sometimes I push parts of it into MySQL itself when it’s more efficient there. Common patterns I use directly in SQL during extraction:

-- Deriving a clean, denormalized reporting row directly in the extract query
SELECT
    o.order_id,
    o.created_at,
    DATE(o.created_at) AS order_date,
    c.customer_id,
    c.email,
    COALESCE(o.total_amount, 0) AS total_amount,
    CASE
        WHEN o.order_status = 'cancelled' THEN 0
        ELSE o.total_amount
    END AS revenue_recognized
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.updated_at > :last_extraction_timestamp;

I lean on pushing simple, set-based transformations (filtering, deriving flags, basic joins) into the SQL extract query itself, since MySQL’s optimizer handles this far more efficiently than row-by-row processing in an external tool. I reserve the ETL tool for genuinely complex logic — multi-source joins, machine learning feature engineering, business rules that don’t map cleanly to SQL.

Loading Strategies Into MySQL

When MySQL is the target (a reporting DB, for instance), load performance matters just as much as extraction did on the source side.

Bulk Loading with LOAD DATA INFILE

For large batch loads, this is dramatically faster than row-by-row INSERT statements:

LOAD DATA INFILE '/tmp/transformed_orders.csv'
INTO TABLE reporting_db.orders_fact
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(order_id, order_date, customer_id, revenue_recognized);

I’ve seen this be an order of magnitude faster than equivalent individual INSERT statements for large files.

Batched Multi-Row Inserts

When data comes from a pipeline rather than a flat file, I batch inserts instead of executing one statement per row:

INSERT INTO orders_fact (order_id, order_date, customer_id, revenue_recognized)
VALUES
  (1001, '2026-07-30', 55, 129.99),
  (1002, '2026-07-30', 78, 45.00),
  (1003, '2026-07-30', 12, 302.50);
  -- batched in groups of a few hundred to a few thousand rows

Upsert Pattern for Incremental Loads

For incremental ETL runs where a row might already exist, I use ON DUPLICATE KEY UPDATE rather than delete-and-reinsert:

INSERT INTO orders_fact (order_id, order_date, customer_id, revenue_recognized)
VALUES (1001, '2026-07-30', 55, 129.99)
ON DUPLICATE KEY UPDATE
    revenue_recognized = VALUES(revenue_recognized),
    order_date = VALUES(order_date);

This requires a unique key (usually the natural business key) on the target table to work correctly.

Load Performance Tuning

For large bulk loads, I temporarily adjust session settings to speed things up:

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

-- ... perform bulk load ...

COMMIT;
SET unique_checks=1;
SET foreign_key_checks=1;
SET autocommit=1;

I disable unique_checks and foreign_key_checks only during controlled, trusted bulk loads where I already know the data is clean — never as a blanket default, since it removes real safety nets.

Orchestrating the Pipeline

I use orchestration tools (Apache Airflow, Dagster, or cloud-native equivalents like AWS Step Functions) to schedule, sequence, and monitor ETL jobs rather than relying on raw cron scripts once a pipeline has more than a couple of steps.

A simplified example of what an Airflow DAG structure looks like for a MySQL-sourced pipeline:

flowchart LR
    A[Extract from MySQL Replica] --> B[Validate Row Counts]
    B --> C[Transform - Clean & Derive Fields]
    C --> D[Load to Staging Table]
    D --> E[Data Quality Checks]
    E --> F[Swap/Merge into Production Reporting Table]
    F --> G[Notify on Success/Failure]

I always include the validation and data quality steps as first-class pipeline stages, not afterthoughts — a pipeline that “succeeds” while silently loading corrupted or incomplete data is worse than one that fails loudly.

Handling Schema Drift

Source schemas change — someone adds a column, renames one, changes a type. I handle this by:

• Using CDC tools (Debezium) that propagate schema change events, so downstream consumers know about changes as they happen rather than breaking silently.
• Validating expected schema at the start of each extraction run and failing fast with a clear error if it doesn’t match.
• Versioning transformation logic alongside schema expectations, so a schema change triggers a deliberate pipeline update rather than a silent data quality issue.

Data Quality Checks I Always Include

-- Row count sanity check between source and staging
SELECT COUNT(*) FROM orders WHERE updated_at > :last_run;
-- compare against staging load count

-- Null/completeness checks on required fields
SELECT COUNT(*) FROM orders_fact WHERE customer_id IS NULL;

-- Referential integrity check post-load
SELECT COUNT(*) FROM orders_fact f
LEFT JOIN customers_dim d ON f.customer_id = d.customer_id
WHERE d.customer_id IS NULL;

If any of these checks fail, I fail the pipeline run rather than loading partial or inconsistent data — a stale-but-correct report is far less damaging than a fresh-but-wrong one.

Security Considerations

• I extract from a read replica with a dedicated, tightly-scoped ETL user (SELECT only, on specific schemas) — never a full-access account.
• I mask or exclude PII/sensitive columns during extraction when the destination doesn’t need or shouldn’t hold them (e.g., a marketing analytics warehouse rarely needs raw payment details).
• I encrypt data in transit between MySQL and the ETL tool (REQUIRE SSL on the ETL user).
• I ensure staging tables holding raw extracted data have the same access controls as the source, since they can contain equally sensitive data.

CREATE USER 'etl_reader'@'%' IDENTIFIED BY 'EtlP@ss1' REQUIRE SSL;
GRANT SELECT ON ecommerce_db.* TO 'etl_reader'@'%';

Troubleshooting Common ETL Issues With MySQL

Best Practices I Follow

• Always extract from a replica, never the primary, for anything beyond trivial queries.
• Use CDC (Debezium/binlog-based) when near-real-time freshness or delete-detection matters; timestamp polling otherwise.
• Push simple set-based transformations into SQL; reserve external tooling for genuinely complex logic.
• Batch loads and use LOAD DATA INFILE for large volumes rather than row-by-row inserts.
• Make idempotency a first-class design goal so failed/retried runs don’t produce duplicates.
• Treat data quality checks as pipeline gates, not optional extras.
• Scope ETL database credentials tightly and encrypt data in transit.

Interview Questions

1. What’s the difference between timestamp-based incremental extraction and CDC, and when would you choose each?
2. How does Debezium capture changes from MySQL without impacting application performance?
3. Why should ETL extraction generally target a read replica rather than the primary?
4. How would you design an idempotent load step for a pipeline that might be retried after a partial failure?
5. What are the tradeoffs of pushing transformation logic into SQL versus an external processing framework?
6. How do you handle schema drift in a source MySQL table without breaking downstream consumers?
7. Why is LOAD DATA INFILE typically much faster than row-by-row INSERT statements?

FAQs

Should transformations happen in MySQL or in the ETL tool? I use a hybrid approach: simple, set-based logic (filtering, basic derivations, joins) in SQL during extraction where MySQL’s optimizer handles it efficiently, and more complex logic (multi-source enrichment, business rules that don’t map to SQL well) in the ETL/transformation layer.

Is Debezium overkill for a small application? For a small application with modest data volumes and where near-daily freshness is fine, timestamp-based polling is simpler to operate and often sufficient. I reach for CDC once near-real-time freshness, reliable delete detection, or high extraction frequency become real requirements.

How do I avoid ETL jobs impacting my production application’s performance? Extract from a read replica, index the columns your extraction queries filter on, chunk large extracts into bounded batches, and schedule heavy full-extraction jobs during off-peak windows where possible.

What happens if an ETL job fails halfway through a load? This is exactly why I design loads to be idempotent — using ON DUPLICATE KEY UPDATE for incremental loads, or loading into a staging table and atomically swapping it into place only after the full load succeeds and passes quality checks.

Summary and Key Takeaways

Using MySQL well in ETL processes comes down to protecting the source system during extraction, choosing the right extraction method for your freshness and completeness needs, pushing transformations to where they run most efficiently, and loading data in a way that’s fast and safely repeatable. The pipelines that hold up over years aren’t the ones with the cleverest transformation logic — they’re the ones with disciplined extraction practices, idempotent loads, and real data quality gates.

Key takeaways:

• Extract from replicas, not the primary, and always in bounded, indexed batches.
• Use CDC via the binary log (Debezium) when you need real-time freshness or delete detection; timestamp polling otherwise.
• Use LOAD DATA INFILE or batched inserts, never row-by-row inserts, for bulk loads.
• Design every load step to be idempotent so retries never produce duplicates or corruption.
• Treat data quality validation as a mandatory pipeline gate, not an optional nice-to-have.

References

• MySQL 8.0 Reference Manual — The Binary Log: https://dev.mysql.com/doc/refman/8.0/en/binary-log.html
• MySQL 8.0 Reference Manual — LOAD DATA Statement: https://dev.mysql.com/doc/refman/8.0/en/load-data.html
• MySQL 8.0 Reference Manual — INSERT … ON DUPLICATE KEY UPDATE: https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
• Debezium Documentation — MySQL Connector: https://debezium.io/documentation/reference/stable/connectors/mysql.html
• MySQL 8.0 Reference Manual — Optimizing INSERT Statements: https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html