Real-Time Multi-Dimensional Data Synchronization Between MySQL and Elasticsearch Across 2 VPS Using Debezium CDC
A detailed technical guide on synchronizing real-time data between MySQL and Elasticsearch using Debezium CDC on two VPS servers, covering setup, configuration, testing, and...
Image credit: nattanan23 · Pixabay Content License
Introduction
Real-time data synchronization between MySQL and Elasticsearch is a common requirement in data analytics, content search, and reporting systems. In this guide, we will build a multi-dimensional real-time data sync system between two VPS servers: one running MySQL (data source) and the other running Elasticsearch (search data warehouse). We will use Debezium CDC (Change Data Capture) to monitor changes in MySQL and automatically push data to Elasticsearch efficiently.
Prerequisites and Requirements
Before starting, ensure you have:
- Two VPS servers (can be NVMe Cloud VPS from NVMe Cloud VPS or Cloud VPS NVMe)
- MySQL 8.0+ installed on the source VPS
- Elasticsearch 7.10+ and Kibana installed on the destination VPS
- Apache Kafka and Kafka Connect installed on both VPS servers (or a central broker)
- Debezium MySQL Connector configured
- Network connectivity between the two VPS servers (ports 3306, 9092, 9200 open)
Configure Debezium CDC for MySQL
On the source VPS (running MySQL), configure Debezium to track database changes.
1. Install and Configure Kafka Connect
Run Kafka Connect with the Debezium plugin:
# Install Kafka Connect (assuming Kafka is already set up)
docker run -d \
--name connect \
--network host \
-e CONNECT_BOOTSTRAP_SERVERS=192.168.1.100:9092 \
-e CONNECT_REST_PORT=8083 \
-e CONNECT_REST_ADVERTISED_HOST_NAME=192.168.1.100 \
-e CONNECT_GROUP_ID="connect-cluster" \
-e CONNECT_CONFIG_STORAGE_TOPIC="connect-configs" \
-e CONNECT_OFFSET_STORAGE_TOPIC="connect-offsets" \
-e CONNECT_STATUS_STORAGE_TOPIC="connect-statuses" \
-e CONNECT_KEY_CONVERTER="org.apache.kafka.connect.storage.StringConverter" \
-e CONNECT_VALUE_CONVERTER="org.apache.kafka.connect.json.JsonConverter" \
-e CONNECT_VALUE_CONVERTER_SCHEMAS_ENABLE="false" \
-e CONNECT_PLUGIN_PATH="/usr/share/java" \
confluentinc/cp-kafka-connect:latest
2. Create Debezium MySQL Connector
Adjust the configuration to connect to MySQL:
{
"name": "mysql-connector",
"config": {
"connector.class": "io.debezium.connector.mysql.MySqlConnector",
"database.hostname": "192.168.1.100",
"database.port": "3306",
"database.user": "debezium",
"database.password": "debezium",
"database.server.id": "184054",
"database.server.name": "mysql-server",
"database.include.list": "your_database",
"table.include.list": "your_database.users, your_database.orders",
"snapshot.mode": "initial",
"key.converter": "org.apache.kafka.connect.json.JsonConverter",
"value.converter": "org.apache.kafka.connect.json.JsonConverter",
"value.converter.schemas.enable": "false"
}
}
Synchronize Data to Elasticsearch
To push data from Kafka to Elasticsearch, use the Elasticsearch Sink Connector.
1. Install Elasticsearch Sink Connector
Add the Elasticsearch Sink plugin to Kafka Connect:
# Download plugin from Confluent Hub
confluent-hub install confluentinc/kafka-connect-elasticsearch:10.0.0
2. Configure Sink Connector
Set up the connector to send data to Elasticsearch:
{
"name": "elasticsearch-sink",
"config": {
"connector.class": "io.confluent.connect.elasticsearch.ElasticsearchSinkConnector",
"connection.url": "http://192.168.1.101:9200",
"type.name": "doc",
"key.converter": "org.apache.kafka.connect.json.JsonConverter",
"value.converter": "org.apache.kafka.connect.json.JsonConverter",
"key.converter.schemas.enable": "false",
"value.converter.schemas.enable": "false",
"topics": "mysql-server.your_database.users, mysql-server.your_database.orders"
}
}
Testing and Validation
After configuration, verify synchronization:
- Run an INSERT statement into MySQL on the source VPS.
- Check Elasticsearch (on the destination VPS) to confirm data appears.
- Use Kibana or curl to query the data:
curl -X GET "http://192.168.1.101:9200/your_database_users/_search" -H 'Content-Type: application/json' -d'
{
"query": { "match_all": {} }
}
'
Troubleshooting
- No data sync: Check Kafka Connect logs; ensure MySQL binlog is enabled and Debezium has proper permissions.
- Slow sync: Monitor CPU, RAM, and network bandwidth between the two VPS servers.
- Cannot connect to Elasticsearch: Verify firewall rules, port 9200, and network settings.
References
Recommended NVMe Cloud VPS Plans to Deploy This Setup
Enterprise RAID 10 NVMe SSDs, CMC & FPT District 9 uplinks with Anycast Anti-DDoS and 99.99% SLA.
VPS-3E
10% VAT included
- CPU: 2 vCPU
- RAM: 4 GB
- SSD: 50 GB NVMe
