Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I run a high traffic MySQL server - and I have observed that my binlogs only get to as big as 50mb, and MySQL creates a new one again.

I already have max_binlog_size set to 1GB - but for some reason - MySQL wont honor it. Are there any other settings that I should be aware about?

I'm on MySQL 4.1.22, CentOS 5 (managed server from LiquidWeb).

root@host2 [~]# mysqladmin variables | grep bin
| binlog_cache_size               | 32768                                       |
| innodb_locks_unsafe_for_binlog  | OFF                                         |
| log_bin                         | ON                                          |
| max_binlog_cache_size           | 18446744073709551615                        |
| max_binlog_size                 | 1073741824                                  |
| sync_binlog                     | 0                                           |

And one more observation: based on "ls -al" output, the date of the binlogs are almost identical in the minutes part, that is, almost always rotated on the 41st minute of every hour.

This is strange because I can't find any cron scripts that could have done this. I asked the LiquidWeb sysadmins and they say they did not control this also.

So, any ideas anyone?

---
root@host2 [~]# ls -al /home/master_mysql/
total 1069004
drwxr-xr-x  3 mysql mysql     4096 Nov 15 23:41 ./
drwx--x--x 13 root  root      4096 Nov 14 17:15 ../
-rw-rw----  1 mysql mysql      122 Nov 14 19:42 mysql-bin.000001
-rw-rw----  1 mysql mysql 27841530 Nov 14 21:41 mysql-bin.000002
-rw-rw----  1 mysql mysql  7899803 Nov 14 21:55 mysql-bin.000003
-rw-rw----  1 mysql mysql       79 Nov 14 21:56 mysql-bin.000004
-rw-rw----  1 mysql mysql 16789873 Nov 14 22:41 mysql-bin.000005
-rw-rw----  1 mysql mysql 33813877 Nov 14 23:41 mysql-bin.000006
-rw-rw----  1 mysql mysql 48819373 Nov 15 00:41 mysql-bin.000007
-rw-rw----  1 mysql mysql 41505054 Nov 15 01:41 mysql-bin.000008
-rw-rw----  1 mysql mysql 44608575 Nov 15 02:41 mysql-bin.000009
-rw-rw----  1 mysql mysql 40341521 Nov 15 03:41 mysql-bin.000010
-rw-rw----  1 mysql mysql 33625553 Nov 15 04:41 mysql-bin.000011
-rw-rw----  1 mysql mysql 28685183 Nov 15 05:41 mysql-bin.000012
-rw-rw----  1 mysql mysql 28054132 Nov 15 06:41 mysql-bin.000013
-rw-rw----  1 mysql mysql 31718242 Nov 15 07:41 mysql-bin.000014
-rw-rw----  1 mysql mysql 34437340 Nov 15 08:41 mysql-bin.000015
-rw-rw----  1 mysql mysql 39474942 Nov 15 09:41 mysql-bin.000016
-rw-rw----  1 mysql mysql 43580287 Nov 15 10:41 mysql-bin.000017
-rw-rw----  1 mysql mysql 51165432 Nov 15 11:41 mysql-bin.000018
-rw-rw----  1 mysql mysql 48298102 Nov 15 12:41 mysql-bin.000019
-rw-rw----  1 mysql mysql 44044967 Nov 15 13:35 mysql-bin.000020
-rw-rw----  1 mysql mysql 53665390 Nov 15 14:41 mysql-bin.000021
-rw-rw----  1 mysql mysql 46259278 Nov 15 15:41 mysql-bin.000022
-rw-rw----  1 mysql mysql 44675487 Nov 15 16:41 mysql-bin.000023
-rw-rw----  1 mysql mysql 41288305 Nov 15 17:41 mysql-bin.000024
-rw-rw----  1 mysql mysql 45883369 Nov 15 18:41 mysql-bin.000025
-rw-rw----  1 mysql mysql 40055136 Nov 15 19:41 mysql-bin.000026
-rw-rw----  1 mysql mysql 39884190 Nov 15 20:41 mysql-bin.000027
-rw-rw----  1 mysql mysql 43919061 Nov 15 21:41 mysql-bin.000028
-rw-rw----  1 mysql mysql 45474412 Nov 15 22:41 mysql-bin.000029
-rw-rw----  1 mysql mysql 43899660 Nov 15 23:41 mysql-bin.000030
-rw-rw----  1 mysql mysql  3625113 Nov 15 23:45 mysql-bin.000031
-rw-rw----  1 mysql mysql     1116 Nov 15 23:41 mysql-bin.index
drwxr-xr-x  2 root  root      4096 Nov 14 19:00 restore_point_20091114/
---

Thanks!

share|improve this question

2 Answers 2

Given that the sizes are nowhere near identical, it's not the size limit being reached, and the exact timing is a dead giveaway -- MySQL will rotate it's binlog whenever it is restarted, or when you tell it to reopen it's other (system) logs -- typically something that logrotate (or another log rotation tool) will do as part of it's operation. Look for what's rotating your logs (if LiquidWeb are supposed to be managing that for you, be afraid... be very afraid).

share|improve this answer

I Agree with womble, max_binlog_size should do it, but it does look like you are getting logs flushed / rotated for you. Check /etc/crontab or any crons inside of /var/spool/cron for any thing odd. Also, maybe set your my.cnf with sql_log_off = on ( im at 5.1 but this should work for you) to get some more info in your mysqld.log, Although alot could just be garbage to look at. Do you have any frontend software that could possibly be doing this? Good luck.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.