Why MySQL binlog contains only one record: log rotation

One of my slave mysql instances contains several unusual binary log files

• mysql-bin.023896 250.01MB 2023-10-11 08:30:06.478997000(last modified)

• mysql-bin.023897 2.27GB 2023-10-11 11:27:23.128550000

• mysql-bin.023898 15.67MB 2023-10-11 11:27:23.261558000

• mysql-bin.023899 281Bytes 2023-10-11 11:27:24.967536000

• mysql-bin.023900 250.01MB 2023-10-11 12:26:16.466269000

By usual I mean the size should be 250MB(max_binlog_size is 250MB), but mysql-bin.023897 conatins 2.27GB, and the subsequent one is 15.67MB, then the next one mysql-bin.023899 is ridiculously small, its size is only hundreds of bytes...

The content of mysql-bin.023899 says nothing but that mysql will record binary logs to mysql-bin.023900

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#231011  3:25:16 server id 1630115779  end_log_pos 123 CRC32 0x29f40f84 Start: binlog v 4, server v 5.7.26-log created 231011  3:25:16
BINLOG '
HKUlZQ/DlylhdwAAAHsAAAAAAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AYQP9Ck=
'/*!*/;
# at 123
#231011  3:25:16 server id 1630115779  end_log_pos 234 CRC32 0x0acbf66f Previous-GTIDs
# ac711cc5-2cff-11ed-82c4-5c6f69e9b080:1-7268,
# ef8421a8-2cf4-11ed-9afa-5c6f69e75ed0:117-1391824263
# at 234
#231011  3:25:16 server id 1630115779  end_log_pos 281 CRC32 0x23f57343 Rotate to mysql-bin.023900  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Questions

-
Why the binlog size grows to 2.27GB all of a sudden and shrinks to 15.67MB, 281Bytes subsequently? The content of mysql-bin.023897 shows that it contains a big transaction, could this be the root cause?

-
Wouldn't it be silly for MySQL to

MySQL closes the current binary log file and opens the next binary log file when any of the following happen:

-
MySQL Server restarts.

-
Someone runs FLUSH LOGS; or FLUSH BINARY LOGS;. It's possible that someone did this twice in quick succession, so one binlog file was opened but got no events before it was closed and the next binlog file was opened. See https://dev.mysql.com/doc/refman/8.0/en/log-file-maintenance.html

-
The current binlog file exceeds max_binlog_size. But the file may go over that, because MySQL will not split the changes associated with one transaction, and transactions have practically no size limit.

Reference: https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_max_binlog_size says:

A transaction is written in one chunk to the binary log, so it is never split between several binary logs. Therefore, if you have big transactions, you might see binary log files larger than max_binlog_size.

With these in mind, does it make sense how some binary log files can be very small, and some can be unexpected large?

StackExchange Database Administrators Q#332066, answer score: 4