0

I have a master (master 1) that replicates to another master (master 2), which then replicates to its slave.

So master 2 had an issue with the binary logs:

Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

Which I've run into before and was able to resolve by running the same commands I ran from this thread, expire_logs_days directive requires change master? (I'll ask a question later about why this is still occuring, not my current issue though).

If interested here's the relevant bit from that thread for how I got it working:

stop slave;
reset slave;
change master to master_log_file='...' , master_log_pos=...
start slave;

so now master 2 is good and replicating from master 1. This time though the slave of master 2 broke as well.

The error I'm getting is:

Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size; the first event 'mysqld-bin.000397' at 244145356, the last event read from './mysqld-bin.000397' at 4, the last byte read from './mysqld-bin.000397' at 4.'

When I run SHOW BINARY LOGS; on the slave I get:

ERROR 1381 (HY000): You are not using binary logging

What happened to my slave? Why's it not working with the logs anymore? The master still has the log file so I figured running the stop, reset, change, start would resolve the issue (because it would re-request the logs) but it didn't.

  • I started researching this, and found one of my own answers from several years ago. I believe there's a condition where the master can write an event to the binlog and replicate it to the slave, and then lose the transaction in a hard crash, resulting in what are essentially phantom binlog entries that the slave saw but that no longer exist on the master, because the transactions were lost. Did your master log a Crashed binlog file... event in the error log? – Michael - sqlbot Jun 1 '18 at 23:56
  • I have +2000 lines of roughly 2018-06-02 02:49:17 1839 [ERROR] /usr/sbin/mysqld: Table './stats/stats' is marked as crashed and should be repaired (to date).I – user3783243 Jun 2 '18 at 6:51
  • That's something different, a consequence of a crash on a MyISAM table. I believe we're looking for a message like Crashed binlog file {name} size is {number}, but recovered up to {number}. Binlog trimmed to {number} bytes. – Michael - sqlbot Jun 2 '18 at 14:30
  • The only crashed reference is the one provided. I'm repairing that table on the master now. I did add expire_logs_days=7 a few weeks ago on the master, could that have have deleted the log and caused the issue? I thought it was for the logs from master1. – user3783243 Jun 2 '18 at 16:59
  • There are multiple issues in this question. expire_logs_days applies only to the server where it is configured. Also, SHOW BINARY LOGS would be run on the master, not the slave, in a 2-server system. – Michael - sqlbot Jun 2 '18 at 18:59
1

I'll bet sync_binlog was turned off.

With it off, the binlog entries just before the crash may not have been flushed to the binlog file, even though they have been sent to the Slave.

Sending replication data from Master to Slave:

  1. Write to the table on the Master.
  2. Buffer up the write to the binlog.
  3. Optionally flush the buffer to the binlog. -- Controlled by sync_binlog
  4. Send the query to the Slave(s).

When sync_binlog=OFF, there is a big chance that the binlog will be shorter than what the slave thinks it should be.

When the Slave-Master connection is reestablished, the Slave picks up where it left off. With sync_binlog=ON, that would be at the exact end of some binlog, and it would decide to move to the next binlog. The manual CHANGE MASTER simulates that.

The CHANGE MASTER to position 0 (or 4) of the next binlog (bump the number by 1).

(I have never used RESET SLAVE; I see not reason for it.)

  • So only the change master is needed to change the master file nd position? For the sync_binlog when I run show variables like '%sync_binlog%' I get back 0 so I guess it is off. Looking at the doc it seems like there are a number of values that this can be set to, and performance will vary. Will setting to 1 cause large issues? – user3783243 Jun 5 '18 at 14:13
  • Not to 0 of the current binlog -- 0 of the next one. The rest of CHANGE MASTER options don't need changing; just those two. Do you have GTID turned on? Of so, my instructions may not be correct. – Rick James Jun 5 '18 at 15:55
  • I mean for the sync_binlog, that should be 1 right so the log must complete writing before transmitting. I'm destroying the current slave because I don't want the data out of sync but I don't want this issue to occur again. – user3783243 Jun 12 '18 at 17:42
  • When off, and the system crashed, MySQL thought it wrote everything, but the OS died without flushing to disk. Upon restart, picking up at the start of the next binlog is the correct place. – Rick James Jun 12 '18 at 20:34
  • How can I contact you for consulting services? – user3783243 Aug 1 '18 at 3:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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