复制问题 1051

 Last_SQL_Errno: 1051
               Last_SQL_Error: Worker 2 failed executing transaction '' at master log mysql-bin.045231, end_log_pos 119055873; Error 'Unknown table 'fission_log.FISS_LOG_20170305'' on query. Default database: 'fission_log'. Query: 'DROP TABLE `FISS_LOG_20170305` /* generated by server */'

==========================================================
start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
 reset slave;
ok

20170419
==========================================================
问题描述
从库备份恢复3307新实例，备份方式innobackup，带有--slave-info选项
1.文件信息
# cat xtrabackup_slave_info 
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.002338', MASTER_LOG_POS=130384423

2.slave status
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
              Master_Log_File: mysql-bin.002338
          Read_Master_Log_Pos: 130384423
               Relay_Log_File: relay-bin.005134
                Relay_Log_Pos: 7296084
        Relay_Master_Log_File: mysql-bin.002338
             Slave_IO_Running: No
            Slave_SQL_Running: No
          Exec_Master_Log_Pos: 130384423
              Relay_Log_Space: 0
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
              . . . . . . . . .
1 row in set (0.00 sec)

3.直接start slave；slave status不能初始化relay log信息
mysql> start slave;show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
              Master_Log_File: mysql-bin.002338
          Read_Master_Log_Pos: 130384423
               Relay_Log_File: relay-bin.005134
                Relay_Log_Pos: 7296084
        Relay_Master_Log_File: mysql-bin.002338
             Slave_IO_Running: No
            Slave_SQL_Running: No
                   Last_Error: Slave failed to initialize relay log info structure from the repository
          Exec_Master_Log_Pos: 130384423
              Relay_Log_Space: 0
               Last_SQL_Errno: 1872
               Last_SQL_Error: Slave failed to initialize relay log info structure from the repository
               .  .  .  .  .  .  .
1 row in set (0.00 sec)

4.reset slave后，slave会从master拉取当前第一个mysql-bin  --如下：mysql-bin.002282
从执行到的pos位点来看，已经执行了部分binlog，就算从正确的slave-info来复制，主从已经出现了数据的不一致
--  Duplicate entry '6717559'
mysql> reset slave;start slave;show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Checking master version
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              .  .  .  .  .
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 263
              .  .  .  .  .
      Slave_SQL_Running_State: System lock
              .  .  .  .  .
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: System lock
                 .  .  .  .  .
              Master_Log_File: mysql-bin.002282
          Read_Master_Log_Pos: 21979921
               Relay_Log_File: relay-bin.000003
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.002282
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
                   .  .  .  .  .
                   Last_Errno: 1062
                   Last_Error: Worker 3 failed executing transaction '' at master log mysql-bin.002282, end_log_pos 702; Could not execute Write_rows event on table fission_exchange.host_special_record; Duplicate entry '6717559' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 702
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 21980394
                   .  .  .  .  .
1 row in set (0.00 sec)

5.忽略数据一致性继续，slave stop状态下不能change 在MTS模式下

mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.002338', MASTER_LOG_POS=130384423;
ERROR 1802 (HY000): CHANGE MASTER cannot be executed when the slave was stopped with an error or killed in MTS mode. Consider using RESET SLAVE or START SLAVE UNTIL.
 
mysql> CHANGE MASTER TO master_host='10.0.1.7',master_port=3306,master_user='repl',master_password='repl',MASTER_LOG_FILE='mysql-bin.002338', MASTER_LOG_POS=130384423;
ERROR 1802 (HY000): CHANGE MASTER cannot be executed when the slave was stopped with an error or killed in MTS mode. Consider using RESET SLAVE or START SLAVE UNTIL.

6. reset slave再change正常
mysql> reset slave;
mysql> CHANGE MASTER TO master_host='10.0.1.7',master_port=3306,master_user='repl',master_password='repl',MASTER_LOG_FILE='mysql-bin.002338', MASTER_LOG_POS=130384423;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
start slave后，slave status正常




备注：
所以正规操作步骤是
1.完全恢复
2.reset slave；
3.change master to （完整选项）