Master-Slave handover of MySQL

In a master and multi-slave environment, if the master library is M and the slave libraries are S1 and S2, when the master library M fails, S1 is set as the new master library, and the configuration of the slave library S2 is modified to point to the new master library S1. In addition, the application should also be notified to change the IP address of the master database, which can be set to the new slave library if the failed master database M is repaired. The detailed steps of master-slave switching are as follows:

1 first, make sure that all slave libraries have performed all updates in relay log, stop the io thread on each slave library, and then check it with the following statement:

Mysql > stop slave io_thread

Mysql > show processlist\ G

Until you see the following statement, it means that all updates have been executed.

Has read all relay log waiting for the slave I/O thread to update it

2 on slave database S1, stop the slave service, and then set S1 as the master database:

Mysql > stop slave

Mysql > reset master

3 on S2 (if there is more than one, on all slave servers, S1 is the master server), stop the slave service, then reset S1 master database, S1 has an IP of 192.168.1.101, and then execute start slave to start replication:

Mysql > stop slave

Mysql > change master to master_host = '192.168.1.101'

Mysql > start slave

4 modify the client application to point to the S1 server:

Delete the master.info and relay-log.info files on the S1 server (the new master library server), otherwise it will start according to the slave library once you restart it.

6 finally, if the M server is restored, it can be set as the slave library of S1 according to the method of S2.

Note: the above step defaults to enabling the log-bin option on S1, so that the binary logs can be copied to other slave libraries after being reset to the master library. Second, the log-slave-updates parameter is not enabled on S1.