0

We were moving our servers to newer machines. And now I spotted that binary logs on master database takes about 18G of space. Previously it was about 3.5G, so change is quite big (~5 times). I didn't notice traffic increase since then (maybe 10%, but not 500%!). There were only small changes in configuration as our goal was to migrate our software.

We changed:

  • debian 6 -> 8
  • mysql 5.1.73 -> 5.5.50
  • max_binlog_size 100M -> 128M

Still same settings:

  • amd64
  • expire_logs_days = 10
  • binlog-format = row

Unfortunately I no longer have an access to old servers. I have a backup though.

I checked mentioned configuration options and our traffic. Is there something that could increase amount of binary logs I didn't think of? Please, point me somewhere :)

Edit 1:

I doubt that traffic profile changed a lot. Only a fixed number (fixed = controlled by us) of devices are communicating with this database. Those devices are logging but I might say it's a stable amount of traffic. Unfortunately I'm new and I don't have much data to compare with.

Database is working for over a month, so there shouldn't be any leftovers from migration itself.

Maybe I should ask a little different question. Is there anything in database setup that might change weight of binary logs? Some additional metadata? Verbosity?

Edit 2: Yes, server id's are different on both machines:

DB1:
grep server-id /etc/mysql/my.cnf 
server-id               = 10

DB2:
grep server-id /etc/mysql/my.cnf 
server-id               = 11
  • binlogs records transactions on master, so is your master server producing this much records? those logs are then copied to relay logs on slave so what is the situation for relay logs then? since you format is ROW so check there might be some huge transactions being generated at Master. – Nawaz Sohail Dec 5 '16 at 12:24
  • Did you do a full data import into the new database with binary logging turned on? That initial import may explain things – Philᵀᴹ Dec 5 '16 at 12:53
  • @Phil, no database was copied from a temporary slave. And that was done around month ago, so all I see is "production" traffic. – Kalavan Dec 5 '16 at 13:30
  • @Nawaz Sohail - slave relay logs are quite small - few MB. What is more, I doubt that traffic changed a lot (of course there is such possibility). See question edit. – Kalavan Dec 5 '16 at 13:38
  • Are the server-id values still distinct? – Rick James Dec 5 '16 at 20:05
0

You seem to have lots of binlogs. Look at their file dates. Pick the one with the smallest difference between it and the previous. Use mysqlbinlog to see what queries are replicated in it. That may give you a good clue.

[

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.