Chris Cowen <[EMAIL PROTECTED]> wrote on 03/20/2006 10:34:48 AM: > Hi > > We have a mySQL database which is being used by a restaurant ordering > system, in which many of the tables > are being used to store menu item information, pricing etc. > The restaurant started off as a single outlet, but is now about to open > some more new premises. > > We would like to have ordering systems in the new premises which can use > the information from the > database on the original machines. For operational reasons, we want to > synchronise the tables that hold > all the menu information once a day, and then use the local copies > throughout the day. Synchronisation will > be over a VPN. We would prefer to do it this way, so that it the VPN > goes down (e.g. WAN or phone line is out), the > restaurant can still operate using the last synchronised copy of the > menu. (as opposed to simply sending the SQL commands over the VPN). > > There will be one "master" machine will be where the restaurant managers > make changes to their menus, > which will get picked up in the morning by the remote machines. The > master will also be used > to store transactions from all the other branches (where it can be > backed up). > > We'd like to synchronise the menu information in the morning, before the > restaurant opens. Then after they close, > the transaction tables for the days sales to be synchronised back to the > master machine. > > What is the usual approach in this sort of case? Do we: > > 1) write our own perl or php script to run the sql commands we need to > synchronise? There's not a lot of tables. I don't know much about MySQL > commands for synchronisation, or even if there are any. > 2) use a third party synchronisation tool ? I looked at SQLyog, but we > don't need a GUI. > 3) is there another way? - for example a built-in mechanism in mysql to > allow duplication with regular synchronisation. > > Sorry if this is a dumb question - but I'm sure this type of scenario > must be fairly common, for example when implementing redundant or > distributed databases, so I would be very interested in hearing about > people experiences and opinions. > > Thanks > > Chris >
For your "master-to-copy" synchronization, MySQL already has the facilities for this. Check the section of the manual for "replication" http://dev.mysql.com/doc/refman/4.1/en/replication.html For the situation where you store transactions in each satellite restaraunt and at the end of the night you want to re-synch with the "master" database back at HQ, that's something you will need to script. The reason is, MySQL replication is all one-way. Each replication source (the master) can auto-synch with one or more destinations (slaves) but each slave can only listen to one master at a time. That means that you cannot setup a database at HQ to listen to your multiple satellite sites using the built in facilities. However, you can cascade several servers and you can set up replication to move in a circular pattern. Each of these designs have positives and negatives and you should really understand replication a little better before making a decision. More details are in the reading. Several varieties of questions similar to yours have also been discussed on this list. You should check the archives, too, for more information: http://lists.mysql.com/ Shawn Green Database Administrator Unimin Corporation - Spruce Pine
