You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 18 Next »

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;


On the BROKEN master server, determine the master position from the master.sql, and then reinitialize the slave
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

Only if everything is OK, and the replication is up to date, then continue.
The status can be monitored with the following command:
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;


Check Masters and Slaves

On all servers now 

SHOW SLAVE STATUS\G

and check that everything is running smoothly

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.

  • No labels