Re: multiple DB copies with periodic synchronization

2006-03-20 Thread SGreen 
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

Re: multiple DB copies with periodic synchronization

2006-03-20 Thread Carl 
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]