How to Sync MySQL Data to Redis: Triggers, UDFs, and Binlog Parsing with Canal

Introduction

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

Solution 1: MySQL Trigger + UDF

When data is modified in MySQL, a trigger fires and calls a custom UDF that writes the changed data into Redis, allowing subsequent reads to be served from the cache. This method is suitable for read‑heavy, write‑light scenarios and avoids concurrency issues, but it can reduce performance if the table is frequently updated.

Typical workflow:

Define a trigger on the target MySQL table.

The trigger invokes the UDF after each INSERT/UPDATE/DELETE.

The UDF connects to Redis and writes the changed record.

Advantages: simple architecture, immediate cache update.

Limitations: triggers add overhead; not ideal for high‑frequency write tables.

UDF implementation (code shown as image):

Trigger definition examples (images):

Solution 2: Binlog Parsing

This approach leverages MySQL’s binary log replication mechanism. The master writes changes to the binlog; a slave (or a custom parser) reads the binlog and applies the changes to Redis.

Workflow:

The master MySQL server writes data modifications to the binlog.

An I/O thread on the replica reads the binlog and stores it in a relay log.

An SQL thread parses the relay log and replays the changes.

In our adaptation, we treat Redis as the replica: when MySQL writes data, we parse the binlog and push the changes into Redis.

Example with a cloud database acting as the master and a local database as the slave, where the local instance parses the binlog and syncs data to Redis for fast reads.

Parsing binlogs is complex because they can be in statement, row, or mixed format, requiring deep knowledge of MySQL internals.

Canal Open‑Source Solution

Canal, an open‑source project from Alibaba, mimics a MySQL slave to capture binlog events and provides them to downstream consumers. It supports MySQL (and MariaDB) and offers a Java‑based API.

Key components:

eventParser – handles the slave protocol and parses raw binlog bytes.

eventSink – filters, transforms, and routes events.

eventStore – persists processed events.

metaManager – manages subscription and consumption metadata.

server – a Canal instance (one JVM).

instance – a logical data queue within a server.

Processing flow:

parse – reads MySQL binlog and pushes events to the sink.

sink – filters and enriches events.

store – persists the final data.

Application code reads from the store and writes to Redis.

Additional Considerations

Some teams reverse the flow—writing first to Redis and later syncing to MySQL—but this is risky because a Redis outage can cause data loss.