Official account: Java Xiaokaxiu, website: Javaxks.com

Author: jiangnan, dong, less links: dongshao.blog.csdn.net/article/det…

This article introduces two ways to synchronize MySQL and Redis cache

  • Solution 1: Refresh Redis through MySQL automatic synchronization, MySQL trigger +UDF function implementation

  • Solution 2: Parse MySQL’s binlog implementation and synchronize data from the database to Redis

I. Plan 1 (UDF)

Scenario analysis:

When we perform data operations on the MySQL database, the corresponding data will be synchronized to Redis. After the synchronization to Redis, the query operation will be searched from Redis

The process is roughly as follows:

  • In MySQL, set a Trigger on the data to be operated on
  • When the NodeServer writes data to MySQL, the trigger is triggered, and then the UDF function of MySQL is called
  • UDF functions can write data to Redis for synchronization

Scheme analysis:

  • This scheme is suitable for scenarios where read more and write less, and there is no concurrent write

  • Because MySQL triggers are inherently inefficient, this scheme is not appropriate if a table is being manipulated frequently

The demo case

  • Here is the MySQL table

  • Here is the parsing code for the UDF

  • Define the corresponding trigger

2. Solution 2 (Parsing binlog)

  • Before introducing scheme 2, let’s introduce the principle of MySQL replication, as shown in the following figure:

    • The master server manipulates the data and writes it to the Bin log

    • Call the I/O thread from the server to read the Bin log of the master server and write it to its own Relay log. Then call the SQL thread to parse the data from the Relay log and synchronize the data to its own database

  • Plan 2 is:

    • The MySQL replication process can be summed up in one word: read the master server Bin log data from the server, so as to synchronize to its own database

    • The same is true of our scheme 2, which is to change the primary server to MySQL and the secondary server to Redis (as shown in the figure below) in concept. When data is written in MySQL, we parse the Bin log of MySQL and then write the parsed data to Redis to achieve synchronization effect

  • For example, here is an example of a cloud database:

    • Cloud databases and local databases are in a master-slave relationship. The cloud database serves as the primary database to provide writes, and the local database serves as the secondary database to read data from the primary database

    • After the local database reads the data, the Bin log is parsed, and the data is written and synchronized to Redis. The client then reads the data from Redis

  • The difficulty of this technical solution lies in: how to parse MySQL Bin Log. However, this requires a deep understanding of the binlog file and MySQL, and because binlogs are in various forms, such as Statement, Row, and Mixedlevel, it is quite a lot of work to analyze binlog implementation synchronization

Canal Open Source technology

  • Canal is an open source project owned by Alibaba and developed purely in Java. Incremental data subscription & consumption based on database incremental log parsing, currently mainly supported by MySQL (also supported by mariaDB)

  • The open source reference address is github.com/liukelin/ca…

  • How it works (imitating MySQL replication) :

    • Canal emulated the interaction protocol of the mysql slave, disguised itself as the mysql slave, and sent the dump protocol to the mysql master

    • Mysql master receives dump request and starts pushing binary log to slave (canal)

    • Canal parses binary log objects (originally byte streams)

  • Architecture:

    • Server represents a running instance of Canal, corresponding to a JVM

    • Instance corresponds to a data queue (1 server corresponds to 1.. N the instance)

    • The instance module:

      EventParser (Data source access, simulates slave and Master interaction, protocol parsing) Distribution work eventStore metaManager Incremental subscription & Consumption information ManagerCopy the code

  • The general analysis process is as follows:

    • Parse parse the MySQL Bin log and put the data into sink

    • Sink filters, processes and distributes data

    • Store Read parsed data from sink and store it

    • Then use design code to synchronize data from store to Redis

    • Parse /sink is wrapped by the framework, and what we do is the store data reading step

  • More information about Cancl can be searched on Baidu
  • The following is a running topology

  • MySQL table synchronization adopts responsibility chain mode, each table corresponds to a Filter. For example, the class design used in Zvsync is as follows:

  • Here are the classes that are used in zvSync concretely, just to add and delete tables whenever they are added or deleted

Three, additional

  • Everything described above in this article has been synchronized from MySQL to cache. But in real development someone might use the following:

    • When the client has data, it saves it to Redis and then synchronizes it to MySQL

    • This scheme is also inherently insecure/unreliable, so if Redis goes down or fails for a short time, data will be lost