MySQL binlog编号最大是多大？

MySQL的二进制日志（binlog）中以二进制格式记录数据库内容修改的“event”。二进制日志是MySQL主备复制，异构数据同步和PITR恢复等重要功能的基础。

二进制日志的文件默认以binlog.#(binlog.000003)的格式命名，新生产的文件# 取当前存在的最大#+1。 那么问题来了，#的最大值是多少，达到这个最大值以后发生binlog切换会发生什么？

先说结论

1、最大值是2147483647

2、编号达到2147483647 后发生binlog切换，mysql进程报错并中止。

测试过程

1、修改binlog.index 
[root@localhost log]# vim binlog.index 
/data/mysql/log/binlog.2147483646

2、保留最后一个binlog日志并改名
[root@localhost log]# mv binlog.000001  binlog.2147483646
[root@localhost log]# ll -h
total 140K
-rw-r-----  1 mysql mysql  172 Oct 21 16:07 binlog.2147483646
-rw-r-----  1 mysql mysql   34 Oct 21 16:08 binlog.index
drwxr-x---. 2 mysql mysql 4.0K Oct 21 16:06 #innodb_redo
-rw-r-----. 1 mysql mysql  91K Oct 21 16:07 mysqld.err

3、启动MySQL
[root@localhost log]# /usr/local/mysql/bin/mysqld_safe &
[1] 47485
[root@localhost log]# 
[root@localhost log]# 
[root@localhost log]# 2022-10-21T08:09:47.852356Z mysqld_safe Logging to '/data/mysql/log/mysqld.err'.
2022-10-21T08:09:47.895417Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/data

4、查看当前二进制日志，启动后创建了一个新文件，编号达到最大值
[root@localhost log]# ll -h
total 144K
-rw-r-----  1 mysql mysql  172 Oct 21 16:07 binlog.2147483646
-rw-r-----  1 mysql mysql  153 Oct 21 16:09 binlog.2147483647
-rw-r-----  1 mysql mysql   68 Oct 21 16:09 binlog.index
drwxr-x---. 2 mysql mysql 4.0K Oct 21 16:09 #innodb_redo
-rw-r-----. 1 mysql mysql  96K Oct 21 16:09 mysqld.err

5、执行flush binary logs 报错 退出
mysql> flush binary logs;
ERROR 1598 (HY000): Binary logging not possible. Message: Can't generate a unique log-filename /data/mysql/log/binlog.(1-999), while rotating the binlog. Aborting the server

2022-10-21T16:11:54.335649+08:00 0 [System] [MY-010232] [Server] XA crash recovery finished.
2022-10-21T16:11:54.461507+08:00 0 [ERROR] [MY-010813] [Server] Log filename extension number exhausted: 2147483647. Please fix this by archiving old logs and updating the index files.
2022-10-21T16:11:54.461566+08:00 0 [ERROR] [MY-010905] [Server] Can't generate a unique log-filename /data/mysql/log/binlog.(1-999).
2022-10-21T16:11:54.461590+08:00 0 [ERROR] [MY-010822] [Server] MYSQL_BIN_LOG::open failed to generate new file name.
2022-10-21T16:11:54.461628+08:00 0 [ERROR] [MY-010119] [Server] Aborting
2022-10-21T16:11:55.985413+08:00 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.31)  MySQL Community Server - GPL.


6、修改binlog.index ,保留最后一个binlog文件并修改编号，重启数据库。
[root@localhost log]# ll -h
total 120K
-rw-r-----  1 mysql mysql  153 Oct 21 16:21 binlog.000001
-rw-r-----  1 mysql mysql   30 Oct 21 16:21 binlog.index
drwxr-x---. 2 mysql mysql 4.0K Oct 21 16:21 #innodb_redo
-rw-r-----. 1 mysql mysql 106K Oct 21 16:21 mysqld.err
[root@localhost log]# 
[root@localhost log]# cat binlog.index 
/data/mysql/log/binlog.000001

定位

sql/binlog.cc::find_uniq_filename
/**
  Find a unique filename for 'filename.#'.

  Set '#' to the highest existing log file extension plus one.

  This function will return nonzero if: (i) the generated name
  exceeds FN_REFLEN; (ii) if the number of extensions is exhausted;
  or (iii) some other error happened while examining the filesystem.

  @return
    nonzero if not possible to get unique filename.
*/
......
/* check if reached the maximum possible extension number */
  if (max_found >= MAX_LOG_UNIQUE_FN_EXT) {
    LogErr(ERROR_LEVEL, ER_BINLOG_FILE_EXTENSION_NUMBER_EXHAUSTED, max_found);
    error = 1;
    goto end;
  }

if (new_index_number > 0) {
    /*
      If "new_index_number" was specified, this means we are handling a
      "RESET MASTER TO" command and the binary log was already purged
      so max_found should be 0.
    */
    assert(max_found == 0);
    next = new_index_number;
  } else
    next = max_found + 1;
  if (sprintf(ext_buf, "%06lu", next) < 0) {
    error = 1;
    goto end;
  }
  *end++ = '.';
...........

如果max_found>=MAX_LOG_UNIQUE_FN_EXT 即0x7FFFFFFF 则报错
{ "ER_BINLOG_FILE_EXTENSION_NUMBER_EXHAUSTED", 10813, "Log filename extension number exhausted: %06lu. Please fix this by archiving old logs and updating the index files.","HY000", "", 2153 },

0x7FFFFFFF转换成十进制就是 2147483647
mysql> select  conv('7FFFFFFF',16,10);
+------------------------+
| conv('7FFFFFFF',16,10) |
+------------------------+
| 2147483647             |
+------------------------+
1 row in set (0.00 sec)

/*
  Maximum unique log filename extension.
  Note: setting to 0x7FFFFFFF due to atol windows
        overflow/truncate.
 */
#define MAX_LOG_UNIQUE_FN_EXT 0x7FFFFFFF

建议

监控binlog文件编号，如将达到最大值及时处理。