mysql主主风险

测试库：
192.168.1.2 主
192.168.1.3 从


192.168.1.4 主 4又是2的从库
192.168.1.5 从


有人修改了192.168.1.2和192.168.1.3的数据库参数后，重启数据库。
忘记了192.168.1.4又是192.168.1.2的从库，导致192.168.1.2和192.168.1.4的主从断掉。
并且在192.168.1.2上创建了新库还原数据删除等操作，导致192.168.1.4提示错误。


模拟如下：
通过从库查看主从状态：

*************************** 1. row *************************** 
 
              Slave_IO_State: Waiting for master to send event 
 
                 
 
                 
 
                 
 
               
 
             
 
         
 
              Relay_Log_File: relay-log.000005 
 
               
 
       
 
            Slave_IO_Running: Yes 
 
           
 
             Replicate_Do_DB:  
 
         
 
          Replicate_Do_Table:  
 
      Replicate_Ignore_Table:  
 
     Replicate_Wild_Do_Table:  
 
 Replicate_Wild_Ignore_Table:  
 
                  Last_Errno: 1049 
 
                  Last_Error: Error 'Unknown database 'sakila'' on query. Default database: 'sakila'. Query: 'create table sakila (id int,name varchar(2))' 
 
                Skip_Counter: 0 
 
         
 
             
 
             
 
              Until_Log_File:  
 
               
 
          Master_SSL_Allowed: No 
 
          Master_SSL_CA_File:  
 
          Master_SSL_CA_Path:  
 
             Master_SSL_Cert:  
 
           Master_SSL_Cipher:  
 
              Master_SSL_Key:  
 
       
 
 Master_SSL_Verify_Server_Cert: No 
 
               
 
               Last_IO_Error:  
 
              Last_SQL_Errno: 1049 
 
              Last_SQL_Error: Error 'Unknown database 'sakila'' on query. Default database: 'sakila'. Query: 'create table sakila (id int,name varchar(2))' 
 
 Replicate_Ignore_Server_Ids:  
 
            Master_Server_Id: 360360 
 
 1 row in set (0.00 sec) 
 


 记录下二进制日志的和点的位置： 
 
 Relay_Master_Log_File: mysql-bin.000006 
 
 Exec_Master_Log_Pos: 194 
 


 停掉从库的slave 
 
 mysql> stop slave; 
 
 Query OK, 0 rows affected (0.01 sec) 
 


 并在主库删除掉新建的库 
 
 mysql> drop database sakila; 
 
 Query OK, 1 row affected (0.04 sec) 
 


 重新进行主从复制 
 
 mysql> show master status; 
 
 +------------------+----------+--------------+------------------+ 
 
           
 
 +------------------+----------+--------------+------------------+ 
 
    388 |              |                  | 
 
 +------------------+----------+--------------+------------------+ 
 
 1 row in set (0.00 sec) 
 


 mysql> change master to master_host='192.168.1.2',master_port=3306,master_user='master',master_password='123456',master_log_file='mysql-bin.000006',master_log_pos=388; 
 
 Query OK, 0 rows affected (0.01 sec) 
 


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


sql_slave_skip_counter参数 
 
'%skip%'; 
 
 +------------------------+-------+ 
 
        | Value | 
 
 +------------------------+-------+ 
 
| ON    | 
 
    | ON    | 
 
      | OFF   
 
   | OFF   
 
    | OFF   
 
   
 
 +------------------------+-------+ 
 
 6 rows in set (0.00 sec) 
 


 默认为0 
 


 mysql> set global sql_slave_skip_counter = 1; 
 


 mysql> start slave; 
 


 还提示错误，继续 
 


 mysql> set global sql_slave_skip_counter = 1; 
 


 mysql> start slave; 
 


 mysql> show slave status\G 
 
 *************************** 1. row *************************** 
 
              Slave_IO_State: Waiting for master to send event 
 
                 
 
                 
 
                 
 
               
 
             
 
         
 
              Relay_Log_File: relay-log.000002 
 
               
 
       
 
            Slave_IO_Running: Yes 
 
           
 
             Replicate_Do_DB:  
 
         
 
          Replicate_Do_Table:  
 
      Replicate_Ignore_Table:  
 
     Replicate_Wild_Do_Table:  
 
 Replicate_Wild_Ignore_Table:  
 
                  Last_Errno: 0 
 
                  Last_Error:  
 
                Skip_Counter: 0 
 
         
 
             
 
             
 
              Until_Log_File:  
 
               
 
          Master_SSL_Allowed: No 
 
          Master_SSL_CA_File:  
 
          Master_SSL_CA_Path:  
 
             Master_SSL_Cert:  
 
           Master_SSL_Cipher:  
 
              Master_SSL_Key:  
 
       
 
 Master_SSL_Verify_Server_Cert: No 
 
               
 
               Last_IO_Error:  
 
              Last_SQL_Errno: 0 
 
              Last_SQL_Error:  
 
 Replicate_Ignore_Server_Ids:  
 
            Master_Server_Id: 360360

           
最后在192.168.1.2上重新建库并还原数据