Binary log in one of my server has grown more than 15 GB even after setting the max size to 1 GB. The MySQL server is as well not starting.
Can anyone highlight what might have caused it ?
Thanks.
Asked
Active
Viewed 4,017 times
2
-
Do you mean you set `max_binlog_size` to 1GB? Is 15GB the size of single binary log file? – quanta Sep 14 '11 at 06:36
-
Yes. max_binlog_size is globally set to 1GB. But I can see a binlog file of 15GB. – Reena Sep 14 '11 at 06:46
-
Does it happen before or after you set `max_binlog_size`? – quanta Sep 14 '11 at 06:54
-
I haven't changed the default max_binlog_size since the MySQL server was configured. Everything was working fine. Suddenly one day the MySQL server crashed and after investigating the binlog size was found to be 15 Gigs. – Reena Sep 14 '11 at 07:01
2 Answers
2
The mysql var max_binlog_size sets the size the currently being written to binlog file will grow to. When that threshold is hit (it can run over if you have open transactions) a new log file will be started. It does not control the the maximum allowable space the that the aggregate of all log files will grow too. AFAIK - there is not a setting for this unless it was very recently introduced. What I typically do is purge old binlogs past some threshold. Eg.
expire_logs_days = 90
You generally want to keep the binlogs since your last known good dump or other crash consistent backup.
Joshua Hoblitt
- 665
- 4
- 11
-
But under what circumstances the binlog size can cross the max_binlog_size of 1GB ? – Reena Sep 14 '11 at 06:49
-
2
It could be that a huge transaction was done.
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.
Muhammad
- 699
- 10
- 20
-
Based on the MySQL docs - "The maximum and default value is 1GB" for max_binlog_size variable. – Muhammad Sep 14 '11 at 07:19