2

My slave machine reset and now on SLAVE SHOW STATUS\G I have the error:

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size'

The slave settings are showing as:

Master_Log_File: bin.003767
Read_Master_Log_Pos: 751854814
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: bin.003767
Slave_IO_Running: No
Slave_SQL_Running: Yes

However the relay-bin files on the slave are: mysql-relay-bin.000001 with 1KB in size. The Master on the other hand has the files: bin.003767 at over 700 megs. But no relay files are being updated or copied to the slave.

Is there any way to resolve this without a full data synchronization from beginning? It takes days to achieve this.

Does anyone know what the issue is here?

Much appreciated!

2
  • Update: I have run: stop slave; reset slave; change master to master_log_file='bin.003767', master_log_pos=4; start slave; and 'seems' like the replication has started again. I am not sure of the success and will monitor it.
    – Dug
    Jan 19, 2020 at 20:52
  • The file and position used should be derived from the consistent snapshot where the backup was taken, before replication started.
    – danblack
    Jan 19, 2020 at 22:09

2 Answers 2

4

To avoid the problem in the future, turn on sync_binlog.

What is happening is that the replication stream had not yet gotten to the Slave when the Master shutdown. The offset into the binlog had been bumped up, but the data was not on the Slave.

After coming up, move to the next binlog and set the position to 0 (or 4, either one works).

More details

When replicating a query, the Primary does two things 'at the same time':

  • Write to the Primary's binlog. Note: Depending on sync_binlog this write may sit in a buffer in RAM for some time.
  • Transmit, via TCP/IP, the same information to the Replica.

The Replica

  • Writes the info (from the network) into the relaylog.
  • Updates the position in the Primary's binlog.

If the Primary crashes and restarts:

  • The binlog was not flushed. Hence, the position that the Replica is pointing to is "off the end" of the binlog.
  • A new binlog is started.

So...

  • The binlog is missing some of the data.
  • The Replica thinks it has a "bad" position.
  • The start of the next binlog is where it needs to point. (master_log_pos=4; =0 also works)
  • (Not relevant to your situation) If the binlog is later to be used for "point in time recovery", it won't have some changes, hence will corrupt the data.

(I have never done RESET SLAVE; I don't think it is necessary.)

3
  • So this worked like a charm, but we had done a little diligence first, and I'd like to know if it was necessary: We have semi-sync replication on for a few replicas, and checked that they all had the same position. I was worried that perhaps the master died while chewing on a write, and maybe some of the replicas would have to get it from the most ahead replica (not expecting master to have that one, and so not wanting to skip that on the slaves that didn't ACK that position before master ate it). Well they all did have the same position, is there a reason that was true & maybe reliably true? Nov 18, 2021 at 17:00
  • 1
    @ChristopherMcGowan - I added a bunch.
    – Rick James
    Nov 18, 2021 at 22:57
  • Ah, that paints a solid picture @rick james. Thanks muchly! Nov 20, 2021 at 5:26
-1

It seems that setting the position to 4 (master_log_pos=4) has reignited the sync.

1
  • 1
    If you did not also move to the next binlog, you will replaying the same data again. Something as simple as UPDATE t SET x=x+1; will have corrupted the data.
    – Rick James
    Nov 20, 2021 at 6:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.