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