mysql 二进制日志后缀数字最大为多少-搜云库技术团队

IDEA2023.1.3破解,IDEA破解,IDEA 2023.1破解,最新IDEA激活码

之前看到mysql二进制日志后面会加一个以数字递增为结尾的后缀，一直在想当尾数到达999999后会发生什么情况，先查了一下官网，对后缀有这样一句介绍：The server creates binary log files in sequence by adding a numeric suffix to the base name

就是说会在你定义的二进制文件名后再加多一个数字为结尾，那么这个数最大为多少呢(此次实验版本为5.7.12)？

我们先让其到999999，再flush logs看一下，发现还是可以继续递增

-rw-r—–. 1 mysql mysql 241 Aug 11 19:05 binlog57.1000000
-rw-r—–. 1 mysql mysql 194 Aug 11 19:05 binlog57.1000001
-rw-r—–. 1 mysql mysql 177 Aug 11 19:02 binlog57.999991
-rw-r—–. 1 mysql mysql 200 Aug 11 19:03 binlog57.999992
-rw-r—–. 1 mysql mysql 200 Aug 11 19:03 binlog57.999993
-rw-r—–. 1 mysql mysql 200 Aug 11 19:04 binlog57.999994
-rw-r—–. 1 mysql mysql 200 Aug 11 19:04 binlog57.999995
-rw-r—–. 1 mysql mysql 200 Aug 11 19:04 binlog57.999996
-rw-r—–. 1 mysql mysql 200 Aug 11 19:04 binlog57.999997
-rw-r—–. 1 mysql mysql 468 Aug 11 19:05 binlog57.999998
-rw-r—–. 1 mysql mysql 241 Aug 11 19:05 binlog57.999999

我们再把后面改成int的最大值（4294967296）再看看，发现依旧可以继续向上加：

-rw-r—–. 1 mysql mysql 241 Aug 11 19:05 binlog57.4294967290
-rw-r—–. 1 mysql mysql 244 Aug 12 09:10 binlog57.4294967291
-rw-r—–. 1 mysql mysql 244 Aug 12 09:10 binlog57.4294967292
-rw-r—–. 1 mysql mysql 244 Aug 12 09:11 binlog57.4294967293
-rw-r—–. 1 mysql mysql 244 Aug 12 09:11 binlog57.4294967294
-rw-r—–. 1 mysql mysql 244 Aug 12 09:11 binlog57.4294967295
-rw-r—–. 1 mysql mysql 244 Aug 12 09:11 binlog57.4294967296
-rw-r—–. 1 mysql mysql 244 Aug 12 09:11 binlog57.4294967297
-rw-r—–. 1 mysql mysql 244 Aug 12 09:11 binlog57.4294967298

按照mysql的数据类型，我们还有更大的bigint，我们再次将此后缀改在2^64，即（18446744073709551616）

重启之后发现此值自动变为了9223372036854775808，即2^63：

-rw-r—–. 1 mysql mysql 520 Aug 12 09:22 binlog57.9223372036854775808
再flush log都也不会变了。

那此时如果我插入记录，并flush logs，binlog里面的内容会怎样？

我们插入一条记录：

[zejin] 3301>insert into t_users values(7,’test’);
Query OK, 1 row affected (0.00 sec)

解释下binlog:
解析一：

[root@zejin240 I3301]# mysqlbinlog ;
;
DELIMITER ;
# at 4
#160812  9:26:52 server id 3301  end_log_pos 123 CRC32 0x500b46f2     Start: binlog v 4, server v 5.7.12-log created 160812  9:26:52
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
3CWtVw/lDAAAdwAAAHsAAAABAAQANS43LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AfJGC1A=
';
# at 123
#160812  9:26:52 server id 3301  end_log_pos 194 CRC32 0x4cf85ce2     Previous-GTIDs
# a97983fc-5a29-11e6-9d28-000c29d4dc3f:1802-1806
# at 194
#160812  9:28:13 server id 3301  end_log_pos 259 CRC32 0x3e10b5ae     GTID    last_committed=0    sequence_number=1
SET @@SESSION.GTID_NEXT= 'a97983fc-5a29-11e6-9d28-000c29d4dc3f:1807';
# at 259
#160812  9:28:13 server id 3301  end_log_pos 332 CRC32 0xe3e2c0d3     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1470965293;
SET @@session.pseudo_thread_id=2;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1;
SET @@session.sql_mode=1436549152;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33;
SET @@session.lc_time_names=0;
SET @@session.collation_database=DEFAULT;
BEGIN
;
# at 332
#160812  9:28:13 server id 3301  end_log_pos 386 CRC32 0xb37366c7     Table_map: `zejin`.`t_users` mapped to number 109
# at 386
#160812  9:28:13 server id 3301  end_log_pos 431 CRC32 0xf13017e6     Write_rows: table id 109 flags: STMT_END_F

BINLOG '
LSatVxPlDAAANgAAAIIBAAAAAG0AAAAAAAEABXplamluAAd0X3VzZXJzAAIDDwKWAADHZnOz
LSatVx7lDAAALQAAAK8BAAAAAG0AAAAAAAEAAgAC//wHAAAABHRlc3TmFzDx
';
### INSERT INTO `zejin`.`t_users`
### SET
###   @1=7 
###   @2='test' 
# at 431
#160812  9:28:13 server id 3301  end_log_pos 462 CRC32 0x58679478     Xid = 20
COMMIT;
ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 134217728, event_type: -27
SET @@SESSION.GTID_NEXT= 'AUTOMATIC'  ;
DELIMITER ;
# End of log file
;
;

[zejin] 3301>flush logs;
Query OK, 0 rows affected (0.01 sec)

解析二：

[root@zejin240 I3301]# mysqlbinlog ;
;
DELIMITER ;
# at 4
#160812  9:29:15 server id 3301  end_log_pos 123 CRC32 0x1a8f3783     Start: binlog v 4, server v 5.7.12-log created 160812  9:29:15
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
ayatVw/lDAAAdwAAAHsAAAABAAQANS43LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AYM3jxo=
';
# at 123
#160812  9:29:15 server id 3301  end_log_pos 194 CRC32 0xfe26d3f7     Previous-GTIDs
# a97983fc-5a29-11e6-9d28-000c29d4dc3f:1802-1807
# at 194
#160812  9:28:13 server id 3301  end_log_pos 259 CRC32 0x3e10b5ae     GTID    last_committed=0    sequence_number=1
SET @@SESSION.GTID_NEXT= 'a97983fc-5a29-11e6-9d28-000c29d4dc3f:1807';
# at 259
#160812  9:28:13 server id 3301  end_log_pos 332 CRC32 0xe3e2c0d3     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1470965293;
SET @@session.pseudo_thread_id=2;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1;
SET @@session.sql_mode=1436549152;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33;
SET @@session.lc_time_names=0;
SET @@session.collation_database=DEFAULT;
BEGIN
;
# at 332
#160812  9:28:13 server id 3301  end_log_pos 386 CRC32 0xb37366c7     Table_map: `zejin`.`t_users` mapped to number 109
# at 386
#160812  9:28:13 server id 3301  end_log_pos 431 CRC32 0xf13017e6     Write_rows: table id 109 flags: STMT_END_F

BINLOG '
LSatVxPlDAAANgAAAIIBAAAAAG0AAAAAAAEABXplamluAAd0X3VzZXJzAAIDDwKWAADHZnOz
LSatVx7lDAAALQAAAK8BAAAAAG0AAAAAAAEAAgAC//wHAAAABHRlc3TmFzDx
';
### INSERT INTO `zejin`.`t_users`
### SET
###   @1=7 
###   @2='test' 
# at 431
#160812  9:28:13 server id 3301  end_log_pos 462 CRC32 0x58679478     Xid = 20
COMMIT;
# at 462
#160812  9:29:15 server id 3301  end_log_pos 521 CRC32 0xc86262e6     Rotate to binlog57.9223372036854775808  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC'  ;
DELIMITER ;
# End of log file
;
;

再插入一条记录:

[zejin] 3301>insert into t_users values(8,’test1′);
Query OK, 1 row affected (0.01 sec)

解析三：

[root@zejin240 I3301]# mysqlbinlog ;
;
DELIMITER ;
# at 4
#160812  9:29:15 server id 3301  end_log_pos 123 CRC32 0x1a8f3783     Start: binlog v 4, server v 5.7.12-log created 160812  9:29:15
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
ayatVw/lDAAAdwAAAHsAAAABAAQANS43LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AYM3jxo=
';
# at 123
#160812  9:29:15 server id 3301  end_log_pos 194 CRC32 0xfe26d3f7     Previous-GTIDs
# a97983fc-5a29-11e6-9d28-000c29d4dc3f:1802-1807
# at 194
#160812  9:31:09 server id 3301  end_log_pos 259 CRC32 0xad1dc1f6     GTID    last_committed=0    sequence_number=1
SET @@SESSION.GTID_NEXT= 'a97983fc-5a29-11e6-9d28-000c29d4dc3f:1808';
# at 259
#160812  9:31:09 server id 3301  end_log_pos 332 CRC32 0x62cde234     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1470965469;
SET @@session.pseudo_thread_id=2;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1;
SET @@session.sql_mode=1436549152;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33;
SET @@session.lc_time_names=0;
SET @@session.collation_database=DEFAULT;
BEGIN
;
# at 332
#160812  9:31:09 server id 3301  end_log_pos 386 CRC32 0xd3a0edfd     Table_map: `zejin`.`t_users` mapped to number 109
# at 386
#160812  9:31:09 server id 3301  end_log_pos 432 CRC32 0x9146b5b0     Write_rows: table id 109 flags: STMT_END_F

BINLOG '
3SatVxPlDAAANgAAAIIBAAAAAG0AAAAAAAEABXplamluAAd0X3VzZXJzAAIDDwKWAAD97aDT
3SatVx7lDAAALgAAALABAAAAAG0AAAAAAAEAAgAC//wIAAAABXRlc3QxsLVGkQ==
';
### INSERT INTO `zejin`.`t_users`
### SET
###   @1=8 
###   @2='test1' 
# at 432
#160812  9:31:09 server id 3301  end_log_pos 463 CRC32 0x594574f2     Xid = 22
COMMIT;
ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 150994944, event_type: -27
SET @@SESSION.GTID_NEXT= 'AUTOMATIC'  ;
DELIMITER ;
# End of log file
;
;

发现flush log后解析的binlog中会产生一个错误：

ERROR: Error in Log_event::read_log_event(): ‘read error’, data_len: 150994944, event_type: -27
对比解析一与解释三就会发现，超过此最大值后，flush log命令会覆盖掉之前的数据，并且binlog后面的数字后缀永远停留在****9223372036854775808。这种情况下肯定是有问题的。

不过话说回来，你说生产环境下什么时候些值才可能超过这个数呢，按每秒产生1个binlog的速度来计算，我们需要9223372036854775808/60/60/24/365/10^9=2924.7亿年，估计它没挂，地球都挂了，它会陪你活到天荒地老……

mysql 二进制日志后缀数字最大为多少

相关推荐

评论抢沙发

学习必备：精选优质专栏

大前端WP主题更专业更方便

觉得文章有用就打赏一下文章作者

微信扫一扫打赏

微信扫一扫打赏

QQ咨询

回顶部

相关推荐

评论 抢沙发

学习必备：精选优质专栏

大前端WP主题 更专业 更方便

觉得文章有用就打赏一下文章作者

微信扫一扫打赏

微信扫一扫打赏

QQ咨询

回顶部

评论抢沙发

大前端WP主题更专业更方便