From my standpoint, you may have potentially introduced data drift into replication.
Baron Schwarz presented this as a puzzle in his blog.
You may have to reload oldS
and newS1
fresh.
At the very least, you should use pt-table-checksum to see if
- the data on Master differs from oldS
- the data on Master differs from newS1
If the differences are not identical, you can either
- reload
oldS
andnewS1
fresh - run pt-table-sync
Before you touch anything, please fix the server-id situation
The spawning of relay logs is to be expected because sibling slaves take turns getting SQL entries from the Master. They simply cannot share the same server-id. The Master will somehow alert subsequent slaves that I gave server-id an SQL statement already. Thus, the I/O thread on subsequent slaves will disconnect and retry.
This methodology of the Master talking to Slaves for this info allowed MySQL (eh, Oracle) to come up with semisync replication. This would break semisync replication as well.