myql 切换主从

# 手动切换
1、主库锁库，禁止写入
mysql> set global super_read_only=ON;
mysql > set global read_only =1;     或者 set global read_only=ON;
mysql > flush tables with read lock;             


#杀掉所有已经建立的连接
mysqladmin -uroot -p processlist|awk -F "|" '{print $2}'|xargs -n 1 mysqladmin -uroot -p kill

2、从库确认主从Executed_Gtid_Set的最后事务一致： 
mysql> show slave status\G；    
               Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.100.102
                   Master_User: slave
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000007
           Read_Master_Log_Pos: 600
                Relay_Log_File: mariadb-relay-bin.000013
                 Relay_Log_Pos: 899
         Relay_Master_Log_File: mysql-bin.000007
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
           Exec_Master_Log_Pos: 600
         Seconds_Behind_Master: 0
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

#注意看Exce_Master_Log_Pos是否和主库同步，sencond_behind_master是否为0；

mysql > show processlist；





3、从库停掉复制进程并清空主从信息：
mysql> stop slave;     
mysql> reset slave all;   #清空所有relaylog（清除日志同步位置标志）并清空内存中的从库信息，并重新生成master.info
mysql> reset master;      #清空所有binlog，这条命令就是原来的FLUSH MASTER

从库关闭只读并开启读写，转为新主库
mysql> set global read_only=off;
mysql> set global super_read_only=off;




4、原来主库执行新主库的复制链路，转为新备库，完成主从切换
mysql> unlock tables;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.111',MASTER_USER='xxx',MASTER_PORT=3306,MASTER_PASSWORD='xxx',master_auto_position=1 ;
mysql> start slave;
mysql> show slave status\G；



5、从库故障解决方法：mysql数据库同步跳过临时错误
slave stop;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
slave start;

两种故障：https://www.jianshu.com/p/af4b44dd7312
         https://www.cnblogs.com/binghe001/p/14008735.html