Tuesday, November 25, 2008

MySQL Replication and bad assumptions

Sometimes I amaze myself in my capacity to make assumptions about how things should work, especially when it comes to test plans... ( You know what happens when we assume, right? )

I had this great idea to setup a couple slaves off a master-master replication set something like this:

MASTER A <--------------> MASTER B
| |
| |
| |

Makes sense, huh?

Then I'll just point our read / write app servers at MASTER A, and point half of our read / only app servers at SLAVE C, and half of our read only servers at SLAVE D. Sweet. Works like a charm.

Oops... Replication shows current everywhere, no alerts going off. But app servers pointed at SLAVE C show higher row counts than app servers on SLAVE D...

Yea, I never bothered to test that. That's what I get for making assumptions - if you could even call it that. When I thought things through, I quickly realized that there was no reason for MASTER B to send any of it's replicated updates on to SLAVE D, because I had told it not to write any of it's updates it received via replication to it's binary log... :)

All you replication experts out there could have probably told me that in your sleep, but of course, I never bothered to ask...

Just a couple lines in the config file could have saved me a few headaches, and a couple embarrassing apologies...

replicate-same-server-id = 0

Figures.. ;)