My advice on multi-master replication: two is sometimes not enough. I have four servers, two of them multi-mastering and each has a slave behind it, allowing me to lose a master but still have capacity to withstand load.
Script tools I had to develop are along these lines:
. snapshot replication script ( I've used this a LOT, I've had to rebuild masters and slaves commonly)
. a firewall toggle script: you need to pull your database in and out of the pool but NOT disturb replication, the IP tables setup permits other databases to replicate but bars web head access when toggled
. a copy of maatkit and scripts to run it across your tables effectively; think of maatkit more like a db script library than a solution itself, it can powerfully destroy your data very fast; you want to be able to tackle the challenge of corrupted tables on one master without destroying valid data that didn't replicate to the other...I've seen this happen.
. a process to update the copy of mysql on your system; the default packages that come with centos, for instance, are over two years old and there's been lotsa bugs fixed in that time...that do things like crash tables
. load monitoring: monitor num of threads, amount of replication lag
. application error log monitoring, where are your queries failing?
. ability to row-by-row compare your tables to find what data failed to replicate; maatkit can help with this but some php and a for loop might be more judicious in some instances. Consider checking a 8GB table in 10,000 row chunks to keep your table from locking up when using maatkit...then use your own script to find the row in the 10,000 row range.
. don't forget mytop
Good luck!
|