一次复制告错的处理，错误代码1236

1、复制状态

 1 mysql> show slave status\G
 2 *************************** 1. row ***************************
 3                Slave_IO_State: 
 4                   Master_Host: source-ip
 5                   Master_User: repl_user
 6                   Master_Port: 3306
 7                 Connect_Retry: 60
 8               Master_Log_File: binlog.018446
 9           Read_Master_Log_Pos: 1249732
10                Relay_Log_File: relay-log.006913
11                 Relay_Log_Pos: 1249875
12         Relay_Master_Log_File: binlog.018446
13              Slave_IO_Running: No
14             Slave_SQL_Running: Yes
15               Replicate_Do_DB: 
16           Replicate_Ignore_DB: information_schema,performance_schema
17            Replicate_Do_Table: 
18        Replicate_Ignore_Table: 
19       Replicate_Wild_Do_Table: 
20   Replicate_Wild_Ignore_Table: 
21                    Last_Errno: 0
22                    Last_Error: 
23                  Skip_Counter: 0
24           Exec_Master_Log_Pos: 1249732
25               Relay_Log_Space: 1250065
26               Until_Condition: None
27                Until_Log_File: 
28                 Until_Log_Pos: 0
29            Master_SSL_Allowed: No
30            Master_SSL_CA_File: 
31            Master_SSL_CA_Path: 
32               Master_SSL_Cert: 
33             Master_SSL_Cipher: 
34                Master_SSL_Key: 
35         Seconds_Behind_Master: NULL
36 Master_SSL_Verify_Server_Cert: No
37                 Last_IO_Errno: 1236
38                 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'binlog.018446' at 1249732, the last event read from '/mysql/mysqllog/binlog/binlog.018446' at 4, the last byte read from '/mysql/mysqllog/binlog/binlog.018446' at 4.'
39                Last_SQL_Errno: 0
40                Last_SQL_Error: 
41   Replicate_Ignore_Server_Ids: 
42              Master_Server_Id: 1
43 1 row in set (0.00 sec)

 

2、相关错误日志

1 170724 8:05:54 [Note] Error reading relay log event: slave SQL thread was killed
2 170724 8:05:57 [Note] Slave I/O thread: connected to master 'repl_user@source-ip:3306',replication started in log 'binlog.018446' at position 1249732
3 170724 8:05:57 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position; the first event 'binlog.018446' at 1249732, the last event read from '/mysql/mysqllog/binlog/binlog.018446' at 4, the last byte read from '/mysql/mysqllog/binlog/binlog.018446' at 4. ( server_errno=1236)
4 170724 8:05:57 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'binlog.018446' at 1249732, the last event read from '/mysql/mysqllog/binlog/binlog.018446' at 4, the last byte read from '/mysql/mysqllog/binlog/binlog.018446' at 4.', Error_code: 1236
5 170724 8:05:57 [Note] Slave I/O thread exiting, read up to log 'binlog.018446', position 1249732
6 170724 8:05:57 [Note] Slave SQL thread initialized, starting replication in log 'binlog.018446' at position 1249732, relay log '/mysql/mysqllog/relay-log/relay-log.006913' position: 1249875

 

3、常见处理方式

1 mysql> stop slave;
2 Query OK, 0 rows affected (0.01 sec)
3 
4 mysql> change master to master_log_file='binlog.018446',master_log_pos=1249732;
5 Query OK, 0 rows affected (0.02 sec)
6 
7 mysql> start slave
8 -> ;
9 Query OK, 0 rows affected (0.00 sec)

 

再次查看，依旧错误

1 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event 'binlog.018446' at 1249732, the last event read from '/mysql/mysqllog/binlog/binlog.018446' at 1249732, the last byte read from '/mysql/mysqllog/binlog/binlog.018446' at 1249751.'

 

4、进入master，查看该二进制的内容

 1 [root@hdp08142 binlog]# /mysql/product/bin/mysqlbinlog --start-position=1249732 binlog.018446
 2 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
 3 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 4 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 5 DELIMITER /*!*/;
 6 # at 4
 7 #170723 23:35:32 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.54-log created 170723 23:35:32 at startup
 8 ROLLBACK/*!*/;
 9 BINLOG '
10 RMJ0WQ8BAAAAZwAAAGsAAAAAAAQANS41LjU0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
11 AAAAAAAAAAAAAAAAAABEwnRZEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
12 '/*!*/;
13 ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 791950946, event_type: 115
14 ERROR: Could not read entry at offset 1249732: Error in log format or read error.
15 DELIMITER ;
16 # End of log file
17 ROLLBACK /* added by mysqlbinlog */;
18 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
19 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
20 [root@hdp08142 binlog]#

发现不能找到该position，错误原因定位为源二进制中不存在这一个position，导致io线程获取事物失败

5、继续处理，尝试找到最近的position

 1 /mysql/product/bin/mysqlbinlog binlog.018446 > /tmp/ha
 2 cat /tmp/ha | grep '# at'
 3 ~~~~
 4 # at 1248086
 5 # at 1248320
 6 # at 1248347
 7 # at 1248411
 8 # at 1248479
 9 # at 1248783
10 # at 1248851
11 # at 1251125
12 # at 1251193
13 # at 1251533
14 # at 1251601

找到和错误position（1249732 ）最近的点：1248851

6、更改复制连接到正确的点

1 mysql> stop slave;
2 Query OK, 0 rows affected (0.00 sec)
3 
4 mysql> change master to master_log_pos=1248851;
5 Query OK, 0 rows affected (0.00 sec)
6 
7 mysql> start slave;
8 Query OK, 0 rows affected (0.00 sec)

查看当前的复制状态，恢复正常

 1 mysql> show slave status\G
 2 *************************** 1. row ***************************
 3 Slave_IO_State: Queueing master event to the relay log
 4 Master_Host: source-ip
 5 Master_User: repl_user
 6 Master_Port: 3306
 7 Connect_Retry: 60
 8 Master_Log_File: binlog.018447
 9 Read_Master_Log_Pos: 17571237
10 Relay_Log_File: relay-log.000002
11 Relay_Log_Pos: 402833
12 Relay_Master_Log_File: binlog.018446
13 Slave_IO_Running: Yes
14 Slave_SQL_Running: Yes
15 Replicate_Do_DB: 
16 Replicate_Ignore_DB: information_schema,performance_schema
17 Replicate_Do_Table: 
18 Replicate_Ignore_Table: 
19 Replicate_Wild_Do_Table: 
20 Replicate_Wild_Ignore_Table: 
21 Last_Errno: 0
22 Last_Error: 
23 Skip_Counter: 0
24 Exec_Master_Log_Pos: 29905083
25 Relay_Log_Space: 92927393
26 Until_Condition: None
27 Until_Log_File: 
28 Until_Log_Pos: 0
29 Master_SSL_Allowed: No
30 Master_SSL_CA_File: 
31 Master_SSL_CA_Path: 
32 Master_SSL_Cert: 
33 Master_SSL_Cipher: 
34 Master_SSL_Key: 
35 Seconds_Behind_Master: 38471
36 Master_SSL_Verify_Server_Cert: No
37 Last_IO_Errno: 0
38 Last_IO_Error: 
39 Last_SQL_Errno: 0
40 Last_SQL_Error: 
41 Replicate_Ignore_Server_Ids: 
42 Master_Server_Id: 1
43 1 row in set (0.00 sec)

 

转载于:https://www.cnblogs.com/magicaltravel/p/7227405.html