[Linux-HA] Recommended MySQL Setup

[Eddie C]

http://www.onlamp.com/lpt/a/6549
Read this acticle they talk about 2 way and 4 way multimater replication.
Its pretty cool. The replication is circular BTW

Edward

On 10/26/06, Dominik Klein <dk at in-telegence.net> wrote:
>
> CA Lists schrieb:
> > I've seen a number of articles on the web about setting up linux-ha and
> > MySQL. A number of them involve using the built-in MySQL replication,
> but it
> > seems that could be problematic if the primary server goes down. So, I
> was
> > curious, is this the generally recommended setup, or would others on the
> > list recommend just using an LSB script to start MySQL on one host and
> > migrate it over to another in the case of a problem? If that's the case,
> > does someone have a sample of an actual LSB compatible script to use
> with
> > linux-ha (I'm using RHEL ES 4), as the default one from MySQL does not
> > support status? Thanks,
>
> A solution that has not been mentioned yet is the following:
>
> Consider two machines A and B.
>
> A is B's slave
> B is A's slave
>
> This is called master-to-master replication. Make sure you set proper
> values for auto_increment_increment and auto_increment_offset. Read more
> on the myspl doc on this!
>
> A has VIP1
> B has VIP2
>
> Writes go to VIP1
> Reads go to VIP2
>
> If A is down, B will take over VIP1, clients have to reconnect but can
> write right away.
>
> If B is down, A will take over VIP2, clients have to reconnect but can
> read right away.
>
> You can also write to VIP1 and VIP2, but your application should then be
> sure that writes to VIP1 are not on the same tables as to VIP2, as there
> is no check on simultaneous updates/inserts.
>
> Backup and Restore:
> Now consider machine C, which runs 2 mysql instances as backup. 1
> instance replicates A, one replicates B. So if A crashes, you can dump
> B(yes, B)'s backup slave, re-insert that data to A und use the slave
> info from the backup slave to get the data A missed while A was offline.
>
> Additional:
> A and B might have 2 NICs each. 1 for mysql connections, 1 direct
> connection between A and B. So if MySQL fails on A, it can re-direct
> mysql connections on VIP1 to the internal IP on B. You could also switch
> VIP1 to B in that case.
> Anyway, you will need some sort of healthcheck on this.
>
> There is an article I wrote about this on:
>
> http://www.austintek.com/LVS/LVS-HOWTO/HOWTO/LVS-HOWTO.failover.html#ha_mysql
> _______________________________________________
> Linux-HA mailing list
> Linux-HA at lists.linux-ha.org
> http://lists.linux-ha.org/mailman/listinfo/linux-ha
> See also: http://linux-ha.org/ReportingProblems
>