How CaptureChangeMySQL streams MySQL changes into NiFi
CaptureChangeMySQL reads MySQL's binary log and emits each insert, update and delete as a change event in a NiFi flow, so a team that needs row-level changes does not have to poll tables on a schedule. The processor connects to the server as a replication client, so it sees changes at the moment they are committed to the log rather than whatever a query happens to return at the time it runs. That distinction matters most for deletes. A polling query such as QueryDatabaseTableRecord only sees rows that still exist, so a removed row disappears without a trace unless the source keeps a soft-delete flag. A binlog reader receives the delete event itself, along with the before-image of the row where the server logs it. The whole capture runs inside your own infrastructure, between your MySQL servers and NiFi nodes, and nothing about the flow requires an external relay service.
Before you build the flow, confirm the MySQL side with your database team. The binary log must be enabled, and the row format must be set so that the change events carry the column values your downstream consumers need. Each capture also needs a server ID that does not collide with any other replica, and a replication-capable account with the privileges the processor requires. Check whether your MySQL deployment is a standalone server, a replica or a managed service, because managed offerings may restrict these settings or the replication user. Confirm the exact prerequisites for your MySQL version against the MySQL documentation and the CaptureChangeMySQL component documentation at the Apache NiFi component reference, since these details change between releases and should not be taken from memory.
Plan for the cases that polling never had to handle. Binlog positions are a stream, so the processor must persist where it stopped, and a NiFi restart, a node change or a purged log can each force a decision about where to resume. Replication also depends on how long your servers keep binary logs, so an outage longer than that retention window can leave a gap that only a fresh snapshot closes. Agree in advance who owns that recovery step, how a gap is detected and how the downstream tables are reconciled. Those decisions shape the design more than the processor settings themselves.
What MySQL, NiFi and the network need before capture starts
Change data capture depends on three things lining up: a MySQL primary that publishes a usable binary log, a database account that can read it, and a NiFi runtime that can reach the server. Confirm each group with the owners of the database, the NiFi platform and the network before you build the flow, because a gap in any one of them stops capture before the first event arrives.
MySQL binary log settings
CaptureChangeMySQL reads the binary log, so the MySQL primary must have binary logging enabled and configured in the format and row image the processor expects. Confirm the required binlog format, row image and GTID settings against the CaptureChangeMySQL component documentation and the MySQL documentation for the exact versions you run. Also check binlog retention, because a purged log file forces a gap or a re-snapshot.
Dedicated replication account
Create a separate database account for NiFi rather than reusing an application login. The account needs the replication privileges the processor uses to read the binary log and the read access needed to obtain table metadata for the captured schemas. Record the grants in your change record so auditors can see that NiFi holds no write privileges on source tables.
NiFi version and Java runtime
Confirm the NiFi release and the Java runtime against the NiFi administration guide before you install anything. NiFi 2 requires Java 21 as the minimum runtime. If you still run NiFi 1.x, plan the upgrade path first, since the 1.x line is out of support and the flow file format changes in 2.x.
Network paths and firewall rules
Every NiFi node that may run the capture processor needs a route to the MySQL primary on the database port, and the source must accept connections from those node addresses. Agree the rules with the network team, including any load balancer or proxy in between. Do not point capture at a replica unless you have confirmed how the processor behaves against it, since failover can change the binlog position.
Cluster state and primary node placement
Decide where capture runs in a clustered NiFi. Confirm how the cluster coordinates state through ZooKeeper, embedded or external, or through Kubernetes ConfigMaps or Leases, and make sure the stored binlog position survives node restarts. Plan which node is primary so the single binlog reader is not duplicated by accident when nodes change.
Test environment with representative schemas
Build a non-production MySQL instance that mirrors the production binlog settings, table types, character sets and a realistic mix of inserts, updates and deletes. Test the schema changes you expect, such as added columns and renamed tables, because these affect how records are parsed downstream. Confirm MySQL version support for your exact release yourself before you rely on the test results.
How event ordering and transaction grouping shape downstream logic
CaptureChangeMySQL emits changes in the order MySQL wrote them to the binary log, but that order is only a guarantee within one source. Downstream logic must decide early whether each row event is processed on its own or whether events are grouped by transaction. A single business operation, such as placing an order that inserts a header row and several line rows and decrements stock, can span multiple row events inside one transaction. If the flow applies those events one at a time, a target can briefly show a partial business state. Grouping by transaction lets the flow commit the whole set together, but it adds buffering and a failure mode where one bad event holds back the group. Confirm in the component documentation how transaction boundaries are surfaced and whether the processor's output is grouped or flat before you commit to either design.
Updates and deletes need explicit handling at the target. Decide whether the processor exposes both the before and after row images for an update, and confirm the exact attributes and record layout in the component documentation for your NiFi 2 version. If your target only accepts upserts, a delete event has no natural place in that model. Plan a rule that turns deletes into soft-delete flags, tombstone rows or actual removals, and test each path with a row that is inserted, updated several times and then deleted. Also decide what happens when an update arrives for a key the target has never seen, since that can occur after a restart or a filtered initial load.
Cross-table ordering is the hardest question because the binlog order is global per source but your targets may be written by separate processors. Two tables that the source updates in one transaction can reach the target through different paths, with different retries and backpressure, so a child row can land before its parent. Choose whether to enforce referential order in the flow, to tolerate temporary gaps and reconcile later, or to write both tables through one ordered path. Verify the choice with fault injection: pause one downstream branch, let the other run, and check what the target shows. Keep these checks in your test plan, since the behavior depends on your queue settings and cluster layout.
Configuring the binlog, the connection and the first capture flow
This sequence turns a MySQL server into a verified change source for NiFi, from binary log settings and a scoped replication account through to a capture flow whose output has been checked for each operation type.
Enable and verify binary logging on a test server
A database administrator enables row-based binary logging on a non-production MySQL server, then confirms the settings with a query against the server variables. The settings to check include whether binary logging is on, that the log format is ROW, and that the full row image is recorded, along with the server ID and retention period. Confirm the exact variable names and recommended values against the MySQL documentation for your version before changing production. The step is complete when the variables read back as expected and a test write creates a new binlog file or position that you can list.
Create a dedicated replication account and confirm its grants
The database administrator creates a separate user for NiFi that is limited to the replication privileges CaptureChangeMySQL needs, plus read access to the schemas it will capture. Avoid reusing an application account or granting administrative privileges. Confirm the required privilege list against the component documentation for your NiFi 2 release. Log in as the new user and run a grants check to verify the privileges, and confirm it cannot write to the captured tables. The step is complete when the grants match the documented list and the account can connect from the NiFi node addresses.
Add the MySQL connection and protect its credentials
In the NiFi canvas, an operator adds the MySQL database connection configuration as a controller service, using the replication account and the host, port and database settings for the test server. Store the password as a sensitive value, either through a sensitive parameter or the NiFi credential store, so it does not appear in the flow definition or the processor properties in plain text. Enable the controller service and confirm it reports a valid state. The step is complete when the service is enabled and a connection check succeeds from the NiFi node that will run the capture.
Configure CaptureChangeMySQL connection and server identity
The operator adds CaptureChangeMySQL to the canvas and points it at the connection service. Set the server identity properties so MySQL treats NiFi as a replica with a unique server ID that does not collide with any other replica or the primary. Confirm the property names and their meaning in the component documentation for your NiFi 2 release, since they differ across versions. Leave the processor stopped at this point. The step is complete when the processor validates without errors and the server ID is recorded in the flow documentation.
Set the starting position and retrieval behavior
Choose where the capture begins: the current binlog position at first start, or a specific file and offset that you have recorded from the server. Record the chosen starting point in the change ticket so the team can explain later where history begins. Confirm how the processor stores its position after restarts, because that state determines whether events are replayed or skipped. The step is complete when the starting position is documented and a second dry check of the binlog position on the server matches what the processor will read.
Run the capture on the primary node first
In a clustered NiFi, schedule CaptureChangeMySQL to run on the primary node only, and connect its success relationship to a downstream processor that writes FlowFiles to a queue you can inspect. Start the processor and confirm it reaches a running state without errors in the bulletin board. Running on one node first keeps the binlog reader to a single consumer while you verify behavior. The step is complete when the processor runs continuously and the bulletin board shows no connection or authentication errors.
Verify inserts, updates and deletes with test writes
On the test server, run a controlled insert, then an update and then a delete against a disposable table, and note the time of each. In NiFi, inspect the FlowFiles produced for each change and check the operation type attribute, the table and schema names, and the row values before and after the change. Confirm that the update carries the expected change and that the delete is recorded as a delete rather than a missing row. Keep the test table and the log of statements so the result can be repeated after restarts. The step is complete when all three operation types appear with the correct values and nothing is missing or duplicated.
Mapping CDC event fields to records, keys and target operations
This table maps each MySQL change type to the event attributes or record fields NiFi produces, the target operation it should drive, the key the target needs, and the decision the team must record. Use it as a template for each source table, and confirm every attribute name against the CaptureChangeMySQL processor documentation before building routes, because names and record shapes can differ between NiFi versions.
| Change type | NiFi fields to confirm | Target operation and key | Decision to document |
|---|---|---|---|
| Insert | Event type attribute and the row values in the record or FlowFile content, confirmed against the processor documentation | Insert or upsert on the primary key of the source table | Whether replays of an already-applied insert are ignored, overwritten or raised as an error |
| Update | Event type attribute and before and after row images, if the processor emits both for the table | Upsert on the primary key, using the after image as the source of truth | Whether unchanged columns are written, and how to handle an update that changes the primary key |
| Delete | Event type attribute and the key columns of the deleted row, confirmed in the processor output | Delete by primary key, or soft delete by setting a tombstone column | Hard delete or soft delete in the target, and retention rules for deleted history |
| Schema change (DDL) | Whether the processor emits DDL events for the table and how they appear in the output, to be confirmed | No direct row operation; a review or schema update step in the target | Who approves a column change, and whether the flow pauses or continues when a column is added or dropped |
| Transaction boundary | Transaction or commit markers, if emitted, and how they group events | Apply all events of one transaction together or in order before commit | Whether the target sees partially applied transactions, and how that is labeled downstream |
| Ordering and duplicates | Binlog position or sequence values carried with each event, to be confirmed | Apply in binlog order per key; discard events at or below the last applied position | Where the last applied position is stored and how it is recovered after a restart |
| Truncate or bulk change | Whether a TRUNCATE or large bulk statement produces discrete row events or a single statement event | Usually a full reload of the target table rather than per-row deletes | Who authorizes a reload and how it is reconciled against the source before cutover |
How restarts, checkpoints and replay avoid silent gaps
The stored read position is the most important state in a CaptureChangeMySQL flow, so treat it as data that can be lost. After a restart, a stop or the loss of a cluster node, the processor should resume from the position it last recorded in the MySQL binary log, which keeps the flow from skipping changes or starting from the current end of the log. Confirm in the component documentation where that state is held and how a surviving node takes it over after a failover, because a position stored on only one node is a gap waiting to happen. The silent gap appears when the saved position no longer exists on the server. If MySQL purged the binary log files that contain it during an outage, the processor cannot read forward from that point, and the events between the saved position and the earliest available log are lost for that consumer. Alert on processor errors and binlog lag so that this condition is noticed while the logs still exist.
Binary log retention decides whether a restart can be recovered at all. Set the retention period to cover the longest outage you expect for the NiFi cluster, including maintenance windows and the time needed to repair a failed node, and confirm the setting name for your MySQL version in the MySQL documentation (on MySQL 8 it is typically binlog_expire_logs_seconds). Then decide, for each captured table, between two recoveries. If the saved position is still on the server, replay from it and let the target absorb duplicates. If the position has been purged or the outage exceeds retention, take a fresh snapshot of the affected tables with an ordinary query, load it into the target, and restart change capture from a position recorded at or before that snapshot. Make the cutover point an explicit step in the runbook, because a snapshot taken after the restart point loses rows and one taken too early duplicates more.
Downstream targets must tolerate replayed events, since any restart can deliver changes that were already written. Design each write so that applying the same insert, update or delete twice leaves the same end state, for example by upserting on the primary key and applying deletes in change order, and store the last applied position in the target where your design allows it. Then rehearse both recoveries in a test environment before go-live. Stop the flow during an active transaction, restart it, and check that no rows are missing or doubled. Next, purge binary logs past a saved position on a test MySQL server and walk the snapshot-and-resume runbook end to end, timing each step. Record the results with the runbook so that the on-call team works from observed behavior.
Securing the MySQL replication account and the change stream
A CDC pipeline reads the same change log that replicas use, so it carries the full content of every captured row. The controls below cover who can connect to MySQL, how the connection and credentials are protected, what the captured events expose inside NiFi, and how the account is maintained over time.
Least-privilege replication account
Create a dedicated MySQL user for CaptureChangeMySQL instead of reusing an administrative or application account. Grant only the replication privileges the processor needs to read the binary log, and restrict table-level reads to the schemas in scope. Ask your DBA to confirm the exact privilege list for your MySQL version, then review it after each MySQL upgrade.
TLS with certificate verification
Encrypt the connection between NiFi and MySQL and configure the client to verify the server certificate against a trusted CA, not just accept any certificate. Confirm that the MySQL server is configured to accept TLS connections and that the NiFi host trusts the issuing CA. Test a verification failure on purpose so you know the check is enforced.
Credentials kept out of flow exports
Store the MySQL password in a controller service that marks it as a sensitive value, so it is encrypted at rest and not written in plain text when the flow is exported or versioned. Keep the sensitive properties key protected and backed up separately from the flow definition. Verify that a flow export from the canvas contains no password.
Restricted canvas and provenance access
Treat provenance as sensitive, because captured events can contain full row values for inserts, updates and deletes. Use the NiFi authentication options that fit your identity estate, such as LDAP, OpenID Connect or SAML 2.0, and limit who can view the canvas, query provenance and download content. Review these policies as part of your access reviews.
Masking or dropping regulated columns
Remove personal or regulated columns before captured events are written to queues, repositories or downstream stores. Use record-level processing or a transform step to drop or hash fields such as national identifiers, card data or health details. Confirm with your data protection owner which columns must never leave the source, because a later masking step cannot recover content already written to provenance.
Rotation of the replication account
Plan a rotation schedule for the MySQL password and any client certificates used by the processor. Update the controller service, verify that the connector reconnects, and confirm that the binlog position is not affected. Record the owner, the rotation procedure and the test that proves the new credential works before retiring the old one.
Common CDC failures, their causes and fixes
This table maps the most common CaptureChangeMySQL failures to their likely causes and the configuration or design fix for each. Start from the symptom column, then check the cause before changing settings. The main takeaway is that most failures trace back to binlog settings, saved positions, target write logic or cluster placement rather than to NiFi itself.
| Symptom | Likely cause | Fix |
|---|---|---|
| No events arrive from MySQL | Binary logging is disabled, the binlog format is not ROW, or the processor points at the wrong host or port | Confirm binary logging is enabled and the binlog format is ROW on the source server. Check the host and port in the processor against the server you intend to read, including any proxy or replica in between. |
| No events arrive, and the connection fails | The replication user lacks the grants needed to read the binlog or query server metadata | Grant the replication user the privileges the processor needs to connect as a replica and read binlog events. Test the account from the NiFi node that runs the processor, not only from your workstation. |
| Events stop after a restart | The binlog position saved in NiFi state refers to a file the MySQL server has already purged | Check the server's binlog retention setting against your outage tolerance. Raise retention so that restarts fall within it, and alert when NiFi's saved position falls behind the oldest available binlog file. |
| Duplicate rows appear in the target | Events are replayed after a restart or failover, and the target write is a plain insert | Make the target write idempotent. Use upserts keyed on the source primary key, and carry the binlog position or transaction identifier as an attribute so repeated events can be recognized and skipped. |
| Deletes never reach the target | The target write logic only handles inserts and updates, so delete events are routed to a path that does nothing | Route events by the operation type attribute and add an explicit delete branch that removes rows by primary key. Keep soft-delete tables as a documented design choice, and verify delete handling with a test row on each release. |
| Events arrive out of order on a multi-node cluster | More than one node runs the CDC processor, so separate nodes read the same binlog stream and write independently | Schedule CaptureChangeMySQL to run on the primary node only, and confirm that the processor is not scheduled on all nodes. Check that downstream processors keep ordering where the target depends on it. |
| NiFi disk usage grows steadily | Captured events are not consumed fast enough, or a downstream relationship is not connected, so FlowFiles queue in the content and flowfile repositories | Set connection back-pressure thresholds, connect or auto-terminate every relationship deliberately, and check the target write throughput. Monitor queue depth so a stalled target shows up before the repository fills. |
| Transactions arrive split across batches | Grouping by transaction is not configured, so a single source commit is written in parts | Enable transaction grouping in the processor so events from one commit are emitted together. Confirm the downstream write waits for the full group before committing its own transaction. |
Questions teams ask before building MySQL CDC in NiFi
How much effort does a MySQL CDC build in NiFi take?
Effort depends mostly on how many tables you capture, how often their schemas change, and how much the target design has to absorb. A single table with stable columns and one append-only target is a small build. Dozens of tables with renamed columns, mixed primary key designs and downstream merge logic is a much larger one. Testing scope adds to it, because each delete, update and restart path needs a verified result in the target. Ask any provider to break the quote into table count, schema change handling, target design and test cases so you can see which driver is moving the number.
Can our team build MySQL CDC in NiFi without outside help?
A capable team can, but the work spans three skill areas that are often held by different people. Someone needs to administer MySQL replication, including binary log format, retention and user privileges. Someone needs NiFi flow design skills to handle event ordering, transaction grouping and restart state. Someone needs data modeling experience to decide how inserts, updates and deletes land in the target. If one of those areas is thin, the risk shows up later as missed deletes or duplicate rows. Check your team against those three areas before deciding whether to build internally or bring in a partner for part of the work.
How long does the work take, in phases?
The work is easier to plan in phases than as a single duration. The first phase confirms MySQL prerequisites, binary log settings and access, and checks the NiFi environment. The second phase builds the capture flow, grouping and state handling in a test environment. The third phase defines the target write logic and tests inserts, updates, deletes and restarts against real change volume. The final phase covers handover, runbooks and monitoring. The length of each phase depends on the scope agreed in writing. Ask for a phase plan with entry and exit criteria rather than a single end date.
What do we need to provide for the engagement?
Plan to provide the following before work starts:
- Read access to the MySQL instance and a privileged account for replication configuration, or a DBA who can apply the settings.
- A test MySQL instance that mirrors the production schema, so changes can be replayed safely.
- Sample data with representative inserts, updates and deletes, including edge cases such as large transactions.
- A named owner for each target system who can confirm the expected result and sign off on test cases.
- Access to the NiFi environment and its change process.
Missing any of these usually extends the timeline, because the team waits on access or agrees the target design late.
What is one-time work and what recurs after go-live?
The build, initial target design, test cases and first handover are one-time costs. Recurring work includes maintaining the flow when source schemas change, monitoring MySQL binary log retention so the capture position is never lost, and applying NiFi upgrades that may affect processors or controller services. Confirm with your MySQL administrator how long binary logs are retained in your environment, because that setting decides how far behind a stopped flow can fall before it must resynchronize. Ask the provider who owns each recurring item after handover.
How is the work scoped and quoted?
Work is scoped in writing before it starts. The scope lists the source databases and tables, the target systems, the event types to capture, the testing approach, the deliverables and the acceptance criteria. Quotes follow from that written scope, so a change in tables or targets changes the quote. DeployOpen does not publish a price list. Ask any provider for the cost components, who performs each activity, which items are one-time and which are recurring, and what is excluded, such as ongoing support or NiFi upgrades.
How DeployOpen scopes, builds and supports MySQL CDC flows
DeployOpen plans, builds and documents Apache NiFi MySQL change data capture flows inside your own infrastructure. Each engagement starts with a written scope that covers binlog prerequisites, event handling, transaction grouping and restart behavior before any flow is built.
How we scope
- The written scope lists the MySQL versions, tables, schemas and downstream targets that the CDC flow must cover.
- Scope records how binlog access, replication accounts and network paths will be provisioned and who owns each step.
- Engagements are fixed-scope, so deliverables, acceptance tests and timelines are agreed in writing before work starts.
How we build
- We configure CaptureChangeMySQL against a test replica, then verify insert, update and delete events end to end.
- Flows are built with transaction grouping, ordering checks and error routing defined, not left to default settings.
- Restart tests confirm that the flow resumes from stored state after a NiFi node stops and comes back online.
How we hand over
- Handover includes the flow definitions, parameter names, controller service settings and a runbook for operators.
- Documentation records the binlog retention and privilege requirements that your MySQL administrators must keep in place.
- Your team receives test cases and a list of open items that need confirmation with your MySQL and NiFi owners.
How we support
- Ongoing support, upgrades and continuous testing are available as a retainer after handover.
- Support covers reviewing changes to MySQL binlog settings and NiFi versions before they reach production flows.
- Retainer scope is agreed in writing, including how incidents are reported and which environments are covered.
