Determine good server
Zuerst muss entschieden werden, welcher der "guter" Server ist. Wenn HAPROXY im Betrieb ist, dann ist der guter Master der auf den die Daten derzeit geschrieben werden.
HAPROXY umstellen
If there is a HAPROXY, then remove the servers on the broken master side from the distribution (also the slave on this side).
On BOTH Master Server
STOP SLAVE;
Make a backup of the good Master DB on the BROKEN Master Server
Previous to Release 3.12:
mysqldump -h<GOOD_MASTER> -uroot -p<PASSWORD> --single-transaction --master-data=2 --databases JTELWeb JTELStats JTELLog --add-drop-database --add-drop-table --events --routines --triggers > master.sql
As of release 3.12 please use the following command:
mysqldump -h<GOOD_MASTER> -uroot -p<PASSWORD> --single-transaction --master-data=2 --databases JTELWeb JTELStats JTELStats2 JTELLog --add-drop-database --add-drop-table --events --routines --triggers > master.sql
In Versions 3.12, 3.14 and 3.15:
If someone logs on to the portal while the dump is being pulled, it will go wrong. Enclosed an SQL query. If the time changes after the query is executed, a login has taken place.
If this happens, the dump has to be pulled again and in the meantime it has to be permanently checked if a login has taken place. Only if this is not the case, the dump can be replicated error-free to the slave.
SELECT Max(dtAcdLoggedIn) FROM Users;
In versions 3.11 and below and version 3.16 this problem does not exist.
On the BROKEN master server, reset the slave and restore the backup
RESET SLAVE; SOURCE master.sql;
CHANGE MASTER TO MASTER_HOST = '<GOOD_MASTER>', MASTER_USER = 'repl', MASTER_PASSWORD = '<PASSWORD>', MASTER_LOG_FILE='<NAME_LOGFILE>', MASTER_LOG_POS=<POSITION_LOGFILE>; START SLAVE;
On the BROKEN master server Check the slave
SHOW SLAVE STATUS\G
watch 'mysql -u root -p<PASSWORD> -e "SHOW SLAVE STATUS\G" 2>/dev/null'
On the BROKEN master server lock all tables and note master position
FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
The positions of SHOW MASTER STATUS are required in the following command.
On the GOOD master server, reposition and start the replication.
CHANGE MASTER TO MASTER_HOST = '<SECOND_MASTER>', MASTER_USER = 'repl', MASTER_PASSWORD = '<PASSWORD>', MASTER_LOG_FILE='<NAME_LOGFILE>', MASTER_LOG_POS=<POSITION_LOGFILE>; START SLAVE;
Unlock the tables on the BROKEN master server
UNLOCK TABLES;
On all servers now
SHOW SLAVE STATUS\G
It is usually not necessary to restore the slaves attached to both masters. If it is, they can be re-initialized with the normal slave recovery procedure.