• mysql从库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'报错处理

    年后回来查看mysql运行状况与备份情况，登录mysql从库查看主从同步状态

     1 mysql> show slave statusG;
 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: 276925387
10                Relay_Log_File: mysql-relay.000001
11                 Relay_Log_Pos: 4
12         Relay_Master_Log_File: master-bin.000113
13              Slave_IO_Running: No
14             Slave_SQL_Running: Yes
15               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: 0
22                    Last_Error: 
23                  Skip_Counter: 0
24           Exec_Master_Log_Pos: 276925387
25               Relay_Log_Space: 120
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: 'Could not find first log file name in binary log index file'
39                Last_SQL_Errno: 0
40                Last_SQL_Error: 
41   Replicate_Ignore_Server_Ids: 
42              Master_Server_Id: 21
43                   Master_UUID: e4a43da7-5b58-11e5-a12f-00163e003632
44              Master_Info_File: /home/data/mysql/master.info
45                     SQL_Delay: 0
46           SQL_Remaining_Delay: NULL
47       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
48            Master_Retry_Count: 86400
49                   Master_Bind: 
50       Last_IO_Error_Timestamp: 170204 10:48:06
51      Last_SQL_Error_Timestamp: 
52                Master_SSL_Crl: 
53            Master_SSL_Crlpath: 
54            Retrieved_Gtid_Set: 
55             Executed_Gtid_Set: 
56                 Auto_Position: 0
57 1 row in set (0.00 sec)
58 
59 ERROR: 
60 No query specified

    突然之间Slave_IO_Running: 状态变成NO了

    解决方法。

    首先在从库上执行

    stop slave;

    查看主库master状态

     1 mysql> show master statusG;
 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 specified
12 
13 mysql> flush logs;
14 Query OK, 0 rows affected (0.11 sec)

    刷新binlog日志

    flush logs;

    刷新后的日志会+1

    例如上面的 File: mysql-bin.000113 会变成 File: mysql-bin.000114

    再次查看master状态

     1 mysql> show master statusG;
 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

    然后就不需要在操作master，切换到从库

    输入CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000114',MASTER_LOG_POS=120;

    执行start slave;

    查看从库状态

     1 mysql> show slave statusG;
 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: 11314
10                Relay_Log_File: mysql-relay.000002
11                 Relay_Log_Pos: 11477
12         Relay_Master_Log_File: mysql-bin.000114
13              Slave_IO_Running: Yes
14             Slave_SQL_Running: Yes
15               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: 0
22                    Last_Error: 
23                  Skip_Counter: 0
24           Exec_Master_Log_Pos: 11314
25               Relay_Log_Space: 11646
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: 0
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: 21
43                   Master_UUID: e4a43da7-5b58-11e5-a12f-00163e003632
44              Master_Info_File: /home/data/mysql/master.info
45                     SQL_Delay: 0
46           SQL_Remaining_Delay: NULL
47       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
48            Master_Retry_Count: 86400
49                   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: 0
57 1 row in set (0.00 sec)
58 
59 ERROR: 
60 No query specified
  • 相关阅读:
    彻底弄懂GMT、UTC、时区和夏令时
    揭秘webpack loader
    揭秘webpack plugin
    封装axios
    webpack优化之玩转代码分割和公共代码提取
    node.js操作数据库之MongoDB+mongoose篇
    GitHub项目徽标
    Java中容易遗漏的小知识点( 一 )（为了和小白一样马上要考试的兄弟准备的，希望小白和大家高过不挂）
    QNX Message Passing，一个让人头秃的 IPC BUG
    【百面】02_模型评估
  • 原文地址：https://www.cnblogs.com/charles1ee/p/6364448.html
Copyright © 2020-2023  润新知