CN114297292A - Data synchronization system based on canal platform and execution method - Google Patents
Data synchronization system based on canal platform and execution method Download PDFInfo
- Publication number
- CN114297292A CN114297292A CN202111557952.XA CN202111557952A CN114297292A CN 114297292 A CN114297292 A CN 114297292A CN 202111557952 A CN202111557952 A CN 202111557952A CN 114297292 A CN114297292 A CN 114297292A
- Authority
- CN
- China
- Prior art keywords
- canal
- log file
- data
- binary log
- adapter
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 8
- 206010028980 Neoplasm Diseases 0.000 claims abstract description 4
- 201000011510 cancer Diseases 0.000 claims abstract description 4
- 230000001360 synchronised effect Effects 0.000 claims description 11
- 238000013507 mapping Methods 0.000 claims description 8
- 230000004044 response Effects 0.000 abstract description 3
- 230000007547 defect Effects 0.000 description 1
- 238000010586 diagram Methods 0.000 description 1
- 230000003993 interaction Effects 0.000 description 1
- 238000004806 packaging method and process Methods 0.000 description 1
- 230000008569 process Effects 0.000 description 1
- 230000009466 transformation Effects 0.000 description 1
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a data synchronization system based on a canal platform and an execution method, comprising three components: canal _ server, canal _ es, and canal _ adapter; the Canal _ server component pulls a binary log file of MySQL in real time; then the Canal _ server component analyzes the binary log file; the cancer _ server component sends data formed after the binary log file is analyzed to a message queue RocketMQ; the Canal _ adapter consumes the data of the RocktMQ and pushes the data to the elastic search, so that the data synchronization delay is greatly reduced, the service system has faster response and higher performance, and the data newly written into MySQL can be inquired by a user in a short time.
Description
Technical Field
The invention belongs to the technical field of databases, and particularly relates to a method for synchronizing a MySQL database to an elastic search, which can reduce resource occupancy rate, shorten query time, improve instantaneity and can be dynamically configured.
Background
Data in the relational database MySQL usually has performance bottleneck when being queried in large data volume and frequently, so that system response is slow, and ElasticSearch (for short: ES) can just solve the query defect of the relational database, supports distributed deployment and query, and has greatly improved performance compared with the relational database, so that the scheme adopted by system design is to write the data into MySQL, synchronize to the ES in an asynchronous mode, and query from the ES.
Under the background that the demand of the internet changes continuously and iterates nowadays, how to meet the demand in a short time can be achieved, and the demand can be configured to become a pain point of a project. The data synchronization system based on the canal platform makes the requirement of continuous transformation iteration of each service system into a configurable point, so that the system requirement can be met more quickly and accurately when the requirement is changed, and the query efficiency of joint call between the service systems is indirectly improved.
Disclosure of Invention
The invention aims to provide a data synchronization system based on a Canal platform.A Canal _ server component pulls a binary log file of a MySQL database table configured by a business system according to requirements in real time; then the Canal _ server component analyzes the binary log file; the cancer _ server component sends data formed after the binary log file is analyzed to a message queue RocketMQ; the Canal _ adapter consumes the data of the RocktMQ, and pushes the data to the Elasticissearch according to the synchronous sql and the Elasticissearch index type and mapping configured by the service system requirements.
The specific technical scheme is as follows:
a data synchronization system based on canal platform comprises three components: canal _ server, canal _ es, canal _ adapter; wherein:
the Canal _ server component pulls a binary log file of MySQL in real time and analyzes the binary log file; sending data formed after resolving the binary log file to a message queue RocktMQ;
the canal _ es is responsible for configuring an Elasticissearch index type and index mapping by a service system, and each service is dynamically configured according to the actual service requirement of the service;
configuring synchronous SQL sentences to be executed in the canal _ adapter, configuring parent-child document associated fields synchronized to the Elasticissearch by each business system according to actual business requirements, and synchronizing a plurality of SQL sentences to associated main key fields under the Elasticissearch index in an associated manner;
the pipe _ es and the pipe _ adapter can also configure the field in the query result of the sql statement to be mapped into an Object type, so that the field type in the result queried in the Elasticsearch is in a JSON format.
The invention synchronizes the MySQL database to the Elasticissearch method, firstly, the Canal _ server component draws the binary log file of the MySQL in real time; then the Canal _ server component analyzes the binary log file; the cancer _ server component sends data formed after the binary log file is analyzed to a message queue RocketMQ; the Canal _ adapter consumes the data of the RocktMQ and pushes the data to the elastic search, so that the data synchronization delay is greatly reduced, the service system has faster response and higher performance, and the data newly written into MySQL can be inquired by a user in a short time. Each business system configures a designated database or table to be monitored in the canal _ server and configures an elastic search index type and an index mapping in the canal _ es according to the actual business requirements of the business system. And synchronous SQL sentences to be executed are configured in the canal _ adapter, so that each business system synchronously pushes data mapped into JSON format by the needed data to the Elasticissearch according to the actual requirement, and the packaging step when the subsequent business system queries the Elasticissearch data is greatly reduced.
The invention has the advantages that: the method can push the log file generated from mysql to a rocktMQ, when the canal _ adapter consumes the message, the message can be pushed to an Elasticisearch according to the configured sql statement, and when a plurality of canal _ adapters are pushed to the same Elasticisearch index, the data can be analyzed and encapsulated according to the canal _ es and canal _ adapter configuration parameters, so that the service system can be directly inquired and used.
Based on the canal platform, the modified content of the database can be obtained in real time by obtaining the binary log generated by operating the database. As long as the database is modified, the modified content can be obtained immediately and is suitable for different service scenes, so that the real-time performance is improved, and the development cost is reduced; aiming at the magnitude of the later pressure of the synchronization service, the dynamic capacity expansion of the server is realized, and the pressure of data synchronization is reduced.
Drawings
FIG. 1 is a schematic diagram of the framework and process of the present invention.
Detailed Description
The present invention will be described in further detail with reference to the accompanying drawings and specific embodiments.
As shown in fig. 1, a data synchronization system based on canal platform includes three components: canal _ server, canal _ es, canal _ adapter; wherein:
the Canal _ server component pulls a binary log file of MySQL in real time and analyzes the binary log file; sending data formed after resolving the binary log file to a message queue RocktMQ;
the canal _ es is responsible for configuring an Elasticissearch index type and index mapping by a service system, and each service is dynamically configured according to the actual service requirement of the service;
configuring synchronous SQL sentences to be executed in the canal _ adapter, configuring parent-child document associated fields synchronized to the Elasticissearch by each business system according to actual business requirements, and synchronizing a plurality of SQL sentences to associated main key fields under the Elasticissearch index in an associated manner;
the pipe _ es and the pipe _ adapter can also configure the field in the query result of the sql statement to be mapped into an Object type, so that the field type in the result queried in the Elasticsearch is in a JSON format.
When the Canal component pulls a binary log file of MySQL in real time, a MySQL slave interaction protocol is simulated, the self is disguised as MySQL slave, a dump request is sent to a MySQL master, and the binary log is pushed to the Canal component when the MySQL master receives the dump request. The canal _ es configures the type of the Elasticissearch index and the index mapping, and can configure the index mapping through configuration; configuring synchronous SQL sentences to be executed in the canal _ adapter, configuring parent-child document associated fields synchronized to the Elasticissearch by each business system according to actual business requirements, and synchronizing a plurality of SQL sentences to associated main key fields under the Elasticissearch index in an associated manner; the pipe _ es and the pipe _ adapter can also configure the field in the query result of the sql statement to be mapped into an Object type, so that the field type in the result queried in the Elasticsearch is in a JSON format.
Claims (2)
1. A data synchronization system based on canal platform is characterized by comprising three components: canal _ server, canal _ es, canal _ adapter; wherein:
the Canal _ server component pulls a binary log file of MySQL in real time and analyzes the binary log file; sending data formed after resolving the binary log file to a message queue RocktMQ;
the canal _ es is responsible for configuring an Elasticissearch index type and index mapping by a service system, and each service is dynamically configured according to the actual service requirement of the service;
configuring synchronous SQL sentences to be executed in the canal _ adapter, configuring parent-child document associated fields synchronized to the Elasticissearch by each business system according to actual business requirements, and synchronizing a plurality of SQL sentences to associated main key fields under the Elasticissearch index in an associated manner;
the pipe _ es and the pipe _ adapter can also configure the field in the query result of the sql statement to be mapped into an Object type, so that the field type in the result queried in the Elasticsearch is in a JSON format.
2. The execution method of the data synchronization system based on the Canal platform as claimed in claim 1, wherein the Canal _ server component pulls the binary log file of the MySQL database table configured by the business system according to the requirement in real time; then the Canal _ server component analyzes the binary log file; the cancer _ server component sends data formed after the binary log file is analyzed to a message queue RocketMQ; the Canal _ adapter consumes the data of the RocktMQ, and pushes the data to the Elasticissearch according to the synchronous sql and the Elasticissearch index type and mapping configured by the service system requirements.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111557952.XA CN114297292A (en) | 2021-12-20 | 2021-12-20 | Data synchronization system based on canal platform and execution method |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111557952.XA CN114297292A (en) | 2021-12-20 | 2021-12-20 | Data synchronization system based on canal platform and execution method |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN114297292A true CN114297292A (en) | 2022-04-08 |
Family
ID=80968179
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202111557952.XA Pending CN114297292A (en) | 2021-12-20 | 2021-12-20 | Data synchronization system based on canal platform and execution method |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN114297292A (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN114584574A (en) * | 2022-04-28 | 2022-06-03 | 武汉四通信息服务有限公司 | Data synchronization method and device, computer equipment and storage medium |
| CN117540151A (en) * | 2023-12-08 | 2024-02-09 | 深圳市亲邻科技有限公司 | Data preprocessing method of data pushing system |
-
2021
- 2021-12-20 CN CN202111557952.XA patent/CN114297292A/en active Pending
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN114584574A (en) * | 2022-04-28 | 2022-06-03 | 武汉四通信息服务有限公司 | Data synchronization method and device, computer equipment and storage medium |
| CN117540151A (en) * | 2023-12-08 | 2024-02-09 | 深圳市亲邻科技有限公司 | Data preprocessing method of data pushing system |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| WO2020147392A1 (en) | Method and system for data synchronization between databases | |
| CN114297292A (en) | Data synchronization system based on canal platform and execution method | |
| CN110175213A (en) | A kind of oracle database synchronization system and method based on SCN mode | |
| CN109063196B (en) | Data processing method and device, electronic equipment and computer readable storage medium | |
| CN106960007A (en) | A kind of data-updating method, device and electronic equipment | |
| CN110190949B (en) | Method, system, apparatus and storage medium for large data volume resource uplink | |
| CN111597160A (en) | Distributed database system, distributed data processing method and device | |
| CN103699638A (en) | Method for realizing cross-database type synchronous data based on configuration parameters | |
| CN104599032A (en) | Distributed memory power grid construction method and system for resource management | |
| CN105405070A (en) | Distributed memory power grid system construction method | |
| CN102508834B (en) | Message-based event application device and message-based event application method | |
| WO2022002044A1 (en) | Method and apparatus for processing distributed database, and network device and computer-readable storage medium | |
| CN106648917B (en) | A kind of method and system that difference update is data cached | |
| CN103942330A (en) | Method and system for processing big data | |
| CN102026228B (en) | Statistical method and equipment for communication network performance data | |
| CN110895547B (en) | Multi-source heterogeneous database data synchronization system and method based on DB2 federation characteristics | |
| CN104881485A (en) | Data synchronizing method and system | |
| CN110740195B (en) | Distributed system data synchronization method and system based on message engine | |
| CN113810466B (en) | Middleware for multi-source heterogeneous data, system and method for applying middleware | |
| CN110555064A (en) | data service system and method for insurance business | |
| CN114676199A (en) | Synchronization method, synchronization system, computer equipment and storage medium | |
| CN110413677A (en) | A kind of Distributed Data Synchronization method and system for supporting concurrently to apply | |
| CN110674221B (en) | Spatial data synchronization method, terminal and computer readable storage medium | |
| WO2021189283A1 (en) | Method and device for data processing, electronic device, and storage medium | |
| CN109669988B (en) | Splitting and synchronizing method and system for inquiring table-building transaction |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |