How to Sync MySQL Data to Redis Cache: Two Practical Solutions and Open‑Source Tools

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

Solution 1 (UDF)

Scenario analysis: When MySQL data is modified, the same changes are written to Redis so that subsequent reads can be served from the cache.

Process overview:

Define a trigger in MySQL to monitor data operations.

The client (NodeServer) writes data to MySQL, causing the trigger to fire.

The trigger invokes a MySQL UDF function.

The UDF writes the data into Redis, achieving synchronization.

Solution analysis: This approach suits read‑heavy, write‑light scenarios without concurrent writes, because MySQL triggers can degrade performance if the table is frequently updated.

Demonstration case:

MySQL table definition (illustrated in image).

UDF parsing code (illustrated in image).

Trigger definition (illustrated in images).

Solution 2 (Binlog Parsing)

MySQL replication principle:

The master writes changes to the binary log (binlog).

The slave reads the binlog via an I/O thread, writes it to a relay log, and an SQL thread parses the relay log to apply changes.

Solution description: Treat MySQL as the master and Redis as the slave; when MySQL writes data, parse its binlog and write the parsed data into Redis, achieving synchronization.

Example: a cloud database instance acts as master, a local database as slave; the local instance parses the binlog and writes data to Redis for client reads.

Difficulty: Parsing MySQL binlog requires deep understanding of binlog formats (Statement/Row/Mixed) and is labor‑intensive.

Canal Open‑Source Technology

Canal is an Alibaba open‑source project written in Java that parses incremental database logs, currently supporting MySQL and MariaDB.

Repository: https://github.com/liukelin/canal_mysql_nosql_sync

Working principle (mimicking MySQL replication):

Canal pretends to be a MySQL slave, sending a dump request to the master.

The master pushes its binary log to Canal.

Canal parses the binary log byte stream.

Architecture components:

eventParser – handles data source access and simulates the slave protocol.

eventSink – filters, transforms, and distributes parsed data.

eventStore – stores 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:

parse – parses MySQL binlog and feeds data to sink.

sink – filters, transforms, and distributes data.

store – persists the parsed data.

Custom code then reads from store and writes to Redis.

Additional Note

While the article focuses on syncing MySQL to cache, some developers reverse the flow—writing first to Redis then syncing to MySQL. This approach is unsafe because a Redis outage can cause data loss.