mysql slave切换master_mysql 5.7 master/salve 切换

切换前

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

2 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)

删除服务器上的master.info和relay-log.info文件，否则下次重启时还会按照从库启动。

# cd /var/lib/mysql/

# rm -f master.info relay-log.info

新的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建立了连接