Info2soft use cookies to help you have a superior and more admissible browsing experience on our website. Privacy Policy
Loading...
By:
Apache Kafka is widely used to collect and distribute high-volume event data across applications and services. MySQL, meanwhile, remains a popular choice for storing structured data and serving application queries. Connecting the two can create a practical pipeline for moving streaming data into a relational database.
If you need to continuously stream data from Kafka to MySQL, one of the most straightforward approaches is Kafka Connect with the JDBC Sink Connector. Instead of developing and maintaining a custom Kafka consumer, Kafka Connect can continuously consume records from Kafka topics and write them to MySQL.
In this guide, you’ll learn how to build a basic Kafka-to-MySQL streaming pipeline, configure the JDBC Sink Connector, verify the incoming data, and address several common issues that can affect reliability and performance.
Kafka-to-MySQL streaming is the process of continuously consuming records from a Kafka topic and writing them to a MySQL database as new events become available.
A typical pipeline looks like this:
Kafka Producer → Kafka Topic → Kafka Connect → JDBC Sink Connector → MySQL
The producer generates events and publishes them to a Kafka topic. Kafka stores these records and makes them available to consumers. Kafka Connect provides the framework for moving those records between Kafka and external systems, while the JDBC Sink Connector converts Kafka records into database operations and writes them to MySQL.
Unlike a one-time data migration, streaming keeps the destination updated as new Kafka events arrive. This makes the approach useful for real-time reporting, operational applications, event-driven workflows, and other scenarios where MySQL needs access to continuously arriving data.
Before starting, prepare the following components:
A running Apache Kafka environment
Kafka Connect
A MySQL Server instance
The JDBC Sink Connector
The MySQL JDBC driver, if required by your connector setup
A Kafka topic containing test records
You should also have permission to create a database and table in MySQL and configure Kafka Connect.
For this tutorial, assume that the Kafka topic is named customer-events and the target MySQL database is kafka_mysql.
To build a Kafka-to-MySQL streaming pipeline, you need to configure the destination database first and then connect it to Kafka through Kafka Connect. The following steps walk through the setup process, from creating the MySQL table to configuring the connector and verifying that data is being written successfully.
First, create a database that will store the data coming from Kafka:
#CREATE DATABASE kafka_mysql;Switch to the new database and create a table:
USE kafka_mysql;
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(150),
created_at TIMESTAMP
);The table structure should match the fields contained in the Kafka records. In a production environment, you should define the schema and data types carefully instead of relying entirely on automatic table creation.
Next, make sure Kafka and Kafka Connect are running.
Kafka provides the event streaming layer, while Kafka Connect runs connectors that transfer data between Kafka and external systems.
For a Kafka-to-MySQL pipeline, you need a Sink Connector. The important distinction is that a sink connector moves data from Kafka to an external destination. The JDBC Sink Connector is therefore the appropriate connector for writing Kafka records into MySQL.
After installing the JDBC Sink Connector, make sure Kafka Connect can locate the connector plugin through its configured plugin path.
You can check the available connectors through the Kafka Connect REST API:
curl http://localhost:8083/connector-plugins
Look for the JDBC sink connector in the returned list.
Create a connector configuration file such as mysql-sink.json.
A basic configuration can look like this:
{
"name": "mysql-sink",
"config": {
"connector.class": "io.confluent.connect.jdbc.JdbcSinkConnector",
"tasks.max": "1",
"topics": "customer-events",
"connection.url": "jdbc:mysql://localhost:3306/kafka_mysql",
"connection.user": "root",
"connection.password": "your_password",
"auto.create": "true",
"auto.evolve": "true",
"insert.mode": "upsert",
"pk.mode": "record_key",
"pk.fields": "id"
}
}
Several parameters are particularly important here.
topics specifies the Kafka topic that the connector should consume. In this example, the connector continuously reads records from customer-events.
connection.url, connection.user, and connection.password define the MySQL destination.
insert.mode=upsert allows the connector to insert new records or update an existing record when the primary key already exists.
pk.mode=record_key tells the connector to use the Kafka record key as the database primary key. Therefore, your Kafka messages should be produced with an appropriate key when using this configuration.
For production workloads, you may need to adjust these settings based on your message format, schema management strategy, and MySQL workload.
Once the configuration is ready, submit it to Kafka Connect:
curl -X POST http://localhost:8083/connectors \
-H "Content-Type: application/json" \
-d @mysql-sink.json
If the connector is created successfully, check its status:
curl http://localhost:8083/connectors/mysql-sink/status
A healthy connector should show a RUNNING state for the connector and its task.
If the task fails immediately, check the Kafka Connect worker logs. Common causes include an incorrect MySQL connection string, invalid credentials, a missing JDBC driver, or a connector plugin that Kafka Connect cannot load.
Now send a test record to the customer-events Kafka topic.
For example:
{
"id": 1001,
"name": "Alice",
"email": "alice@example.com",
"created_at": "2026-08-27T09:30:00"
}
Once the record is consumed by the JDBC Sink Connector, query MySQL:
SELECT * FROM customers;
You should see the corresponding record in the customers table.
At this point, the basic Kafka-to-MySQL streaming pipeline is working. Whenever new records arrive in the Kafka topic, the connector can continue processing them and writing them to MySQL.
Kafka Connect is not the only way to stream Kafka data into MySQL. Another option is to write a custom Kafka consumer using Java, Python, or another programming language.
A custom consumer gives developers complete control over message processing. You can implement custom transformations, validation rules, business logic, and database operations before writing data to MySQL.
However, this flexibility also means that your team needs to maintain the application, handle retries, manage offsets, monitor failures, and optimize database writes.
For a straightforward data integration pipeline, Kafka Connect with a JDBC Sink Connector can reduce this development and maintenance overhead. A custom consumer makes more sense when the data requires complex application-specific processing that cannot be handled effectively by connector configuration.
Getting data from Kafka into MySQL is relatively straightforward. Keeping the pipeline reliable and performant at scale requires additional planning.
Writing every Kafka record as an individual database transaction can create unnecessary overhead when the event volume increases.
Batching multiple records before writing them to MySQL can reduce transaction overhead and improve throughput. Connector settings such as batch size should be tuned according to your Kafka traffic and MySQL server capacity.
You should also review MySQL indexes and table design because excessive indexing can increase the cost of high-volume writes.
Kafka consumers may reprocess records depending on how offsets and failures are handled. Your MySQL schema should therefore have a clear strategy for identifying records.
Using a stable primary key and an appropriate insert.mode can help prevent unintended duplicate rows and support update scenarios.
However, upsert behavior should be designed around the semantics of your application. Not every Kafka event represents a complete database row, and some event streams are better modeled as append-only event tables.
A pipeline can appear healthy while the destination is actually falling behind.
Monitor:
Kafka consumer lag
Connector and task status
MySQL write latency
Connector errors
Database connection failures
Consumer lag is particularly useful because it indicates whether Kafka records are accumulating faster than the sink can process them.
Schema changes are another common challenge in streaming pipelines.
For example, your Kafka producer might add a new field such as:
phone_number
while the MySQL table does not yet contain the corresponding column.
Using auto.evolve may help with some schema changes, but it should not replace a deliberate schema management strategy for production environments. Changes to field types, primary keys, or required fields can have more serious consequences.
If the Kafka-to-MySQL pipeline does not work as expected, start by checking the connector status and Kafka Connect worker logs. The following sections cover common issues that can prevent the connector from loading, processing records, or writing data to MySQL.
If the JDBC connector does not appear when you call the connector plugin API, check whether the connector has been installed in a directory included in Kafka Connect’s plugin.path.
Restart the Kafka Connect worker after making installation or configuration changes when required.
Start by checking the connector status:
curl http://localhost:8083/connectors/mysql-sink/status
Then verify that:
The Kafka topic contains records.
The connector task is running.
MySQL credentials are valid.
The target database and table are accessible.
Kafka Connect logs do not report conversion or database errors.
Review your Kafka record keys, primary key configuration, and insert.mode.
If the connector is configured with insert.mode=insert while records are replayed, duplicate-key errors or duplicate application-level records may occur depending on the table design.
If MySQL is receiving data more slowly than Kafka produces it, check consumer lag first.
You may then need to review connector task parallelism, batch settings, Kafka partitions, MySQL indexes, database resources, and network latency.
Increasing connector tasks alone does not necessarily improve performance. The destination database must also be able to handle the additional write workload.
Streaming Kafka data into MySQL is useful when applications need continuously updated relational data rather than periodic batch imports.
For example, a company may use Kafka as an event backbone while keeping selected operational data in MySQL for application queries. A real-time reporting system may also consume events from Kafka and persist them in MySQL so dashboards can query structured data with low latency.
The architecture can also be useful when multiple applications publish events to Kafka but downstream systems require a relational representation of those events.
The key advantage is that data does not have to wait for a scheduled ETL job before becoming available in MySQL.
Kafka-to-MySQL streaming is one part of a broader real-time data integration strategy. When organizations also need continuous database replication, cross-platform migration, or data movement between heterogeneous database environments, a dedicated database integration solution can simplify these workflows.
i2Stream is designed for real-time database replication and migration across different database environments. It can help organizations reduce the need to build and maintain separate scripts for every database movement scenario, particularly when real-time data synchronization extends beyond a single Kafka-to-MySQL pipeline.
If your environment involves both streaming workflows and broader database replication requirements, i2Stream can be considered as part of a unified data integration strategy.
Can Kafka stream data directly to MySQL?
Kafka does not normally write directly to MySQL. A connector such as the JDBC Sink Connector or a custom Kafka consumer is typically used to consume Kafka records and write them to MySQL.
How do I connect Kafka to MySQL?
One common approach is to install Kafka Connect and the JDBC Sink Connector, configure the MySQL connection and Kafka topic, and then start the connector through the Kafka Connect REST API.
Can Kafka stream data to MySQL in real time?
Yes. A Kafka-to-MySQL pipeline can continuously process new Kafka records and write them to MySQL. The actual latency depends on Kafka, connector configuration, network conditions, and MySQL performance.
What is the Kafka JDBC Sink Connector?
The JDBC Sink Connector is a Kafka Connect connector designed to consume Kafka records and write them to JDBC-compatible relational databases such as MySQL.
Is Kafka Connect better than a custom Kafka consumer?
It depends on the use case. Kafka Connect is generally convenient for standard data integration pipelines, while a custom consumer provides greater control when complex application-specific processing is required.
Streaming data from Kafka to MySQL provides a practical way to make continuously generated event data available in a relational database. For a standard integration scenario, Kafka Connect + JDBC Sink Connector provides a straightforward approach without requiring a custom consumer application.
Once the basic pipeline is working, focus on batch writes, key management, schema evolution, connector monitoring, and failure handling to make the solution reliable at scale.
· Enterprise & Mid-market Customers Worldwide
· Support team available to assist you throughout your trial
· Start a 60-day free trial or view demo to see how Info2Soft protects enterprise data.
