mysql software: any suggestions to oversee my mysql replication server?

Question

I've had a tough time setting up my replication server. Is there any program (OS X, Win, Linux or php no problem) that lets me monitor and resolve replication issues? (btw, for those -uhm- following, ive been on this issue here, here, here and here. Either i'm going about this the wrong way or i must be immensely thick)

My production database is several megs in size and growing. Every time the database replication stops and the databases inevitably begin to slide out of sync i cringe. My last resync from dump took almost 4 hours roundtrip!

As always, even after sync, I run into this kind of show-stopping error":

Error 'Duplicate entry '252440' for key 1' on query.

I would love it if there was some way to closely monitor whats going on and perhaps let the software deal with it. I'm even all ears for service companies which may help me monitor my data better. Or an alternate way to mirror altogether.

Edit: going through my previous questions i found this which helps tremendously. I'm still all ears on the monitoring solution.

cheers,

/mp

Answer 1

To monitor the servers we use the free tools from Maatkit ... simple, yet efficient.

The binary replication is available in 5.1, so I guess you've got some balls. We still use 5.0 and it works OK, but of course we had our share of issues with it.

We use a Master-Master replication with a MySql Proxy as a load-balancer in front, and to prevent it from having errors:

• we removed all unique indexes
• for the few cases where we really needed unique constraints we made sure we used REPLACE instead of INSERT (MySql Proxy can be used to guard for proper usage ... it can even rewrite your queries)
• scheduled scripts doing intensive reports are always accessing the same server (not the load-balancer) ... so that dangerous operations are replicated safely

Yeah, I know it sounds simple and stupid, but it solved 95% of all the problems we had.

Answer 2

We use mysql replication to replicate data to close to 30 servers. We monitor them with nagios. You can probably check the replication status and use an event handler to restart it with 'SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; Start Slave;'. That will fix the error, but you'll lose the insert that caused the error.

About the error, do you use memory tables on your slaves? I ask this because the only time we ever got a lot of these error they where caused by a bug in the latests releases of mysql. 'Delete From Table Where Field = Value' will delete only one row in memory tables even though they where multiple rows.

mysql bug descritpion