阅读说明：本技术 一种网闸隔离的数据库之间数据同步方法 (Data synchronization method between databases isolated by gatekeepers ) 是由 罗炜祥 郑二磊 张壹芬 陈江义 于 2021-07-30 设计创作，主要内容包括：本发明公开了一种网闸隔离的数据库之间数据同步方法,包括,数据库监听数据表的数据变化并记录到监听中间表或数据导出表中；基于作业调度和存储过程,将监听中间表的数据解析为SQL语句,并同步至数据导出表中,以及,将数据导出表中的数据转换为第一导出文件；将第一导出文件通过隔离的数据交换保存至另一个系统；另一个系统基于作业调度或人为操作将第一导出文件转换为SQL语句,导入SQL语句并记录导入信息,从而实现数据同步；本发明提出的网闸隔离的数据库之间数据同步方法,保证了数据的安全性和可靠性,适配市面上的关系型数据库,跟踪导入导出信息,对异常的导入进行修改并重新执行,避免大量问题SQL导入导致的数据出现死锁。(The invention discloses a method for synchronizing data between databases isolated by a gatekeeper, which comprises the following steps that the database monitors data change of a data table and records the data change into a monitoring intermediate table or a data export table; analyzing the data of the monitoring intermediate table into SQL statements based on the operation scheduling and storing process, synchronizing the SQL statements into a data export table, and converting the data in the data export table into a first export file; saving the first export file to another system through isolated data exchange; the other system converts the first export file into an SQL statement based on operation scheduling or manual operation, imports the SQL statement and records import information, thereby realizing data synchronization; the method for synchronizing the data between the databases isolated by the gatekeeper ensures the safety and reliability of the data, is suitable for the relational database in the market, tracks import and export information, modifies and re-executes abnormal import, and avoids deadlock of the data caused by massive problem SQL import.)

1. A method for synchronizing data between databases isolated by a gatekeeper is applied to an intelligent manufacturing system provided with the gatekeeper, the intelligent manufacturing system is at least isolated into a first system and a second system of two independent operation and maintenance through the gatekeeper, wherein the first system is based on the operation and maintenance of the first database, and the second system is based on the operation and maintenance of the second database, and the method comprises the following steps:

the first/second system database monitors the data change of the data table and records the data change into the monitoring intermediate table/data export table;

if the data change of the data table is recorded into the monitoring intermediate table, analyzing the data of the monitoring intermediate table into SQL sentences and synchronizing the SQL sentences into a data export table;

converting the data which are not exported in the data export table into a first export file based on database operation scheduling and storage processes;

storing a first export file of a first/second system in a first directory, and storing the first export file of the first/second system in a second directory of a second/first system through isolated data exchange;

the second/first system converts the first export file into an SQL statement based on job scheduling or manual operation, imports the SQL statement and records import information;

and the second/first system determines whether the imported data exists or not based on the first export file, executes the SQL statement to realize data synchronization if the imported data exists, and records the corresponding SQL statement and abnormal operation to a data import table of the second/first database if the imported data does not exist.

2. The method for synchronizing data between databases isolated by gatekeeper according to claim 1, wherein if the data change of the data table is recorded in the middle table, the data in the middle table is analyzed into SQL statement and synchronized into the data export table, specifically comprising:

based on the operation scheduling, regularly calling a storage process, and analyzing the data in the monitoring intermediate table into SQL sentences which are inserted/modified/deleted;

and inserting the analyzed SQL statement into a data export table, and marking the updated data in the data export table as the unextended data.

3. The method for synchronizing data between gatekeeper isolated databases according to claim 2, wherein the converting the data not exported in the data export table into the first export file based on the database job scheduling and storing process specifically comprises:

writing the data marked as not exported into the text file based on 3DES encryption;

and compresses the text file into a compressed file as a first export file.

4. The method for data synchronization between databases isolated by gatekeeper according to claim 1, wherein said second/first system converts the first exported file into SQL statements, imports the SQL statements, and records import information based on job scheduling or manual operation, specifically comprising:

the second/first system regularly queries a second directory or manually synchronizes a first export file based on job scheduling, so that the first export file stored in the second directory is decrypted and decompressed into a text file, wherein the text file at least comprises a synchronous data volume and synchronous data information, the job scheduling at least comprises Spring job scheduling or Windows job scheduling, and the synchronous data information at least comprises a synchronous data table name, a data operation type, a main key ID and an SQL statement;

analyzing the text file into SQL sentences;

the method comprises the steps of importing an SQL statement and recording SQL import information, wherein the import information at least comprises an imported file name, import time, an import state, an import person and imported SQL detail data.

5. The method according to claim 4, wherein the second/first system determines whether the imported data exists based on the first export file, if so, executes the SQL statement to implement data synchronization, otherwise, records the corresponding SQL statement and the abnormal operation to the data import table of the second/first database, and specifically includes:

determines whether the imported data exists based on the first export file decrypting the data table name, the operation type and the primary key ID in the decompressed text file,

if the operation type is newly added, inquiring whether the data exists or not based on the data table name and/or the primary key ID,

if the data table name and/or the primary key ID exist, not executing the SQL statement, and recording the corresponding SQL statement and abnormal operation into the data import table;

and if the data table name and the primary key ID do not exist, executing the SQL statement, thereby realizing data synchronization.

6. The method for data synchronization between databases isolated by gatekeeper according to claim 4, wherein importing SQL statements and recording SQL import information specifically comprises:

the SQL statements are imported to record the synchronous data volume and all the SQL statements are imported, and the SQL statements are executed one by one;

if the SQL statement is abnormal, the operation of the imported SQL statement is not rolled back, and the SQL statement, the SQL statement execution condition, the SQL abnormity and the abnormal information are recorded in the data import table.

7. The method for data synchronization between gatekeeper isolated databases according to claim 1, further comprising re-editing the SQL statements failing to import and export and re-importing and exporting them based on the data import table and the data export table.

8. The method for data synchronization between gatekeeper isolated databases of any of claims 1 to 7, wherein said first/second database is a SQLSERVER database or a domestic Dameng database.

9. The method of claim 8, wherein the first/second databases listen to data changes in the data table and record the data changes in the listened intermediate table/data export table, and the method further comprises:

if the first database and/or the second database is/are a domestic dream database, monitoring data addition or deletion or updating in a data table by the domestic dream database based on a table-level trigger, and recording the data in a data export table;

if the first database and/or the second database is SQLSERVER, the data of the listening data table is added or deleted or updated based on SQLSERVER CDC and recorded into the listening intermediate table.

10. The method of synchronizing data between gatekeeper isolated databases of claim 9, further comprising: if the domestic dream reaching database monitors data updating in the data table based on the table level trigger, comparing data before and after updating operation, and if the data before and after updating are not different, not recording the data in the data export table; if the SQLSERVER monitors data updating in the data table based on SQLSERVER CDC, the SQLSERVER compares new and old data through a storage process, and if no difference exists, the SQLSERVER does not update the SQLSERVER into the monitoring intermediate table.

Technical Field

The invention relates to the field of databases, in particular to a method for synchronizing data among databases isolated by a gatekeeper.

Background

The application relates to the product manufacturer of key technique, because production data relates to high-level commercial secret even national secret in the production process, so generally carry out the operation and maintenance in two sets of independent systems of special net and industrial control net through the split of gatekeeper with intelligent manufacturing system. The private network manufacturing system is mainly responsible for inputting confidential personnel information, confidential production basic data and confidential production data of other workshops; the manufacturing system of the industrial control network is mainly responsible for the input of production data of formal product production lines and the input of business process flow circulation data in the industrial control network. Although the two systems are isolated by the network gate and independent from each other, the two systems supplement each other and are mutually dependent.

The existing data synchronization methods mainly include three types: 1) data synchronization is realized through an interface; 2) data synchronization is realized through EXCEL data import; 3) and data synchronization is realized through database import.

The method adopts EXCEL data import, a plurality of business interrelated data exist in the manufacturing system, the data synchronization fault tolerance is low by directly adopting the EXCEL, and the business circulation can not be realized due to data error in the intelligent manufacturing system caused by incomplete import of business document interrelated data. And the EXCEL import needs a large amount of EXCEL export templates, the EXCEL export amount of a large-scale database is large, and the problems that the efficiency of an intelligent manufacturing system is reduced due to database deadlock and the like easily occur during the import.

The database import has a lot of database products on the market, two intelligent manufacturing systems isolated from each other by a gateway of the same manufacturer have scenes using different database management systems, the database import needs to convert exported database files according to different databases, the conversion operation is troublesome, and the data loss is easily caused. Different intelligent manufacturing systems isolated at the gatekeeper have independent business processes, and if the database is directly used for importing, the data generated by the respective business processes of the manufacturing systems are easily covered, so that the data loss is caused.

Disclosure of Invention

The invention provides a method for synchronizing data between databases isolated by a gatekeeper, which is applied to an intelligent manufacturing system isolated by the gatekeeper and needing to synchronize partial basic data and service data.

The technical scheme adopted by the invention for overcoming the technical problems is as follows:

the invention provides a method for synchronizing data between databases isolated by a gatekeeper, which is applied to an intelligent manufacturing system provided with the gatekeeper, wherein the intelligent manufacturing system is at least isolated into a first system and a second system which are independent in operation and maintenance through the gatekeeper, the first system is based on the operation and maintenance of the first database, the second system is based on the operation and maintenance of the second database, and the method comprises the following steps: the first/second system database monitors the data change of the data table and records the data change into the monitoring intermediate table/data export table; if the data change of the data table is recorded into the monitoring intermediate table, analyzing the data of the monitoring intermediate table into SQL sentences and synchronizing the SQL sentences into a data export table; converting the data which are not exported in the data export table into a first export file based on database operation scheduling and storage processes; storing a first export file of a first/second system in a first directory, and storing the first export file of the first/second system in a second directory of a second/first system through isolated data exchange; the second/first system converts the first export file into an SQL statement based on job scheduling or manual operation, imports the SQL statement and records import information; and the second/first system determines whether the imported data exists or not based on the first export file, if so, executes the SQL statement to realize data synchronization, otherwise, records the corresponding SQL statement and abnormal operation to a data import table of the second/first database.

Further, if the data change of the data table is recorded into the monitoring intermediate table, the data of the monitoring intermediate table is analyzed into an SQL statement, and the SQL statement is synchronized into the data export table, which specifically includes: based on the operation scheduling, regularly calling a storage process, and analyzing the data in the monitoring intermediate table into SQL sentences which are inserted/modified/deleted; and inserting the analyzed SQL statement into a data export table, and marking the updated data in the data export table as the unextended data.

Further, based on the database operation scheduling and storage process, converting the data not exported in the data export table into a first export file, specifically comprising: writing the data marked as not exported into the text file based on 3DES encryption; and compresses the text file into a compressed file as a first export file.

And the text file is written by adopting a 3DES encryption algorithm and then compressed into a compressed file, so that the safety and reliability of the information are ensured.

Further, the second/first system converts the first export file into an SQL statement based on job scheduling or manual operation, imports the SQL statement, and records import information, which specifically includes: the second/first system regularly queries a second directory or manually synchronizes a first export file based on job scheduling, so that the first export file stored in the second directory is decrypted and decompressed into a text file, wherein the text file at least comprises a synchronous data volume and synchronous data information, the job scheduling at least comprises Spring job scheduling or Windows job scheduling, and the synchronous data information at least comprises a synchronous data table name, a data operation type, a main key ID and an SQL statement; analyzing the text file into SQL sentences; the method comprises the steps of importing an SQL statement and recording SQL import information, wherein the import information at least comprises an imported file name, import time, an import state, an import person and imported SQL detail data.

All the import and export information is recorded, so that the tracking problem of related personnel performing data synchronization is facilitated, and the import and export conditions are checked.

Further, the second/first system determines whether the imported data exists based on the first export file, if so, executes the SQL statement to implement data synchronization, otherwise, records the corresponding SQL statement and the abnormal operation to the data import table of the second/first database, and specifically includes: determining whether imported data exists or not based on the data table name, the operation type and the primary key ID in the text file subjected to decryption and decompression by the first export file, if the operation type is newly increased, inquiring whether the data exists or not based on the data table name and/or the primary key ID, if the data table name and/or the primary key ID already exists, not executing the SQL statement, and recording the corresponding SQL statement and abnormal operation into the data import table; and if the data table name and the primary key ID do not exist, executing the SQL statement, thereby realizing data synchronization.

Avoiding the repeated import of unnecessary data.

Further, importing the SQL statement and recording the imported information of the SQL specifically includes: the SQL statements are imported to record the synchronous data volume and all the SQL statements are imported, and the SQL statements are executed one by one; if the SQL statement is abnormal, the operation of the imported SQL statement is not rolled back, and the SQL statement, the SQL statement execution condition, the SQL abnormity and the abnormal information are recorded in the data import table.

Further, the method also comprises the step of editing the SQL sentence with failed import and export again and importing and exporting the SQL sentence again based on the data import table and the data export table.

The problem of tracking related personnel for executing data synchronization is facilitated, and the problem that the SQL is modified to be executed again or is omitted to be modified by an operator according to the actual situation can be conveniently solved, so that the problems that deadlock occurs to data and the like caused by a large amount of problems SQL is imported when the data is imported are solved.

Further, the first database/the second database is a SQLSERVER database or a domestic Dameng database.

Further, the first/second database monitors data changes of the data table and records the data changes in the monitoring intermediate table/data export table, and the method specifically includes: if the first database and/or the second database is/are a domestic dream database, monitoring data addition or deletion or updating in a data table by the domestic dream database based on a table-level trigger, and recording the data in a data export table; if the first database and/or the second database is SQLSERVER, the data of the listening data table is added or deleted or updated based on SQLSERVER CDC and recorded into the listening intermediate table.

Further, if the domestic dreaming database monitors data updating in the data table based on the table level trigger, comparing data before and after updating operation, and if the data before and after updating are not different, not recording the data in the data export table; if the SQLSERVER monitors data updating in the data table based on SQLSERVER CDC, the SQLSERVER compares new and old data through a storage process, and if no difference exists, the SQLSERVER does not update the SQLSERVER into the monitoring intermediate table.

And comparing the data before and after updating the data, and avoiding operating unnecessary data.

The invention has the beneficial effects that:

1. the SQLSERVER CDC technique and the table level trigger technique of Dameng database are used to monitor the data change of the corresponding database in real time.

2. And updating operation is monitored for the data, and new and old data are compared, so that unnecessary data are prevented from being repeatedly imported when the data are imported.

3. The data change generated by the add, delete and modify operation is exported by using an SQL (structured query language) statement, and the corresponding SQL statement is directly executed when the data change is imported, so that most relational databases in the market can be adapted;

4. the exported SQL statement is encrypted and written into a text file by using a 3DES encryption algorithm technology and then compressed into a compressed file, so that the safety and reliability of information are ensured;

5. the imported and exported information is tracked, related personnel who perform data synchronization can conveniently track the relevant problems of the exported data and the data import condition, and abnormal imported SQL can be modified to re-execute the import operation.

6. The abnormal conditions of data circulation of the unidirectional network gate and the bidirectional network gate are processed, particularly, repeated detection is carried out on the ID of the main key, and the problems that data deadlock occurs due to massive SQL import problems in data import and the like are avoided.

Drawings

FIG. 1 is a flowchart of an embodiment of a method for synchronizing data between databases isolated by gatekeeper of the present invention;

FIG. 2 is a flowchart of an embodiment of the present invention for implementing data synchronization between databases isolated by gatekeeper

FIG. 3 is a schematic diagram of a data import/export implementation flow according to an embodiment of the present invention;

Detailed Description

In order to facilitate a better understanding of the invention for those skilled in the art, the invention will be described in further detail with reference to the accompanying drawings and specific examples, which are given by way of illustration only and do not limit the scope of the invention.

In order to better understand the technical solution of the present application, some terms in the present application will be described first.

The intelligent manufacturing system comprises: production informatization management system facing workshop execution layer of manufacturing enterprise

SQLSERVER CDC: and (3) Change Data Capture (CDC), wherein the Change Data Capture records the insertion, update and deletion activities of the SQL Server table. Operation of the SQLServer writes a log, which is also the source of the CDC capture data. The source table that opens the cdc inserts data into the log table at the time of insertion, update, and delete activities. The cdc captures the change data into the change table through a capture process, which we can capture through a lookup function provided by the cdc.

A network gate: two independent host systems are isolated through a gateway, so that no physical connection, logical connection and information transmission protocol for communication exist between the systems, no information exchange according to the protocol exists, and no protocol ferry is performed only in a data file form.

Unidirectional network gate: to ensure that data in a high-security level network cannot flow to a low-security level network, data in a low-security level network can flow to a high-security level network.

Bidirectional network gate: data in the high-density level network and the low-density level can be subjected to isolated data exchange.

3 DES: triple data encryption algorithm.

SQL statement: and the structured query statement is used for accessing data and querying, updating and managing the relational database system.

As shown in fig. 1, the flowchart of the method for synchronizing data between databases isolated by a gatekeeper of the present invention is applied to an intelligent manufacturing system configured with a gatekeeper, and the intelligent manufacturing system is at least isolated into a first system and a second system of two independent operations and maintenance through the gatekeeper, where the first system is based on a first database operation and maintenance, and the second system is based on a second database operation and maintenance.

If the first/second database is an SQLSERVER database, monitoring data changes of the data table in real time by utilizing the SQLSERVER CDC technology and recording the data changes into a database monitoring table, acquiring the monitored data changes through database operation scheduling and storing processes, judging whether the data needs to be exported or not, and analyzing the data needing to be exported into SQL statements and recording the SQL statements into a data export table;

if the first system database and the second system database are Dameng databases, a table-level trigger is used for monitoring data change of a data table in real time, data information before and after updating is compared during data updating, whether the updated data need to be exported or not is judged, the data to be exported is analyzed into SQL statements and recorded in a data export table, the data which are not exported in the data export table are encrypted through a 3DES encryption algorithm and converted into a first export file, and the data state is changed into exported, so that repeated export of the data is prevented.

Compressing and storing a first export file of a first/second system in a first directory of the first/second system, and storing the first export file of the first/second system in a second directory of a second/first system through isolated data exchange; the second/first system reads the import file in the second directory of the second/first system based on Spring job scheduling or human operation events, reads the file after decompression, decrypts the content of the encrypted file into corresponding SQL statements, executes the corresponding SQL statements and records the execution records into an import information table; and the second/first system database judges whether newly added data exists or not based on the SQL in the first file after decompression and decryption, determines whether SQL statements are executed or not, executes the SQL statements to realize data synchronization if the newly added data exists, and otherwise records the corresponding SQL statements and abnormal operation to a data import table of the second/first database.

Fig. 2 is a schematic diagram of a data synchronization process. Two independent manufacturing systems monitor data changes through a database, the system exports SQL statements of the changed data to be encrypted and compressed into compressed files, and data synchronization between the intelligent manufacturing systems is realized through importing and exporting the encrypted and compressed SQL files.

The following describes a method for synchronizing data between gatekeeper isolated databases according to the present invention with reference to specific embodiments.

As shown in fig. 3, the smart manufacturing system is divided into an independent manufacturing system a and an independent manufacturing system B by setting a unidirectional gatekeeper or a bidirectional gatekeeper, and the networks of the manufacturing system a and the manufacturing system B are independent and the interfaces are independent.

In one embodiment of the present invention, manufacturing system A employs a SQLSERVER database and intelligent system B employs a Damascene database and synchronizes changes to the data in manufacturing system A to manufacturing system B.

The SQLSERVER of the manufacturing system A carries out the operations of adding, deleting and modifying production basic data, production data, inspection data, database access and storage data and the like, monitors the data adding, deleting and modifying operations in the data table for starting CDC in real time by using SQLSERVER CDC technology, and records SQL sentences related to the operations into the monitoring intermediate table.

The Dammann database of the manufacturing system B monitors data adding, deleting and modifying operations in the data table of the opening trigger through the table-level trigger of the data table, and records SQL sentences relevant to the operations into the monitoring intermediate table.

According to the SQLSERVER CDC technology of the SQLSERVER database, after a CDC function which needs to capture a data change table is started, the database can automatically generate a table name _ CT table of a CDC, dbo and CDC function starting function, old data and new data which are updated are compared according to a field [ __ $ operation ] in the table, the difference between the old data and the new data is compared through a storage process, and if the difference is not found, the old data and the new data are not updated into a monitoring intermediate table.

The database is used as a business scheduling timing call storage process to analyze the data of the monitoring intermediate table into information such as an operation table, an operation SQL statement and the like, update the information into a data synchronization export table and mark an import state as un-export.

The SQLSERVER of the manufacturing system A starts a scheduling task to timely lead out the data marked with the data not to be led out in the data synchronization lead-out table, and the operation data information is encrypted and written into the text by utilizing the 3DES encryption technology and compressed into a compressed file to be under a specified directory.

And the exported encrypted compressed file is transmitted to a specified directory for importing under the manufacturing system B through a removable storage medium, the manufacturing system B periodically inquires the imported folder through a scheduling task, and un-imported data is decrypted and decompressed.

In some embodiments, the removable storage medium may be an optical disk, a secure U disk.

The dreams database of the manufacturing system B in the network 2 parses the decrypted and decompressed file and executes the operation SQL statements in the file, and records the executed SQL statements and the statement execution conditions in the system. The manufacturing system B under the network 2 executes the imported data SQL one by one, thereby realizing the one-way gatekeeper data synchronization from the system A to the system B.

In some embodiments, the problem SQL statement will not roll back all SQL statements if it encounters a problem, but rather roll back the problem SQL and record the problem SQL statement, all SQL and all SQL execution conditions, whether abnormal SQL is present, and abnormal information. An operator can check all the problem SQL through the records and can re-edit and execute the problem SQL so as to ensure the correctness of the data.

In some embodiments, a bidirectional gatekeeper is arranged between a system a and a system B and data synchronization is required, the system a adds new data, an SQLSERVER database of the system a monitors data adding operations in a data table for starting CDC in real time by using SQLSERVER CDC technology, and records SQL statements related to the operations in a monitoring intermediate table, the SQLSERVER database analyzes data in the monitoring intermediate table into insert SQL statements by using a service scheduling timing, inserts the analyzed SQL statements into a data export table, marks updated data in the data export table as unextended data, encrypts and writes the unextended data into a text file based on 3DES encryption, and compresses the text file into a compressed file as a first export file for data synchronization.

The synchronous data comprises a synchronous data table name, an operation type, a primary key ID and an SQL statement, and the system B is used for processing the data according to the data table name, the operation type and the primary key ID.

And the first export file is imported into the removable storage medium through manual or task scheduling, and the first export file is saved to a second directory of the system B through the removable storage medium. And the system B acquires the first export file by regularly inquiring the second directory, decrypts and decompresses the first export file to acquire a text file, and analyzes the text file into an SQL statement. And when the operation type is new, judging whether the data table name or the primary key ID queries the existence of the new data, if not, executing an SQL statement and executing corresponding task operation of the new data. If the abnormal information exists, the execution is not continued, and the abnormal information record is saved in the data export table.

This is to solve the abnormal situation of the repeated synchronous data when the bidirectional gatekeeper data is synchronized. After the new data generated by the system A is synchronized to the system B, the system B can synchronously increase the data, the system B can also produce a data packet with the same primary key ID and produce a synchronization file, if the system A receives the synchronization file, the judgment is carried out according to the primary key ID, the SQL file with the same primary key ID is not processed, and the repeated synchronization is avoided.

In some embodiments, data of both the system a and the system B need to be changed, bidirectional synchronization is performed through the gatekeeper, when data synchronization is not performed yet, a new piece of data is added to the system a, a new piece of data is added to the system B, and the primary key IDs in the synchronization files generated by the two systems are the same, so that subsequent manual intervention is required to modify the primary key ID in the data synchronization process.

In some embodiments, all export information of system a and system B records information such as exporter, number of export data and export time, and import SQL also records file name of import, import time, import status, importer and SQL detailed data of import. The related personnel tracking problem of data synchronization is convenient to execute.

In some embodiments, if the SQL statement is not executed in the data import process, the abnormal SQL is recorded in the input import table, and then an operator can modify the SQL to execute again or ignore the problem SQL according to the actual situation, so that problems such as deadlock and the like caused by massive problem SQL import during data import are avoided.

In some embodiments, if the data in the system a is modified but the modified data is not saved, the actual data is not changed, when the SQLSERVER CDC trigger monitors that the data in the data table is an update operation, the difference between the old data and the new data is compared, and if the difference is not found, the operation record is not updated to the monitoring intermediate table, so as to avoid operating on unnecessary data.

The foregoing merely illustrates the principles and preferred embodiments of the invention and many variations and modifications may be made by those skilled in the art in light of the foregoing description, which are within the scope of the invention.