mysql slave切换master_mysql5.7主从切换（master/slave switchover）

切换前

192.168.56.100 master

192.168.56.200 slave

切换后

192.168.56.100 slave

192.168.56.200 master

MySQL [(none)]> select version();

+------------+ | version() | +------------+ | 5.7.21-log | +------------+

1 row in set (0.01 sec)

master上执行 192.168.56.100

# /etc/init.d/mysql.server stop

MySQL server PID file could not be found! [ OK ]

slave上执行 192.168.56.200

MySQL [(none)]> stop slave io_thread;

MySQL [(none)]> show processlist\G

*************************** 1. row ***************************

Id: 4

User: root

Host: localhost

db: NULL

Command: Query

Time: 0

State: starting

Info: show processlist

*************************** 2. row ***************************

Id: 9

User: system user

Host:

db: NULL

Command: Connect

Time: 167

State: Slave has read all relay log; waiting for more updates

Info: NULL

rows in set (0.00 sec)

一定要确保出现 Slave has read all relay log，才能继续往下运行

MySQL [(none)]> stop slave;

MySQL [(none)]> reset master;

MySQL [(none)]> create user 'replicator'@'192.168.56.%' identified by 'mysqlmysql'; MySQL [(none)]> grant replication slave on *.* to 'replicator'@'192.168.56.%';

MySQL [(none)]> flush privileges;

MySQL [(none)]> show processlist;

+----+------+-----------+------+---------+------+----------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+------+-----------+------+---------+------+----------+------------------+

| 4 | root | localhost | NULL | Query | 0 | starting | show processlist |

+----+------+-----------+------+---------+------+----------+------------------+

1 row in set (0.00 sec)

新的slave上执行 192.168.56.100

现在需要把老的master变为一个新的slave

MySQL [(none)]> change master to master_host='192.168.56.200', master_user='replicator', master_password='mysqlmysql', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=1194;

MySQL [(none)]> start slave;

MySQL [(none)]> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.200

Master_User: replicator

Master_Port: 3306

Connect_Retry:

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 1194

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes ` ` `

确保Slave_IO_Running，Slave_SQL_Running均为Yes

新的master上执行 192.168.56.200

MySQL [(none)]> show processlist\G

*************************** 1. row ***************************

Id: 5

User: root

Host: localhost

db: NULL

Command: Query

Time: 0

State: starting

Info: show processlist

*************************** 2. row ***************************

Id: 7

User: replicator

Host: 192.168.56.100:44070

db: NULL

Command: Binlog Dump

Time: 206

State: Master has sent all binlog to slave; waiting for more updates

Info: NULL

2 rows in set (0.00 sec)

出现Master has sent all binlog to slave;说明master已经和slave建立了连接。