0

There is a master-slave setup of a mysql db. The replication was going on fine until pos y when an error was thrown Client requested master to start replication from position > file size. I tried to restart replication from an earlier position but it failed. I checked for binlog file size and it seems the size is currently x (with x <<< y). However, I do have logs for replication from pos x to pos y. I don't have access or knowledge of any activity on the db. Didn't find any relevant article where binlog file was truncated to a smaller size.

I want to understand what all happening on the db could shrink a particular binlog file? Does restart or failover could lead to such a scenario?

2 Answers 2

0

It sounds like the length of the binlog was not stored in a timely manner.

Add confguration:

sync_binlog = 1

is probably the solution -- if this problem occurs after a crash and server restart.

4
  • I checked sync_binlog is already enabled on the database. {'Variable_name': 'sync_binlog', 'Value': '1000'} So, this shouldn't be the issue.
    – toros
    Commented Nov 21, 2023 at 17:15
  • 1
    @toros - sync_binlog is not a boolean. The value is not 1 or 0 meaning on or off. The value is how many transactions between syncs. So in your case, if the value is 1000, you could lose up to 1000 transactions worth of binlog if there's a crash. This is rare, but it is a risk. Commented Nov 21, 2023 at 17:17
  • @BillKarwin Thanks, makes sense here. However, in a different case, sync_binlog value is 1 and still seeing the same error. With value 1, we will either have the transaction log or not. There couldn't be a case where some logs are deleted due to crash, right?
    – toros
    Commented Nov 21, 2023 at 17:40
  • @toros - I don't think sync_binlog controls the existence of the transaction log, only the frequency of flushing. 0 = "flush when you have to"; 1= "flush always", 1000 = almost as bad as 0.
    – Rick James
    Commented Nov 21, 2023 at 18:11
0

There couldn't be a case where some logs are deleted due to crash, right?

No, I've never seen files be truncated. Binary log files are append-only.

It is normal for old binary log files to be removed (but not shrunk) if they are older than expire_logs_days or binlog_expire_logs_seconds. Files are not removed precisely at the moment when they become too old; they are checked at the time a new binlog file is opened. This occurs when the last file reaches the max size, or the MySQL Server restarts, or someone runs FLUSH LOGS. But even in these cases, old files are not truncated or shrunk, they are just removed.

So one possibility is some external process (not mysqld) deliberately truncated or renamed files.

It's more likely that someone issued a CHANGE REPLICATION SOURCE command (aka CHANGE MASTER) and misconfigured the log name or log position from which to read.

I've also seen a case where writes to the binary log were failing, because of a disk that was physically degraded, so writes were failing. So the file was virtually size x, and the replica was reading from that position, but the latter portion of the file wasn't actually on disk — it resided only in the OS filesystem cache in RAM. Then the server rebooted. After the reboot, the binlog file was physically size x, but the replica had already replayed changes past x, and expected the file to be larger.

This scenario should not be possible if you have set sync_binlog=1, because commits will fail immediately if the OS can't sync the file to disk.

To be honest, in your position I would just trash the replica and start over. Make a fresh backup from the source (I would use Percona XtraBackup), and use it to overwrite the replica's data and start from there. At my last job, we reinitialized replicas every week. It was one of our SOP's that we had scripted. It's not worth the time to figure out how a replica got misconfigured, when it's so easy to just reinitialize it.

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.