Synchronise two MySQL Dbs over WAN

[LittleSmudge]

I have a MySQL database with MSAccess front ends interfacing over ODBC.

Currently the users are at 8 geographically disperced sites connected via a VPN WAN and all is working okay.

However, one of the application databases is almost excusivly used at one remote site. Therefore the suggestion is to put an instance of MySQL ( we're using 4.1.12 ) at the remote site and let users interface to that ( quicker responce to user interface and resilient to WAN failure as they'll be running on LAN only. )

All of this I have done.

HOWEVER, the next step is to synchronise the copy at the remote site with the local ( head office copy )
( Thoughts are - that if this can be done at each site then the whole system is resilient to WAN fall over as local offices can keep working one their local instance if the WAN is lost )

I've tooked at MySQL Cluster but it is not recommended for Internet connectivity.

Any thoughts on how this can be achieved ?




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[TonyGroves]

Have a look at replication:

http://dev.mysql.com/doc/mysql/en/replication.html

 

[LittleSmudge]

Thanks Tony - I'm having a look into that.

Thinking about the problem off-line I've got some Un-natural Primary Key issues that make it a whole lot more complicated. However, from an initial look at MySQL replication I think it could lead to a workable solution.


Regards,



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[ericbrunson]

Note that your replicas will need to be query only. You can only write to the master. You can (apparently) set up a dual-master configuration, but I've never done it.

 

[lgarner]

Dual-master works, but isn't supported and you have to be very careful that conflicts can't happen. Virtually any DB error will break replication. I use it in a failover configuration.

 

[Kozusnik]

A small PERL script might work well. I manage several interfaces between databases (mostly HL7) and what I would recommend would be...

Create a new TINY INT field with a default of 0. You shouldn't have to change your programs at all for this.

Allow each record to be written. Write a PERL script to log in, query the database for all records where that field is zero. Store all fields in a array.

Use the same script to update the other database, then go back and mark that (those) record(s) as 1.

Next the script sleeps for a few seconds.

Later,
Mark