we are designing a data collecting system, at each site we have a
database and have a central database,we will write data to site's
database, we want the site-database can publish
the data to central-database Asynchronously, how could we do that?
single replication sounds work,but it will replicate all update
,including delete ,we dont want that,we want only inserted data .
rolandsun@gmail.com wrote:
> we are designing a data collecting system, at each site we have a
> database and have a central database,we will write data to site's
> database, we want the site-database can publish
> the data to central-database Asynchronously, how could we do that?
>
> single replication sounds work,but it will replicate all update
> ,including delete ,we dont want that,we want only inserted data .
>
This is something I have seen done in many different ways. Basically
your central database is a slave to many masters - unless you also push
something to the remote databases on a regular basis (n-way
replication). I have not read all of the rules and abilities of the
replication options, but so far I do not see how to restrict your
replicated data to just insert/update. Maybe this weekend I will see if
I can dig something up.
Make sure your tables that are to be replicated have an insert timestamp
and an update timestamp. you would then write a process that would
extract records inserted/updated since last update (have an update table
that uses a timestamp to determine the "end of the last period") and
push those records to the central repository.
Now what gets really fun here is any referencial integrity that must be
maintained. Description tables must be uploaded before data tables etc...
Just thought of something...
If you use multi-master replication it might be possible to have a
tablename in the slave (central-server) that has a before-delete trigger
to move the data to a "history" table.
These are all things you will need to consider and I generally charge
big $$$ for creating/designing such databases :)