DPTCloud
Back to articles
Technology Insight

Lightweight Real-Time Search: Building a Low-Overhead CDC Pipeline from MySQL to Meilisearch using Debezium on a VPS

May 30, 2026

Introduction: The Real-Time Search Challenge in Modern Web Applications

In today's fast-paced digital economy, user experience hinges on speed and relevance. When users interact with a search bar, they expect instantaneous, typo-tolerant, and highly accurate results. Traditional relational databases like MySQL are exceptional for transactional integrity (ACID compliance) but fall short when handling complex, full-text search queries at scale. Executing intensive LIKE '%query%' statements can quickly degrade database performance, leading to slow response times and potential system downtime.

To solve this, architectural best practices recommend offloading search workloads to dedicated search engines. Meilisearch has emerged as a premier choice for developer-centric, lightning-fast, and open-source search. However, introducing a separate search engine introduces a critical engineering challenge: How do we synchronize data from our primary MySQL database to Meilisearch in real time without overloading our system?

Traditional approaches rely on dual-writing from the application layer or running periodic cron jobs. Dual-writing introduces tight coupling and data inconsistency risks if one write fails. Cron jobs create data lag and put immense strain on the database during batch intervals. The elegant solution to this problem is Change Data Capture (CDC). In this comprehensive guide, we will design and deploy a ultra-lightweight CDC pipeline using Debezium and Kafka Connect, streaming data seamlessly from MySQL to Meilisearch on a single Virtual Private Server (VPS).

Understanding the Architecture: CDC, Debezium, and Meilisearch

Before diving into the implementation, it is crucial to understand the component lifecycle of a Change Data Capture pipeline. CDC operates by monitoring the database's transaction log—specifically the Binary Log (binlog) in MySQL. Every insert, update, or delete operation is recorded in this log. Because CDC reads the log asynchronously, it captures data mutations with near-zero overhead on the primary database engine.

The Role of Debezium and Kafka Connect

Debezium is a distributed, open-source CDC platform built on top of Apache Kafka. It hooks directly into the MySQL binlog, capturing row-level changes and converting them into structured event streams. Typically, Debezium requires a full Apache Kafka cluster, which demands significant memory and CPU resources—often cost-prohibitive for small-to-medium businesses running on a VPS.

To keep our architecture "siêu nhẹ" (ultra-lightweight), we will utilize a minimized deployment pattern. By leveraging a single-node Kafka/ZK engine or utilizing Debezium Server (a standalone runner) paired with lightweight event routers, we can dramatically compress the infrastructure footprint. The event stream is then consumed and transformed into HTTP POST payloads optimized for Meilisearch's document ingestion API.

Why Meilisearch on a VPS?

Meilisearch is written in Rust, making it incredibly fast and memory-efficient compared to Java-heavy alternatives like Elasticsearch. This makes it the perfect resident for a VPS deployment, allowing you to achieve sub-50ms search responses while sharing server resources with your data pipeline.

Prerequisites and Environment Setup

To successfully follow this guide, ensure your VPS meets the following minimum requirements:

  • OS: Ubuntu 22.04 LTS or newer
  • Hardware: Minimum 2 vCPUs, 4GB RAM (8GB recommended)
  • Software: Docker Engine v20.10+ and Docker Compose v2+ installed
  • Network: Ports 3306 (MySQL), 7700 (Meilisearch), and 8083 (Kafka Connect) properly firewalled and secured

Step 1: Configuring MySQL for Change Data Capture

For Debezium to capture mutations, the MySQL server must be configured to emit row-based binary logs. This requires modifying the MySQL configuration file (typically /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf).

Add or verify the following parameters inside the [mysqld] section:

server-id = 223344
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
expire_logs_days = 7

Let's break down why these settings are mandatory:

  • server-id: A unique ID within the MySQL replication topology. Debezium connects to MySQL as if it were a replica server.
  • log_bin: Enables binary logging and defines the base name for the log files.
  • binlog_format: Must be set to ROW. This tells MySQL to log the actual changes to individual rows rather than the SQL statements.
  • binlog_row_image: Set to FULL ensures that the log contains both the pre-image (before change) and post-image (after change) of the row data.

After saving the configuration, restart the MySQL service to apply the changes:

sudo systemctl restart mysql

Creating the Debezium Database User

Debezium requires a dedicated user with specific privileges to read the replication stream. Log into your MySQL console and execute the following queries:

CREATE USER 'debezium'@'%' IDENTIFIED BY 'YourSecurePassword';
GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'debezium'@'%';
FLUSH PRIVILEGES;

Step 2: Designing the Lightweight Docker Compose Stack

To orchestrate our ultra-lightweight pipeline, we will consolidate our services into a single docker-compose.yml file. We will include ZooKeeper, a single-node Kafka broker, Debezium Connect, and Meilisearch. To conserve VPS memory, we will explicitly set resource constraints on the containers.

Create a directory named cdc-pipeline and establish the following compose configuration:

version: '3.8'
services:
  zookeeper:
    image: confluentinc/cp-zookeeper:7.4.0
    environment:
      ZOOKEEPER_CLIENT_PORT: 2181
    deploy:
      resources:
        limits:
          memory: 256M

  kafka:
    image: confluentinc/cp-kafka:7.4.0
    depends_on:
      - zookeeper
    ports:
      - "9092:9092"
    environment:
      KAFKA_BROKER_ID: 1
      KAFKA_ZOOKEEPER_CONNECT: zookeeper:2181
      KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://kafka:9092,PLAINTEXT_HOST://localhost:9092
      KAFKA_LISTENER_SECURITY_PROTOCOL_MAP: PLAINTEXT:PLAINTEXT,PLAINTEXT_HOST:PLAINTEXT
      KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1
    deploy:
      resources:
        limits:
          memory: 512M

  debezium:
    image: debezium/connect:2.3.0.Final
    depends_on:
      - kafka
    ports:
      - "8083:8083"
    environment:
      BOOTSTRAP_SERVERS: kafka:9092
      GROUP_ID: 1
      CONFIG_STORAGE_TOPIC: my_connect_configs
      OFFSET_STORAGE_TOPIC: my_connect_offsets
      STATUS_STORAGE_TOPIC: my_connect_statuses
      CONFIG_REPLICATION_FACTOR: 1
      OFFSET_REPLICATION_FACTOR: 1
      STATUS_REPLICATION_FACTOR: 1
    deploy:
      resources:
        limits:
          memory: 768M

  meilisearch:
    image: getmeili/meilisearch:v1.2.0
    ports:
      - "7700:7700"
    environment:
      - MEILI_MASTER_KEY=YourMasterKey123!
      - MEILI_ENV=production
    volumes:
      - meili_data:/meili_data
    deploy:
      resources:
        limits:
          memory: 512M

volumes:
  meili_data:

Launch the stack using the following command:

docker compose up -d

Verify that all containers are healthy and running by executing docker compose ps. By constraining the JVM allocations using Docker limits, we ensure that the entire stack operates comfortably within a ~2GB memory envelope, leaving plenty of overhead on your VPS.

Step 3: Registering the Debezium MySQL Connector

With the infrastructure running, we must configure the Debezium MySQL connector. This connector acts as the engine that polls the binlog and publishes events to Kafka. We interact with Debezium via its REST API.

Create a configuration payload named mysql-connector.json:

{
  "name": "mysql-vps-connector",
  "config": {
    "connector.class": "io.debezium.connector.mysql.MySqlConnector",
    "tasks.max": "1",
    "database.hostname": "your_vps_ip_or_host",
    "database.port": "3306",
    "database.user": "debezium",
    "database.password": "YourSecurePassword",
    "database.server.id": "184054",
    "database.topic.prefix": "vps_db",
    "database.include.list": "ecom_db",
    "table.include.list": "ecom_db.products",
    "schema.history.internal.kafka.bootstrap.servers": "kafka:9092",
    "schema.history.internal.kafka.topic": "schemahistory.products"
  
}

Submit this configuration to Debezium using curl:

curl -X POST -H "Content-Type: application/json" --data @mysql-connector.json http://localhost:8083/connectors

Debezium will perform an initial snapshot of the ecom_db.products table and begin listening to subsequent binlog mutations. Each database modification is streamed to a Kafka topic named vps_db.ecom_db.products.

Step 4: Bridging Kafka Streams to Meilisearch

To complete our synchronization pathway, data arriving in the Kafka topic must be pushed to Meilisearch. While enterprise environments use complex Sink Connectors, we can utilize an ultra-lightweight node or Python consumer daemon on the VPS to handle this translation efficiently.

The script acts as a reactive agent: it reads the multi-layered JSON payload from Debezium, extracts the after object (representing the new state of the database row), handles deletion events if the payload contains a "op": "d" parameter, and streams the updates to Meilisearch's document ingest endpoint.

Example Data Transformation

When a product is updated in MySQL, Debezium emits an event structured like this:

{
  "before": { "id": 101, "name": "Old Smartphone", "price": 500 },
  "after": { "id": 101, "name": "Next-Gen Smartphone", "price": 549 },
  "op": "u"
}

Our synchronization worker filters this payload, isolating the after object, and formats it directly for Meilisearch:

POST http://localhost:7700/indexes/products/documents

Because Meilisearch natively indexes primary keys dynamically, any update to an existing id simply overwrites the old index entries seamlessly, ensuring real-time accuracy.

Best Practices for VPS Production Deployments

When maintaining an open-source CDC stack on bounded VPS instances, observe the following operational guardrails:

  • Monitor Disk Space: MySQL binlogs and Kafka log segments can expand rapidly. Ensure you configure appropriate log retention strategies (e.g., KAFKA_LOG_RETENTION_HOURS: 24).
  • Secure Internal Ports: Never expose Kafka (9092), ZooKeeper (2181), or Debezium (8083) to the public internet. Use internal Docker networks or strict UFW firewalls to limit access to localhost.
  • Graceful Recovery: Debezium tracks its offsets natively in Kafka. If your worker process or VPS restarts unexpectedly, the system will resume streaming from the exact millisecond it went offline, avoiding data gaps.

Conclusion

Building an ultra-lightweight Change Data Capture pipeline from MySQL to Meilisearch empowers your architecture with enterprise-grade search features without the associated cloud cost premiums. By combining Debezium's log-based event streaming with Meilisearch's efficient execution model, you create a system that is decoupled, highly scalable, and structurally resilient. Implement these techniques today to deliver sub-millisecond search capabilities that scale gracefully on your own independent virtual infrastructure.

Lightweight Real-Time Search: Building a Low-Overhead CDC Pipeline from MySQL to Meilisearch using Debezium on a VPS | DPTCloud