7

I have a MySQL 5.7 running on a Centos 6. I enabled Binary Logging and didn't provide any custom value for the maximum size of Binary Log in my configuration file. By default, the parameter max_binlog_size = 1073741824 which is equivalent to 1GB. This means, once a binary log reaches the size of 1GB, a new file should be created.

I observed in my server that all the binary log files are of different sizes with lot of contrast like 200GB, 165GB, 4GB etc...

Why does this happen when the maximum size is 1GB for a binary log file? Is it necessary to mention even the default maximum size explicitly in the configuration file?

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
Yashwanth Aluru
  • 183
  • 1
  • 1
  • 8

2 Answers2

8

The reason your binary logs are so gigantic is simple: YOU ARE COMMITTING TOO MUCH DATA PER SINGLE TRANSACTION !!! Why is this the case ? According to the MySQL Documentation on max_binlog_size:

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.

SUGGESTION

Please try to commit data in smaller chunks.

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
1

If the files are indeed too large, you might consider changing the binlog_expire_logs_seconds configuration: https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_expire_logs_seconds

By default, it is set to keep the logs files for 30 days. so you might get many files of 1GB... setting the value to a smaller value (7 days for example) will reduce the total size the binary logs will require.

Once the configuration is set, you'll need to restart the server, and Mysql will clear the old files automitically.

Roey
  • 111
  • 2