mysql5.7主从切换

一、查看当前mysql是主库还是从库

主库

mysql> show processlist;
+----+------+------------------+--------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host             | db     | Command     | Time | State                                                         | Info             |
+----+------+------------------+--------+-------------+------+---------------------------------------------------------------+------------------+
|  2 | llw  | 10.0.0.135:60454 | NULL   | Binlog Dump | 6166 | Master has sent all binlog to slave; waiting for more updates | NULL             |
|  3 | root | localhost        | testdb | Sleep       | 2306 |                                                               | NULL             |
|  4 | llw  | 10.0.0.1:50833   | testdb | Sleep       | 3441 |                                                               | NULL             |
|  5 | llw  | 10.0.0.1:50844   | testdb | Sleep       | 3538 |                                                               | NULL             |
|  6 | root | localhost        | NULL   | Query       |    0 | starting                                                      | show processlist |
+----+------+------------------+--------+-------------+------+---------------------------------------------------------------+------------------+
5 rows in set (0.00 sec)

查看主库当前状态

从库

mysql> show processlist;
+----+-------------+----------------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host           | db   | Command | Time | State                                                  | Info             |
+----+-------------+----------------+------+---------+------+--------------------------------------------------------+------------------+
|  1 | system user |                | NULL | Connect | 3759 | Slave has read all relay log; waiting for more updates | NULL             |
|  2 | system user |                | NULL | Connect | 6234 | Waiting for master to send event                       | NULL             |
|  4 | root        | localhost      | NULL | Sleep   | 1549 |                                                        | NULL             |
|  5 | liuliu      | 10.0.0.1:50836 | NULL | Sleep   | 3614 |                                                        | NULL             |
|  6 | root        | localhost      | NULL | Query   |    0 | starting                                               | show processlist |
+----+-------------+----------------+------+---------+------+--------------------------------------------------------+------------------+
5 rows in set (0.00 sec)

查看从库当前状态

 二、对主库全库锁定

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

三、停止slave的io线程

mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)

四、把slave提升为master

Stop slave;    
Reset master; ## 删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。    
Reset slave; ##删除master.info文件和relay-log.info文件以及所有的relay log文件并重新启用一个新的relay log文件。    
Reset slave all; ## 相对于RESET SLAVE，RESET SLAVE ALL还会删除内存中的连接信息    

Stop slave; 
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.03 sec)

mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)

mysql> reset slave all;
Query OK, 0 rows affected (0.03 sec)

五、查看slave是否为只读模式

show variables like 'read_only';

修改为读写

vim /etc/my.cnf
read-only=0

六、将原来的master变为slave，在新的master上创建同步用户

grant replication slave on *.* to '设置远程登录用户名'@'%' identified by '密码';

七、将新的slave设置为只读模式

vim /etc/my.cnf 【永久】
read-only=1

mysql> set global read_only=0;【临时】
Query OK, 0 rows affected (0.00 sec)

八、将新的slave上释放全局锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

九、在新的slave

mysql> reset master;
Query OK, 0 rows affected (0.44 sec)

十、在新的slave上配置连接信息