Log-base data replication from a source database to a target database

• US 9,442,995 B2
• Filed: 07/31/2013
• Issued: 09/13/2016
• Est. Priority Date: 07/27/2010
• Status: Expired

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Dubey, Avinash, Pareek, Alok, Lakshminarayanan, Mahadevan, Corbin, Scott
• Primary Examiner(s): Nguyen, Loan T
• Application Number:
  US13/956,175
• Publication Number:
  US 20130318044A1
• Time in Patent Office: 1,140 Days
• US Class Current: 1/1
• 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 for data replication between a source database and a target database, comprising:

a computer including a micro-processor;

a source database having data stored therein, and one or more binary logs associated therewith which record event data describing changes made to the data during a transaction at the source database;

a replication module running on the computer and including a plurality of reader and processor classes, each class executing on a dedicated thread, to replicate the changes made to the data at the source database, for use with a target database, by

reading, from the one or more binary logs, the event data describing the changes made to the data during the transaction,

processing the event data to determine events describing one or more full or partial data changes at the source database, and

storing records into a record queue of a particular size, wherein the records reflect the determined events;

wherein, upon a subsequent request to replicate data to the target database, the system invokes a plurality of record classes to retrieve the records stored in the record queue to create a trail file, wherein the trail file is then communicated to the target database and used therein to replicate the changes made to the data, from the source database to the target database.

2. The system of claim 1, wherein all of the event data are statements that operate to modify data in the source database.

3. The system of claim 1, wherein the replication module is configured to receive event data that represent modifications to the source database only.

4. The system of claim 1, further comprising one or more libraries that access the source database.

5. The system of claim 4, wherein the replication module includes one or more binary log classes that wrap around the libraries.

6. The system of claim 1, wherein the replication module is configured to read event data from one or more additional binary log files.

7. The system of claim 1, wherein the source database includes transactional tables, and updates to the transactional tables are cached until committed.

8. The system of claim 1, wherein the source database includes non-transaction tables, and updates to the non-transaction tables are received into the binary log file when executed.

9. A method that uses log-based replication to transfer data between a source database and a target database, comprising:

providing a replication module including a plurality of reader and processor classes on a computing device, wherein the computing device includes a processor and a source database having data stored therein, and one or more binary logs associated therewith which record event data describing changes made to the data during a transaction at the source database;

using the plurality of reader and processor classes, each class executing on a dedicated thread, to replicate, the changes made to the data at the source database, for use with a target database, including

reading, from the one or more binary logs, the event data describing the changes made to the data during the transaction,

processing the event data to determine events describing one or more full or partial data changes at the source database, and

storing records into a record queue of a particular size, wherein the records reflect the determined events; and

invoking a plurality of record classes to retrieve the records stored in the record queue to create a trail file, upon a subsequent request to replicate data to the target database, wherein the trail file is then communicated to the target database and used therein to replicate the changes made to the data, from the source database to the target database.

10. The method of claim 9, wherein all of the event data are statements that operate to modify data in the source database.

11. The method of claim 9, wherein the replication module is configured to receive event data that represent modifications to the source database only.

12. The method of claim 9, wherein one or more libraries access the source database.

13. The method of claim 12, wherein the replication module includes one or more binary log classes that wrap around the libraries.

14. The method of claim 9, wherein the replication module is configured to read event data from one or more additional binary log files.

15. The method of claim 9, wherein the source database includes transactional tables, and updates to the transactional tables are cached until committed.

16. The method of claim 9, wherein the source database includes non-transaction tables, and updates to the non-transaction tables are received into the binary log file when executed.

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

providing a replication module including a plurality of reader and processor classes on a computing device, wherein the computing device includes a processor and a source database having data stored therein, and one or more binary logs associated therewith which record event data describing changes made to the data during a transaction at the source database;

using the plurality of reader and processor classes, each class executing on a dedicated thread, to replicate, the changes made to the data at the source database, for use with a target database, including

reading, from the one or more binary logs, the event data describing the changes made to the data during the transaction,

processing the event data to determine events describing one or more full or partial data changes at the source database, and

storing records into a record queue of a particular size, wherein the records reflect the determined events; and

invoking a plurality of record classes to retrieve the records stored in the record queue to create a trail file, upon a subsequent request to replicate data to the target database, wherein the trail file is then communicated to the target database and used therein to replicate the changes made to the data, from the source database to the target database.

18. The non-transitory computer readable storage medium of claim 17, wherein all of the event data are statements that operate to modify data in the source database.

19. The non-transitory computer readable storage medium of claim 17, wherein the replication module is configured to receive event data that represent modifications to the source database only.

20. The non-transitory computer readable storage medium of claim 17, wherein one or more libraries access the source database.