首页 » MySQL » How to start MySQL slave/replica skip missing binlogs?

How to start MySQL slave/replica skip missing binlogs?

Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’

Sometimes our MySQL master-slave environment has not been used for a long time,slave server is stopped for some reason for a few hours/days, and the master binlog has been automatically deleted. when you resume replication on the slave it fails with above error, and I don’t care about the loss of transactions between them in my test DB. Of course, the production environment may need to rebuild the slave database. Because mysql is logical replication, you can reset to specify the location of synchronization, unlike oracle dataguard, which can only be recreate. and oracle can be configure archivelog delete policy like following, But mysql is not so lucky, it doesn’t check the log if it still needs.

Configure RMAN with one of the following options:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
Delete when it has been applied into the Standby.

CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;
Delete when it has been sent to the Standby although it has not been applied.

I’ll shows how to skip the missing log and continue to apply binlog.

# on Slave

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.56.200
                  Master_User: rpl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000020
          Read_Master_Log_Pos: 3499150
               Relay_Log_File: mysql-relay-bin.000032
                Relay_Log_Pos: 217
        Relay_Master_Log_File: mysql-bin.000020
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3499150
              Relay_Log_Space: 3514241
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                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'
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 200
                  Master_UUID: 9f3a3f6b-a547-11e9-aa6a-0800279fd10d
             Master_Info_File: /usr/local/mysql-5.7.26-el7-x86_64/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 220317 11:01:53
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 9f3a3f6b-a547-11e9-aa6a-0800279fd10d:1-10004
            Executed_Gtid_Set: 3ccfe5d1-a22c-11e9-a10b-08002738fa8b:1-2,
9f3a3f6b-a547-11e9-aa6a-0800279fd10d:1-10004
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

Tip:
‘Could not find first log file name in binary log index file’
This error occurs when the slave server required binary log for replication no longer exists on the master database server.
When you investigate you will find that the master server is no longer requesting binary logs which the slave server needs to pull in order to synchronize data. Possible reasons for this include the master server expired binary logs via system variable expire_logs_days – or someone manually deleted binary logs from master via PURGE BINARY LOGS command or via ‘rm -f’ command or maybe you have some cronjob which archives older binary logs to claim disk space, etc. So, make sure you always have the required binary logs exists on the master server and you can update your procedures to keep binary logs that the slave server requires by monitoring the “Relay_master_log_file” variable from SHOW SLAVE STATUS output. Moreover, if you have set expire_log_days in my.cnf old binlogs expire automatically and are removed. This means when MySQL opens a new binlog file, it checks the older binlogs, and purges any that are older than the value of expire_logs_days (in days).

In order to resolve this problem, the only clean solution I can think of is to re-create the slave server from a master server backup or from other slave in replication topology.

# on master

mysql> show variables like 'expire%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 14    |
+------------------+-------+
1 row in set (0.00 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000022 |       201 |
| mysql-bin.000023 |       177 |
| mysql-bin.000024 |       154 |
| mysql-bin.000025 |       154 |
| mysql-bin.000026 |       718 |
| mysql-bin.000027 |       741 |
+------------------+-----------+
6 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                            |
+------------------+----------+--------------+------------------+----------------------------------------------+
| mysql-bin.000027 |      741 |              |                  | 9f3a3f6b-a547-11e9-aa6a-0800279fd10d:1-10008 |
+------------------+----------+--------------+------------------+----------------------------------------------+
1 row in set (0.00 sec)

[root@master data]# ls -l
total 123268
-rw-r--r--  1 root  root       768 Jul  7  2019 all.sql
drwxr-x---  2 mysql mysql       81 Jul 13  2019 anbob
-rw-r-----  1 mysql mysql       56 Jul 13  2019 auto.cnf
-rw-r-----. 1 mysql mysql       56 Jul  6  2019 auto.cnf.bak
-rw-r-----  1 mysql mysql      441 Mar 16 15:16 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Mar 17 10:34 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Mar 17 10:34 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul  6  2019 ib_logfile1
-rw-r-----  1 mysql mysql 12582912 Mar 17 10:34 ibtmp1
drwxr-xr-x  2 root  root         6 Jul  8  2019 log
-rw-r-----. 1 mysql mysql    78580 Jul  8  2019 master.err
-rw-r-----  1 mysql mysql       48 Mar 17 10:56 master.info
-rw-r-----  1 mysql mysql        5 Mar 17 10:34 master.pid
drwxr-x---. 2 mysql mysql     4096 Jul  6  2019 mysql
-rw-r-----  1 mysql mysql      201 Mar 16 15:16 mysql-bin.000022
-rw-r-----  1 mysql mysql      177 Mar 16 15:16 mysql-bin.000023
-rw-r-----  1 mysql mysql      154 Mar 17 10:29 mysql-bin.000024
-rw-r-----  1 mysql mysql      154 Mar 17 10:30 mysql-bin.000025
-rw-r-----  1 mysql mysql      718 Mar 17 10:34 mysql-bin.000026
-rw-r-----  1 mysql mysql      741 Mar 17 10:37 mysql-bin.000027
-rw-r-----  1 mysql mysql      114 Mar 17 10:34 mysql-bin.index

Note:
the binlog mysql-bin.000020 is not longger exists on master. and the oldest binlog was 000022. lost binary logs for replica. I don’t care the data loss for test, so I will reset to postion to the 000022 binlog.

# on master

mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000022' limit 10;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000022 |   4 | Format_desc    |       200 |         123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000022 | 123 | Previous_gtids |       200 |         154 |                                       |
| mysql-bin.000022 | 154 | Rotate         |       200 |         201 | mysql-bin.000023;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
3 rows in set (0.00 sec)

# on slave

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000022', MASTER_LOG_POS=123;
Query OK, 0 rows affected (0.03 sec)

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


mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.200
                  Master_User: rpl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000027
          Read_Master_Log_Pos: 741
               Relay_Log_File: mysql-relay-bin.000008
                Relay_Log_Pos: 954
        Relay_Master_Log_File: mysql-bin.000027
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 741
              Relay_Log_Space: 4079
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 200
                  Master_UUID: 9f3a3f6b-a547-11e9-aa6a-0800279fd10d
             Master_Info_File: /usr/local/mysql-5.7.26-el7-x86_64/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 9f3a3f6b-a547-11e9-aa6a-0800279fd10d:10005-10008
            Executed_Gtid_Set: 3ccfe5d1-a22c-11e9-a10b-08002738fa8b:1-2,
9f3a3f6b-a547-11e9-aa6a-0800279fd10d:1-10008
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

Note:
Already applied to 000027 binlog.

test sync is ok.

# on master

mysql> use anbob;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table test200(id int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into test200 values (100);
Query OK, 1 row affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                            |
+------------------+----------+--------------+------------------+----------------------------------------------+
| mysql-bin.000027 |     1195 |              |                  | 9f3a3f6b-a547-11e9-aa6a-0800279fd10d:1-10010 |
+------------------+----------+--------------+------------------+----------------------------------------------+
1 row in set (0.00 sec)

# on slave

# on slave
mysql> use anbob;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from test200;
+------+
| id   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

— over —

打赏

对不起,这篇文章暂时关闭评论。