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 —
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。