Binlog mining dictionary implementation method

Abstract

The invention discloses a binlog mining dictionary implementation method which is characterized by comprising the following steps: step 1, before mining, carrying out initialization preparation action on a dictionary database, the initialization preparation action comprising deploying the dictionary database, checking a source database and a database table structure of the dictionary database; step 2, capturing a ddl record in a replay binlog mining process; and step 3, performing backtracking mining at a specified position of the library table. According to the invention, the dictionary information in the mining process is obtained at extremely low cost, the dictionary database is additionally introduced to serve as a persistent layer, and the ddl operation is replayed to the dictionary database, so that the dictionary state of the database at the mining time point can be restored, and the problem that the dictionary and the mining log progress are consistent is guaranteed. The problems that log mining dictionary information cannot be obtained before the existing mysql8.0 and the overhead of binlog recording dictionary information after the existing mysql8.0 is too large are solved.

Description

technical field [0001] The invention relates to the field of analysis and application of relational database change logs, in particular to a method for realizing binlog log mining dictionaries synchronized with mining progress. Background technique [0002] With the vigorous development of the Internet, more and more companies use mysql database. In the process of using mysql, binlog logs are often captured and analyzed for change data, such as data synchronization between different data centers mysql. For versions prior to mysql8.0, after each data modification operation, the binlog log file will record the data change information, but will not record the dictionary information corresponding to each column of data, such as column name and column type, and mysql8 After .0, even if the parameters for recording dictionary information are configured, the dictionary information will be recorded for each change operation, which will cause excessive disk space usage. [0003] Fa...

AI Technical Summary

Problems solved by technology

For versions prior to mysql8.0, after each data modification operation, the binlog log file will record the data change information, but will not record the dictionary information corresponding to each column of data, such as column name and column type, and mysql8 After .0, even if the parameters for recording dictionary information are configured, the dictionary information will be recorded for each change operation, which will cause excessive disk space usage

[0003] Faced with this problem, you can query in real time to obtain the dictionary of the current table and load it into the cache, but the dictionary data cannot be consistent with the binlog mining progress. If there is a delay or stop of data mining, the mined data And the dictionary information of the real-time acquisition table may be misplaced, resulting in incorrect matching of mining information

[0004] The existing technology "mysqlbinlog performs data recovery (redo) based on a certain offset" https: / / www.cnblogs.com / chuanzhang053 / p / 8874951.html can only realize the recovery and synchronization of mysql data, but cannot Obtaining the dictionary information corresponding to the mining information does not achieve the effect required for log mining analysis and application

In addition, the existing technology "mysql8.0 new parameter binlog_row_metadata_" https: / / www.mytecdb.com / blogDetail.php? id=141, although binlog_row_metadata is set to FULL, dictionary information can be recorded, but it can only be used after mysql8.0, and if the dictionary information is recorded, the binlog log will become huge, occupying disk, affecting the performance of the database, etc.

Images