MySQL Replication / Failover

[idx]

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

[idx]

Just installed FreeBSD 5.3 and the gmirror program [in GEOM] looks pretty slick for network-based file systems. (iSCSI'ish) I think I'm going to buy the `High Performance MySQL` book by Jeremy Zawodny and see how that works out.

The new dell dx280 is pretty darn fast [for me at least] so there's not much waiting for things to compile. At any rate it should be an interesting learning experience.

-r

[idx]

Not sure if anyone is interested, but it's been an interesting ride. (almost there)

I ended up installing spread as a base for internal messaging between nodes and wrote a little perl daemon to facilitate things like forcing backup nodes to sync, reload their daemon, shutdown, etc...

So upon boot a script checks the spread room to see if another server thinks it's the master.. If so and that server has a lower priority it sends me an email and sits there.. From there I can manually initiate a sync script so the more powerful machine will sync then takeover as the master..

As far as the failover issue with MySQL, it seemed to work best that the new master run a `reset master;` then contact each of the other backup nodes to change master (replication is on a network internal to the cluster, so there's no virtual IP there..) and run a `reset slave;`.. Otherwise the slave SQL thread on the backup DB's kept halting due to duplicated binlog statements...

If a node gets too far out of sync and it missed some updates a simple `load data from master;` does the trick.

Also ended up installing the hobbit monitoring system as a replacement for big brother.. Really nice stuff and works well during failover.

Still think I need to get more in tune with mysql replication, but it's working well enough to let the users loose on it..

In limited testing it seems to be pretty seamless. I setup a page to refresh every second then shutdown one node and the next starts working without much delay. (page increments a session var -- sessions were moved to the DB, so no loss during failover) It sometimes takes 3-5s, but that's not bad for cheapo hardware and my shoddy code


-r