If your version of MySQL supports replication, you can use Circular
replication. Of course, the more recent version you use, the fewer bugs
you will potentially run into.
Circular replication (dual master/dual slave) is only semi-stable unless
you perform some kind of application level locking to form your own
distributed locking scheme. MySQL has yet to implement distributed
(multiple server) table locking so that an insert or update on one master
does not conflict with a statement executed at the same time on the other
master.
Many people solve this problem by only performing INSERT,UPDATE, or DELETE
statement on one master or the other but not both at the same time. I have
seen several scripts that "auto-promote" one server to "head master" and
deal with the issues of re-synching a previously downed replication
partner. Search this list (and others) and hit those books. Workable
solutions exist and are available.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Victor Medina <linux@stripped> wrote on 09/27/2004 01:34:33 PM:
> Hi Jim, Hi all!
>
> Doing a two way replication, assures me that i can write and read data
> from both servers? I mean, the app servers, can read from any data base
> on demand. If i write to server A, will server B automatically be
> notified of the change, and be properly updated? If i change server B
> instead, will server A be notified and updated?
>
> For this to work, what MySQL version do i have to use? 4, 4.1, 5.0????
>
> I own both books by Jeremy and Paul, in Paul's book (the second edition,
> BTW) I almost certainly understood that replication is one way only,
> Master2Slave, the "cheat" ( just to give it a name! =) ) of configuring
> Master and Slave in a two way relationship is safe, a dirty hack or a
> feature? Paul's book is for MySQL 4, what version do i need for this to
> work?
>
> I can hard code the app to query any of the two db, but i _MUST_ assure
> that at any given time, they both have the same data.
>
> As always, thanxs! =)
>
> Vic.
>
>
>
> On Mon, 2004-09-27 at 12:43, Jim Grill wrote:
> > > Hi guys!
> > >
> > > I am having some doubts in how to implement a high availability,
> > > geographically distributed MySQL DB.
> > >
> > > The thing is, I would like to have.. let's say two (02) app. servers
> > > with two (02) DB servers, running exactly the same data. In case one
> > > fail, the other will be available. The thing is, how can i ensure
that
> > > both DB servers has exactly the same data? I took a look at
Replication
> > > features, but it seems to only use MyISAM tables. This way should
not
> > > work since, both App. Servers can insert data at any given moment,
but
> > > having ONLY one server isn't the answer neither. I would like to
know
> > > any suggestions on how to solve a problem like this. Any ideas are
> > > welcome. Any suggestions are also VERY welcome.
> > >
> > Replication is *not* limited to MyIsam tables only. The only
limitation that
> > exists in replication regarding MyIsam tables is the "LOAD DATA FROM
MASTER"
> > command, which is slow anyway.
> >
> > All you need to do is either tar up the databases you want replicated
and
> > move them to the slave or create a dump of the databases you want. You
can
> > do this with "mysqldump --all-databases > myfile.sql" or for a
selection of
> > databases "mysqldump --databases mydb1 mydb2 mydb3 > myfile.sql".
> >
> > Once you have your databases loaded on the slave server follow the
normal
> > procedure for replication outlined here:
> > http://dev.mysql.com/doc/mysql/en/Replication_HOWTO.html
> >
> > You can set up dual or "two-way" replication by repeating the
replication
> > procedure on the master by specifying the salve as the master. This
would be
> > useful in the event that the master goes down and you need bring it
back up
> > to speed after repairs. Of course you can run any of the commands
necessary
> > to create a slave or a master without have to take down the serveror
create
> > a two-way replication scenario as long as the server-id's are
different.
> >
> > I hope that helps.
> >
> > Jim Grill
> >
> >
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
