Database replication across different database platforms

• US 7,702,698 B1
• Filed: 03/01/2005
• Issued: 04/20/2010
• Est. Priority Date: 03/01/2005
• Status: Expired due to Fees

• Current Assignee: R2 Solutions LLC (Acacia Research Corporation)
• Original Assignee: Yahoo! Inc. (Apollo Global Management, Inc.)
• Inventors: Chakravarthy, Susarla
• Primary Examiner(s): Truong; Cam Y T
• Application Number:
  US11/070,724
• Time in Patent Office: 1,876 Days
• Field of Search: 707/200, 707/202
• US Class Current: 707/804
• CPC Class Codes: G06F 16/273 Asynchronous replication or reconciliation

Abstract

Database replication across different database platforms is carried out by transforming a binary log of database updates made to a first database into an SQL loader format of a second database and then processing the transformed log of updates into the second database. The transformation of the binary log includes the step of parsing the binary log into SQL statements, storing the SQL statements as a flat file, and transforming the flat file into an input file having the SQL loader format of the second database. The metadata of the flat files is stored in a separate database. As the flat files are transformed into input files of the second database, the status of the flat files is updated in the metadata database.

Claims

1. A method of replicating updates made to a first database in a second database, the method comprising:

reading a generated log of the updates, from a first position to a second position, related to the first database, wherein the log of the updates is a binary log and the first database is associated with a mysqlbinlog utility, wherein the mysqlbinlog utility is used to read the log of the updates;

parsing the log of the updates into a file comprising Structure Query Language (SQL) statements;

storing metadata related to the file comprising the SQL statements in a third database, wherein the metadata includes a processing status of the file and a file name associated with the file, wherein the first database is different from the third database;

transforming the file comprising the SQL statements into a SQL loader format of the second database, based in part upon the stored metadata in the third database, wherein the stored metadata is updated according to the processing status of the file;

processing the transformed file as an input to the second database to make at least one update to the second database; and

reading the generated log of the updates, from a third position to a fourth position, related to the first database, for transforming the log of the updates as input to the second database, wherein information included in the log of the updates between the third position and the fourth position was generated after information included in the log of the updates between the first position and the second position, and information included between the third position and the fourth position is for updating at least one update of the second database, wherein the updates recorded in the binary log having log positions that are greater than stored log position are parsed into the SQL statements;

waiting a predetermined amount of time to repeat the transforming of the file and the processing of the transformed file for updating, wherein multiple files each comprising SQL statements are generated and tracked using the third database, wherein the third database is controlled by a third server, wherein the first database is controlled by a first server and the second database is controlled by a second server, and wherein the log of the updates is stored in a local memory of the first server and the transformed file is stored in a local memory of the second server, wherein the transforming of the file includes a transformation of data stored as dates and a transformation of table names.

2. The method according to claim 1, wherein the log of updates is read using the mysqlbinlog utility and parsed.

3. The method according to claim 1, further comprising recording a log position of the last item in the log of updates that has been parsed.

4. The method according to claim 3, wherein the updates in the log having log positions that are subsequent to the last recorded log position are parsed.

5. The method of claim 1, wherein the metadata includes information related to a table to be updated in the second database and corresponding SQL statements.

6. A database replication system comprising:

a first database server programmed to receive inputs from users and generate a log of edits made by the users to a first database, wherein the log of edits is a binary log and the first database is associated with a mysqlbinlog utility, wherein the mysqlbinlog utility is used to read the log of edits;

a memory for storing the log of edits;

a second database server programmed to receive as inputs to a second database;

a first transformed file in a Structure Query Language (SQL) loader format of the second database, the first transformed file transformed from a first file comprising a first set of SQL statements, and a second transformed file in a SQL loader format of the second database, the second transformed file transformed from a second file comprising a second set of SQL statements,

wherein the first file comprising the first set of SQL statements that is parsed from the log of edits between a first position and a second position, the first transformed file is transformed based in part on stored metadata related to the first set of SQL statements, including metadata related to a processing status of the first file, the second file comprising the second set of SQL statements is parsed from the log of edits between a third and fourth positions, and the second transformed file is transformed based in part on stored metadata related to the second set of SQL statements, including metadata related to processing status of the second file, information included in the log of edits between the third and fourth position was generated after information included in the log of edits between the first position and the second position, and the second file updates an update of the second database included in the first file, wherein the edits recorded in the binary log having log positions that are greater than the stored log position are parsed into the SQL statements of the first set or the SQL statements of the second set; and

a third database server programmed to;

receive as inputs to a third database the metadata related to the first set of SQL statements, including metadata related to the processing status of the first file and a file name associated with the first file, wherein the processing status of the first file is updated in response to the first file being transformed into the first transformed file, wherein the first database is different from the third database; and

wait a predetermined amount of time to repeat the transforming of the first transformed file and processing of the first file for updating, wherein multiple files each comprising SQL statements are generated and tracked using the third database, wherein the third database is controlled by a third server, wherein the first database is controlled by a first server and the second database is controlled by a second server, and wherein the log of updates is stored in a local memory of the first server and the transformed file is stored in a local memory of the second server, wherein the transforming of the first transformed file includes a transformation of data stored as dates and a transformation of table names.

7. The system according to claim 6, wherein the second database server is further programmed to parse the log of edits into SQL statements to generate the first file comprising the first set of SQL statements, and to convert the first file comprising the first set of SQL statements into the first transformed file having the SQL loader format of the second database.

8. A computer-readable storage medium encoded with executable program instructions to be executed in a database replication system comprising a first database, a second database and a third database, the program instructions for:

reading a generated log of the updates, from a first position to a second position, related to the first database, wherein the log of the updates is a binary log and the first database is associated with a mysqlbinlog utility, wherein the mysqlbinlog utility is used to read the log of the updates;

parsing the log of the updates into a file comprising Structure Query Language (SQL) statements;

storing metadata related to the file comprising the SQL statements in the third database, wherein the metadata includes a processing status of the file and a file name associated with the file, wherein the first database is different from the third database;

transforming the file comprising the SQL statements into a SQL loader format of the second database, based in part upon the stored metadata in the third database, wherein the stored metadata is updated according to the processing status of the file;

processing the transformed file as an input to the second database to make at least one update to the second database; and

reading the generated log of the updates, from a third position to a fourth position, related to the first database, for transforming the log of the updates as input to the second database, wherein information included in the log of the updates between the third position and the fourth position was generated after information included in the log of the updates between the first position and the second position, and information included between the third position and the fourth position is for updating at least one update of the second database, wherein the updates recorded in the binary log having log positions that are greater than stored log position are parsed into the SQL statements;

waiting a predetermined amount of time to repeat the transforming of the file and the processing of the transformed file for updating, wherein multiple files each comprising SQL statements are generated and tracked using the third database, wherein the third database is controlled by a third server, wherein the first database is controlled by a first server and the second database is controlled by a second server, and wherein the log of the updates is stored in a local memory of the first server and the transformed file is stored in a local memory of the second server, wherein the transforming of the file includes a transformation of data stored as dates and a transformation of table names.

9. The computer-readable storage medium according to claim 8, wherein the program instructions are further for recording a log position of a last item in the log of edits that has been parsed.

10. The computer-readable storage medium according to claim 9, wherein the program instructions are further for parsing such that only the edits in the log having log positions that are subsequent to a last recorded log position are parsed.

11. The computer-readable storage medium according to claim 8, wherein the program instructions that carry out parsing invoke the mysqlbinlog utility.