Yupeng Ma
← Back to projects

Case study

mysqlsmom

A Python tool for synchronizing MySQL data to Elasticsearch through full loads and binlog-based incremental updates.

Role · Creator

  • Python
  • MySQL
  • Redis
  • Elasticsearch
View mysqlsmom on GitHub ↗
MySQL binlog events enter a Python synchronization process, which transforms changes for Elasticsearch while Redis stores the last processed binlog position.

  1. 01

    Overview

    Context
    Applications needed Elasticsearch indexes to follow MySQL data through an initial full load and subsequent incremental changes.
    My contribution
    Created and published the Python project, its transformation pipeline, and its full and incremental synchronization modes.
    Technology
    Python, MySQL binlog, Redis, and Elasticsearch.
    Result
    The public repository had 281 GitHub stars in the verified 2026-08-25 snapshot.

  2. 02

    The Problem

    Keeping Elasticsearch aligned with MySQL requires both an initial full load and a resumable incremental change stream with custom transformation logic.

  3. 03

    My Role

    Creator

    • Designed the Python synchronization flow around MySQL binlog events.
    • Implemented Elasticsearch synchronization, custom transformation pipelines, and Redis-backed binlog checkpoints.
    • Published and maintained the project as open-source software.

  4. 04

    Architecture

    A focused map of the components and responsibility boundaries that matter to this project.

    Component map

    Full and incremental synchronization components

    1. 01MySQL source

      Tables and row-based binlog

    2. 02Full-load reader

      Initial table synchronization

    3. 03Binlog reader

      Insert and update events

    4. 04Transform pipeline

      Configurable row-to-document logic

    5. 05Redis checkpoint

      Last binlog filename and position

    6. 06Elasticsearch

      Search-index destination

    • MySQL sourceFull-load readerinitial rows
    • MySQL sourceBinlog readerbinlog
    • Full-load readerTransform pipelinerows
    • Binlog readerTransform pipelineevents
    • Binlog readerRedis checkpointresume position
    • Transform pipelineElasticsearchindex documents
    MySQL feeds two processing paths: an initial full-load reader and an incremental binlog reader. Both use configurable transformation logic before documents reach Elasticsearch, while Redis stores the incremental reader's resume position.

  5. 05

    How It Works

    The primary sequence that moves work or data through the system.

    Flow

    Incremental change processing flow

    1. 01MySQL change

      Inserted or updated row

    2. 02Binlog event

      Row-based change record

    3. 03Transform / route

      Configured filters and handlers

    4. 04Elasticsearch write

      Transformed search document

    5. 05Checkpoint

      Redis filename and position

    • MySQL changeBinlog eventrecord
    • Binlog eventTransform / routefilter
    • Transform / routeElasticsearch writeindex
    • Elasticsearch writeCheckpointsave progress
    A MySQL row change becomes a binlog event, passes through configured filters and transformation handlers, and is written to Elasticsearch. After processed work is flushed, the reader records its binlog position in Redis so the stream can resume after a restart.

  6. 06

    Key Technical Decisions

    Problem
    A full data load does not keep Elasticsearch current after it finishes.
    Choice
    Support a full synchronization mode followed by binlog-based incremental updates.
    Why
    The full load establishes the index, while the binlog stream carries later inserts and updates without repeated table scans.
    Trade-off
    Incremental mode depends on row-based MySQL binlogs and must track stream progress across restarts.
    Problem
    An incremental reader needs to resume from the last processed binlog event after a restart.
    Choice
    Store the last binlog filename and position in Redis.
    Why
    A durable checkpoint lets the reader continue from known progress instead of restarting the stream blindly.
    Trade-off
    Redis becomes an additional runtime dependency for incremental synchronization.

  7. 07

    Challenges

    • Transforming MySQL rows and binlog events into Elasticsearch documents through configurable pipelines.
    • Keeping an early open-source data tool understandable and usable outside its original environment.

  8. 08

    Outcome

    • Published a reusable Python path for full and incremental MySQL-to-Elasticsearch synchronization.
    • Reached 281 GitHub stars by the 2026-08-25 snapshot, providing a public interest signal for the project.

    Related public work

  9. 09

    What I Learned

    Learned
    Incremental synchronization needs an explicit checkpoint boundary as well as a clear source-to-destination transformation path.
    Differently today
    I would separate source reading, transformation, checkpointing, and destination writing behind typed interfaces, add integration tests around restart behavior, and target a supported Python runtime.
    Reflection
    The lasting value of an early open-source tool is both the implementation and the public evidence that other developers found the problem worth solving.