Re: multiple DB copies with periodic synchronization
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: 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]
Re: multiple DB copies with periodic synchronization
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
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]