Chris, Interesting problem. We just went through a similar scenario. Our setup is:
1. A central server that has all data for everyone. 2. Remote databases (could be on either a single workstation or a server.) Requirements: 1. All administration (price changes, etc.) is done (web interface) on the central server. 2. Each remote site must be able to process transactions even if communications with central site are lost. 3. Only the data of interest to each remote site is replicated in that site's database. How we implemented: 1. A separate Java application runs on each remote computer that has a copy of a database. Periodically (timer), this application looks for data that has changed since it last checked. It knows the data that has changed because, for those tables that we want replicated, the primary key and table ID are put into a special table using triggers. 2. The changed remote data is sent to the central server (we have a Java application listening on a specific port) where it is stored and the primary key is returned to the remote (that way we always know the serial of the other side.) 3. When the remote has sent all the data it has accumulated, it asks the central server for any changed data (uses same process to determine what constitutes changed data) and that data is sent to the remote. In our case, we may have many remotes that are interested in the same or different data (several organizations may be running on the same central server.) Of course, there are the usual processes to make certain that data gets from one side to the other and that, once the data gets to the other side, it never comes over again (unless it is changed again.) Just our way of doing this (after three false starts.) Thanks, Carl ----- Original Message ----- From: "Chris Cowen" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Monday, March 20, 2006 10:34 AM Subject: multiple DB copies with periodic synchronization > 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 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 3/17/2006 > > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 3/17/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
