4

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

  1. 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?

  2. Wouldn't it be silly for MySQL to record only a rotate event in mysql-bin.023899 and jump to the next binlog file? Why MySQL is doing this?

3
  • Look at the dates on the files as you read Bill's Answer. Also, what was you value of max_binlog_size?
    – Rick James
    Oct 12, 2023 at 5:31
  • @RickJames Thank you for replying! I have updated the question to provide more information.
    – Steve Wu
    Oct 13, 2023 at 2:54
  • 2.27GB in 3 hours !! 250MB in 52 minutes !! That is a massive rate of writing binlogs. If there is a Replica server, see if it has a different server_id. If not, you have a loop. Else, look at what monstrous queries you are running -- such as UPDATEing a billion-row table.
    – Rick James
    Oct 13, 2023 at 4:13

1 Answer 1

4

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

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?

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.