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.