0

Lets say my mysql replication is enabled for long time, and the server has been quite busy, so new bin log files get created every few seconds. So in a few months, it is possible for latest bin log file to have name "mysql-bin.999999". Does the next file again become "mysql-bin.000001"?

The question is important from check pointing view, since I'm using the replication to stream changes of the DB. If the name starts back from 000001, then I cannot assume that file names would be always incrementing, so if older record are received, then it cannot be ignored just because file name is numerically smaller.

Ouroboros
  • 1,432
  • 1
  • 19
  • 41
  • It will continue up to INT_MAX, which should suffice for the next month or two. – Solarflare Aug 29 '17 at 19:24
  • So does that mean it would "rotate" back to 0000001? – Ouroboros Aug 29 '17 at 20:24
  • No, it will continue to 2147483647. Then it throws an error (unless you purged older files). You might want to prepare for that. – Solarflare Aug 29 '17 at 21:02
  • I'm storing just last 1 days worth of data. So lets say there are a 100K bin log files when it reaches INTMAX. Then what happens? – Ouroboros Aug 30 '17 at 09:25
  • 3
    As I said, if you have used up 2 billion indexes (58 years of 100K files/day), it will stop with an error. You will then have to reset it. Is this a real concern? It sounds more like you should rethink a process that relies on 100k binlogs/day, but has to rely on the filename to work properly. There are about 2 billion other things that can go wrong before that (e.g. someone changing the binlog filename/stopping replication/some error in replication; mysql (if it still exists) changing the log format in the next 50 years (and not increasing that max number); skynet taking over your server). – Solarflare Aug 30 '17 at 10:58
  • Thanks Solarflare, I just wanted to understand what exactly would happen. Although its not entirely unlikely to have that kind of load on a RDB. – Ouroboros Aug 30 '17 at 11:37
  • The next log file after `mysql-bin.999999` is definitely `mysql-bin.1000000`. You do, however, need to ensure that you are comparing the actual numbers numerically and not doing a lexical (string) comparison... because '1' < '9'. – Michael - sqlbot Sep 18 '17 at 01:46

0 Answers0