How to Keep MySQL and Redis In Sync: Two Practical Solutions Explained

This article introduces two approaches for synchronizing MySQL data with a Redis cache.

Solution 1: MySQL Trigger + UDF

When data is modified in MySQL, a trigger monitors the operation. The client (NodeServer) writes data to MySQL, the trigger fires, and calls a custom UDF function. The UDF writes the data directly into Redis, achieving real‑time synchronization.

Advantages: suitable for read‑heavy, write‑light scenarios without concurrent writes. Drawbacks: MySQL triggers add overhead; frequent table updates make this approach inefficient.

Solution 2: Binlog Parsing

MySQL replication works by the master writing changes to a binary log (binlog). The slave reads the binlog via an I/O thread, stores it in a relay log, and an SQL thread applies the changes to its own database.

In this solution, the master is MySQL and the “slave” is Redis. When MySQL writes data, we parse the binlog and push the parsed events into Redis, keeping the cache up‑to‑date.

Key difficulty: parsing MySQL binlog requires deep knowledge of binlog formats (Statement, Row, Mixed) and substantial implementation effort.

Canal Open‑Source Solution

Canal, an Alibaba open‑source project written in Java, mimics a MySQL slave to subscribe to and consume incremental data from MySQL binlog. It supports MySQL and MariaDB.

Architecture components:

eventParser – simulates slave protocol, parses master packets.

eventSink – filters, transforms, and distributes parsed data.

eventStore – persists the processed data.

metaManager – manages subscription and consumption metadata.

server – a Canal runtime instance (one JVM).

instance – a data queue; one server can host multiple instances.

Parsing flow:

Canal establishes a dump connection to the MySQL master.

The master pushes binary log events to Canal.

Canal parses the binary log bytes into structured events.

Parsed events pass through the sink for filtering and transformation.

The store persists the final data, which can then be written to Redis.

Canal’s design follows a responsibility‑chain pattern where each database table corresponds to a filter, simplifying the addition or removal of tables.

Overall, the two MySQL‑to‑Redis synchronization strategies and the Canal framework provide flexible options depending on read/write patterns, performance requirements, and development resources.