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

本文探讨了MySQL二进制日志文件名后缀数字达到999999后如何继续递增,直至int最大值4294967296。实验结果显示,即使超过此值,仍能继续生成新的日志,但当后缀达到2^64(18446744073709551616)时,系统自动将其变为2^63(9223372036854775808),并且在插入记录并刷新日志后,binlog内容正常。虽然超过该数值的情况在实际生产环境中几乎不可能出现,但了解这一限制仍然是有必要的。
摘要由CSDN通过智能技术生成

之前看到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 --no-defaults -vv binlog57.9223372036854775808/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER/*!*/;

# at4#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 binlogis either in use or was notclosed properly.

BINLOG'3CWtVw/lDAAAdwAAAHsAAAABAAQANS43LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AfJGC1A='/*!*/;

# at123#160812 9:26:52 server id 3301 end_log_pos 194 CRC32 0x4cf85ce2 Previous-GTIDs

# a97983fc-5a29-11e6-9d28-000c29d4dc3f:1802-1806# at194#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'/*!*/;

# at259#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/*!*/;/*!\C utf8*//*!*/;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

/*!*/;

# at332#160812 9:28:13 server id 3301 end_log_pos 386 CRC32 0xb37366c7 Table_map: `zejin`.`t_users` mapped to number 109# at386#160812 9:28:13 server id 3301 end_log_pos 431 CRC32 0xf13017e6 Write_rows: table id 109flags: STMT_END_F

BINLOG'LSatVxPlDAAANgAAAIIBAAAAAG0AAAAAAAEABXplamluAAd0X3VzZXJzAAIDDwKWAADHZnOz

LSatVx7lDAAALQAAAK8BAAAAAG0AAAAAAAEAAgAC//wHAAAABHRlc3TmFzDx'/*!*/;

###INSERT INTO`zejin`.`t_users`

###SET###@1=7 /*INT meta=0 nullable=0 is_null=0*/###@2='test' /*VARSTRING(150) meta=150 nullable=0 is_null=0*/# at431#160812 9:28:13 server id 3301 end_log_pos 462 CRC32 0x58679478 Xid = 20

COMMIT/*!*/;

ERROR: Errorin Log_event::read_log_event(): 'read error', data_len: 134217728, event_type: -27

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /*added by mysqlbinlog*/ /*!*/;

DELIMITER ;

#End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[zejin] 3301>flush logs;

Query OK, 0 rows affected (0.01 sec)

解析二:

[root@zejin240 I3301]# mysqlbinlog --no-defaults -vv binlog57.9223372036854775808/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER/*!*/;

# at4#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 binlogis either in use or was notclosed properly.

BINLOG'ayatVw/lDAAAdwAAAHsAAAABAAQANS43LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AYM3jxo='/*!*/;

# at123#160812 9:29:15 server id 3301 end_log_pos 194 CRC32 0xfe26d3f7 Previous-GTIDs

# a97983fc-5a29-11e6-9d28-000c29d4dc3f:1802-1807# at194#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'/*!*/;

# at259#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/*!*/;/*!\C utf8*//*!*/;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

/*!*/;

# at332#160812 9:28:13 server id 3301 end_log_pos 386 CRC32 0xb37366c7 Table_map: `zejin`.`t_users` mapped to number 109# at386#160812 9:28:13 server id 3301 end_log_pos 431 CRC32 0xf13017e6 Write_rows: table id 109flags: STMT_END_F

BINLOG'LSatVxPlDAAANgAAAIIBAAAAAG0AAAAAAAEABXplamluAAd0X3VzZXJzAAIDDwKWAADHZnOz

LSatVx7lDAAALQAAAK8BAAAAAG0AAAAAAAEAAgAC//wHAAAABHRlc3TmFzDx'/*!*/;

###INSERT INTO`zejin`.`t_users`

###SET###@1=7 /*INT meta=0 nullable=0 is_null=0*/###@2='test' /*VARSTRING(150) meta=150 nullable=0 is_null=0*/# at431#160812 9:28:13 server id 3301 end_log_pos 462 CRC32 0x58679478 Xid = 20

COMMIT/*!*/;

# at462#160812 9:29:15 server id 3301 end_log_pos 521 CRC32 0xc86262e6 Rotate to binlog57.9223372036854775808 pos: 4

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /*added by mysqlbinlog*/ /*!*/;

DELIMITER ;

#End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

再插入一条记录:

[zejin] 3301>insert into t_users values(8,'test1');

Query OK, 1 row affected (0.01 sec)

解析三:

[root@zejin240 I3301]# mysqlbinlog --no-defaults -vv binlog57.9223372036854775808/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER/*!*/;

# at4#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 binlogis either in use or was notclosed properly.

BINLOG'ayatVw/lDAAAdwAAAHsAAAABAAQANS43LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AYM3jxo='/*!*/;

# at123#160812 9:29:15 server id 3301 end_log_pos 194 CRC32 0xfe26d3f7 Previous-GTIDs

# a97983fc-5a29-11e6-9d28-000c29d4dc3f:1802-1807# at194#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'/*!*/;

# at259#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/*!*/;/*!\C utf8*//*!*/;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

/*!*/;

# at332#160812 9:31:09 server id 3301 end_log_pos 386 CRC32 0xd3a0edfd Table_map: `zejin`.`t_users` mapped to number 109# at386#160812 9:31:09 server id 3301 end_log_pos 432 CRC32 0x9146b5b0 Write_rows: table id 109flags: STMT_END_F

BINLOG'3SatVxPlDAAANgAAAIIBAAAAAG0AAAAAAAEABXplamluAAd0X3VzZXJzAAIDDwKWAAD97aDT

3SatVx7lDAAALgAAALABAAAAAG0AAAAAAAEAAgAC//wIAAAABXRlc3QxsLVGkQ=='/*!*/;

###INSERT INTO`zejin`.`t_users`

###SET###@1=8 /*INT meta=0 nullable=0 is_null=0*/###@2='test1' /*VARSTRING(150) meta=150 nullable=0 is_null=0*/# at432#160812 9:31:09 server id 3301 end_log_pos 463 CRC32 0x594574f2 Xid = 22

COMMIT/*!*/;

ERROR: Errorin Log_event::read_log_event(): 'read error', data_len: 150994944, event_type: -27

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /*added by mysqlbinlog*/ /*!*/;

DELIMITER ;

#End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

发现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亿年,估计它没挂,地球都挂了,它会陪你活到天荒地老……

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值