MySQL master-slave-master-slave normal switch, main library outage switch.

MySQL master-slave Switch manual

Master-slave Architecture

Operation and Maintenance Department

V1.0

. Year May 24

Normal switching

1. Check slave synchronization Status

1) in master execution: show Processlist;

Show Master have sent all binlog to slave; Waiting for Binlog to be updated

2) performed in Slave: Show Processlist;

Showing slave have read all relay log; Waiting for the slave I/O thread to update it

Mysql> show slave status \g;

Check that IO and SQL threads are normal, and if no indicates synchronization inconsistency, the slave synchronization needs to be kept consistent with the master-slave data.

3) stop slave io thread

Execute:mysql> STOP SLAVE io_thread in SLAVE

Mysql> SHOW processlist;

Ensure the status is: have read all relay log

Slave can be promoted to master when the above is done:

4) upgrade Slave to Master

Stop slave;

Reset master;

Reset slave All; After the 5.6.3 Release

Reset slave; Prior to version 5.6.3

See if slave is read-only: Show variables like ' read_only ';

Read-only mode needs to modify the my.cnf file, comment Read-only=1, and restart the MySQL service.

Or do not restart the use command to turn off read-only, but the next reboot expires: set globalread_only=off;

Mysql> Show master Status \g;

Note: The Reset slave All command removes the replication parameter from the library, and then the show slave status\g information is returned as empty.

5) Turn the original master into slave

To create a synchronization user on the new master:

Grant Replication Slave on * * [email protected] ' IP of slave ' identified by ' replpwd ';

To reset the Binlog on the new slave:

Reset master;

Change Master to master_host= ' 192.168.0.104 ',//master server IP

master_port=3306,

Master_user= ' Repl ',

Master_password= ' Replpwd ',

Master_log_file= ' master-bin.000001 ',//master server-generated logs

master_log_pos=?;/ /master Binlog Pos

The last two steps above can be performed in master: Show Master Status

Start Slave:start slave; and view slave status: Show slave status\g;

Abnormal switching

Host failure or downtime:

1) Execute in Salve:

Stop slave;

Reset Master;

See if read-only mode: show variables like ' read_only ';

Read-only mode needs to modify the my.cnf file, comment Read-only=1, and restart the MySQL service.

Or do not restart the use command to turn off read-only, but the next reboot expires: set globalread_only=off;

View show slave status \g;

View Show master status \g;

Change from the library IP address to the main library IP address to test whether the app connection is working correctly.

< Finish >

MySQL master-slave-master-slave normal switch, main library outage switch.