mysql binlog文件扩展序号最大是多少

MySQL


摘要：你知道mysql的binlog文件扩展后缀序号最大到多少吗？

思考一个问题

我们知道mysql刚开始生成的binlog文件是从binlog.000001开始的，那么当日志文件到binlog.999999后是会从000001重新开始呢还是会继续递增呢？

测试一

测试下当binlog文件名达到binlog.999999后会怎么样？（在mysql5.6.35上测试）
首先关闭mysqld服务，手动生成一个binlog.999999的空文件

[root@mysql2 ~]# /opt/mysql/bin/mysqladmin shutdown --socket=/data/mysql/run/mysql.sock
170526 04:01:15 mysqld_safe mysqld from pid file /data/mysql/data/mysql2.pid ended
[1]+  Done                    /opt/mysql/bin/mysqld_safe --defaults-file=/opt/my3306.cnf  (wd: /opt)
(wd now: ~)
[root@mysql2 ~]# ps -ef|grep mysqld
root     11033 29391  0 04:01 pts/0    00:00:00 grep mysqld
[root@mysql2 ~]# ls -lrt /data/mysql/log/binlog/
total 16
-rw-r--r-- 1 root  root  1119 Mar  3 16:56 undo.sql
-rw-rw---- 1 mysql mysql   74 May 16 01:15 binlog.index
-rw-rw---- 1 mysql mysql  231 May 16 01:15 binlog.000033
-rw-rw---- 1 mysql mysql  254 May 26 04:01 binlog.000034
[root@mysql2 ~]# touch /data/mysql/log/binlog/binlog.999999
[root@mysql2 ~]# chown -R mysql.mysql /data/mysql/log/binlog/binlog.999999
[root@mysql2 ~]# ls -lrt /data/mysql/log/binlog/                          
total 16
-rw-r--r-- 1 root  root  1119 Mar  3 16:56 undo.sql
-rw-rw---- 1 mysql mysql   74 May 16 01:15 binlog.index
-rw-rw---- 1 mysql mysql  231 May 16 01:15 binlog.000033
-rw-rw---- 1 mysql mysql  254 May 26 04:01 binlog.000034
-rw-r--r-- 1 mysql mysql    0 May 26 04:02 binlog.999999

然后启动mysqld服务，看看会产生什么样的日志：

[root@mysql2 ~]# /opt/mysql/bin/mysqld_safe --defaults-file=/opt/my3306.cnf &
[1] 11042
[root@mysql2 ~]# 170526 04:03:36 mysqld_safe Logging to '/data/mysql/log/error.log'.
170526 04:03:36 mysqld_safe Starting mysqld daemon with databases from /data/mysql/data
[root@mysql2 ~]# ls -lrt /data/mysql/log/binlog/                             
total 16
-rw-r--r-- 1 root  root  1119 Mar  3 16:56 undo.sql
-rw-rw---- 1 mysql mysql  254 May 26 04:01 binlog.000034
-rw-r--r-- 1 mysql mysql    0 May 26 04:02 binlog.999999
-rw-rw---- 1 mysql mysql   75 May 26 04:03 binlog.index
-rw-rw---- 1 mysql mysql  231 May 26 04:03 binlog.1000000
[root@mysql2 ~]# cat /data/mysql/log/binlog/binlog.index 
/data/mysql/log/binlog/binlog.000034
/data/mysql/log/binlog/binlog.1000000
[root@mysql2 ~]#

可以看到产生的新日志是binlog.1000000。由此可见binlog日志的后缀序号并不局限于6位数，而是会继续递增，那么最大能到多少呢？
接下来我们去5.7.18版本的源码中看看，在Binlog.cc文件中看到如下代码：

/**
  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.
*/
/*从上面这段注释可以看出这里定义了一个查找binlog文件的方法，产生新的binlog日志是现有最高日志文件的扩展名加1*/
static int find_uniq_filename(char *name)
{
  uint                  i;
  char                  buff[FN_REFLEN], ext_buf[FN_REFLEN];
  struct st_my_dir     *dir_info;
  struct fileinfo *file_info;
  ulong                 max_found= 0, next= 0, number= 0;
  size_t		buf_length, length;
  char			*start, *end;
  int                   error= 0;
  DBUG_ENTER("find_uniq_filename");
  length= dirname_part(buff, name, &buf_length);
  start=  name + length;
  end=    strend(start);
  *end='.';
  length= (size_t) (end - start + 1);
  if ((DBUG_EVALUATE_IF("error_unique_log_filename", 1, 
      !(dir_info= my_dir(buff,MYF(MY_DONT_SORT))))))
  {						// This shouldn't happen
    my_stpcpy(end,".1");				// use name+1
    DBUG_RETURN(1);
  }
  file_info= dir_info->dir_entry;
  for (i= dir_info->number_off_files ; i-- ; file_info++)
  {
    if (strncmp(file_info->name, start, length) == 0 &&
	is_number(file_info->name+length, &number,0))
    {
      set_if_bigger(max_found, number);
    }
  }
  my_dirend(dir_info);
  /* check if reached the maximum possible extension number */
  if (max_found == MAX_LOG_UNIQUE_FN_EXT)  /*当找到的数是定义的最大数时，就退出*/
  {
    sql_print_error("Log filename extension number exhausted: %06lu. \
Please fix this by archiving old logs and \
updating the index files.", max_found);
    error= 1;
    goto end;
  }
  next= max_found + 1;
  if (sprintf(ext_buf, "%06lu", next)<0)
  {
    error= 1;
    goto end;
  }
  *end++='.';

再看下MAX_LOG_UNIQUE_FN_EXT的定义：

/*
  Maximum unique log filename extension. /*这里定义了最大的日志文件扩展名序号*/
  Note: setting to 0x7FFFFFFF due to atol windows
        overflow/truncate.
 */
#define MAX_LOG_UNIQUE_FN_EXT 0x7FFFFFFF

我们知道以0x开头的数据表示16进制，将它转换为10进制后：

mysql> SELECT CONV('7FFFFFFF',16,10);
+------------------------+
| CONV('7FFFFFFF',16,10) |
+------------------------+
| 2147483647             |
+------------------------+
1 row in set (0.00 sec)
mysql> select pow(2,31)-1;
+-------------+
| pow(2,31)-1 |
+-------------+
|  2147483647 |
+-------------+
1 row in set (0.00 sec)

由此可见，binlog后缀扩展序号的最大值是：2^31 - 1。

测试二

测试当达到binlog.2147483647后再进行flush logs；MySQL会发生什么事情。

[root@mysql2 ~]# touch /data/mysql/log/binlog/binlog.2147483645
[root@mysql2 ~]# chown -R mysql.mysql /data/mysql/log/binlog/binlog.2147483645
[root@mysql2 ~]# ls -lrt /data/mysql/log/binlog                               
total 16
-rw-r--r-- 1 root  root  1119 Mar  3 16:56 undo.sql
-rw-rw---- 1 mysql mysql  254 May 26 04:01 binlog.000034
-rw-r--r-- 1 mysql mysql    0 May 26 04:02 binlog.999999
-rw-rw---- 1 mysql mysql   75 May 26 04:03 binlog.index
-rw-rw---- 1 mysql mysql  231 May 26 04:03 binlog.1000000
-rw-r--r-- 1 mysql mysql    0 May 26 04:50 binlog.2147483645
[root@mysql2 ~]# /opt/mysql/bin/mysqladmin shutdown --socket=/data/mysql/run/mysql.sock
170526 04:51:33 mysqld_safe mysqld from pid file /data/mysql/data/mysql2.pid ended
[1]+  Done                    /opt/mysql/bin/mysqld_safe --defaults-file=/opt/my3306.cnf
[root@mysql2 ~]# /opt/mysql/bin/mysqld_safe --defaults-file=/opt/my3306.cnf &
[1] 13009
[root@mysql2 ~]# 170526 04:51:50 mysqld_safe Logging to '/data/mysql/log/error.log'.
170526 04:51:50 mysqld_safe Starting mysqld daemon with databases from /data/mysql/data
mysql> show master status;
+-------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                             |
+-------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------------+
| binlog.2147483646 |      231 |              |                  | 82ea218f-ffe4-11e6-977d-005056a740da:1-8727440,
82ea218f-ffe4-11e6-977d-005056a740db:1-430292 |
+-------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                             |
+-------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------------+
| binlog.2147483647 |      231 |              |                  | 82ea218f-ffe4-11e6-977d-005056a740da:1-8727440,
82ea218f-ffe4-11e6-977d-005056a740db:1-430292 |
+-------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> flush logs;
ERROR 1098 (HY000): Can't generate a unique log-filename /data/mysql/log/binlog/binlog.(1-999)
mysql> show master status;
Empty set (0.00 sec)

错误日志如下：

[root@mysql2 ~]# tailf /data/mysql/log/error.log                             
2017-05-26 04:52:50 13978 [Warning] Next log extension: 2147483642. Remaining log filename extensions: 5. Please consider archiving some logs.
2017-05-26 04:52:56 13978 [Warning] Next log extension: 2147483643. Remaining log filename extensions: 4. Please consider archiving some logs.
2017-05-26 04:53:01 13978 [Warning] Next log extension: 2147483644. Remaining log filename extensions: 3. Please consider archiving some logs.
2017-05-26 04:53:04 13978 [Warning] Next log extension: 2147483645. Remaining log filename extensions: 2. Please consider archiving some logs.
2017-05-26 04:53:09 13978 [Warning] Next log extension: 2147483646. Remaining log filename extensions: 1. Please consider archiving some logs.
2017-05-26 04:53:11 13978 [Warning] Next log extension: 2147483647. Remaining log filename extensions: 0. Please consider archiving some logs.
2017-05-26 04:53:14 13978 [ERROR] Log filename extension number exhausted: 2147483647. Please fix this by archiving old logs and updating the index files.
2017-05-26 04:53:14 13978 [ERROR] Can't generate a unique log-filename /data/mysql/log/binlog/binlog.(1-999)
2017-05-26 04:53:14 13978 [ERROR] Could not open /data/mysql/log/binlog/binlog for logging (error 0). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.

报错提示无法生成binlog文件了，查看生成的日志：

[root@mysql2 ~]# ls -lrt /data/mysql/log/binlog                                        
total 44
-rw-r--r-- 1 root  root  1119 Mar  3 16:56 undo.sql
-rw-rw---- 1 mysql mysql  254 May 26 04:01 binlog.000034
-rw-r--r-- 1 mysql mysql    0 May 26 04:02 binlog.999999
-rw-rw---- 1 mysql mysql  254 May 26 04:51 binlog.1000000
-rw-r--r-- 1 mysql mysql    0 May 26 04:52 binlog.2147483645
-rw-rw---- 1 mysql mysql  279 May 26 04:53 binlog.2147483646
-rw-rw---- 1 mysql mysql  362 May 26 04:53 binlog.index
-rw-rw---- 1 mysql mysql  231 May 26 04:53 binlog.2147483647
[root@mysql2 ~]# cat /data/mysql/log/binlog/binlog.index 
/data/mysql/log/binlog/binlog.000034
/data/mysql/log/binlog/binlog.1000000
/data/mysql/log/binlog/binlog.2147483645
/data/mysql/log/binlog/binlog.2147483646
/data/mysql/log/binlog/binlog.2147483647
[root@mysql2 ~]#

可以看出虽然报错貌似是想产生binlog.(1-999)的，事实上也没产生。说明当binlog序号达到最大值后既无法递增也无法重新从000001开始！
虽然实际工作中不大可能遇到binlog序号用完的情况，如果一旦遇到只能将日志清空然后重启mysqld服务，日志会继续从binlog.000001开始。

[root@mysql2 ~]# rm -rf /data/mysql/log/binlog/binlog.*
[root@mysql2 ~]# ls -lrt /data/mysql/log/binlog        
total 4
-rw-r--r-- 1 root root 1119 Mar  3 16:56 undo.sql
[root@mysql2 ~]#  /opt/mysql/bin/mysqld_safe --defaults-file=/opt/my3306.cnf &
[1] 17966
[root@mysql2 ~]# 170526 05:06:20 mysqld_safe Logging to '/data/mysql/log/error.log'.
170526 05:06:20 mysqld_safe Starting mysqld daemon with databases from /data/mysql/data
mysql> show master status;
+---------------+----------+--------------+------------------+----------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+---------------+----------+--------------+------------------+----------------------------------------+
| binlog.000001 |      455 |              |                  | 82ea218f-ffe4-11e6-977d-005056a740db:1 |
+---------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
[root@mysql2 ~]# cat /data/mysql/log/binlog/binlog.index 
/data/mysql/log/binlog/binlog.000001
[root@mysql2 ~]# ls -lrt /data/mysql/log/binlog/binlog.index    
-rw-rw---- 1 mysql mysql 37 May 26 05:06 /data/mysql/log/binlog/binlog.index
[root@mysql2 ~]#

总结：

  1. mysql binlog的最大sequence是：pow(2,31)-1 = 2147483647
  2. 当binlog接近这个值是小于1000开始向error log中写入警告
  3. binlog的sequence达到最大值时，不管有没有mysql-bin.000001类似这样的文件，mysqld都是退出。
  4. 在mysql产生binlog时会读取当前日件文目录下的log-bin的base name获取下一个日志文件的后面的Seq。 所以日志目录下文件太多，会影响MySQL的启动及日志切换。这里也有一个大的隐患运行中给放一个较大的日志文件，在下次日志文件切换时有可能很快就接近于最大值，造成mysqld crash退出。
  5. 一定要监控error log的输出以及binlog序号并足够重视，一旦监控到binlog序号接近最大值时就要找机会对主库进行reset master重置binlog序号从000001开始！
    参考：http://wubx.net/mysql-binlog-max-sequence/

本文标题:mysql binlog文件扩展序号最大是多少

文章作者:一条大河

发布时间:2017-05-25, 19:39:53

最后更新:2017-05-25, 22:06:22

原始链接:http://riverdba.github.io/2017/05/25/mysql-binlog-filename/

许可协议: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。

文章目录
  1. 1. 思考一个问题
  2. 2. 测试一
  3. 3. 测试二
|