MYSQL database heterogeneous log based replication

• US 8,510,270 B2
• Filed: 03/31/2011
• Issued: 08/13/2013
• Est. Priority Date: 07/27/2010
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Pareek, Alok, Dubey, Avinash, Corbin, Scott, Lakshminarayanan, Mahadevan
• Primary Examiner(s): Nguyen, Loan T
• Application Number:
  US13/077,760
• Publication Number:
  US 20120030172A1
• Time in Patent Office: 866 Days
• Field of Search: None
• US Class Current: 707/635
• CPC Class Codes: G06F 11/1471 involving logging of persistent data for recovery
• CPC Class Codes: G06F 11/2094 Redundant storage or storage space G06F11/2056 takes precedence
• CPC Class Codes: G06F 11/2097 maintaining the standby controller/processing unit updated initialisation or re-synchronisation thereof G06F11/1658 and subgroups
• CPC Class Codes: G06F 16/10 File systems; File servers
• CPC Class Codes: G06F 16/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
• CPC Class Codes: G06F 16/273 Asynchronous replication or reconciliation
• CPC Class Codes: G06F 16/275 Synchronous replication
• CPC Class Codes: G06F 16/3331 Query processing

Abstract

A system and method for transferring data between different types of systems, and in particular uses log-based replication to transfer data between different types of systems. 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, 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.

Claims

1. A system that uses log based replication to transfer data between a MySQL database or system that uses binary log files, and a second database or system, comprising:

a computer including a micro-processor;

one or more libraries for use in accessing a first database or system that uses binary log files; and

a vendor access module (VAM), running on the computer, and further including;

a plurality of binary log classes that wrap around the libraries,

a plurality of event classes, and

a plurality of reader and processor classes;

wherein the VAM

recognizes event data as recorded in a binary log file by the first database or system,

uses the plurality of event classes to convert the event data into data records,

reads and processes the data records with the reader and processor classes, and sends the data records to a record queue, and

communicates with an extract process to process the data records and to generate a trail file containing the processed data;

wherein the processed data is set up to be applied to the second database or system.

2. The system of claim 1, wherein the first database or system is a MySQL database and the binary log file is a MySQL binary log file, and wherein the second database or system is a non-MySQL database.

3. The system of claim 1, wherein the VAM is configured to recognize only a subset of the event data types recorded in the binary log file, and convert the subset of the event data types into data records.

4. The system of claim 1, wherein the extract process is an Oracle GoldenGate extract process, and the trail file is an Oracle GoldenGate trail file.

5. The system of claim 1, wherein the VAM provides an Application Program Interface (API) for the extract process to retrieve data records for particular transactions.

6. The system of claim 1, wherein a plurality of VAM event classes provide processing of events from the binary log;

a plurality of VAM binary log classes, operable to wrap around libraries provided by the first database or system; and

a plurality of VAM reader and processor classes, capable of reading and processing records from binlog classes and put the records into a data record queue.

7. The system of claim 1, wherein the VAM uses a log index file to determine a current binary log file to use, from a plurality of binary log files.

8. The system of claim 1, wherein the VAM recognizes log rotation events recorded in the binary log file, and used this information to obtain a next binary log name.

9. A method that uses log based replication to transfer data between a MySQL database or system that uses binary log files, and a second database or system, comprising the steps of:

providing one or more libraries for use in accessing a first database or system that uses binary log files,

providing a vendor access module (VAM) including a plurality of binary log classes that wrap around the libraries,

a plurality of event classes, and

a plurality of reader and processor classes,

recognizing event data as recorded in a binary log file by the first database or system,

converting the event data into data records by using the plurality of event classes,

reading and processing the data records with the reader and processor classes, and sending the data records to a record queue, and

communicating with an extract process to process the data records and to generate a trail file containing the processed data,

wherein the processed data is set up to be applied to the second database or system.

10. The system of claim 9, wherein the first database or system is a MySQL database and the binary log file is a MySQL binary log file, and wherein the second database or system is a non-MySQL database.

11. The method of claim 9, wherein the VAM is configured to recognize only a subset of the event data types recorded in the binary log file, and convert the subset of the event data types into data records.

12. The method of claim 9, wherein the extract process is an Oracle GoldenGate extract process, and the trail file is an Oracle GoldenGate trail file.

13. The method of claim 9, wherein the VAM provides an Application Program Interface (API) for the extract process to retrieve data records for particular transactions.

14. The system of claim 9, wherein a plurality of VAM event classes provide processing of events from the binary log;

a plurality of VAM binary log classes, operable to wrap around libraries provided by the first database or system; and

a plurality of VAM reader and processor classes, capable of reading and processing records from binlog classes and put the records into a data record queue.

15. The method of claim 9, wherein the VAM uses a log index file to determine a current binary log file to use, from a plurality of binary log file.

16. The method of claim 9, wherein the VAM recognizes log rotation events recorded in the binary log file, and used this information to obtain a next binary log name.

17. A non-transitory computer readable medium, including instructions stored thereon, wherein the instructions, when read and executed by a computer, cause the computer to perform the steps comprising:

providing one or more libraries for use in accessing a first database or system that uses binary log files,

providing a vendor access module (VAM) including

a plurality of binary log classes that wrap around the libraries,

a plurality of event classes, and

a plurality of reader and processor classes,

recognizing event data as recorded in a binary log file by the first database or system,

converting the event data into data records by using the plurality of event classes,

reading and processing the data records with the reader and processor classes, and sending the data records to a record queue, and

communicating with an extract process to process the data records and to

generate a trail file containing the processed data;

wherein the processed data is set up to be applied to a second database or system.