MySQL Change Data Capture (CDC): Complete Guide

MySQL Change Data Capture (CDC) is a method used to capture and track real-time data changes in a MySQL database and synchronize them with target systems, ensuring accurate and up-to-date data. This guide provides an in-depth look at MySQL CDC methods and benefits for business leaders, data engineers, and IT professionals.

Who is this guide for?

This resource is designed to support a wide range of data-focused and data-adjacent professionals who meet two simple requirements:

• You're part of an organization that uses the relational database MySQL, or intends to migrate to it.
• You're aware of the industry interest in change data capture (CDC) and want to learn more.

Business leaders: you need a no-nonsense foundation to make decisions. We cover the essentials of MySQL and CDC, plus the pros and cons of different approaches.

Engineers and IT professionals: you know the systems you work with intimately, but figuring out the best changes to make can be time-consuming. Here you'll find a succinct intro to a range of MySQL CDC methods.

Implementing a new data architecture or strategy requires understanding and buy-in from teams that may not work together day-to-day. A common starting point makes that collaboration easier. That's what this guide is for.

Why Should You Use MySQL CDC?

MySQL is a popular open-source relational database. Though it's free to use, MySQL has been owned and sponsored by a corporation since its inception: first by the Swedish company MySQL AB, then by Sun Microsystems, and now by Oracle Corporation.

There are several other widely used relational databases, including PostgreSQL, Oracle, SQL Server, and Db2.

Here are a few reasons teams choose MySQL:

• Performance: MySQL was designed for fast performance and supports multiple storage engines.
• Security: MySQL has a mature set of security features, which suits sensitive data environments.
• ACID compliance: Transactions are reliable and fault-tolerant, which protects data integrity.
• Open-source with commercial backing: MySQL is open source and also has official support from Oracle.
• Straightforward setup: Compared with some other relational databases, MySQL is simpler to stand up, which makes it attractive to organizations of all sizes.

MySQL is commonly used as the primary transactional database in a data stack. It acts as the fast, robust system of record that powers applications and tracks rapidly changing information.

Consider one very common application: e-commerce. An online store needs a transactional database to track customers and inventory and to process sales. MySQL is a good fit because it's a fast, secure, row-based relational database.

Just as an online store is rarely the only application a retailer builds, MySQL rarely stands alone as a data system. Relational databases aren't ideal for every workflow, most notably analytics. MySQL is great at the transactions behind sales and record-keeping, but it's a poor fit for large-scale analysis. To understand store performance, marketing, and inventory, a retailer needs a system built for analysis. Data warehouses are designed for exactly that, and they're ill-suited to transactional workflows, which makes them complementary to systems like MySQL.

Moving data from MySQL to a warehouse, and keeping it current, is the job CDC does.

To learn more about different types of data storage, see database vs. data warehouse vs. data lake.

What is Change Data Capture (CDC) for MySQL?

MySQL change data capture (CDC) is the process of capturing data changes in a MySQL database and making them available to other systems, such as data warehouses or analytics platforms.

CDC solves the problem of synchronizing data between MySQL and other systems in a way that is:

• Timely, in a world that expects fresh data within minutes or seconds.
• Reliable in the face of inevitable errors and downtime.
• Low-impact on performance, even as your data grows to terabyte scale.

Done correctly, change data capture can meet all three requirements

Change data capture, or CDC, is the process of recognizing a change in a source data system so that a downstream system can act on that change, usually by updating a target system to reflect the new information.

MySQL is one of the most commonly requested sources for CDC, and it's well suited to it.

• For database administrators, simpler CDC methods are easy to set up using SQL alone.
• Advanced, real-time CDC is also attainable using MySQL's binary log, or binlog.

In the next section, we’ll cover these methods in more depth.

What Are the Methods for MySQL Change Data Capture (CDC)?

When people talk about change data capture today, they usually mean real-time CDC, where changes from MySQL reach the target within seconds or milliseconds. Real-time CDC is event-driven and scales well, though it has historically been harder to build.

CDC has no timing requirement by definition, though. Different methods exist, and they range from batch or real-time. Your choice of method and MySQL connector has a large effect on the latency, reliability, and source-database impact of your pipeline.

The three approaches below span that range:

• CDC using queries (polling based).
• CDC using triggers (captures changes as they happen, inside MySQL).
• CDC using the binlog (real-time, log based).

Queries for MySQL Change Data Capture

SQL queries are a simple method for batch CDC in MySQL. You can run them from the command line or a client like MySQL Workbench.

For this method to work, the source table needs a version number or timestamp column that indicates when each row was last updated. If it doesn't have one, the trigger method below is usually a better fit.

Your query will look something like this:

plaintext
SELECT *

FROM my_table

WHERE time_updated > :time_of_last_query;

You bind :time_of_last_query to the timestamp of your previous run, then run the query on a recurring interval using MySQL's built-in Event Scheduler.

The selected rows still have to reach the target system, which is where most of the complexity lives. That usually means either a bespoke ETL pipeline or an event-driven framework such as Kafka Connect.

Triggers for MySQL Change Data Capture

An alternative method uses MySQL triggers: functions that fire on insert, update, and delete events for a given table.

You can write those events to a second table, or use them to maintain the timestamps that the query method relies on. Either way, you now have a log of the changes, but it's still inside MySQL. As with the query method, you then have to move those changes to the destination system, which means building or buying a pipeline.

Triggers also add write overhead to the source, because each captured change becomes an extra write inside the same transaction. A common practice is to run capture against a replica rather than the primary, which adds another layer to maintain.

MySQL Binary Log for Real-time Change Data Capture

The MySQL binary log (binlog) is the cornerstone of real-time CDC. It records every data-changing operation in a MySQL database as a binary stream. It was originally designed for replication and recovery, and that same log enables log-based, low-latency CDC.

Binlog-based CDC reads the log rather than querying the tables, so it captures inserts, updates, and deletes with little load on the source, and it does so in near real time. Setting it up involves a few configuration steps on the MySQL side plus a connector that parses the binary log into structured change events. The full list is in the MySQL CDC setup checklist below.

Setting up a binlog-based pipeline by hand can be complex, particularly the parsing, queueing, and schema-management work. That's where CDC platforms come in.

The open-source project Debezium helped standardize real-time CDC by decoding binlog events and publishing them to Kafka topics. It supports MySQL and several other databases out of the box.

Today, several platforms, including Estuary, make the binlog approach more accessible by removing the need for a separate streaming layer like Kafka and by simplifying deployment, monitoring, and transformations.

When evaluating your options, consider:

• Source scale and change volume.
• Data engineering resources available.
• Destination systems and update-frequency requirements.
• Security and compliance constraints.

With the binlog approach and the right platform, you can achieve low-latency replication from MySQL to warehouses, lakes, and analytics tools without major strain on your transactional systems.

MySQL CDC methods compared

Each method fits a different situation. Use this table to choose one at a glance, then see the setup checklist for the binlog path.

MySQL CDC setup checklist

If you're implementing binlog-based CDC, work through these steps on the source database. Managed services handle some of them for you, so verify rather than assume.

1. Confirm binary logging is enabled. Check with:

plaintext
SHOW VARIABLES LIKE 'log_bin';

Binary logging is on by default in MySQL 8.0 and later. It was off by default in 5.7, which reached end of life in October 2023. On managed services, whether it's on by default varies by provider and how the instance is configured, so verify it and enable it through provider settings if needed (see step 7).

2. Confirm binlog_format = ROW. ROW is the default in 8.0+, and as of MySQL 8.4 it's the only format recommended for new setups (binlog_format is deprecated). STATEMENT and MIXED don't reliably produce row-level change data.

3. Set binlog_row_image = FULL so change events include complete before and after row images. FULL is the default; MINIMAL and NOBLOB log less and can leave a downstream consumer without the columns it needs.

4. Ensure every captured table has a primary key. CDC tools need a stable row identity to apply updates and deletes downstream.

5. Set binlog retention with recovery in mind. On self-hosted MySQL 8.0+, use binlog_expire_logs_seconds (default 2592000, or 30 days). Keep enough history that a pipeline can recover from downtime without a full re-backfill. On Amazon RDS and Aurora, retention isn't set through the server variable; use the stored procedure instead:

plaintext
CALL mysql.rds_set_configuration('binlog retention hours', 168);

On RDS for MySQL the default is NULL (binary logs aren't retained), so set it explicitly; the value is capped at 168 hours (7 days), a tight ceiling to plan around. Aurora MySQL uses the same procedure but allows up to 2160 hours (90 days). Cloud SQL and Azure Database for MySQL manage retention through their own settings.

6. Create a dedicated CDC user with least privilege. This step is easy to miss:

plaintext
CREATE USER 'cdc_user'@'%' IDENTIFIED BY '...';
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON . TO 'cdc_user'@'%';
GRANT SELECT ON my_database.* TO 'cdc_user'@'%';

REPLICATION CLIENT and REPLICATION SLAVE are global privileges (ON *.*). The user also needs SELECT on the captured tables and read access to information_schema for automatic table discovery.

7. For managed databases, check provider-specific requirements. RDS and Aurora use parameter groups plus the retention procedure above; Cloud SQL enables binary logging through its backup and point-in-time-recovery settings; Azure Database for MySQL uses server parameters and exposes the binary log through its data-out replication feature.

Once these are in place, a CDC connector parses the binlog into structured change events and delivers them to your target systems.

Common MySQL CDC mistakes

A few configuration and operational mistakes account for most broken MySQL CDC pipelines.

• Leaving binlog_format on STATEMENT or MIXED. Only ROW reliably carries the row-level before and after data that CDC needs. This is the default in 8.0+, but verify it, especially on older or heavily customized instances.
• Retention too short, or not set at all. If binary logs expire before a stalled pipeline catches up, the only recovery path is a full re-backfill. On RDS the retention default means logs aren't kept at all, so set binlog retention hours before you rely on the pipeline.
• Tables without a primary key. Without a stable row identity, a consumer can't apply updates and deletes correctly. Add a primary key (or a unique NOT NULL key) to every captured table.
• An over-privileged or missing CDC user. Create a dedicated least-privilege user rather than capturing as an admin account, and grant only the replication, SELECT, and information_schema access the connector needs.
• Forgetting managed-service configuration. On RDS, Aurora, Cloud SQL, and Azure, the server variables aren't always directly settable. Changes go through parameter groups, flags, or server parameters, and some require a reboot.
• Ignoring time-zone handling for DATETIME columns. MySQL DATETIME values carry no time-zone information. Set the server time_zone explicitly so downstream systems interpret timestamps consistently.

Estuary for Real-Time MySQL CDC

Estuary is a real-time data integration platform, and it includes a MySQL capture connector that performs log-based CDC by reading the binary log.

The connector captures inserts, updates, and deletes as they happen, with latency typically in the sub-second to low-seconds range. It works with self-hosted MySQL, Amazon RDS, Amazon Aurora, Google Cloud SQL, and Azure Database for MySQL.

What the MySQL connector does:

• Automatic schema discovery. The connector inspects the database and generates specifications for the tables it captures, so you don't have to define each one by hand.
• Backfill plus streaming. It takes an initial snapshot of existing table contents, then switches to continuous binlog reads, so downstream systems receive both history and live changes. For very large tables, backfill can be disabled per table. The connector checkpoints its position in the binlog, so it doesn't have to re-read a whole table to resume.
• Secure connectivity. It supports SSL/TLS and SSH tunneling for databases in private networks, plus private deployment options.
• Real-time delivery to many destinations. Captured data can be materialized to warehouses and other systems, including Snowflake, BigQuery, Databricks, PostgreSQL, and Elasticsearch, in real time.

Because Estuary reads the binlog rather than polling tables, it adds little load to the source, and because it manages the parsing, queueing, and schema handling, you don't need to run a separate streaming layer like Kafka. For a fuller walkthrough of your options, see Estuary's guide to capturing data from MySQL.

Key MySQL Features and Terms for Change Data Capture

Real-time MySQL CDC relies on a few of MySQL's core features. Here's a quick reference.

Binary log (binlog)

The MySQL binlog, or binary log, is a transaction log that records change events in the source MySQL database and writes them to a binary file. It's a native MySQL feature, and it's enabled by default in MySQL 8.0 and later. (It was off by default in 5.7; you can confirm the current state with SHOW VARIABLES LIKE 'log_bin';.) The binlog enables two important workflows:

• Data recovery. When a server is restored from a backup, binlog events are replayed to catch up on everything that happened since the backup was taken.
• Replication. Many workflows depend on database replicas, and MySQL's replication mechanism is the basis of log-based CDC.

Binlog retention varies by MySQL version and hosting provider. On self-hosted MySQL 8.0+, retention is controlled by binlog_expire_logs_seconds (default 2592000 seconds, or 30 days). The expire_logs_days variable was removed in MySQL 8.4. On managed services, retention is set through provider settings. Keep enough retention for your backfill and recovery needs, typically at least several days.

Source-replica architecture

In MySQL, the main server is called the source. Servers that the source replicates to are called replicas.

Replication

Several types of replication are available in MySQL, all of which use the binary log. Replication can be:

• Asynchronous (the default), semisynchronous, or delayed. Asynchronous replication can still happen in near real time.
• Row-based (the default) or statement-based. Row-based replication copies only the changes that occurred, rather than the entire SQL statement.

CDC setups typically use the defaults.

Additional Resources

Estuary resources:

• Blog series on CDC
• Estuary documentation
• Estuary and the MySQL CDC connector code on GitHub
• Get started with Estuary today

Other resources:

• MySQL documentation for topics mentioned: triggers, event scheduler, binary log
• Custom Postgres CDC use-cases: Shopify, Netflix, Fundbox

Related Articles

• Postgres CDC
• SQL Server CDC
• MongoDB CDC
• Salesforce CDC