What level of control do you have on the remote end that is collecting/dumping the data? Can you specify the command/arguments on how to dump? Is it possible to turn on binary logging and manually ship the logs rather than shipping the dump, effectively manually doing replication?
I agree with others, in general this approach smells like a bad idea. However, updating data from a remote system in batch is quite common, except often it's done at the application level polling things like web services and perhaps some business logic to ensure integrity is maintained. Attempting to do it within the constructs of the database itself is understandable, but there are risks when not adding that "layer" of logic to ensure state is exactly as you expect it during a merge. At risk of giving you too much rope to hang yourself: if you use mysqldump to dump the database, if you use the --replace flag you'll convert all INSERT statements to REPLACE, which when you merge will update or insert the record, effectively "merging" the data. This may be one approach you want to look at, but may not be appropriate depending on your specific situation. S On Mon, Feb 29, 2016 at 11:12 AM, lejeczek <pelj...@yahoo.co.uk> wrote: > On 29/02/16 15:42, Gary Smith wrote: > >> On 29/02/2016 15:30, lejeczek wrote: >> >>> On 28/02/16 20:50, lejeczek wrote: >>> >>>> fellow users, hopefully you experts too, could help... >>>> >>>> ...me to understand how, and what should be the best practice to dump >>>> database, then drop it and merge the dumps.. >>>> What I'd like to do is something probably many have done and I wonder >>>> how it's done best. >>>> A box will be dumping a database (maybe? tables if it's better) then >>>> dropping (purging the data) it and on a different system that dump swill be >>>> inserted/aggregated into the same database. >>>> It reminds me a kind of incremental backup except for the fact that >>>> source data will be dropped/purged on regular basis, but before a drop, a >>>> dump which later will be used to sort of reconstruct that same database. >>>> >>>> How do you recommend to do it? I'm guessing trickiest bit might this >>>> reconstruction part, how to merge dumps safely, naturally while maintaining >>>> consistency & integrity? >>>> Actual syntax, as usually any code examples are, would be best. >>>> >>>> many thanks. >>>> >>>> >>>> I guess dropping a tables is not really what I should even consider - >>> should I just be deleting everything from tables in order to remove data? >>> And if I was to use dumps of such a database (where data was first >>> cleansed then some data was collected) to merge data again would it work >>> and merge that newly collected data with what's already in the database >>> >> This sounds like a remarkably reliable way to ensure no data integrity. >> What exactly are you trying to achieve? Would replication be the magic word >> you're after? >> >> I realize this all might look rather like a bird fiddling with a worm > instead of lion going for quick kill. I replicate wherever I need and can, > here a have very little control over one end. > On that end with little control there is one simple database, which data > I'll need to be removed on regular basis, before removing I'll be dumping > and I need to use those dumps to add, merge, aggregate data to a database > on the other end, like: > today both databases are mirrored/identical > tonight awkward end will dump then remove all the data, then collect some > and again, dump then remove > and these dumps should reconstruct the database on the other box. > > Pointers on what to pay the attention to, how to test for consistency & > integrity, would be of great help. > > > Gary >> >> > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > >
