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