Mysql slave database Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file' error handling, gotfatalerror1236
You will be back later to view the mysql running status and backup status, and log on to mysql to view the master-slave synchronization status from the database.
1 mysql> show slave status\G; 2 *************************** 1. row *************************** 3 Slave_IO_State: 4 Master_Host: 101.200.*.* 5 Master_User: backup 6 Master_Port: 3306 7 Connect_Retry: 60 8 Master_Log_File: master-bin.000113 9 Read_Master_Log_Pos: 27692538710 Relay_Log_File: mysql-relay.00000111 Relay_Log_Pos: 412 Relay_Master_Log_File: master-bin.00011313 Slave_IO_Running: No14 Slave_SQL_Running: Yes15 Replicate_Do_DB: 16 Replicate_Ignore_DB: 17 Replicate_Do_Table: 18 Replicate_Ignore_Table: 19 Replicate_Wild_Do_Table: 20 Replicate_Wild_Ignore_Table: 21 Last_Errno: 022 Last_Error: 23 Skip_Counter: 024 Exec_Master_Log_Pos: 27692538725 Relay_Log_Space: 12026 Until_Condition: None27 Until_Log_File: 28 Until_Log_Pos: 029 Master_SSL_Allowed: No30 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: NULL36 Master_SSL_Verify_Server_Cert: No37 Last_IO_Errno: 123638 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'39 Last_SQL_Errno: 040 Last_SQL_Error: 41 Replicate_Ignore_Server_Ids: 42 Master_Server_Id: 2143 Master_UUID: e4a43da7-5b58-11e5-a12f-00163e00363244 Master_Info_File: /home/data/mysql/master.info45 SQL_Delay: 046 SQL_Remaining_Delay: NULL47 Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it48 Master_Retry_Count: 8640049 Master_Bind: 50 Last_IO_Error_Timestamp: 170204 10:48:0651 Last_SQL_Error_Timestamp: 52 Master_SSL_Crl: 53 Master_SSL_Crlpath: 54 Retrieved_Gtid_Set: 55 Executed_Gtid_Set: 56 Auto_Position: 057 1 row in set (0.00 sec)58 59 ERROR: 60 No query specified
Suddenly Slave_IO_Running: The status changes to NO
Solution.
First, execute
Stop slave;
View master Status of the master database
1 mysql> show master status\G; 2 *************************** 1. row *************************** 3 File: mysql-bin.000113 4 Position: 276925387 5 Binlog_Do_DB: 6 Binlog_Ignore_DB: 7 Executed_Gtid_Set: 8 1 row in set (0.00 sec) 9 10 ERROR: 11 No query specified12 13 mysql> flush logs;14 Query OK, 0 rows affected (0.11 sec)
Refresh binlog
Flush logs;
The refresh log will be + 1
For example, the File: mysql-bin.000113 above will become File: mysql-bin.000114
View master status again
1 mysql> show master status\G; 2 *************************** 1. row *************************** 3 File: mysql-bin.000114 4 Position: 120 5 Binlog_Do_DB: 6 Binlog_Ignore_DB: 7 Executed_Gtid_Set: 8 1 row in set (0.00 sec) 9 10 ERROR: 11 No query specified
Then you do not need to operate the master and switch to the slave database.
Enter change master to MASTER_LOG_FILE = 'mysql-bin.000114 ', MASTER_LOG_POS = 120;
Run start slave;
View slave database status
1 mysql> show slave status\G; 2 *************************** 1. row *************************** 3 Slave_IO_State: Waiting for master to send event 4 Master_Host: 101.200.*.* 5 Master_User: backup 6 Master_Port: 3306 7 Connect_Retry: 60 8 Master_Log_File: mysql-bin.000114 9 Read_Master_Log_Pos: 1131410 Relay_Log_File: mysql-relay.00000211 Relay_Log_Pos: 1147712 Relay_Master_Log_File: mysql-bin.00011413 Slave_IO_Running: Yes14 Slave_SQL_Running: Yes15 Replicate_Do_DB: 16 Replicate_Ignore_DB: 17 Replicate_Do_Table: 18 Replicate_Ignore_Table: 19 Replicate_Wild_Do_Table: 20 Replicate_Wild_Ignore_Table: 21 Last_Errno: 022 Last_Error: 23 Skip_Counter: 024 Exec_Master_Log_Pos: 1131425 Relay_Log_Space: 1164626 Until_Condition: None27 Until_Log_File: 28 Until_Log_Pos: 029 Master_SSL_Allowed: No30 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: 036 Master_SSL_Verify_Server_Cert: No37 Last_IO_Errno: 038 Last_IO_Error: 39 Last_SQL_Errno: 040 Last_SQL_Error: 41 Replicate_Ignore_Server_Ids: 42 Master_Server_Id: 2143 Master_UUID: e4a43da7-5b58-11e5-a12f-00163e00363244 Master_Info_File: /home/data/mysql/master.info45 SQL_Delay: 046 SQL_Remaining_Delay: NULL47 Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it48 Master_Retry_Count: 8640049 Master_Bind: 50 Last_IO_Error_Timestamp: 51 Last_SQL_Error_Timestamp: 52 Master_SSL_Crl: 53 Master_SSL_Crlpath: 54 Retrieved_Gtid_Set: 55 Executed_Gtid_Set: 56 Auto_Position: 057 1 row in set (0.00 sec)58 59 ERROR: 60 No query specified