mysql 5.7 master、salve 切换

os: centos 7.4
db: mysql 5.7.21
切换前

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关闭mysql

# /etc/init.d/mysql.server stop
MySQL server PID file could not be found!                  [  OK  ]

slave上执行

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上执行

现在需要把老的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上执行

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