Pop quiz: how can one slave break another slave
Suppose you have a MySQL master-slave replication setup. It is very straightforward, nothing exotic at all. Imagine that it is the simplest possible setup. Everything is fine, there is nothing wrong or misconfigured with either server.
Now you add another slave, and replication on your existing slave fails. You did not change anything on your existing slave or master. How did this happen?
using the same server-id would cause frequent disconnect/connect cycles on both slaves, so that could be one possible solution. However, that might go against what you said about “nothing is wrong or misconfigured with either server” (if that includes the 3rd server)
Nick
14 Mar 09 at 3:04 pm
You win. Nothing was wrong or misconfigured until the 3rd server was added as a slave.
Xaprb
14 Mar 09 at 3:35 pm
Right. Always fun to win :-)
Nick
14 Mar 09 at 3:49 pm
Thankfully, the log messages are helpful enough in this case to catch it.
Peter Radavich
14 Mar 09 at 4:07 pm
I’m late… I knew that.. =D
George Guimarães
14 Mar 09 at 4:08 pm
Nick, I’ll buy you a beer at the conference! George, maybe next time?
Peter, yes. There are cases when the log messages aren’t helpful, but a quick check of the log in this case is all it takes.
Xaprb
14 Mar 09 at 5:30 pm
I knew it too! It hurts me from time to time:
http://datacharmer.blogspot.com/2009/03/deceiving-error-message-while-setting.html
Nice quiz!
Giuseppe
Giuseppe Maxia
14 Mar 09 at 7:31 pm
Can I tie? I knew it was server-id before I saw the comments :)
The master should be able to look at the source IP and use an internal server ID instead of having to be told what server-id to use.
This is a brittle design.
Kevin Burton
14 Mar 09 at 11:38 pm
even better if the server could auto generate server-id based on the IP address to which server is bound to(in case of multiple IPs probably the first IP on in the list of IPs which mysql is listening on). This will ensure that there are no duplication of server-ids in a replication cluster.
:)
hareesh
16 Mar 09 at 2:23 am
oops
That might break the replication if teh IP is reconfigured for one/both of teh servers in a master master setup.
My bad :p
hareesh
16 Mar 09 at 2:31 am
Funny….I just did this today:)
Justin Huff
16 Mar 09 at 5:45 pm