Mysql – Synchronise two databases (but not really)

backupMySQLreplication

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

Best Answer

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).

Related Question