Mysql Distributed Databases

Kimball Larsen kimball at kimballlarsen.com
Sat Feb 5 02:12:21 MST 2005


This may have been posed before, but:
   
    Using MySQL, is it possible to set up a distributed database wherein 
it mattereth not which node is updated?  

    From reading the docs, I gather that typical MySQL replication is 
pretty much a one-way road.  Updates to the master are propagated to the 
slaves.  Simple enough... but what if I can't get to the master and I 
have to update a slave instead... is there a process to get those 
updates rolled back into the master?

    A more concrete example:  Let's say I have opened a chain of burger 
joints.  Each burger joint needs to know how many ketchup packets they 
have on hand.  As ketchup packets are distributed to customers, the 
burger joint updates its records to reflect the decrease in ketchup 
stock.  Now, let's say that each of your locations would like to know 
how much ketchup is left at the other locations.  This would be useful 
when a particular location runs low - it can simply request some from a 
location with plenty.  This seems like a perfect spot to stick a 
centralized server that keeps track of all the inventory for everyone.

    But what happens if the network connection to a particular location 
goes down?

    That location can no longer update the stock levels on the main 
server... pandemonium!

Ideally, here is the sort of solution I am looking for:  To service 
customer requests, I MUST have access to a data store that has 
information about other points of sale in my business.  I cannot 
guarantee that I will always have a good connection to each of the 
points of sale.  I would like to have a "master" centralized data store 
that all locations use by default.  If a network connection dies, I 
would like that location to have a local copy of the master that is as 
up to date as possible.  As soon as the connection is restored, I'd like 
to have any transactions that were recorded on the local copy pushed 
back to the master, so the other points of sale can become aware of the 
data changes.

Suggestions?  Can MySQL even do this?

Thanks!

-- Kimball





More information about the PLUG mailing list