MySQL and Redis cache synchronization scheme

This article introduces two solutions for the synchronization of MySQL and Redis caches

• Solution 1: Through MySQL automatic synchronization refresh Redis, MySQL trigger + UDF function to achieve
• Solution 2: Analyze the binlog implementation of MySQL and synchronize the data in the database to Redis

Option 1 (UDF)

• Scenario analysis: When we MySQL data library, we will synchronize the corresponding data to Redis at the same time. After synchronizing to Redis, the query operation will be searched from Redis
• The process is roughly as follows:
• Set the trigger Trigger for the data to be operated in MySQL, and monitor the operation
• When the client (NodeServer) writes data to MySQL, the trigger will be triggered. After the trigger, the MySQLUDF function will be called
• UDF function can write data to Redis, so as to achieve the effect of synchronization

• case analysis:
• This scheme is suitable for scenarios where there is more reading and less writing, and there is no concurrent writing
• Because MySQL trigger itself will cause a decrease in efficiency, if a table is frequently operated, this solution shows that it is inappropriate

Demo case

Below is the MySQL table

Below is the parsing code of UDF

Define the corresponding trigger

Scenario 2 (parse binlog)

Before introducing Option 2, let's first introduce the principle of MySQL replication, as shown in the following figure:

• The main server operates the data and writes the data into the Bin log
• Call the I/O thread from the server to read the Bin log of the main server and write it to its own Relay log, and then call the SQL thread to parse the data from the Relay log, thereby synchronizing to its own database

Option 2 is:

• The entire replication process of MySQL above can be summarized in one sentence, that is: read the data in the Bin log of the main server from the server and synchronize it to its own database
• The same is true for our solution 2, which is conceptually changing the master server to MySQL and the slave server to Redis (as shown in the figure below). When there is data to write in MySQL, we parse the MySQL Bin log, and then change The parsed data is written to Redis to achieve the effect of synchronization. (Search for the technical road of migrant workers in the official account, reply "1024", and give you a technical treasure)

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

• The cloud database has a master-slave relationship with the local database. The cloud database serves as the main database for writing, and the local database serves as the slave database to read data from the main database.
• After the local database reads the data, it parses the Bin log, then writes and writes the data to Redis, and then the client reads the data from Redis

The difficulty of this technical solution lies in: how to parse MySQL Bin Log. But this requires a very in-depth understanding of binlog files and MySQL. At the same time, because binlog has multiple 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 under Alibaba, developed in pure Java. Based on database incremental log analysis, provide incremental data subscription & consumption, currently mainly supports MySQL (also supports mariaDB)

The open source reference address is: https://github.com/liukelin/canal \_mysql\_nosql\_sync

Working principle (imitating MySQL replication):

• Canal simulates the interactive protocol of mysql slave, pretends to be mysql slave, and sends dump protocol to mysql master
• The mysql master receives the dump request and starts to push the binary log to the slave (that is, canal)
• Canal parses the binary log object (original byte stream)

Architecture:

• eventParser (data source access, simulated slave protocol and master interaction, protocol analysis)
• eventSink (Parser and Store linker, data filtering, processing, and distribution work)
• eventStore (data storage)
• metaManager (Incremental Subscription & Consumer Information Manager)
• server represents a running instance of canal, 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 parses MySQL Bin log, and then puts the data into the sink
• sink filters, processes, and distributes data
• The store reads the parsed data from the sink and stores it
• Then use the design code to write the data in the store synchronously into Redis.
• Among them, parse/sink is encapsulated by the framework, what we do is the step of reading the store data

More about Cancl can be searched on Baidu

Below is the running topology diagram

The synchronization of MySQL tables adopts the chain of responsibility mode, and each table corresponds to a Filter. For example, the classes to be used in zvsync are designed as follows:

The following are the classes to be used in the materialized zvsync. Whenever a table is added or deleted, just add and delete directly.

Attach

All of the above in this article are synchronized from MySQL to the cache. However, in actual development, some people may use the following scheme: After the client has data, save it in Redis first, and then synchronize it to MySQL. This scheme itself is also unsafe/unreliable, so if Redis There is a brief downtime or failure, then data will be lost

Author | Jiangnan, Dong Shao
Source| https://dongshao.blog.csdn.net/article/details/107190925