A simple method for synchronizing heterogeneous databases

The title is a bit tall, is to solve the practical application of a problem. Do an android application, used to record the daily consumption bill, the beginning is a stand-alone version, my wife said too low, at least to see each other's consumption. To this end, I also specifically wrote a set of Protobuf-based RPC components for network communications, http://www.cnblogs.com/zmkeil/p/5176758.html.

　　The app itself is simple, with a few simple, rough uis that cover adding, deleting, changing functions, plus a backend service component for uploading bills and syncing others ' bills. It is also a little perfectly formed, see a few. Code See Https://github.com/zmkeil/MyBill, can be installed directly to use, do not want the bill by my peeping, in the configuration of the server address is scrambled.

The things to Do

Anyway, according to the way the DBA works, the simplest way to synchronize a database is to put all the operations on one library completely on the other, and MySQL's master-slave library uses Binlog to copy all of the INSERT, UPDATE, delete operations, To achieve synchronization, which is actually an incremental synchronization of the idea. Using this idea, we mainly do two things in this application:

1. Upload your own bill operations to the service side;
2. Synchronize other people's billing operations from the service side.

To minimize complexity, we have defined only two types of billing operations: INSERT, update. Add a is_deleted field to the table and update the field to implement the Delete function.
Grasp a most basic principle, the service side is only responsible for recording billing operations, not to save any client state (such as what has been synchronized, etc.), in other words, the server only provides the most basic requirements: 1) There is a new operation, I wrote the operation into the database, and recorded it, for others to synchronize ; 2) to synchronize the operation of others, you need to provide the operation from which to start the synchronization, the maximum number of synchronization.

Interface Preview

First look at the PROTOBUF-RPC service, very simple, only one interface.

Package Microbill;option cc_generic_services = true;message Record {enum Type {NEW = 0;　　UPDATE = 1;　　} Required Typetype = 1;　　Required Stringid = 2;　　Required FIXED32 year = 3;　　Required FIXED32 month = 4;　　Optional FIXED32 day = 5;　　Optional FIXED32 Pay_earn = 6;　　Optional String gay = 7;　　Optional String comments = 9; Optional FIXED32 cost = 10;}　　Message Billrequest {Required String gay = 1;　　Push self ' s records repeated Record records = 2;　　Pull other ' s records optional fixed32begin_index = 3; Optional fixed32max_line = 4 [default = 10];}　　Message Billresponse {Required boolstatus = 1;　　Optional stringerror_msg = 2; Repeated recordrecords = 4;} Service Billservice {RPC update (billrequest) returns (Billresponse);}

Request has three areas of information:

• Who is the current user
• This minor upload billing action (can be empty, have all uploaded)
• The start index of someone else's billing operation that needs to be synchronized (in ... This is numbered, the implementation is shown later), and a maximum of several operations are synchronized (preventing packets from being too large).

Response has two areas of information:

• Whether the upload was successful
• If someone else has a new action, a new action is recorded in the records.

Specific implementation

Here's a look at the specific implementation. Of course, Android uses SQLite to record bills, and the server uses MySQL. The application itself does not need real-time, but reliable, can not appear data duplication, missing and so on. The program runs on the client for a short period of time, the user may turn off the record, and the network does not necessarily open. The service side of the operating environment is relatively stable, the program can always run (as long as not crash), the network is more stable. Key issues to solve:

1. How does the client know which operations have been successfully uploaded, and what actions are waiting to be uploaded?
2. Server to receive multiple users upload up the operation, how to save, management, in order to facilitate the synchronization of others?
3. How does the client log what actions someone else has synchronized?

　　the first question, which focuses on the first thing. This is relatively simple, purely client-side things, do not need to service end mates. can refer to the binlog idea of MySQL, specifically with a records.txt file to record all operations, the format is as follows:

Index    operate    ID

1. Index starts from 1, increments sequentially, and uniquely indicates the operation. This allows you to use another file Updated_index.txt to record which one has been uploaded (in order to upload), this file is very simple, just record an index. Then the next upload, first of all, according to Updated_index.txt to find the starting index to upload, and then to Records.txt to find (direct seek to the index line can), each default upload 2 operations. The Updated_index.txt file (that is, the original index + = 2) is updated only if Response.Status = True. Here are two questions:

• Over time, Records.txt will be very big, every upload to start from scratch seek will be very performance. Here I am separated by the month, each month a separate document. The service side will not have this problem, because the data is resident memory.
• Sometimes due to network problems, an upload has been to the server, the service has been updated, but response did not correctly back to the client, then the client will not update the Updated_index.txt, the next time will be repeated upload these operations records. It doesn't matter, the server has done the deduplication (very simple, as long as the primary key features can be used), but still recorded in the Operation list, the other clients will be downloaded to duplicate operations, it is OK, the client also did to download the deduplication. Ha ha! It's a bit of a hole here, my wife found out.

2, operate record the operation type, as described above, only insert,update two kinds of operation
3, ID records the object of this operation (the ID field value in the library), if according to Binlog, should record the operation data (such as cost,comment,day, etc.), but that will be more complex. So only the ID value is recorded, and then to the library to check the specific data. There's an optimized point here: For the update operation, only one field may be updated, but all fields are filled in the request.

• In addition, the ID here is of type string, the format "User_year_month_int", consisting of the user name, year, month, and an incremented integer, so that each user's ID will not be the same.

　　The second to third problem , mainly for the second thing, is actually the client and server-side coordination, to achieve the purpose of synchronization between multiple clients. First of all, the service side of all the bills are recorded in a table, but also with the ID as the key value, as mentioned above, this ID value is not duplicated.
1, the service side in accordance with the user dimension, upload the operation record to manage. Prepares a queue for each user, and the elements in the queue are similar to each record in the Records.txt file on the client. The difference is that someone else's actions are recorded: Whenever a user uploads a new action record, the server first writes the action to the library, and then adds the action to all other users ' queues.
2, in addition each user prepares a file (Append mode opens), each action record writes to the queue, writes to the file first. When the service end multiplicity, the queue can be recovered from the file.
3, the customer requests come, first take out the Begin_index,max_line field, and then to his own queue to find, if Begin_index has exceeded the length of the queue, stating that there is no new update; otherwise find the Max_line action record, The Response.records (same client) is populated with the specific data from the ID to the library.

• There is a problem here, if there are many users at the same time, in addition to their own, others are mixed together. Because only me and my wife two people to use, here will do it; in fact, you can distinguish it by adding a field user to the queue element.

4, the client with a sync_index.txt file, the next time you want to synchronize the action record index, the initial 1, each time response.records is not empty, update the value (+ = Respons.records.count ()).

Summary

Just beginning to think very simple, but now back and forth fast 4 months, hehe ~ ~ Now there is a more OK version, the code is not rigorous, fill and fill, the function is OK.

Remember just started to write RPC framework, enthusiasm high, daily work to write to the early morning 2, 3 points, then exactly the coldest time, give yourself a praise. Later writing Android, it is more procrastination, and user operations directly related, will be more annoying.

To this.

Report:

RPC Framework, http://www.cnblogs.com/zmkeil/p/5176758.html
Service-side code, Https://github.com/zmkeil/microbill-server.git
Android Code, Https://github.com/zmkeil/MyBill.git

A simple method for synchronizing heterogeneous databases