Hi
I have built and am testing a php module that is capable of this.
I have an ultra high availability project where it must survive a server or
network failure and switch seamlessly to the backup server.
All queries are executed through a db abstraction class. It basically does
the same as replication, but is live.
If the query changes the data (i.e. UPDATE, INSERT etc) then it attempts to
connect to the slave, if it fails to connect, it saves the query in a local
table and executes it when it can connect.
The normal flow is .....
execute query on master
if (connect to slave)
{
execute query saved in slave db on master db (this will only happen if
there has been a master<-->slave connection failure or the master was
offline, and synchronises the master with the slave)
execute query saved in master db on slave db (this will only happen if
there has been a master<-->slave break in communication or the slave was
offline. This synchronises the slave.)
delete or mark as executed, all saved queries
execute current query on slave
}
else
{
//if it cannot connect
save query to local table (which will be executed when connection can be
established)
}
I am still testing to see if there are any odd effects, when there is an
intermittent fault so a user session is spread over both machines, for
example.
The same process could be adapted to cover multiple slaves, with the master
collecting all the saved queries from the slaves and then executing them.
Perhaps you could timestamp the queries saved during a master outage so that
they are execute in the correct order.
Food for thought, be interested to hear how you get on.
Let me know if I can help
Peter
-----------------------------------------------
Excellence in internet and open source software
-----------------------------------------------
Sunmaia
www.sunmaia.net
[EMAIL PROTECTED]
tel. 0121-242-1473
-----------------------------------------------
> -----Original Message-----
> From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]]
> Sent: 10 May 2002 23:07
> To: Christophe Demange
> Cc: [EMAIL PROTECTED]
> Subject: Re: Distributed MySQL : How to synchronize ?
>
>
> On Fri, May 10, 2002 at 04:51:15PM +0200, Christophe Demange wrote:
> > Hello,
> >
> > I have a question:
> >
> > We want to real-time synchronize a MySQL database part with a
> > distant database. We have a central database containing all the
> > data. This database must share several parts (records) with the
> > local databases. All transactions made in the central database must
> > be replicated to the corresponding local database. This will ensure
> > to have the data available while the network is down. In the same
> > time some transactions can be made in the local database, this
> > includes to have a dual-way replication mechanism.
>
> Hmmm.
>
> > I have searched replication documentation and articles and I have
> > only found master-slave relationship. We think this master-slaves
> > relationship doesn't exactly fit our needs.
>
> It probably doesn't, but...
>
> > Please could you explain us what is the best solution to use for
> > this architecture.
>
> Since you asked, here's a thought that'll require a bit of research
> and testing on your end. It might get you some of the way there.
>
> Rather than write records to a single table on the master, break
> things out into several identically structured tables. The create a
> MERGE table out of them. Run your write queries against the
> underlying table appropriate for the record.
>
> That *may* allow the slaves to replicate only the data they need. I
> say "may" because I don't know enough about your application to make
> that judgement.
>
> The only problem arises in the getting updates made on the slaves back
> to the master. A slave in MySQL can only have one master, so that's a
> more difficult problem.
>
> Jeremy
> --
> Jeremy D. Zawodny, <[EMAIL PROTECTED]>
> Technical Yahoo - Yahoo Finance
> Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936
>
> MySQL 3.23.47-max: up 92 days, processed 2,403,910,165 queries
> (301/sec. avg)
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
