Shulou(Shulou.com)05/31 Report--

The main content of this article is "how to fix the problem of MySQL8.x binlog dislocation". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to fix the problem of parsing MySQL8.x binlog dislocation.

Write at the front

MySQL is one of the most widely used relational databases in the Internet industry, and MySQL is open source. The in-depth study of MySQL can deepen our understanding of the principle of database. Since the open source mykit-data, many partners have tried it out and reported that mykit-data cannot correctly parse the binlog of MySQL8. So I tested that when mykit-data parses the binlog of MySQL5.x, there is no problem, and it can correctly parse the resulting data. However, when parsing the binlog of MySQL8.x, there is always a 12-bit difference between the number of binlog log digits and the number of binlog log digits, resulting in resolution failure.

The article has been included in:

Https://github.com/sunshinelyz/technology-binghe

Https://gitee.com/binghe001/technology-binghe

Problem repair

Today is too late, I am still studying the source code of MySQL 8.0.20, the problem repair process to write a detailed article to share with my friends. Here, I will directly say how I solved this problem.

There is always a bit deviation between the parsing result of MySQL5.x binlog and that of MySQL8.x binlog. There is no problem for the original code of the framework to parse MySQL5.x directly. There is a problem of bit dislocation when parsing MySQL8.x.

During this period, I flipped through almost all the official documents of MySQL and rewrote the function of parsing binlog logs in mykit-data. Parsing MySQL5.x is no problem, parsing MySQL8.x is still misplaced.

What went wrong? When I was at a loss to solve the problem, I suddenly thought of a train of thought: isn't it always misplaced to solve MySQL8.x binlog? Then I will read out the extra bits of binlog data and directly ignore it, so that the subsequent parsing operations of binlog will not be aligned.

Give it a try, so I added the following code to the source code of the mykit-data framework.

The above code is an encapsulation for parsing the checksum reading of MySQL binlog bits. When the number of binlog bits read does not reach the limit for reading, the data of binlog is read until the read binlog bits reach the limit for reading. Specific internal logic, friends can read the source code of mykit-data.

After adding this logic, we tested and verified that the binlog parsing the MySQL 8.x database was successful! The problem that has plagued me for several days has been solved inadvertently!

From the result of solving this problem, the binlog of MySQL8.x is essentially longer than the binlog bits of MySQL5.x, and the identifiers used to separate different event bits are spliced in the middle. When parsing the binlog log of MySQL8.x, we can directly ignore these identifiers that separate different event bits, so that the parsing bits of binlog can be aligned so that the next event can be parsed correctly. And this treatment will not affect the parsing result.

Most of the time, when you are struggling to solve a problem and can't find a solution, you will inadvertently solve this thorny problem at some inadvertent moment. but the premise is that you need to deeply understand its principle and try various ways and methods to solve it!

About mykit-data

Mykit-data is a completely open source data heterogeneous middleware, which supports plug-in and visual data heterogeneity framework, and supports full, real-time / timing incremental data synchronization from MySQL to MySQL, MySQL to Oracle, Oracle to MySQL, Oracle to Oracle. Complete plug-in, visual operation. Through the log to avoid data loss in the synchronization process as much as possible. Support failed retry, manual intervention, support for viewing synchronized data and detailed log information.

MySQL5.x, MySQL8.x,Oracle 11g and above are currently supported. More heterogeneous data sources will be supported in the form of plug-ins later. For example, data sources such as SQL Server, Postgre SQL, Redis, ES, Hadoop, HBase, MongoDB, TiDB will be added later.

At this point, I believe you have a deeper understanding of "how to fix and analyze the problem of MySQL8.x binlog dislocation". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.