Multi-table data synchronization method and device, medium and product

Abstract

The embodiment of the invention relates to the field of computer data processing, and discloses a multi-table data synchronization method and device, a medium and a product. Configuring a Canal platform to monitor a Binlog of the relational database, and obtaining an increment change of the relational database in real time; the relational database comprises a Mysql database and an Oracle database; a Canal instance is consumed through an application program, and according to the content of the Binlog, an operation type of an insert, an operation type of an update and an operation type of a release are distinguished; the method comprises the steps that an index is created in Elasticsearch, a main table field and an association table field are defined, and the association table field adopts a nested type; and synchronizing the multi-table data in the relational database to a corresponding index of the Elasticsearch by using a script and an upsert function. The problem that the throughput rate has a bottleneck when data is changed for consumption can be at least solved.

Description

Technical Field

[0001] The present application relates to the field of computer data processing, and in particular to a multi-table data synchronization method, device, medium and product. Background Art

[0002] In modern computer data processing, relational databases (such as MySQL databases) are often widely used in actual business scenarios. When such databases process large-scale data, joint table query operations often encounter performance bottlenecks due to the limitations of their structured data storage design. Specifically, when the amount of data is large, the efficiency of joint table query will be significantly reduced, resulting in excessive consumption of physical resources of the database. In extreme cases, this situation may cause the database to crash, thereby affecting the normal operation of all upper-level application systems that rely on the database.

[0003] In order to optimize the performance of joint table queries, wide tables are usually considered. However, relational databases have limited support for one-to-many relationship queries (although MySQL 5.7.8 supports JSON fields and can use virtual fields for queries, and Oracle's higher versions also have similar support, but support for JSONArray is poor and performance is average). In this context, Elasticsearch (ES), as an open source search engine based on Apache Lucene (TM), provides an effective solution. ES can process unstructured data, use JSON as the document serialization format, and naturally support the storage of nested structures and arrays, thereby enabling the storage and query of one-to-many relationships.

[0004] However, in actual applications, when multiple tables need to be synchronized to one ES index, especially when one-to-many or nested relationships are involved, existing synchronization methods often have bottlenecks. Traditional methods usually pull Mysql change data in batches at regular intervals or use Canal to pull and monitor Binlog content, and then use the data ID set to query related fields from Mysql and assemble the data to synchronize to ES. This method requires additional queries of a large amount of data outside of Binlog, resulting in a large data synchronization granularity, which is difficult to meet the real-time requirements of data, and there is also a bottleneck in data throughput. Summary of the invention

[0005] One purpose of the present application is to provide a multi-table data synchronization method, device, medium and product, at least to solve the problem of throughput bottleneck when changing data for consumption.

[0006] To achieve the above objectives, some embodiments of the present application provide the following aspects:

[0007] In the first aspect, some embodiments of the present application also provide a multi-table data synchronization method, the method comprising configuring the Canal platform to listen to the Binlog log of a relational database, and obtaining the incremental changes of the relational database in real time; the relational database comprises a Mysql database; consuming the Canal instance through an application, distinguishing insert, update and delete operation types according to the Binlog log content; creating an index in Elasticsearch, defining the main table fields and associated table fields, and the associated table fields adopt a nested type; using a script script and an upsert function to synchronize the multi-table data in the relational database to the corresponding index of Elasticsearch.

[0008] In a second aspect, some embodiments of the present application further provide an electronic device, comprising: one or more processors; and a memory storing computer program instructions, wherein the computer program instructions, when executed, cause the processor to perform the steps of the method described above.

[0009] In a third aspect, some embodiments of the present application further provide a computer-readable medium having computer program instructions stored thereon, wherein the computer program instructions can be executed by a processor to implement the method as described above.

[0010] In a fourth aspect, some embodiments of the present application further provide a computer program product, comprising a computer program / instruction, which implements the steps of the method described above when executed by a processor.

[0011] Compared with related technologies, the embodiment of the present application provides a solution based on the Canal platform to improve the speed of synchronizing multi-table data to Elasticsearch. By refining the synchronization granularity, using the nested type and script of Elasticsearch, and implementing multi-threaded parallel processing, the speed and efficiency of data synchronization are greatly improved. At the same time, the solution simplifies the data synchronization process, ensures the consistency and idempotence of data, supports complex multi-table associations, and has good scalability. These beneficial effects significantly improve the real-time and reliability of large-scale data synchronization. BRIEF DESCRIPTION OF THE DRAWINGS

[0012] One or more embodiments are exemplarily described by pictures in the corresponding drawings, and these exemplified descriptions do not constitute limitations on the embodiments. Elements with the same reference numerals in the drawings represent similar elements, and unless otherwise stated, the figures in the drawings do not constitute proportional limitations.

[0013] Figure 1 A flowchart of a multi-table data synchronization method provided according to an embodiment of the present application;

[0014] Figure 2 This is an architecture diagram of a multi-table data synchronization system provided according to an embodiment of the present application;

[0015] Figure 3 A flowchart of another multi-table data synchronization method provided according to an embodiment of the present application;

[0016] Figure 4 The present invention is a schematic diagram of the structure of an electronic device provided according to an embodiment of the present application. DETAILED DESCRIPTION

[0017] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0018] First embodiment

[0019] The first embodiment of the present application relates to a multi-table data synchronization method, which improves the speed of synchronizing multi-table data to Elasticsearch (hereinafter referred to as "ES") based on the Canal platform. The method simulates the Mysql database Slave interaction protocol to pull and parse Binlog logs to achieve efficient parallel synchronization of multi-table data. Figure 1 As shown, the method may include the following steps:

[0020] S101, configure the Canal platform to monitor the Binlog log of the relational database, and obtain the incremental changes of the relational database in real time; the relational database includes a MySQL database;

[0021] S102, consuming the Canal instance through the application, and distinguishing the insert, update, and delete operation types according to the Binlog log content;

[0022] S103, creating an index in Elasticsearch, defining the main table fields and the associated table fields, wherein the associated table fields are of the nested type;

[0023] S104, using a script and an upsert function, synchronizing the multi-table data in the relational database to the corresponding index of Elasticsearch.

[0024] First, the system monitors the Binlog log of the MySQL database through the Canal platform to obtain the incremental changes of the database; the relational database also includes the Oracle database, and the Ogg platform can be used to monitor the log content of the Oracle database. The following implementation is mainly explained around the MySQL database. Canal is a specially designed data synchronization tool. By simulating the Slave protocol of MySQL, it can accurately pull Binlog data and push this data to the consumer end in real time. The core functions of Canal include incremental data subscription and consumption, and can distinguish the three types of operations in the database: insert, update, and delete, providing a basis for subsequent data processing.

[0025] In ES, this embodiment uses the nested type to store the data of the association table. ES provides two types of storage objects: object and nested. The nested type is a specialized version of the object data type, allowing object arrays to be indexed in a way that can be queried independently of each other. The object type will be flattened during storage, causing the original correlation between fields to be lost and unable to fully represent complex data relationships. Therefore, the nested type is more suitable for storing complex data structures containing one-to-many relationships.

[0026] In actual operation, the application consumes the Binlog content pulled from MySQL by listening to the instance provided by Canal. For each Binlog record, the application first determines its type (insert, update, delete) and executes the corresponding synchronization logic.

[0027] New operation (insert): When new data is detected, the application uses the ES_update API combined with the upsert function to write the new data into the ES index. If the document does not exist, upsert will automatically create a new document; if the document already exists, it will update the fields in the document. For nested fields, the data will be added as an array element to the existing data structure.

[0028] Update operation (update): When data update is detected, the application uses script to modify the document content in ES. The script can use the update API to change the content of the _source field and is expressed in the form of ctx._source. Before ES 6.X, the default scripting language was Groove, but it has been deprecated due to security issues and is now replaced by painless scripting language. Painless has the characteristics of high performance and security, and supports Java lambda expressions, which can flexibly handle complex update logic.

[0029] Delete operation: When data deletion is detected, the application also uses scripts to operate the document in ES. For nested fields, the delete operation removes the specified array element or sets the field to empty, thereby maintaining data consistency.

[0030] It is not difficult to find that compared with the related art, in the solution provided by the embodiment of the present application, by independently distributing the data of different tables to multiple consumer threads, a large number of data changes can be processed simultaneously. This not only improves the speed of data synchronization, but also ensures the consistency and integrity of the data. In addition, there is no need to rely on additional data queries during data synchronization, and all synchronization operations can be completed using the Binlog content. This design simplifies the data synchronization process and reduces the complexity and resource consumption of the system. In summary, this embodiment introduces the Canal platform and its efficient Binlog data processing mechanism, combined with Elasticsearch's nested type storage structure and painless scripting language, to achieve efficient synchronization of multi-table data, which is suitable for business scenarios that need to process large amounts of complex data.

[0031] Second embodiment

[0032] The second embodiment of the present application relates to a multi-table data synchronization system based on the Canal platform. The second implementation is an improvement on the first embodiment, and the specific improvements are:

[0033] like Figure 2 As shown in the figure, the data source of the system is a MySQL database. In business systems, MySQL is used to store structured data, and usually involves joint table queries of multiple tables. As the amount of data increases, single table or joint table queries may face performance bottlenecks, so it is necessary to synchronize this data to ES to take advantage of ES's fast query capabilities.

[0034] When data in MySQL changes (such as insert, update, and delete operations), these changes will be recorded in the Binlog log. Binlog is the binary log file of MySQL, which records all changes to the database data. In order to obtain these changes in real time, the system uses the Canal tool to simulate the Slave protocol of MySQL to pull and parse the Binlog content.

[0035] As the core component of data synchronization, Canal monitors and parses the MySQL Binlog log by simulating the Slave interaction protocol of the MySQL database. Canal generates parsed Binlog data and provides it to subsequent consumer applications for processing. The parsed content includes all incremental data changes, such as insert, update, delete operation types and corresponding data content.

[0036] The consumer application is responsible for receiving and processing the Binlog data delivered by the Canal. Specifically, the application distributes the data to multiple consumer threads according to different table names and operation types. Each consumer thread is responsible for processing data changes in a specific table to ensure parallel processing and orderly consumption of data. The allocation of consumer threads is based on unique identifiers such as primary key IDs to ensure data consistency.

[0037] When processing Binlog data, the consuming application executes different synchronization logics according to the operation type of the data:

[0038] New operation (insert): The system inserts new data into the ES index through the ES_update API combined with the upsert function. If the document does not exist, a new document is created; if the document already exists, the relevant fields in the document are updated.

[0039] Update operation: The system uses the script language painless to perform update operations. The script can perform complex update operations on the document content in ES, such as modifying the nested data structure.

[0040] Delete operation (delete): The system uses a script to delete a specific data item from ES. For nested fields, the script can accurately remove the specified element in the array or set the entire field to empty.

[0041] After the data is synchronized to ES, ES provides two storage object types: object and nested. This embodiment uses the nested type to store complex multi-table related data, especially one-to-many relationship data. The nested type allows the data to maintain integrity and nested structure when querying, avoiding the flattening problem of object type data.

[0042] It is not difficult to find that in the embodiment of the present application, through the above steps, the system realizes real-time data synchronization between Mysql and ES, ensuring the consistency and integrity of the data. The system can quickly synchronize data changes to ES without increasing the burden on the Mysql database, greatly improving the efficiency of data query and the response speed of the system.

[0043] Third embodiment

[0044] The third embodiment of the present application relates to a multi-table data synchronization method. The third implementation is an improvement on the first embodiment, and the specific improvements are:

[0045] like Figure 3 As shown in the figure, the process covers the synchronization of multi-table data from the MySQL database to Elasticsearch (hereinafter referred to as "ES"), which is divided into the processing of the main table and the associated table, and processes the insert, update and delete operations separately.

[0046] At the beginning, the process starts with listening to the Binlog log obtained by Canal. Binlog records all data changes in the MySQL database. Data change type identification, the system first identifies whether the source of the changed data is the main table or the associated table, and then further processes it according to the operation type (insert, update, delete).

[0047] The main table data processing includes: update operation, when the ES storage field is updated, determine whether the associated stock data ID is updated; when it is determined that the associated stock data ID is updated, query the associated stock data, encapsulate the entity, and use the _update API to update it according to the ES primary key ID. End the process in other cases.

[0048] Insert operation determines whether there is related stock data. If so, query the related stock data, encapsulate the entity, combine it with upsert, and use the _update API to update it according to the ES primary key ID. If there is no related stock data, encapsulate the entity, combine it with upsert, and use the _update API to update it according to the ES primary key ID.

[0049] Delete operation: use DELETE request to delete based on ES primary key ID.

[0050] The data processing of the associated table includes: delete operation, judging whether it is an array: when the table data is an array, the script removes the elements according to the primary key of the associated table, judging whether it is the stock data associated with the main table; when the table data is not an array, the script directly updates the field to null, judging whether it is the stock data associated with the main table. When it is the stock data associated with the main table, update it based on the primary key of the associated table, using updateByQuery (applicable to the case where the amount of associated data is not large, and the case of large data must be changed to batch query and batch update); when it is not the stock data associated with the main table, use the _update API to update it according to the ES primary key ID.

[0051] Insert operation, determine whether it is the stock data associated with the main table: when it is not the stock data associated with the main table, determine whether it is an array; when it is the stock data associated with the main table, end the process. When it is determined to be an array, the script determines whether the array already exists; when it is determined not to be an array, the script directly updates the field, encapsulates the entity, combines upsert execution, and uses the _update API to update according to the ES primary key ID. When the array does not exist, the script initializes the array and sets the elements; when the array exists, the script adds elements, encapsulates the entity, combines upsert execution, and uses the _update API to update according to the ES primary key ID.

[0052] The update operation determines whether the ES storage field is updated. When the ES storage field is updated, determine whether it is an array; if it is an array, the script locates the element according to the primary key of the associated table and updates it; if it is not an array, the script updates it according to the primary key. Determine whether it is the stock data associated with the main table. If it is the stock data associated with the main table, update it based on the primary key of the associated table and use updateByQuery; if it is not the stock data associated with the main table, use the _update API to update it according to the ES primary key ID.

[0053] It is not difficult to find that compared with related technologies, the entire process ensures that data changes in the Mysql database can be synchronized to the ES in real time, and maintains data consistency and integrity in the case of complex data structures. Through the careful processing of the main table and associated table data, especially the special processing of stock data and array types, this embodiment further improves the data synchronization efficiency and data management capabilities of the system.

[0054] Fourth embodiment

[0055] The fourth embodiment of the present application relates to a multi-table data synchronization method. The fourth implementation is an improvement based on the first embodiment, and the specific improvements are:

[0056] The following data model is used as an example.

[0057] User table (user)

[0058] id: primary key id of user information table

[0059] age: age

[0060] adminId: administrator id (primary key id of the administrator table)

[0061] id age adminId 10 24 15

[0062] Administrator table (admin)

[0063] id: primary key id of the administrator table

[0064] name: Administrator name

[0065]

[0066] User portrait table (userPortrait), the user table has a one-to-one relationship with it

[0067] id: the primary key id of the user portrait table (same as the primary key id of the user table)

[0068] Liveness: activity

[0069]

[0070] User receiving address table (userReceivingAddress), the user table has a one-to-many relationship with it.

[0071] id: primary key id of user delivery address table

[0072] userId: ID of the user table

[0073] name: name

[0074] phone: mobile phone number

[0075] address: address

[0076]

[0077] To design an ES index, first sort out all the fields used as query conditions in the main table and associated table, as well as some return result fields, based on the SQL of the MySQL joint table query. The fields of the main table are directly set as the primary fields in the ES index, and the primary key ID is also used as the "_id" field of the ES document, and the associated table is defined in the form of a nested type. Because in this solution, logical matching based on the primary key is required when adding, deleting, and modifying the associated table, the primary key ID field of the associated table is also required as the logical primary key of the nested field (defined in the solution). Because it is a logical primary key, fields other than the primary key ID can also be used in special scenarios, which is more flexible. For example, when the main table is one-to-many associated with the table, the data of the associated table is grouped by a certain field A in SQL and the latest one is taken. Then field A can be used as the logical primary key, and only the latest one is stored by continuously overwriting and updating, omitting the original grouping query steps in MySQL.

[0078] For the sake of convenience, here we build an ES index based on the complete data model of the example

[0079]

[0080]

[0081]

[0082] Configure the main table and related table involved in the joint table query to listen in the Canal platform, and then consume them through the application to obtain the parsed Binlog content.

[0083] When an application consumes Canal messages, it first distinguishes messages from different tables and delivers them to their respective consumer threads. In scenarios that require higher efficiency, it can further perform more detailed allocation based on the primary key ID of each table. Ultimately, it only needs to ensure that the Binlog content can be consumed independently and orderly from the dimension of each data item in each table.

[0084] After obtaining a specific Binlog content in the consumer thread, the corresponding field part in ES is determined by the table name, and then the corresponding incremental synchronization processing is performed on this part of the field according to different operation types (additional insert, update, deletion). This is also the core design of this solution to improve the speed of multi-table data synchronization with Elasticsearch. The details are as follows:

[0085] (1) The first dimension to consider is whether the table is a primary table or a related table.

[0086] Regardless of whether it is the main table or the associated table (the associated table associated with the main table ID), the basic method of insert operation is the same. Since it cannot be guaranteed that the first table consumed during the parallel consumption of insert operations on the main table and the associated table is the main table, the upsert feature of ES is used to create an ES document for the first insert operation, whether it is the main table or the associated table, and all subsequent ones are updated.

[0087] The insert operation of the main table uses the update API combined with the upsert function. If the document does not exist, the fields in the upsert are added. If it exists, the update operation is performed according to the local document parameter doc.

[0088] ##user table insert:

[0089]

[0090]

[0091] When the consumed Binlog is an update or delete operation, the fields of the main table are directly set as the main fields in the ES index, so the processing method is relatively simple. You can directly use the _updateAPI and DELETE request operations according to the "_id" of the ES document.

[0092] ##user table update:

[0093]

[0094] ##user table delete

[0095] DELETE es_user / _doc / 10

[0096] (2) The second dimension to consider is whether the primary table is one-to-one or one-to-many with respect to the associated table.

[0097] The insert operation of the associated table uses the update API combined with the script and upsert function. When the document does not exist, the fields in the upsert are added. If it exists, it is necessary to distinguish whether the main table is one-to-one or one-to-many with the associated table. The one-to-one relationship is relatively simple. The script is used to directly set the nested structure to the data content of this time. For a one-to-many relationship, the nested structure corresponding to the associated table stores an object array. The script is used to first determine whether the array already exists. If so, add the element. Otherwise, initialize the array and set the element.

[0098] When the consumed Binlog is a delete operation, the one-to-one relationship uses a script to directly empty the nested structure. For a one-to-many relationship, the script locates the specific element in the nested structure according to the logical primary key of the associated table and removes it.

[0099] When the consumed Binlog is an update operation, the update process is performed through the script according to the logical primary key of the associated table in the nested structure (one-to-many needs to locate the specific element).

[0100] ① The main table is one-to-one with the associated table: User portrait table

[0101] ##User portrait table insert:

[0102]

[0103]

[0104] ##User portrait table update:

[0105]

[0106] ##User portrait table delete:

[0107]

[0108]

[0109] ② The main table is one-to-many to the associated table: User delivery address table##User delivery address table insert:

[0110]

[0111]

[0112] ##User delivery address table update:

[0113]

[0114]

[0115] ##User delivery address table delete:

[0116]

[0117] (3) The third dimension to consider is whether the associated table is the stock data associated with the main table.

[0118] In fact, it is a question of who is associated with whom. There are two ways of association. One is that the main table is associated through the ID of the associated table. In this case, the associated table is the stock data for the main table. The other is that the associated table is associated through the ID of the main table. In this case, the main table is the stock data for the associated table. Because the subject here is the main table, it is sufficient to consider whether the associated table is the stock data for the main table from the perspective of the main table. In fact, the above-mentioned processing methods for associated tables are all for the latter case. The associated table is associated through the ID of the main table.

[0119] For the former case, the main table is associated through the ID of the associated table. First, there is no need to monitor the insert operation of the associated table. When monitoring the insert operation of the main table, query the data according to the ID of the associated table. When monitoring the update and delete operations of the associated table, use ES's updateByQuery API (suitable for cases where the amount of associated data is not large, and for larger cases, batch query and batch update should be used instead) to use the ID of the associated table as the query condition and use the script to update. There are also one-to-one and one-to-many considerations (one-to-many may be that the main table stores multiple IDs of the associated table separated by commas through one field, or uses an associated relationship table to store separately. In this case, it is necessary to monitor the associated relationship table). The script is the same as the scenario where the associated table is associated through the ID of the main table.

[0120] ##Administrator table update:

[0121]

[0122]

[0123] ##Administrator table delete:

[0124]

[0125]

[0126] It is not difficult to find that in the embodiments of the present application, by optimizing the Elasticsearch (ES) index design and data synchronization strategy, efficient parallel synchronization of multi-table data is achieved. In particular, by using the nested structure to store one-to-many data, dynamically updating data using scripts and upsert functions, and carefully processing the main table and associated tables, the consistency and real-time nature of the data are ensured. This not only improves the data synchronization speed, but also simplifies the management of complex data structures and improves the stability and scalability of the system.

[0127] The step division of the above methods is only for the purpose of clear description. When implemented, they can be combined into one step or some steps can be split and decomposed into multiple steps. As long as they include the same logical relationship, they are all within the scope of protection of this patent; adding insignificant modifications to the algorithm or process or introducing insignificant designs without changing the core design of the algorithm and process are all within the scope of protection of this patent.

[0128] In addition, some embodiments of the present application also provide an electronic device. The electronic device may be a digital computer in various forms, such as a laptop computer, a desktop computer, a workbench, a personal digital assistant, a server, a blade server, a mainframe computer, etc. The electronic device may also be a mobile device in various forms, such as a personal digital processing, a cellular phone, a smart phone, a wearable device, and other similar computing devices.

[0129] The electronic device includes: one or more processors; and a memory storing computer program instructions, wherein when the computer program instructions are executed, the processor executes the steps of the method provided in any one or more of the above embodiments. Figure 4 An exemplary structural diagram of the electronic device is disclosed. Figure 4 As shown, the electronic device includes: one or more processors 1101, memory 1102, and interfaces for connecting various components, including high-speed interfaces and low-speed interfaces. The various components are connected to each other using different buses and can be installed on a common mainboard or installed in other ways as needed. The processor can process instructions executed in the electronic device, including instructions stored in or on the memory to display graphical information of the GUI on an external input / output device (such as a display device coupled to the interface). In some other embodiments, if necessary, multiple processors and / or multiple buses can be used together with multiple memories and multiple memories. Similarly, multiple electronic devices can be connected, and each device provides some necessary operations (for example, as a server array, a group of blade servers, or a multi-processor system). Among them, the components shown in this article, their connections and relationships, and their functions are only used as examples, and are not intended to limit the implementation of the present application described and / or required herein.

[0130] The electronic device may further include: an input device 1103 and an output device 1104. The processor 1101, the memory 1102, the input device 1103 and the output device 1104 may be connected via a bus or other means. Figure 4 The example of connecting through bus is taken in the following.

[0131] The input device 1103 can receive input digital or character information, and generate key signal input related to the user settings and function control of the electronic device, such as a touch screen, a keypad, a mouse, a track pad, a touch pad, an indicator bar, one or more mouse buttons, a trackball, a joystick and other input devices. The output device 1104 may include a display device, an auxiliary lighting device (e.g., an LED) and a tactile feedback device (e.g., a vibration motor), etc. The display device may include, but is not limited to, a liquid crystal display (LCD), a light emitting diode (LED) display and a plasma display. In some embodiments, the display device may be a touch screen.

[0132] To provide interaction with a user, the electronic device may be a computer. The computer has: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and a pointing device (e.g., a mouse or a trackball), through which the user can provide input to the computer. Other types of devices may also be used to provide interaction with the user; for example, the feedback provided to the user may be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user may be received in any form (including acoustic input, voice input, or tactile input).

[0133] In the embodiments of the present application, a computer program / instruction is stored on a computer-readable medium, and when the computer program / instruction is executed by a processor, the steps of the method provided by any one or more of the above embodiments are implemented. The computer-readable medium may be included in the electronic device described in the above embodiments; or it may exist independently without being assembled into the device. The above computer-readable medium carries one or more computer-readable instructions.

[0134] The memory 1102 can be used as a non-transient computer-readable storage medium, which can be used to store non-transient software programs, non-transient computer executable programs and modules. The processor 1101 executes various functional applications and data processing of the server by running the non-transient software programs, instructions and modules stored in the memory 1102, so as to implement the program instructions / modules corresponding to the method provided by any one or more embodiments in the embodiments of the present application.

[0135] The memory 1102 may include a program storage area and a data storage area, wherein the program storage area may store an operating system, an application required for at least one function; the data storage area may store data created according to the use of the electronic device, etc. In addition, the memory 1102 may include a high-speed random access memory, and may also include a non-transient memory, such as at least one disk storage device, a flash memory device, or other non-transient solid-state storage device. In some embodiments, the memory 1102 may optionally include a memory remotely arranged relative to the processor 1101, and these remote memories may be connected to the electronic device via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0136] It should be noted that the computer-readable medium described in the present application may be a computer-readable signal medium or a computer-readable storage medium or any combination of the above two. The computer-readable medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared or semiconductor system, device or device, or any combination of the above. More specific examples of computer-readable storage media may include, but are not limited to: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In the present application, a computer-readable medium may be any tangible medium containing or storing a program that can be used by or in combination with an instruction execution system, device or device.

[0137] Computer readable media include permanent and non-permanent, removable and non-removable media, and can be implemented by any method or technology to store information. Information can be computer readable instructions, data structures, modules of programs or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, read-only compact disk (CD-ROM), digital versatile disk (DVD) or other optical storage, magnetic cassettes, magnetic tape disk storage or other magnetic storage devices or any other non-transmission medium that can be used to store information that can be accessed by a computing device.

[0138] Computer program code for performing the operations of the present application may be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages, such as Java, Smalltalk, C++, and conventional procedural programming languages, such as "C" or similar programming languages. The program code may be executed entirely on the user's computer, partially on the user's computer, as a separate software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving a remote computer, the remote computer may be connected to the user's computer via any type of network, including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0139] In the above-described embodiments, it can be implemented in whole or in part by software, hardware, firmware or any combination thereof. For example, it can be implemented by using an application specific integrated circuit (ASIC), a general-purpose computer or any other similar hardware device. In certain embodiments, the software program of the present application can be executed by a processor to implement the above steps or functions. Similarly, the software program of the present application (including relevant data structures) can be stored in a computer-readable recording medium, for example, a RAM memory, a magnetic or optical drive or a floppy disk and similar devices. In addition, some steps or functions of the present application can be implemented by hardware, for example, as a circuit that cooperates with a processor to perform each step or function.

[0140] The computer program product provided in the embodiment of the present application includes one or more computer programs / instructions, and when the computer program / instructions are executed by the processor, the process or function described in the embodiment of the present application is generated in whole or in part. The computer may be a general-purpose computer, a special-purpose computer, a computer network, or other programmable devices. The computer instructions may be stored in a computer-readable storage medium, or transmitted from a computer-readable storage medium to another computer-readable storage medium, for example, the computer instructions may be transmitted from a website site, a computer, a server or a data center by wired (e.g., coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) mode to another website site, computer, server or data center. The computer-readable storage medium may be any available medium that a computer can access or a data storage device such as a server or a data center that includes one or more available media integrations. The available medium may be a magnetic medium, (e.g., a floppy disk, a hard disk, a tape), an optical medium (e.g., a DVD), or a semiconductor medium (e.g., a solid state disk (SSD)), etc.

[0141] The flow chart or block diagram in the accompanying drawings shows the possible architecture, function and operation of the equipment, method and computer program product according to various embodiments of the present application. In this regard, each box in the flow chart or block diagram can represent a module, a program segment or a part of a code, and the module, the program segment or a part of the code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a different order from the order marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram and / or flow chart, and the combination of the boxes in the block diagram and / or flow chart can be implemented with a dedicated system for hardware that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0142] The scope of the present application is defined by the appended claims rather than the above description, and it is intended that all changes that fall within the meaning and scope of the equivalent elements of the claims are included in the present application. Any figure mark in the claims should not be regarded as limiting the claims involved. In addition, it is clear that the word "comprising" does not exclude other units or steps, and the singular does not exclude the plural. Multiple units or devices stated in the device claim may also be implemented by one unit or device through software or hardware. The words "first", "second", etc. are only used to distinguish the description, and do not indicate any particular order, nor can they be understood as indicating or implying relative importance.

[0143] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any person skilled in the art can easily mention changes or substitutions within the technical scope disclosed in the present application, which should be included in the protection scope of the present application. Therefore, the protection scope of the present application should be based on the protection scope of the claims, and the above embodiments should be regarded as exemplary and non-restrictive.

Claims

1. A multi-table data synchronization method, characterized in that: The method comprises: The Canal platform is configured to monitor the Binlog log of the relational database and obtain the incremental changes of the relational database in real time; the relational database includes a Mysql database; Consume the Canal instance through the application and distinguish the insert, update, and delete operation types based on the Binlog log content; Create an index in Elasticsearch and define the main table fields and the associated table fields. The associated table fields use the nested type. Use scripts and upsert functions to synchronize multi-table data in the relational database to the corresponding indexes of Elasticsearch.

2. The method according to claim 1, characterized in that The consuming of the Canal instance by the application program includes: the application program distributes the data in the Binlog log to independent consumption threads according to the table name and the primary key ID, and performs orderly consumption.

3. The method according to claim 2, characterized in that The nested type field of Elasticsearch is used to store data of a one-to-many relationship between a main table and an associated table.

4. The method according to claim 3, characterized in that The script uses the painless scripting language to perform incremental synchronization operations on the nested type fields.

5. The method according to claim 4, characterized in that The upsert function is used to add a new document when the corresponding document does not exist in the Elasticsearch, and to perform an update operation if the corresponding document exists.

6. The method according to any one of claims 1 to 5, characterized in that The method further comprises: When the operation type is insert, the document in the Elasticsearch is created or updated through the upsert function; When the operation type is update, the script is used to update the corresponding fields in the Elasticsearch, including updating the nested type fields; When the operation type is delete, the script is used to delete the corresponding data in Elasticsearch, or the nested type field is set to empty.

7. The method according to claim 6, characterized in that The step of synchronizing the multi-table data of the relational database to the corresponding index of Elasticsearch includes: synchronously writing the multi-table data into the Elasticsearch in sections and in parallel.

8. An electronic device, characterized in that: The electronic device comprises: one or more processors; and A memory storing computer program instructions, which, when executed, cause the processor to perform the steps of the method as claimed in any one of claims 1 to 7.

9. A computer readable medium having a computer program / instruction stored thereon, characterized in that: When the computer program / instructions are executed by a processor, the steps of the method according to any one of claims 1 to 7 are implemented.

10. A computer program product comprising a computer program / instructions, characterized in that When the computer program / instructions are executed by a processor, the steps of the method according to any one of claims 1 to 7 are implemented.

Images