Anyone have a good bit of experience with this?
I currently replicate the data on one server to another and it's working fine, but I'm looking to add several servers into the mix (via freevrrpd) and have mysql be able to fail over to another server then recover when the primary is back..
With freevrrpd I can execute scripts when a server becomes a primary or backup (aliases the IP/mac/etc.), but I'm not solid on the mysql end.
setup:
P - Primary. freebsd, apache, mysql, php, etc..
B1
B2
B3 - ^^ backup servers. All identical except for the private IP's. File system data is synced from
P to each [rsync] and mysql is obviously replicated.
So the MCSE guy hits ctr-alt-del several times to get a login prompt on
P so it reboots. FreeVRRPD sees this and promotes
B1 to the primary so the IP-side is well. Mysql is still running as a slave thread, so we stop it (STOP SLAVE; )..
__now. Since B1 took over the virtual IP (previously used by
P) I don't believe the other backup servers will need to change anything. (eg:
change master to)
The big question is: what about other backup servers that have read up to a certain point [in the binlog] on the primary, but are now going to ultimately hit the
B1 server due to the IP alias change?
The same thing goes when we want
P to come back up as the primary. Fire up mysql then manually start a slave thread in order to sync back up then have freevrrpd promote it back?
Heck, maybe mysql's cluster product is the way to go. Doesn't sound like it's GPL..
-r