MySQL 빈로그란 무엇입니까? MySQL binlog 사용 및 형식 분석
- 零下一度원래의
- 2017-05-05 16:30:4323296검색
Binlog는 누구에게나 친숙할 것입니다. 마스터-슬레이브 복제 또는 경우에 따라 데이터 복구에 사용됩니다. binlog는 바이너리 데이터이므로 일반적으로 이를 보려면 mysqlbinlog 도구를 사용해야 합니다. 이 노트는 mysqlbinlog 도구 뒤에서 수행되는 작업을 이해하기 위해 binlog 형식을 분석합니다.
1. binlog를 작성하는 경우
binlog를 작성하는 경우를 설명하기 전에 binlog의 목적을 간략하게 소개하겠습니다. Binlog는 MySQL 데이터 업데이트 또는 잠재적 업데이트를 기록하는 데 사용되는 바이너리 로그 파일입니다(예: DELETE 문이 삭제를 실행하지만 실제로 조건을 충족하는 데이터가 없음). MySQL 마스터-슬레이브에서 의존하는 binlog입니다. 복제. mysql에서 binlog를 활성화하려면 my.cnf에 log_bin 매개변수를 설정해야 하며, binlog_do_db
및 binlog_ignore_db를 통해 binlog를 기록하도록 데이터베이스를 지정하여 binlog를 기록하지 않도록 데이터베이스를 지정할 수도 있습니다. mysql 실행을 위해 binlog를 활성화하려면 SET SQL_LOG_BIN=1 명령을 사용하여 설정할 수 있습니다. 설정이 완료되면 binlog를 테스트할 수 있습니다.
innodb 엔진의 redo/undo 로그와 mysql binlog는 완전히 다른 로그라는 점에 유의해야 합니다.
a) 다릅니다. 수준. redo/undo 로그는 innodb 계층에 의해 유지되는 반면, binlog는 mysql 서버 계층에 의해 유지됩니다. 기록되는 것은 모든 엔진의 업데이트 작업에 대한 로그 기록입니다. innodb의 다시 실행/실행 취소 로그에 대한 자세한 설명은 Jiang Chengyao의 저서 "MySQL Technology Insider - Innodb Storage Engine"의 관련 장을 참조하세요.
b) 녹음된 내용이 다릅니다. Redo/Undo 로그는 각 페이지의 수정 사항을 기록하며, 이는 물리적 로그 + 논리적 로그(Redo 로그는 페이지에 대한 물리적 로그, 논리적 로그는 페이지에서 사용되는 로그, Undo 로그는 논리적 로그)의 조합입니다. 목적은 데이터 보안 일관성을 보장하는 것입니다. Binlog는
DELETE FROM TABLE WHERE i > 1명령문 등과 같은 트랜잭션 작업 내용을 기록합니다. 어떤 엔진을 사용하더라도 형식은 당연히 바이너리입니다.mysqlbinlog -vv BINLOG명령을 사용하면 됩니다.c) 녹화 시점이 다릅니다. redo/undo 로그는 트랜잭션 실행 중에 계속 기록됩니다. binlog는 트랜잭션이 커밋된 후에만 로그에 기록됩니다. 이전 설명이 잘못되었습니다. 지적해 주셔서 감사합니다. 물론, binlog가 디스크에 플러시되는 것은
sync_binlog매개변수와 관련이 있습니다.
분명히 SELECT 및 데이터 업데이트와 관련되지 않은 기타 명령문을 실행할 때 binlog는 기록되지 않지만 데이터 업데이트와 관련된 명령문은 기록됩니다. innodb와 같은 트랜잭션을 지원하는 엔진의 경우 binlog가 기록되기 전에 트랜잭션을 제출해야 합니다.
binlog를 디스크로 플러시하는 타이밍은 sync_binlog 매개변수와 관련이 있습니다. 0으로 설정되면 MySQL이 binlog 새로 고침을 제어하지 않고 파일 시스템이 캐시 새로 고침을 제어한다는 의미입니다. 0 이외의 값으로 설정되면, 이는 모든 sync_binlog 트랜잭션마다 MySQL이 파일 시스템의 새로 고침 작업을 호출하여 binlog를 디스크에 새로 고치는 것을 의미합니다. 1로 설정하는 것이 가장 안전합니다. 시스템 장애가 발생하면 최대 하나의 트랜잭션 업데이트가 손실되지만 일반적으로 성능에 영향을 미치므로 특정 일관성이 희생됩니다. 더 나은 성능을 얻으십시오.
SHOW MASTER LOGS 명령을 통해 현재 binlog 개수를 확인할 수 있습니다. 예를 들어 다음은 내 컴퓨터의 mysql의 binlog 상황입니다. 첫 번째 열은 binlog 파일 이름이고 두 번째 열은 binlog 파일 크기입니다. expire_logs_days을 설정하여 binlog 보존 시간을 지정할 수 있습니다. binlog 이름을 지정하거나 보존 날짜를 지정할 수 있습니다. purge master logs to BINLOGNAME; 및 purge master logs before DATE;.
...... | mysql-bin.000018 | 515 | | mysql-bin.000019 | 504 | | mysql-bin.000020 | 107 | +------------------+-----------+
2 Binlog 형식 분석
2.1 binlog 파일 형식 소개
Binlog 형식은 명령문, 행 및 혼합의 세 가지 유형으로 구분됩니다. Mysql5.5는 기본적으로 명령문 모드를 사용합니다. 물론 마스터-슬레이브 동기화에서는 일반적으로 比如语句中包含UUID函数，以及LOAD DATA IN FILE语句 등과 같은 일부 명령문이 지원되지 않으므로 명령문 모드를 사용하지 않는 것이 좋습니다. 일반적으로 혼합 형식을 권장합니다. 당분간 이 세 가지 형식의 차이점에 관계없이 binlog의 저장 형식을 살펴보겠습니다. Binlog는 바이너리 파일의 모음입니다. 물론 mysql-bin.xxxxxx를 볼 수 있는 binlog 파일 외에도 mysql-bin.index라는 binlog 인덱스 파일도 있습니다. 공식 문서에 기록된 대로 binlog 형식은 다음과 같습니다.
binlog 파일은 0xfe 'b''i'에 해당하는 0Xfe62696e 값의 매직 넘버로 시작합니다. 'N'.
Binlog는 일련의 binlog 이벤트로 구성됩니다. 각 binlog 이벤트에는 헤더와 데이터라는 두 부분이 포함됩니다.
헤더 부분에서는 이벤트 생성 시간, 서버 등 이벤트의 공개 유형 정보를 제공합니다.
데이터 섹션에서는 특정 데이터 수정 등 이벤트에 대한 구체적인 정보를 제공합니다.
从mysql5.0版本开始，binlog采用的是v4版本，第一个event都是
format_desc event用于描述binlog文件的格式版本，这个格式就是event写入binlog文件的格式。关于之前版本的binlog格式，可以参见http://dev.mysql.com/doc/internals/en/binary-log-versions.html接下来的event就是按照上面的格式版本写入的event。
最后一个
rotate event用于说明下一个binlog文件。binlog索引文件是一个文本文件，其中内容为当前的binlog文件列表。比如下面就是一个mysql-bin.index文件的内容。
/var/log/mysql/mysql-bin.000019 /var/log/mysql/mysql-bin.000020 /var/log/mysql/mysql-bin.000021
接下来分析下几种常见的event，其他的event类型可以参见官方文档。event数据结构如下：
+=====================================+ | event | timestamp 0 : 4 | | header +----------------------------+ | | type_code 4 : 1 | | +----------------------------+ | | server_id 5 : 4 | | +----------------------------+ | | event_length 9 : 4 | | +----------------------------+ | | next_position 13 : 4 | | +----------------------------+ | | flags 17 : 2 | | +----------------------------+ | | extra_headers 19 : x-19 | +=====================================+ | event | fixed part x : y | | data +----------------------------+ | | variable part | +=====================================+
2.2 format_desc event
下面是我在FLUSH LOGS之后新建的一个全新的binlog文件mysql-bin.000053，从binlog第一个event也就是format_desc event开始分析（mysql日志是小端字节序）：
root@ubuntu:/var/log/mysql# hexdump -C mysql-bin.000053 00000000 fe 62 69 6e b8 b2 7f 56 0f 04 00 00 00 67 00 00 |.bin...V.....g..| 00000010 00 6b 00 00 00 01 00 04 00 35 2e 35 2e 34 36 2d |.k.......5.5.46-| 00000020 30 75 62 75 6e 74 75 30 2e 31 34 2e 30 34 2e 32 |0ubuntu0.14.04.2| 00000030 2d 6c 6f 67 00 00 00 00 00 00 00 00 00 00 00 00 |-log............| 00000040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 13 |................| 00000050 38 0d 00 08 00 12 00 04 04 04 04 12 00 00 54 00 |8.............T.| 00000060 04 1a 08 00 00 00 08 08 08 02 00 |...........|
对照官方文档中的说明来看下format_desc event格式：
+=====================================+ | event | timestamp 0 : 4 | | header +----------------------------+ | | type_code 4 : 1 | = FORMAT_DESCRIPTION_EVENT = 15 | +----------------------------+ | | server_id 5 : 4 | | +----------------------------+ | | event_length 9 : 4 | >= 91 | +----------------------------+ | | next_position 13 : 4 | | +----------------------------+ | | flags 17 : 2 | +=====================================+ | event | binlog_version 19 : 2 | = 4 | data +----------------------------+ | | server_version 21 : 50 | | +----------------------------+ | | create_timestamp 71 : 4 | | +----------------------------+ | | header_length 75 : 1 | | +----------------------------+ | | post-header 76 : n | = array of n bytes, one byte per event | | lengths for all | type that the server knows about | | event types | +=====================================+
前面4个字节是固定的magic number,值为0x6e6962fe。接着是一个format_desc event，先看下19个字节的header。这19个字节中前4个字节0x567fb2b8是时间戳，第5个字节0x0f是event type，接着4个字节0x00000004是server_id，再接着4个字节0x00000067是长度103，然后的4个字节0x0000006b是下一个event的起始位置107，接着的2个字节的0x0001是flag（1为LOG_EVENT_BINLOG_IN_USE_F，标识binlog还没有关闭，binlog关闭后，flag会被设置为0），这样4+1+4+4+4+2=19个字节的公共头就完了(extra_headers暂时没有用到)。然后是这个event的data部分，event的data分为Fixed data和Variable data两部分，其中Fixed data是event的固定长度和格式的数据，Variable data则是长度变化的数据，比如format_desc event的Fixed data长度是0x54=84个字节。下面看下这84=2+50+4+1+27个字节的分配：开始的2个字节0x0004为binlog的版本号4，接着的50个字节为mysql-server版本，如我的版本是5.5.46-0ubuntu0.14.04.2-log，与SELECT version();查看的结果一致。接下来4个字节是binlog创建时间，这里是0；然后的1个字节0x13是指之后所有event的公共头长度，这里都是19；接着的27个字节中每个字节为mysql已知的event（共27个）的Fixed data的长度；可以发现format_desc event自身的Variable data部分为空。
2.3 rotate event
接着我们不做额外操作，直接FLUSH LOGS，可以看到一个rotate event，此时的binlog内容如下:
...... 00000060 ................................. c2 b3 7f 56 04 |..............V.| 00000070 04 00 00 00 2b 00 00 00 96 00 00 00 00 00 04 00 |....+...........| 00000080 00 00 00 00 00 00 6d 79 73 71 6c 2d 62 69 6e 2e |......mysql-bin.| 00000090 30 30 30 30 35 34 |000054| 00000096
前面的内容跟之前的几乎一致，除了format_desc event的flag从0x0001变成了0x0000。然后从0x567fb3c2开始是一个rotate event。依照前面的分析，前面19个字节为event的header，其event type是0x04，长度为0x2b=43，下一个event起始位置为0x96=150，然后是flag为0x0000，接着是event data部分，首先的8个字节为Fixed data部分，记录的是下一个binlog的位置偏移4，而余下来的43-19-8=16个字节为Variable data部分，记录的是下一个binlog的文件名mysql-bin.000054。对照mysqlbinlog -vv mysql-bin.000053可以验证。
ssj@ubuntu:/var/log/mysql$ mysqlbinlog -vv mysql-bin.000053 ... # at 4 #151227 17:43:20 server id 4 end_log_pos 107 Start: binlog v 4, server v 5.5.46-0ubuntu0.14.04.2-log created 151227 17:43:20 BINLOG ' uLJ/Vg8EAAAAZwAAAGsAAAAAAAQANS41LjQ2LTB1YnVudHUwLjE0LjA0LjItbG9nAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA== '/*!*/; # at 107 #151227 17:47:46 server id 4 end_log_pos 150 Rotate to mysql-bin.000054 pos: 4 ...
2.4 query event
刷新binlog，设置binlog_format=statement，创建一个表CREATE TABLEtt(ivarchar(100) DEFAULT NULL) ENGINE=InnoDB, 然后在测试表tt中插入一条数据insert into tt values('abc')，会产生3个event，包括2个query event和1个xid event。其中2个query event分别是BEGIN以及INSERT 语句，而xid event则是事务提交语句（xid event是支持XA的存储引擎才有的，因为测试表tt是innodb引擎的，所以会有。如果是myisam引擎的表，也会有BEGIN和COMMIT,只不过COMMIT会是一个query event而不是xid event）。
mysql> show binlog events in 'mysql-bin.000060'; +------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+ | mysql-bin.000060 | 4 | Format_desc | 4 | 107 | Server ver: 5.5.46-0ubuntu0.14.04.2-log, Binlog ver: 4 | | mysql-bin.000060 | 107 | Query | 4 | 175 | BEGIN | | mysql-bin.000060 | 175 | Query | 4 | 266 | use `test`; insert into tt values('abc') | | mysql-bin.000060 | 266 | Xid | 4 | 293 | COMMIT /* xid=138 */ | +------------------+-----+-------------+-----------+-------------+--------------------------------------------------
binlog如下：
....... 0000006b 01 9d 82 56 02 04 00 00 00 44 00 00 00 af 00 00 |...V.....D......| 0000007b 00 08 00 26 00 00 00 00 00 00 00 04 00 00 1a 00 |...&............| 0000008b 00 00 00 00 00 01 00 00 00 00 00 00 00 00 06 03 |................| 0000009b 73 74 64 04 21 00 21 00 08 00 74 65 73 74 00 42 |std.!.!...test.B| 000000ab 45 47 49 4e |EGIN| 000000af 01 9d 82 56 02 04 00 00 00 5b 00 00 00 0a 01 00 |...V.....[......| 000000bf 00 00 00 26 00 00 00 00 00 00 00 04 00 00 1a 00 |...&............| 000000cf 00 00 00 00 00 01 00 00 00 00 00 00 00 00 06 03 |................| 000000df 73 74 64 04 21 00 21 00 08 00 74 65 73 74 00 69 |std.!.!...test.i| 000000ef 6e 73 65 72 74 20 69 6e 74 6f 20 74 74 20 76 61 |nsert into tt va| 000000ff 6c 75 65 73 28 27 61 62 63 27 29 |lues('abc')| 0000010a 01 9d 82 56 10 04 00 00 00 1b 00 00 00 25 01 00 |...V.........%..| 0000011a 00 00 00 8a 00 00 00 00 00 00 00 |...........|
抛开format_desc event，从0000006b开始分析第一个query event。头部跟之前的event一样，只是query event的type为0x02，长度为0x44=64，下一个event位置为0xaf=175。flag为8，接着是data部分，从format_desc event我们可以知道query event的Fixed data部分为13个字节，因此也可以算出Variable data部分为64-19-13=32字节。
Fixed data：首先的4个字节0x00000026为执行该语句的thread id，接下来的4个字节是执行的时间0(以秒为单位)，接下来的1个字节0x04是语句执行时的默认数据库名字的长度，我这里数据库是test，所以长度为4.接着的2个字节0x0000是错误码（注：通常情况下错误码是0表示没有错误，但是在一些非事务性表如myisam表执行
INSERT...SELECT语句时可能插入部分数据后遇到duplicate-key错误会产生错误码1062，或者是事务性表在INSERT...SELECT出错不会插入部分数据，但是在执行过程中CTRL+C终止语句也可能记录错误码。slave db在复制时会执行后检查错误码是否一致，如果不一致，则复制过程会中止）,接着2个字节0x001a为状态变量块的长度26。Variable data：从0x001a之后的26个字节为状态变量块（这个暂时先不管），然后是默认数据库名test，以0x00结尾，然后是sql语句BEGIN，接下来就是第2个query event的内容了。
第二个query event与第一个格式一样，只是执行语句变成了insert into tt values('abc')。
第三个xid event为COMMIT语句。前19个字节是通用头部，type是16。data部分中Fixed data为空，而variable data为8个字节，这8个字节0x000000008a是事务编号（注意事务编号不一定是小端字节序，因为是从内存中拷贝到磁盘的，所以这个字节序跟机器相关）。
2.5 table_map event & write_rows event
这两个event是在binlog_format=row的时候使用，设置binlog_format=row，然后创建一个测试表
CREATE TABLE `trow` ( `i` int(11) NOT NULL, `c` varchar(10) DEFAULT NULL, PRIMARY KEY (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1`
执行语句INSERT INTO trow VALUES(1, NULL), (2, 'a')，这个语句会产生一个query event，一个table_map event、一个write_rows event以及一个xid event。
mysql> show binlog events in 'mysql-bin.000074'; | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+ | mysql-bin.000074 | 4 | Format_desc | 4 | 107 | Server ver: 5.5.46-0ubuntu0.14.04.2-log, Binlog ver: 4 | | mysql-bin.000074 | 107 | Query | 4 | 175 | BEGIN | | mysql-bin.000074 | 175 | Table_map | 4 | 221 | table_id: 50 (test.trow) | | mysql-bin.000074 | 221 | Write_rows | 4 | 262 | table_id: 50 flags: STMT_END_F | | mysql-bin.000074 | 262 | Xid | 4 | 289 | COMMIT /* xid=245 */
对应的mysql-bin.000074数据如下：
... #query event (BEGIN) 0000006b 95 2a 85 56 02 04 00 00 00 44 00 00 00 af 00 00 |.*.V.....D......| 0000007b 00 08 00 26 00 00 00 00 00 00 00 04 00 00 1a 00 |...&............| 0000008b 00 00 00 00 00 01 00 00 00 00 00 00 00 00 06 03 |................| 0000009b 73 74 64 04 21 00 21 00 08 00 74 65 73 74 00 42 |std.!.!...test.B| 000000ab 45 47 49 4e |EGIN| #table_map event 000000af 95 2a 85 56 13 04 00 00 00 2e 00 00 00 dd 00 00 |.*.V............| 000000bf 00 00 00 32 00 00 00 00 00 01 00 04 74 65 73 74 |...2........test| 000000cf 00 04 74 72 6f 77 00 02 03 0f 02 0a 00 02 |..trow........| #write_rows event 000000dd 95 2a 85 56 17 04 00 00 00 29 00 00 00 06 01 00 |.*.V.....)......| 000000ed 00 00 00 32 00 00 00 00 00 01 00 02 ff fe 01 00 |...2............| 000000fd 00 00 fc 02 00 00 00 01 61 |........a| #xid event 00000106 95 2a 85 56 10 04 00 00 00 1b 00 00 00 21 01 00 |.*.V.........!..| 00000116 00 00 00 f5 00 00 00 00 00 00 00 |...........|
0x0000006b-0x000000ae为query event，语句是BEGIN,前面已经分析过。
table_map event
0x0000000af开始为table_map event。除去头部19个字节，Fixed data为8个字节，前面6个字节0x32=50为table id，接着2个字节0x0001为flags。
Variable data部分，首先1个字节0x04为数据库名test的长度，然后5个字节是数据库名test+结束符。接着1个字节0x04为表名长度，接着5个字节为表名trow+结束符。接着1个字节0x02为列的数目。而后是2个列的类型定义，分别是0x03和0x0f（列的类型MYSQL_TYPE_LONG为0x03，MYSQL_TYPE_VARCHAR为0x0f)。接着是列的元数据定义，首先0x02表示元数据长度为2，因为MYSQL_TYPE_LONG没有元数据，而MYSQL_TYPE_VARCHAR元数据长度为2。接着的0x000a就是MYSQL_TYPE_VARCHAR的元数据，表示我们在定义表时的varchar字段c长度为10，最后一个字节0x02为掩码，表示第一个字段i不能为NULL。关于列的类型以及元数据等更详细的信息可以参见http://dev.mysql.com/doc/internals/en/table-map-event.html。
write_rows event
从0x000000dd开始为write_rows event，除去头部19个字节，前6个字节0x32也是table id，然后两个字节0x0001为flags。接着的1个字节0x02为表中列的数目。然后1个字节0xff各个bit标识各列是否存在值，这里表示都存在。
接着的就是插入的各行数据了。第1个字节0xfe的各个bit标识该行变化之后各列是否为NULL，为NULL记为1.这里表示第1列不为NULL，因为第一行数据插入的是(1,NULL)。接下来是各列的数据，第一列是MYSQL_TYPE_LONG,长度为4个字节，所以0x00000001就是这个值。第二列是NULL不占字节。接下来是第二行，先是0xfc标识两列都不为NULL，先读取第一列的4个字节0x00000002也就是我们插入的数字2，然后读取第二列，先是一个字节的长度0x01，然后是内容0x61也就是字符'a'。到此，write_rows event也就分析完了。rows相关的event还有update_rows event和delete_rows event等，欲了解更多可以参见官方文档。
最后是xid event，之前已经分析过，不再赘述。
2.6 intvar event
intvar event在binlog_format=statement时使用到，用于自增键类型auto_increment，十分重要。intval event的Fixed data部分为空，而Variable data部分为9个字节，第1个字节用于标识自增事件类型 LAST_INSERT_ID_EVENT = 1 or INSERT_ID_EVENT = 2，余下的8个字节为自增ID。
创建一个测试表 create table tinc (i int auto_increment primary key, c varchar(10)) engine=innodb;，然后执行一个插入语句INSERT INTO tinc(c) values('abc');就可以看到intvar event了，这里的自增事件类型为INSERT_ID_EVENT。而如果用语句INSERT INTO tinc(i, c) VALUES(LAST_INSERT_ID()+1, 'abc')，则可以看到自增事件类型为LAST_INSERT_ID_EVENT的intvar event。
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+------------- | mysql-bin.000079 | 4 | Format_desc | 4 | 107 | Server ver: 5.5.46-0ubuntu0.14.04.2-log, Binlog ver: 4 | | mysql-bin.000079 | 107 | Query | 4 | 175 | BEGIN | | mysql-bin.000079 | 175 | Intvar | 4 | 203 | INSERT_ID=1 | | mysql-bin.000079 | 203 | Query | 4 | 299 | use `test`; insert into tinc(c) values('abc') | | mysql-bin.000079 | 299 | Xid | 4 | 326 | COMMIT /* xid=263 */
3 简单总结
上面提到，binlog有三种格式，各有优缺点：
statement：基于SQL语句的模式，某些语句和函数如UUID, LOAD DATA INFILE等在复制过程可能导致数据不一致甚至出错。
row：基于行的模式，记录的是行的变化，很安全。但是binlog会比其他两种模式大很多，在一些大表中清除大量数据时在binlog中会生成很多条语句，可能导致从库延迟变大。
mixed：混合模式，根据语句来选用是statement还是row模式。
不同版本的mysql在主从复制要慎重，虽然mysql5.0之后都用的V4版本的binlog了，估计还是会有些坑在里面，特别是高版本为主库，低版本为从库时容易出问题。在主从复制时最好还是主库从库版本一致，至少是大版本一致。mysql复制是个大的话题，希望有时间能单独总结一篇笔记。
【相关推荐】
2. MySQL最新手册教程
위 내용은 MySQL 빈로그란 무엇입니까? MySQL binlog 사용 및 형식 분석의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!