Synchronise two databases (but not really)

Question

I'm trying to design the following system.

An arbitrary number of of embedded systems will each record data constantly and indefinitely. It is required that these devices upload their data to a server which maintains a complete history of all captured data (and records the source of the data by an ID number of something). The embedded systems are required to keep a small local history of about a day.

Is it possible to use MySQL's replication functionality to do this? How would it work, since the local DB will be deleted periodically, while the server copy must never be deleted.

Another way is possibly just periodically running a program to upload the day worth of data and clear the table, but this seems messier especially on an intermittent internet connection.

What do you think would be a good design for this situation?

Thanks

Answer 1

Yes, but by using tricks. MySQL has statement based replication, which causes the same SQL to be executed on the target as on the source. So the question is, can you write a SQL statement that does what you want? The answer is yes if you can exclude the rows in the target somehow. One way to do this would be a column that is 0 on all the sources, but which you update to 1 on the target by some mechanism (batch job, trigger, etc). Then when you execute the DELETE on the source WHERE ... AND is_target=0 the statement will do nothing when it is replicated.

There is no way that I am aware of to simply have the target system filter out deletes (this can be done with with Shareplex so I assume GoldenGate too, but that costs money).

Answer 2

How about a design approach:

a) Each of the embedded systems dumps its data into an SQL file which it submits to the master database as soon as a connection is made (maybe on a daily basis) This means that even if a device is connected once a week the files from the past week are uploaded, and also separates out the functionality for backing up and uploading the files to the master.

b) on the master database a cron job is executed to load the data from each embedded device

This is assuming that it is okay for the master to have the data at least a day late, how much data is collected at the mobile device, and how intermittent the connection is.

Answer 3

I actually wrote a conceptual way to perform something like.

Back on April 26 2011, I wrote an answer to 'MySQL in star topology' in StackOverflow for the poster Johan. Two days later, Johan posted the exact same question in ServerFault. I provided more insight into this.

With regard to the StackOverflow answer I gave, I suggested a Star Topology where the Central DB Server is a Slave and each Laptop coming with new data would be temporarily be used as a Master. Of course, the Central DB Server can only be connected to one Master at a time.

It would be up to you to implement a common database on all external embbedded systems (EES) that match the Central DB Server's DB design.

Here are the details to remember when implementing this

• Each EES would have to have binary logging enabled
• Each EES would have expired_logs_days set to 2 or 3 so that binary logs would autodelete.
• You could have the EES keep the last binary log file and position in a text file and supply that info to the Central DB Server for it to know from what file and position to run the CHANGE MASTER TO command.

Of course, all this is conceptual but this is how it can be done.