MySQL Master - Master Broken
- by Recc
I've Inherited a Mysql master master system, I've noticed the second master (lets call it slave from now on as it's running on a 'slave' machine) stopped getting its db's updated.
I saw that
Master:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave: (with an error I truncated)
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '3' for key 'PRIMARY'' on [...]
I don't know what caused it to process considering we cant get duplicate there. What's important is to resume normal operations;
Right now I've stop slave; on the Master and stop slave; on the Slave because I saw that if I change records on the Slave the changes Do Get Propagated to Master which is in active use.
How do I:
Force sync EVERYTHING from master to slave without affecting data on master? Then hopefully have slave pickup replication as usual?
UPDATE
OK I Tried deleting all tables on slave then it complained in that error section that the 'table' doesnt exist. So i made a no data dump of Master, and made sure I have only empty tables in Secondary (slave).
I start slave; on slave BUT now it's complaining about bloody alter table statements for instance:
Last_Errno: 1060
Last_Error: Error 'Duplicate column name [...] Query: 'ALTER TABLE [...]
How to skip the fracking alter statements I just want to replicate the bloody data and be done with it, my tables have the lates changes already FFS and now its complaining about changes made after the replication seized weeks ago
How do I reset the log or something?
OUTSTANDING
Why would this start happening? The "Secondary" is propagating to "Primary". "Primary" is not propagating to "Secondary".
But any fixes I tried to do left it in the same state Yes-Yes Yes-No with same Last_Error.
I think around that time the server was taken off the network, could that confuse MySQL in some way?
