MySQL Heterogeneous Log-Based Replication
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current methods for data migration and integration between MySQL and non-MySQL databases are inefficient, particularly in handling non-bulk data, requiring application modifications and resulting in high computational taxing and slow response times due to the lack of log-based change data capture (CDC) for logical replication.
Innovation Solution
A system and method utilizing log-based replication to transfer data between MySQL and non-MySQL databases, capturing data changes from the MySQL binary log, transforming them, and applying them to target systems, ensuring low latency, reduced overhead, and non-invasive replication with guaranteed delivery, enabling real-time data synchronization and zero-downtime migrations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If ETL/EAI/EII technologies are used to integrate data between MySQL and non-MySQL systems, then data integration capability is improved, but computational overhead increases and processing speed decreases
Solution Approach 1:
The patent extracts the data change capture function from the application layer and places it in the database layer by directly reading the MySQL binary log. This separates the integration logic from the application, allowing ETL processes to operate independently without burdening the production system with query overhead.
Solution Approach 2:
The patent introduces an intermediary component (data change capture program) that sits between MySQL and the target system. This intermediary reads changes from the binary log and delivers them to the target system, eliminating the need for applications to directly access multiple systems and reducing computational overhead.
2Loss of energy
If periodic ETL methods are used for data synchronization, then system overhead is reduced, but data latency increases
Solution Approach 1:
The patent implements continuous data change capture by continuously monitoring the MySQL binary log for new entries. Unlike periodic ETL that runs at scheduled intervals, this approach continuously captures and delivers data changes as they occur, eliminating data latency while maintaining low system overhead through efficient log-based reading.
3Extent of automation
If application modifications are made to publish data (EAI), then real-time data publishing capability is improved, but application complexity and maintenance burden increase
Solution Approach 1:
The patent enables the database system to serve itself by automatically capturing and publishing data changes through binary log reading. Applications don't need to modify their code to publish data - the system self-services by monitoring the binary log and automatically delivering changes to target systems, reducing application complexity while maintaining real-time publishing capability.
4Adaptability or versatility
If remote network access is used to integrate multiple systems (EII), then system integration scope is expanded, but response time decreases
Solution Approach 1:
The patent replaces the mechanical network communication approach with a more efficient mechanism by using binary log-based change capture. Instead of applications remotely accessing multiple systems over networks, the system substitutes this with direct binary log reading and efficient data delivery mechanisms, expanding integration scope while improving response time.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
A system and method for transferring data between different types of systems, and in particular uses log-based replication to transfer data between, e.g. a MySQL database or system, and another type of database or system. In accordance with an embodiment, the system can be used to perform a one-time or initial copy of the MySQL data from a source database system to a target database system, and/or to replicate the on-going transactions captured from a MySQL database's binary log into one or additional non-MySQL database(s) on a continuous basis, such that the two systems are synchronized for transactions of interest. In accordance with an embodiment, full or partial data changes can be extracted from the MySQL binary log, optionally transformed, skipped or augmented, output or written to a file (which in accordance with an embodiment can be implemented as a trail file, or an Oracle GoldenGate trail file), and then applied at any of one or more target systems (e.g. another MySQL database, or a non-MySQL database), thereby synchronizing the source and target systems.