mysql binlog文件格式详解

binlog格式分为statement,row以及mixed三种,mysql5.5默认的还是statement模式,当然我们在主从同步中一般是不建议用statement模式的,因为会有些语句不支持,比如语句中包含UUID函数,以及LOAD DATA IN FILE语句等,一般推荐的是mixed格式。暂且不管这三种格式的区别,看看binlog的存储格式是什么样的。binlog是一个二进制文件集合,当然除了我们看到的mysql-bin.xxxxxx这些binlog文件外,还有个binlog索引文件mysql-bin.index。如官方文档中所写,binlog格式如下:

  • binlog文件以一个值为0Xfe62696e的魔数开头,这个魔数对应0xfe 'b''i''n'。
  • binlog由一系列的binlog event构成。每个binlog event包含header和data两部分。
    • header部分提供的是event的公共的类型信息,包括event的创建时间,服务器等等。
    • data部分提供的是针对该event的具体信息,如具体数据的修改。
  • 从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文件的内容。

来源:https://www.jianshu.com/p/c16686b35807

mysql binlog 查找

08-25

接着昨天的mysql delete还原,我现在大致的思路是,从还原一个最近备份的表,然后再从日志文件里面找,可是如果日志文件非常大的情况下找起来非常的麻烦,我想能不能通过linux sed 或者grep 找出和表相关的操作, grep 'xxx' 1.sql 值能列出和xxx相关的操作,但是列不出时间等等。下面是日志文件的部分,我现在想得到和xxx相关的东西包括时间等,例如:其他的不想要,这个正则表达式怎么写??rnBEGINrn/*!*/;rn# at 170rn#100824 11:26:27 server id 1 end_log_pos 277 Query thread_id=1 exec_time=0 error_code=0rnuse test_dbo/*!*/;rnSET TIMESTAMP=1282620387/*!*/;rnupdate xxx set ChineseName='abc'rn/*!*/;rn# at 277rn#100824 11:26:27 server id 1 end_log_pos 304 Xid = 38rnCOMMIT/*!*/;rn# at 304rn#100824 11:26:27 server id 1 end_log_pos 376 Query thread_id=3 exec_time=658 error_code=0rnSET TIMESTAMP=1282620387/*!*/;rnBEGINrn/*!*/;rn# at 376rn#100824 11:26:27 server id 1 end_log_pos 483 Query thread_id=3 exec_time=658 error_code=0rnSET TIMESTAMP=1282620387/*!*/;rnupdate xxx set ChineseName='abc'rn/*!*/;rn# at 483rn#100824 11:26:27 server id 1 end_log_pos 510 Xid = 54rnCOMMIT/*!*/;rnrn所有的日志文件rnBEGINrn/*!*/;rn# at 170rn#100824 11:26:27 server id 1 end_log_pos 277 Query thread_id=1 exec_time=0 error_code=0rnuse test_dbo/*!*/;rnSET TIMESTAMP=1282620387/*!*/;rnupdate xxx set ChineseName='abc'rn/*!*/;rn# at 277rn#100824 11:26:27 server id 1 end_log_pos 304 Xid = 38rnCOMMIT/*!*/;rn# at 304rn#100824 11:26:27 server id 1 end_log_pos 376 Query thread_id=3 exec_time=658 error_code=0rnSET TIMESTAMP=1282620387/*!*/;rnBEGINrn/*!*/;rn# at 376rn#100824 11:26:27 server id 1 end_log_pos 483 Query thread_id=3 exec_time=658 error_code=0rnSET TIMESTAMP=1282620387/*!*/;rnupdate xxx set ChineseName='abc'rn/*!*/;rn# at 483rn#100824 11:26:27 server id 1 end_log_pos 510 Xid = 54rnCOMMIT/*!*/;rn# at 510rn#100824 11:40:13 server id 1 end_log_pos 582 Query thread_id=1 exec_time=0 error_code=0rnSET TIMESTAMP=1282621213/*!*/;rnBEGINrn/*!*/;rn# at 2353rn#100825 9:13:49 server id 1 end_log_pos 2443 Query thread_id=8 exec_time=0 error_code=0rnSET TIMESTAMP=1282698829/*!*/;rndelete from dddrn/*!*/;rn# at 2443rn#100825 9:13:49 server id 1 end_log_pos 2470 Xid = 202rnCOMMIT/*!*/;rn# at 2470rn#100825 9:13:53 server id 1 end_log_pos 2542 Query thread_id=8 exec_time=0 error_code=0rnSET TIMESTAMP=1282698833/*!*/;rnBEGINrn/*!*/;rn# at 2542rn#100825 9:13:53 server id 1 end_log_pos 2639 Query thread_id=8 exec_time=0 error_code=0rnSET TIMESTAMP=1282698833/*!*/;rndelete from zzzrn/*!*/;rn# at 2639rn#100825 9:13:53 server id 1 end_log_pos 2666 Xid = 206rnCOMMIT/*!*/;rn# at 2666rn#100825 9:13:54 server id 1 end_log_pos 2738 Query thread_id=8 exec_time=0 error_code=0rnSET TIMESTAMP=1282698834/*!*/;rnBEGINrn/*!*/;rn# at 2738rn#100825 9:13:54 server id 1 end_log_pos 2835 Query thread_id=8 exec_time=0 error_code=0rnSET TIMESTAMP=1282698834/*!*/;rndelete from zzzrn/*!*/;rn# at 2835rn#100825 9:13:54 server id 1 end_log_pos 2862 Xid = 207rnCOMMIT/*!*/;rn# at 2862rn#100825 9:15:34 server id 1 end_log_pos 2954 Query thread_id=8 exec_time=1 error_code=0rnSET TIMESTAMP=1282698934/*!*/;rnDROP TABLE `qqq`rn/*!*/;rnDELIMITER ;rnrnrnrn

mysql binlog解析

07-02

1.binlog是什么?rnmysql服务器可分为主服务器和从服务器。主服务器维护一个更新的记录,叫做binlog,即二进制日志。二进制日志记录主库发生的更新事件。二进制日志默认存放在/var/log/mysql中,可以通过my.cnf中log_bin进行修改。名字是mysql-bin.xxxxxx,从1开始;同时还存在mysql-bin.index文件,里面保存了mysql现在存在的binlog日志。rn从服务器获取主库的二进制日志之后就可解析并对从库的数据做同样的更新。从服务器有两个线程,IO线程、SQL线程。I/O线程下载主库的二进制日志,并保存在本地,为临时的relay日志(中继日志),SQL线程获取中继日志的数据进行从库更新。在主从复制的过程中从库需要指定binlog文件名以及所要读取的位置在文件中的偏移量。主库在把当前的binlog日志发送完之后,会自动发送后继日志。rnbinlog记录更新事件的方式有三种:rn一:statement(基于语句的复制),保存的是高层的sql语句,优点是传输的数据比较少,缺点是很难做到主从一致,譬如rand()会在不同的地方产生不同的值rn二:row(基于行的复制),保存的是记录变化前和变化后的数据,优点是不容易出错,可以做到原样复制,缺点是传输的数据可能会很多,譬如某个delete语句删除一个表里几百万行,基于statement的方式只会产生一个event,而基于row的方式会产生几百万条rn三:mixed(混合方式),不会在从库产生歧义的语句只记录sql语句,会产生歧义的语句使用row方式,兼顾前两者的优点rn选择哪种可以在my.cnf里的binlog_formate进行修改rnrn2.如何获取主库的binlog文件rn在同主库建立链接之后,可以通过cli_advanced_command发送COM_BINLOG_DUMP命令到主库来获取二进制日志。该命令的格式可见http://dev.mysql.com/doc/internals/en/com-binlog-dump.html,该条协议里面需要指定二进制日志名以及偏移地址,有一点需要注意,还需要发送从库的server_id,该id不能与主库现有的从库server_id重复,不然会被拒绝,而且也不能发0,不然当主库发送完所有的日志之后就会不再发送,可能与从库的更新逻辑不符,如果想让主库有新的更新再发送新的数据过来的话,该值不能为0。rnrn3.binlog文件解析rnbinlog文件可以看成是各种event的集合,event就是事件,当然一次更新可能会产生多个event,event有很多种,常见的有QUERY_EVENT(记录一条query语句,在基于语句的复制和基于行的复制都会有,譬如begin事件)、ROTATE_EVENT(二进制日志更换一个新文件,可能是因为文件大小达到限制,或者是mysql服务器重启了)、XID_EVENT(commit事件)、WRITE_ROWS_EVENT,UPDATE_ROWS_EVENT,DELETE_ROWS_EVENT(统称为row event,只有在基于行的复制方式下才会产生)、TABLE_MAP_EVENT(row event之前产生,为的是对row event解析提供依据)。rn每个event都有一个19字节的Binlog Event header,包括四个字节的timestamp,一个字节的Binlog Event Type,4个字节的server_id(该id表面binlog的源server是哪个,用来在循环复制中过滤event),四个字节的event包大小,四个字节的下一个event起始偏移,两个字节的Binlog Event Flagrn除了19个字节的包头,还可能有提交头(但也可能没有),之后就是payloadrnrn可以用show binlog events 来查看日志中的event:rn+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+rn| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |rn+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+rn| mysql-bin.000001 | 4 | Format_desc | 2 | 107 | Server ver: 5.5.37-0ubuntu0.12.04.1-log, Binlog ver: 4 |rn| mysql-bin.000001 | 107 | Rotate | 2 | 150 | mysql-bin.000009;pos=4 |rn+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+rn上面是两个eventsrnrnmysql自带mysqlbinlog来解析binlog文件,譬如mysqlbinlog mysql-bin.000001,可以看到各个event的详细信息,摘取某个event做下示范:rn# at 4rn#140618 11:53:06 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.37-0ubuntu0.12.04.1-log created 140618 11:53:06 at startuprn# Warning: this binlog is either in use or was not closed properly.rnROLLBACK/*!*/;rnBINLOG 'rnIg2hUw8BAAAAZwAAAGsAAAABAAQANS41LjM3LTB1YnVudHUwLjEyLjA0LjEtbG9nAAAAAAAAAAAArnAAAAAAAAAAAAAAAAAAAiDaFTEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==rn'/*!*/;rn这是一个format description event,第一行表明在日志中的偏移,第二行的前半部分是公共头,之后的就是payload提取出来的信息rnrn也可以用hexdump -C mysql-bin.000001来查看字节流,注意mysql日志是小端字节序的:rn00000000 fe 62 69 6e 22 0d a1 53 0f 01 00 00 00 67 00 00 |.bin"..S.....g..|rn00000010 00 6b 00 00 00 01 00 04 00 35 2e 35 2e 33 37 2d |.k.......5.5.37-|rn00000020 30 75 62 75 6e 74 75 30 2e 31 32 2e 30 34 2e 31 |0ubuntu0.12.04.1|rn00000030 2d 6c 6f 67 00 00 00 00 00 00 00 00 00 00 00 00 |-log............|rn00000040 00 00 00 00 00 00 00 00 00 00 00 22 0d a1 53 13 |..........."..S.|rn00000050 38 0d 00 08 00 12 00 04 04 04 04 12 00 00 54 00 |8.............T.|rn00000060 04 1a 08 00 00 00 08 08 08 02 00 53 0d a1 53 02 |...........S..S.|rn每个binlog日志开头会有四个字节0x6e 0x69 0x62 0xfe, 之后会是一个FORMAT_DESCRIPTION_EVENT,先看前19个字节的公共头,0x53a10d22是时间戳1403063586,0x0f是type,之后是0x00000001是server_id,0x00000067是长度,103,0x0000006b,下一个event的起始位置是107,0x0001是flag,该event没有提交头,之后是payload,2个字节0x0004说明binlog版本号是4,之后是50个字节的mysql-server version,之后是4个字节0x53a10d22表明binlog的建立时间,然后是0x13表明之后所有event的公共头长度,肯定是19,最后是一个以\0结尾的字符串,该字符串每个字节表明相应的event的提交头的长度,譬如第一个字节是START_EVENT_V3,它的包长度是0x38。rnrn前面简略的说了下binlog event的情况以及FORMAT_DESCRIPTION_EVENT的详细解析,现在重点讲一下几个比较重要的event。rnrnROTATE_EVENTrn当文件超过限制的时候或者重启的时候会产生该rotate event,说明需要使用新的日志文件了rnpost-headerrn8个字节的偏移量rnpay_loadrn下一个日志文件的名称rnrnXID_EVENTrn当commit的时候产生该eventrnpayloadrn8个字节的事务idrnrnQUERY_EVENTrn当begin的时候会产生 内容为"BEGIN"的query eventrn当使用statement复制的时候,所有插入、更新、删除操作都会产生一个query event,里面的内容就是原始query语句rnpost-headerrn4字节的线程idrn4个字节的执行时间rn1个字节的数据库长度rn2个字节的错误码rn2个字节的状态变量长度rnpayloadrn状态变量(字符串,长度由前面字段决定)//我也不清楚有什么用rn库名称rn1个字节的空白rnsql语句rnrnTABLE_MAP_EVENTrn基于row复制的情况下产生的event,之后必定伴随一个row event,为row event的格式解析提供依据rnpost-headerrn6字节的表idrn2字节的flagsrnpayloadrn1个字节的库长度rn库名rn1个字节的空白rn1个字节的表长度rn表名rn1个字节的空白rn不定字节的列数量rn列类型字符串,长度由上一个字段决定,表明每个列的类型 Protocol::ColumnType, 例如MYSQL_TYPE_TINYrn不定字节的metadatarn(column-count + 8) / 7个字节长度的掩码,每一位表明相应的列是否可为NULL,可以则是1,不可则为0,譬如00000001,表明第一列可为NULL,其余列(如果有的话)不可为NULLrnrn现在详细说下metadata,每一列在里面占据的字节数可参考http://dev.mysql.com/doc/internals/en/table-map-event.htmlrn举例:rncreate table test1(id int primary key, firstname char(40), middlename varchar(40),lastname text);rninsert into test1(1,"bo","hu","tang");rnshow binlog events in 'mysql-bin.000015' from 349;rn+------------------+-----+------------+-----------+-------------+--------------------------------+rn| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |rn+------------------+-----+------------+-----------+-------------+--------------------------------+rn| mysql-bin.000015 | 349 | Query | 1 | 417 | BEGIN |rn| mysql-bin.000015 | 417 | Table_map | 1 | 469 | table_id: 41 (test.test1) |rn| mysql-bin.000015 | 469 | Write_rows | 1 | 515 | table_id: 41 flags: STMT_END_F |rn| mysql-bin.000015 | 515 | Xid | 1 | 542 | COMMIT /* xid=119 */ |rn+------------------+-----+------------+-----------+-------------+--------------------------------+rn我们从469看起hexdump -C mysql-bin.000015 -s 417 -n 52rn000001a1 20 c0 b3 53 13 01 00 00 00 34 00 00 00 d5 01 00 | ..S.....4......|rn000001b1 00 00 00 29 00 00 00 00 00 01 00 04 74 65 73 74 |...)........test|rn000001c1 00 05 74 65 73 74 31 00 04 03 fe 0f fc 05 fe 28 |..test1........(|rn000001d1 28 00 02 0e |(...|rn前面19个字节是公共头,略去不看,0x000000000029,是表id41,0x0001是flags,0x04是库长度,0x74657374是库名test,之后是一个字节的空白,之后0x05表明表长度,0x7465737431是表名test1,之后又是一个空白字节,0x04是列数量4,0x03fe0ffc是各列的类型,0x03是 MYSQL_TYPE_LONG,0xfe是 MYSQL_TYPE_STRING,0x0f 是MYSQL_TYPE_VARCHAR 0xfc是 MYSQL_TYPE_BLOB,之后的字节0x05是metadata的开头,表明总共有5个字节是metadata,MYSQL_TYPE_LONG不占字节, MYSQL_TYPE_STRING占两个字节0xfe28,MYSQL_TYPE_VARCHAR占两个字节0x2800,MYSQL_TYPE_BLOB占一个字节0x02,最后一个字节是掩码,0x0e表明只有第一列不能为空,id这一列是主键,确实不能为空。rn一般来说像字符串类型的非定长列需要通过额外的metadata来分享row event里的数据,int,timestamp的长度孤单,所以是不需要的。rn对于这张表而言,MYSQL_TYPE_STRING的meta是0xfe28, 0xfe看上去是Protocol::ColumnType,放在这里应该是重复了,0x28就是40,建表时候该列的长度,但事实上这个长度也没用,MYSQL_TYPE_STRING的话在row event读取该列的数据的时候肯定先读取一个字节的数据长度,再去读取列数据;MYSQL_TYPE_VARCHAR的meta是0x28 0x00,大小是40,就是该列建表时的长度,40小于256,所以真正读取row event的时候只需要先读一个字节的长度,再读真实数据,如果大于256,那么就需要先读两个字节的长度,再读取数据;MYSQL_TYPE_BLOB的meta是0x02,那么读row event的时候先读取两个字节的长度,再读取真实数据。rnrnrnWRITE_ROWS_EVENTv0rnUPDATE_ROWS_EVENTv0rnDELETE_ROWS_EVENTv0rnWRITE_ROWS_EVENTv1rnUPDATE_ROWS_EVENTv1rnDELETE_ROWS_EVENTv1rnrn这些都是row event 当主库变动的时候,如果复制方式是row,那么就会产生这些eventrnpayloadrn一:headerrn6个字节的表idrn2个字节的flagsrn二:bodyrn不定长度的列数量rn记录变化之前的掩码(如果之前存在该条记录),表明各列是不是存在的,长度为(num of columns+7)/8 暂记为p_birn记录变化之后的掩码(如果之后存在该条记录),表明各列是不是存在的,长度为(num of columns+7)/8 暂记为p_airn三:rows 可能有多条rnnull map 记录变化之前各列是不是为null,为null的话该列记为1,map的长度为(p_bi中相对应的列1的数量+7)/8rn变化前的记录 可以通过TABLE_MAP_EVENT中的信息进行解析rnnull map 记录变化之后各列是不是为null,为null的话该列记为1,map的长度为(p_ai中相对应的列1的数量+7)/8rn变化后的记录 可以通过TABLE_MAP_EVENT中的信息进行解析rnrn现在分析上面例子:rnhexdump -C mysql-bin.000015 -s 469rn000001d5 20 c0 b3 53 17 01 00 00 00 2e 00 00 00 03 02 00 | ..S............|rn000001e5 00 00 00 29 00 00 00 00 00 01 00 04 ff f0 01 00 |...)............|rn000001f5 00 00 02 62 6f 02 68 75 04 00 74 61 6e 67 20 c0 |...bo.hu..tang .|rn00000205 b3 53 10 01 00 00 00 1b 00 00 00 1e 02 00 00 00 |.S..............|rn00000215 00 77 00 00 00 00 00 00 00 |.w.......|rn前面19个字节是公共头,忽略,0x000000000029是表id,0x0001是flags,04是列数量,0xff是p_ai,四列都是1,都存在,0xf0表明前四列不为null,都需要进行解析,后面就是各列数据:第第一列是MYSQL_TYPE_LONG,四个字节,读取0x00000001,就是1rn第二列是MYSQL_TYPE_STRING,读取一个字节的长度,0x02, 再读取0x02个字节,0x626f,就是“bo”rn第三列是MYSQL_TYPE_VARCHAR,读取一个字节的长度,0x02,再读取0x02个字节,0x6875,就是“hu”rn第三列是MYSQL_TYPE_BLOB,读取两个字节的长度,0x0004,再读取0x04个字节,0x74616e67,就是“tang”rn分析结束rnrnINTVAR_EVENTrnpayloadrn1个字节的typern8个字节的valuern当type为 0x02时,说明该event是 INSERT_ID_EVENTrn在用statement作复制的时候,该event很重要,在自增主键的情况下,需要在每次插入记录的时候,设置一下INSERT_ID(set session insert_id=xxx),这样才能保证主从库的主键一致。有一种情况,主库有个自增主键id,有个unique key是name,当插入一条记录导致name重复的话,id还是加了1,这样的话,主库就会有一个缺失的主键,但是binlog在从库执行的时候,肯定会成功,不会出现这样的缺失主键情况,导致主从主键不一致。rnrnrnPS:在进行statement的主从复制的时候,如果语句里有now()的话,主从数据可能不一致,一种解决方法是在每次在从库执行query的之前,set session timestamp=xxx,这样主从的时间就一致了rnRand()的情况怎么解决还不清楚。 rnrnrnrnrnrnrnrnrnrnrnrnrnrn

没有更多推荐了,返回首页

私密
私密原因:
请选择设置私密原因
  • 广告
  • 抄袭
  • 版权
  • 政治
  • 色情
  • 无意义
  • 其他
其他原因:
120
出错啦
系统繁忙,请稍后再试