MySQL master-slave switch

MySQL master-slave switch

Master stop, from the boss
The steps are as follows:

1 Confirm that all synchronization operations have been completed from the server:
Stop slave Io_thread
Show Processlist

Until you see the status: XXX has read all relay log indicates that the update is complete

2 stop slave service from server
Stop slave

3 switch from the server to the primary server:
Reset Master

Complete the switchover.
PS: If you set the Read Only option from the server, you need to remove this parameter and restart the database

Master-Slave Switching:
Final Result: Main Library writable, read-only from library

First, ensure that the master-slave database opens the binary log by adding log-bin=log-bin-name in the [mysqld] section of MY.CNF
Then check to see if mysql> show variables is turned on; Log-bin on

In order to not miss the update of the database when switching, the primary database must be stopped and read-only:
Set global Read_only=1;
(Note and flush TABLES with READ LOCK; UNLOCK TABLES; unlock)
Show variables like ' read_only% '; Confirm read_only on

Then flush logs in the main library mysql>; Refresh the Log-bin, please.
View mysql> show slave status \g from the gallery for full updates

View the status of the main library mysql> show Master status \g If there are data updates

If not, you can stop mysql> stop slave from the library.

View the status of the new main library mysql> show master status \g record file and Pos

You can then turn off the read-only property of the new main library mysql> set global read_only=0;

New from library Changing the connection's main library information mysql> change master to

Master_host= ' 192.168.30.122 ',
Master_user= ' Repl ',
Master_password= ' 123456 ',
master_port=3306,
Master_log_file= ' mysql-bin.000001 ',
master_log_pos=120;
At this point can start new from the library:mysql> start slave;
View New from library status:mysql> show slave status\g

PS: Data inconsistency after master-slave switchover, special egg-ache conflict various set global sql_slave_skip_counter = 1; Skipping conflicts
So in the future from the switch time must ensure that the main library data has been put into the end, no data re-write when the master from the switch.

MySQL master-slave switch