"L. Yeung" <[EMAIL PROTECTED]> wrote on 09/07/2004 08:38:38:
> Hi! I wanted to set-up a master-slave replication on
> my win2k box. When my master server fails, my slave
> server will automatically becomes my new "master
> server". And when my master server is back online, any
> changes on my slave server is replicated back to my
> master server.
>
> Normal: A -> B
> ^
> L-------- inserts/updates from clients
> Failure: X B
> New Master: X B <- inserts/updates from clients
> Master is back: A <- B x- inserts/updates are blocked
> manually.
> Normal: A -> B
> ^
> L-------- inserts/updates from clients
>
> Any idea? Thanks.
You cannot do this automatically: you need a supervisor progream.
The way you need to do it is to have both machines come up with their
slave thread *not* running. The supervisor then determines which is master
and which slave, and starts the appropriate slave thread running. If it
determines that the original master has failed, it stops replicatio on the
slave and directs updates to it: the slave has now become master. When the
original master reappears, it determines that updates have been made to
the original slave later than those to the original master, it instructs
the originl master to reload its databse from the slave. Master and slave
have now exchanged roles.
The determination of which is the most-recently updated is done by a
single row, single column table which is incremented whenever the slave
takes over from the master. If, at startup, two active machines are found
with differeent values in this entry, the higher value becomes master and
the lower must be re-synchronized. If the values are the same, the slave
status can be inspected to see which is slaving to which.
We have implemented such a system in our own middleware. We have a target
changeover time of 10 seconds, which we are meeting easily. It only works
for MyISAM tables, since LOAD DATA FROM MASTER is only available for
these.
Note to MySQL development team: this request comes up often enough that I
hope the idee of embedding this supervisor in the MySQL daemon is at least
on the wish list.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
