MySQL Replication breaks

[hoggle]

Hi,

as mentioned in the topic we have the following issue:

In short:

After transfering the Dump Export from the Master to the Slave, importing it and renabling the replication we get PK Errors after some time.


In long:
We are using MySQL 5.1.17 with GTID enabled.


We have a Master-Master scenario; but we make sure that all the traffic goes only to the first node.

What we do on both nodes:
stop slave;
reset slave;
reset master;

Then We do a full export on node A transfering the data directy to node B like this:
mysqldump --defaults-extra-file=/etc/my.live.cnf --user="root" --all-databases --single-transaction --add-drop-database --triggers --routines --events | mysql --host=nodeB --port=3305 --user=root

This takes a while(~1 hour). All db's sum up to about 25GB.

After completing the import we start the slave on node B.

It starts appy all changes on but after some time (seconds) the replication stops with this kind of errors:

Last_SQL_Error: Error 'Duplicate entry '4' for key 'PRIMARY'' on query. Default database: 'survey'. Query: 'INSERT INTO `survey_survey_49945`
(`49945XF836X27154`, `49943X8C36X27157`,`lastpage`,`startlanguage`) VALUES ('1', '',2, 'de-informal')'

Game Over.

We've tried this procedure many time with differnt settings but always end up with the same issue.

Help is very much appreciated !

 

[akretschmer]

Last_SQL_Error: Error 'Duplicate entry '4' for key 'PRIMARY'' on query. Default database: 'survey'. Query: 'INSERT INTO `survey_survey_49945`
(`49945XF836X27154`, `49943X8C36X27157`,`lastpage`,`startlanguage`) VALUES ('1', '',2, 'de-informal')'

Game Over.

Indeed. Obviously you wrote to the standby. Come on, that's MySQL. Rebuild the replication again and have fun, until the next error.

This is a german speaking forum, can we switch the language to german?

 

[hoggle]

na klar. Hatte den ganzen Text nur schon fertig.

Ein Update(13:51): Es scheint so als hätte ich die Replikation konsistent wiederhergestellt.

Was ich jetzt anders gemacht habe:

1) mysqldump --defaults-extra-file=/etc/my.live.cnf --flush-logs --quick --disable-keys --extended-insert --user="root" --all-databases --single-transaction --add-drop-database --triggers --routines --events | mysql --host=NodeB --port=3305 --user=root

2) NACH dem vollständigen Dumpimport und VOR dem ersten Starten des Slave Prozesses:
CHANGE MASTER TO MASTER_HOST = 'nodeA', MASTER_PORT = 3306, MASTER_USER = 'repl', MASTER_PASSWORD = 'xxxxxxx', MASTER_AUTO_POSITION = 1;

3) Beim DB Stoppen VORHER erst den Slave Prozess anhalten

4) Automatisches Starten des Slave Prozesses vermeiden durch: skip-slave-start = true

5) Nach dem Neustart der DB Slave Prozess manuell starten

Es läuft jetzt seit ca. 15 Minuten wieder rund.

 

[akretschmer]

Es läuft jetzt seit ca. 15 Minuten wieder rund.

Immerhin ;-)

Es gibt bessere und robustere Systeme als MySQL.