Back to articles
DevOps & Systems

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...

DPTCloud Engineering4 min read
Real-Time Multi-Dimensional Data Synchronization Between MySQL and Elasticsearch Across 2 VPS Using Debezium CDC

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

DPTCloud Tier III Infrastructure

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.

View 22 VPS Plans
ECO · vn-02

VPS-2E

94.600 ₫ / mo

10% VAT included

  • CPU: 2 vCPU
  • RAM: 2 GB
  • SSD: 30 GB NVMe
ECO · vn-02Recommended

VPS-3E

170.500 ₫ / mo

10% VAT included

  • CPU: 2 vCPU
  • RAM: 4 GB
  • SSD: 50 GB NVMe
BASIC · vn-01Recommended

VPS-05

457.600 ₫ / mo

10% VAT included

  • CPU: 4 vCPU
  • RAM: 8 GB
  • SSD: 50 GB NVMe

Related Articles

View all
ZaloZalo SupportDiscord0384 753 478