List:General Discussion« Previous MessageNext Message »
From:Jim Grill Date:September 27 2004 6:42pm
Subject:Re: high availability question
View as plain text  
(Sorry for the long reply)
> 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?

Yes...Just like that. :-) They will both have the same data. I have a
similar set up in my office. In fact, I have a two way replication of all my
databases on two Linux machines and then I replicate only a few databases
from one of the Linux machines on a windows machine for cross platform
development purposes.

> safe, a dirty hack or a
> feature?

Well..... The thing is: the master server has no clue it is a master or how
many slaves are present. So, in effect, a master can also be a slave and
slaves can have slaves. This includes two-way replication. I'd say it's a
feature and not a dirty hack.

Safe? ...that depends.

The only thing that is not safe is when you write to both machines at the
same time. There is a possibility that updates can appear out of order at
heavy load times even after all the changes have propagated both ways. That
is to say that while you will have all your data in both databases but
records may be in different orders if they were inserted or updated before
propagation completed. This could be huge problem is the same record was
updated on both servers or if you rely on LAST_INSERT_ID() to be accurate.
There is a plethora of possible problems due to that fact.

There are two scenarios:

1) For performance: Perform updates and inserts on the master and perform
all reads from the slave. You can also replicate the master and slave on two
more servers to add some redundancy. Two way replication is not necessary
for this situation. If you know that you will read 70% of the time and write
only 30% of the time you could use the master for INSERT, UPDATE, and DELETE
and then use two slaves for reads.

2) For redundancy: Two way replication - both servers will be up to date and
you can read or write to either server. In this scenario it is best to
always read and write to *one server at a time* (the master) unless it
dies - then switch to the slave while repairs are made. You can then STOP
SLAVE on the slave and bring the master back up and replicate the slave from
the mysql client prompt. When the master as back up to date STOP SLAVE on
the master and START SLAVE on the slave again.

Note that in both scenarios two-way replication is not really necessary. The
only real benefit to having two way replication is if your a lazy bum like
me and don't want to have to type anything or think to much when a server
dies or you feel like switching server for some other reason.

I am aware of scenarios where two-way replication is used along with special
client code that will automatically connect to the slave if the master dies.
This way if a database server crashes in the middle of the night the client
code will simply redirect connections to the slave or back to the master if
it somehow comes back to life (how lucky). I'm not too sure how safe this
idea is and I'm sure that some unnecessary overhead is introduced by testing
for live connections all the time.

My best advice to you is to set up a few servers and start testing.
Replication is not at all hard to accomplish.

Best of luck.

Jim Grill

> 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
> > > 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
> > exists in replication regarding MyIsam tables is the "LOAD DATA FROM
> > command, which is slow anyway.
> >
> > All you need to do is either tar up the databases you want replicated
> > move them to the slave or create a dump of the databases you want. You
> > do this with "mysqldump --all-databases > myfile.sql" or for a selection
> > databases "mysqldump --databases mydb1 mydb2 mydb3 > myfile.sql".
> >
> > Once you have your databases loaded on the slave server follow the
> > procedure for replication outlined here:
> >
> >
> > You can set up dual or "two-way" replication by repeating the
> > 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
> > to speed after repairs. Of course you can run any of the commands
> > to create a slave or a master without have to take down the server or
> > 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:
> To unsubscribe:

high availability questionVictor Medina27 Sep
  • Re: high availability questionJim Grill27 Sep
    • Re: high availability questionVictor Medina27 Sep
      • Re: high availability questionSGreen27 Sep
      • Re: high availability questionJeremy Zawodny27 Sep
  • Re: high availability questionJim Grill27 Sep