Synchronization scheme of MySQL and redis cache


Two schemes of MySQL and redis cache synchronization

Scheme 1: refresh redis automatically and synchronously through mysql, and implement MySQL trigger + UDF function

Scheme 2: parse the binlog implementation of MySQL and synchronize the data in the database to redis

1、 Option 1 (UDF)
Scenario analysis: when we operate on the MySQL database, we synchronize the corresponding data to redis at the same time. After the data is synchronized to redis, the query operation is searched from redis

The process is as follows

In mysql, set trigger trigger for the data to be operated, and monitor the operation
When the client (NodeServer) writes data to MySQL, the trigger is triggered, and the UDF function of MySQL is called after triggering.
UDF function can write data to redis to achieve the effect of synchronization

Step by step, take you into the world of netty!

Scheme analysis:
This scheme is suitable for the situation of more reading and less writing, and no concurrent writing
Because MySQL trigger itself will reduce the efficiency, if a table is often operated, this scheme is not suitable
Demonstration case

Here is the MySQL table

Here is the UDF parsing code

Who moved my mainframe? Flexible use of history command

Define the corresponding trigger



2、 Scheme 2 (parsing binlog)
Before introducing scheme 2, let’s first introduce the principle of MySQL replication, as shown in the following figure:

The master server operates the data and writes it to the bin log
The I / O thread is called from the server to read the bin log of the master server and write it to its own relay log. Then the SQL thread is called to parse the data from the relay log and synchronize it to its own database

Join hands with Alibaba cloud to send a wave of super welfare! There are not only low-cost ECs, but also mechanical keyboard, airpods pro and other good gifts!

Scheme 2 is as follows:

The whole replication process of MySQL can be summarized as follows: read the data in the bin log of the main server from the server, and then synchronize it to its own database
The same is true in our scheme 2, which is to change the master server to MySQL and the slave server to redis (as shown in the figure below). When there is data written in mysql, we will parse the bin log of MySQL, and then write the parsed data to redis, so as to achieve the effect of synchronization

For example, the following is an example of cloud database analysis:

Cloud database and local database are master-slave relationship. As the master database, cloud database mainly provides write services, while the local database reads data from the master database as the slave database
After the local database reads the data, it parses the bin log, writes and synchronizes the data to redis, and then the client reads the data from redis

The difficulty of this technical solution is how to parse the bin log of MySQL. However, this requires a very deep understanding of binlog files and mysql. At the same time, because binlog has many forms of statement / row / mixedlevel, the workload of analyzing binlog to achieve synchronization is very large
Canal open source technology
Canal is an open source project of Alibaba, which is developed in pure Java. Based on database incremental log parsing, it provides incremental data subscription & consumption. Currently, it mainly supports MySQL (also supports MariaDB)

Open source reference addresses…

How it works (mimicking MySQL replication)

Canal simulates the interaction protocol of MySQL slave, disguises itself as MySQL slave, and sends dump protocol to MySQL master
MySQL master receives the dump request and starts to push the binary log to slave (that is, canal)
Canal parses binary log object (originally byte stream)

Ten thousand words long article: help you break the technical barrier of Java NiO

Event parser (data source access, simulate slave protocol, interact with master, protocol analysis)
Event sink (parser and store linker for data filtering, processing and distribution)
Eventstore (data store)
Metamanager (incremental subscription & consumption information manager)
Server represents a canal running instance, corresponding to a JVM
Instance corresponds to a data queue (1 server corresponds to 1.. n instances)
Instance module:

The general analysis process is as follows
Parse the bin log of MySQL, and then put the data into sink
Sink filters, processes and distributes data
Store reads the parsed data from sink and stores it
Then you can use the design code to write the data in the store to redis synchronously
Among them, parse / sink is encapsulated by the framework, and what we do is the data reading step of the store

More about cancl can be searched by Baidu
Here is the running topology

Strange boss: barber shop boy to Ali P10

MySQL table synchronization adopts responsibility chain mode, and each table corresponds to a filter. For example, the class design to be used in zvsync is as follows:

The following are the specific classes to be used in zvsync. Whenever you add or delete a table, you can add or delete it directly

3、 Additional
In this paper, the above is from MySQL synchronization to cache. But in the actual development, someone may use the following scheme: after the client has data, it will be saved to redis first, and then synchronized to MySQL. This scheme itself is not safe / reliable, so if redis has a short-term downtime or failure, it will lose data


This work adoptsCC agreementReprint must indicate the author and the link of this article

Recommended Today

Implementation example of go operation etcd

etcdIt is an open-source, distributed key value pair data storage system, which provides shared configuration, service registration and discovery. This paper mainly introduces the installation and use of etcd. Etcdetcd introduction etcdIt is an open source and highly available distributed key value storage system developed with go language, which can be used to configure sharing […]