mysql binlog解析

sele

　　1.binlog是什么？
　　mysql服务器可分为主服务器和从服务器。主服务器维护一个更新的记录，叫做binlog，即二进制日志。二进制日志记录主库发生的更新事件。二进制日志默认存放在/var/log/mysql中，可以通过my.cnf中log_bin进行修改。名字是mysql-bin.xxxxxx，从1开始；同时还存在mysql-bin.index文件，里面保存了mysql现在存在的binlog日志。
　　从服务器获取主库的二进制日志之后就可解析并对从库的数据做同样的更新。从服务器有两个线程，IO线程、SQL线程。I/O线程下载主库的二进制日志，并保存在本地，为临时的relay日志（中继日志），SQL线程获取中继日志的数据进行从库更新。在主从复制的过程中从库需要指定binlog文件名以及所要读取的位置在文件中的偏移量。主库在把当前的binlog日志发送完之后，会自动发送后继日志。
　　binlog记录更新事件的方式有三种：
　　一：statement（基于语句的复制），保存的是高层的sql语句，优点是传输的数据比较少，缺点是很难做到主从一致，譬如rand()会在不同的地方产生不同的值
　　二：row（基于行的复制），保存的是记录变化前和变化后的数据，优点是不容易出错，可以做到原样复制，缺点是传输的数据可能会很多，譬如某个delete语句删除一个表里几百万行，基于statement的方式只会产生一个event，而基于row的方式会产生几百万条
　　三：mixed（混合方式），不会在从库产生歧义的语句只记录sql语句，会产生歧义的语句使用row方式，兼顾前两者的优点
　　选择哪种可以在my.cnf里的binlog_formate进行修改
　　2.如何获取主库的binlog文件
　　在同主库建立链接之后，可以通过cli_advanced_command发送COM_BINLOG_DUMP命令到主库来获取二进制日志。该命令的格式可见http://dev.mysql.com/doc/internals/en/com-binlog-dump.html，该条协议里面需要指定二进制日志名以及偏移地址，有一点需要注意，还需要发送从库的server_id，该id不能与主库现有的从库server_id重复，不然会被拒绝，而且也不能发0，不然当主库发送完所有的日志之后就会不再发送，可能与从库的更新逻辑不符，如果想让主库有新的更新再发送新的数据过来的话，该值不能为0。
　　3.binlog文件解析
　　binlog文件可以看成是各种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解析提供依据）。
　　每个event都有一个19字节的Binlog Event header，包括四个字节的timestamp，一个字节的Binlog Event Type，4个字节的server_id（该id表面binlog的源server是哪个，用来在循环复制中过滤event）,四个字节的event包大小，四个字节的下一个event起始偏移，两个字节的Binlog Event Flag
　　除了19个字节的包头，还可能有提交头（但也可能没有），之后就是payload
　　可以用show binlog events 来查看日志中的event：
　　+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
　　| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                   |
　　+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
　　| mysql-bin.000001 |   4 | Format_desc |         2 |         107 | Server ver: 5.5.37-0ubuntu0.12.04.1-log, Binlog ver: 4 |
　　| mysql-bin.000001 | 107 | Rotate      |         2 |         150 | mysql-bin.000009;pos=4                                 |
　　+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
　　上面是两个events
　　mysql自带mysqlbinlog来解析binlog文件，譬如mysqlbinlog mysql-bin.000001，可以看到各个event的详细信息，摘取某个event做下示范：
　　# at 4
　　#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 startup
　　# Warning: this binlog is either in use or was not closed properly.
　　ROLLBACK/*!*/;
　　BINLOG '
　　Ig2hUw8BAAAAZwAAAGsAAAABAAQANS41LjM3LTB1YnVudHUwLjEyLjA0LjEtbG9nAAAAAAAAAAAA
　　AAAAAAAAAAAAAAAAAAAiDaFTEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
　　'/*!*/;
　　这是一个format description event，第一行表明在日志中的偏移，第二行的前半部分是公共头，之后的就是payload提取出来的信息
　　也可以用hexdump -C mysql-bin.000001来查看字节流，注意mysql日志是小端字节序的：
　　00000000  fe 62 69 6e 22 0d a1 53  0f 01 00 00 00 67 00 00  |.bin"..S.....g..|
　　00000010  00 6b 00 00 00 01 00 04  00 35 2e 35 2e 33 37 2d  |.k.......5.5.37-|
　　00000020  30 75 62 75 6e 74 75 30  2e 31 32 2e 30 34 2e 31  |0ubuntu0.12.04.1|
　　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 22 0d a1 53 13  |..........."..S.|
　　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 53 0d a1 53 02  |...........S..S.|
　　每个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。
　　前面简略的说了下binlog event的情况以及FORMAT_DESCRIPTION_EVENT的详细解析，现在重点讲一下几个比较重要的event。
　　ROTATE_EVENT
　　当文件超过限制的时候或者重启的时候会产生该rotate event，说明需要使用新的日志文件了
　　post-header
　　8个字节的偏移量
　　pay_load
　　下一个日志文件的名称
　　XID_EVENT
　　当commit的时候产生该event
　　payload
　　8个字节的事务id
　　QUERY_EVENT
　　当begin的时候会产生 内容为"BEGIN"的query event
　　当使用statement复制的时候，所有插入、更新、删除操作都会产生一个query event，里面的内容就是原始query语句
　　post-header
　　4字节的线程id
　　4个字节的执行时间
　　1个字节的数据库长度
　　2个字节的错误码
　　2个字节的状态变量长度
　　payload
　　状态变量（字符串，长度由前面字段决定）//我也不清楚有什么用
　　库名称
　　1个字节的空白
　　sql语句
　　TABLE_MAP_EVENT
　　基于row复制的情况下产生的event，之后必定伴随一个row event，为row event的格式解析提供依据
　　post-header
　　6字节的表id
　　2字节的flags
　　payload
　　1个字节的库长度
　　库名
　　1个字节的空白
　　1个字节的表长度
　　表名
　　1个字节的空白
　　不定字节的列数量
　　列类型字符串，长度由上一个字段决定，表明每个列的类型 Protocol::ColumnType， 例如MYSQL_TYPE_TINY
　　不定字节的metadata
　　(column-count + 8) / 7个字节长度的掩码，每一位表明相应的列是否可为NULL，可以则是1，不可则为0，譬如00000001，表明第一列可为NULL，其余列（如果有的话）不可为NULL
　　现在详细说下metadata，每一列在里面占据的字节数可参考http://dev.mysql.com/doc/internals/en/table-map-event.html
　　举例：
　　create table test1(id int primary key, firstname char(40), middlename varchar(40),lastname text);
　　insert into test1(1,"bo","hu","tang");
　　show  binlog events in 'mysql-bin.000015' from 349;
　　+------------------+-----+------------+-----------+-------------+--------------------------------+
　　| Log_name         | Pos | Event_type | Server_id | End_log_pos | Info                           |
　　+------------------+-----+------------+-----------+-------------+--------------------------------+
　　| mysql-bin.000015 | 349 | Query      |         1 |         417 | BEGIN                          |
　　| mysql-bin.000015 | 417 | Table_map  |         1 |         469 | table_id: 41 (test.test1)      |
　　| mysql-bin.000015 | 469 | Write_rows |         1 |         515 | table_id: 41 flags: STMT_END_F |
　　| mysql-bin.000015 | 515 | Xid        |         1 |         542 | COMMIT /* xid=119 */           |
　　+------------------+-----+------------+-----------+-------------+--------------------------------+
　　我们从469看起hexdump -C mysql-bin.000015 -s 417 -n 52
　　000001a1  20 c0 b3 53 13 01 00 00  00 34 00 00 00 d5 01 00  | ..S.....4......|
　　000001b1  00 00 00 29 00 00 00 00  00 01 00 04 74 65 73 74  |...)........test|
　　000001c1  00 05 74 65 73 74 31 00  04 03 fe 0f fc 05 fe 28     |..test1........(|
　　000001d1  28 00 02 0e                                                          |(...|
　　前面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这一列是主键，确实不能为空。
　　一般来说像字符串类型的非定长列需要通过额外的metadata来分享row event里的数据，int，timestamp的长度孤单，所以是不需要的。
　　对于这张表而言，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的时候先读取两个字节的长度，再读取真实数据。
　　WRITE_ROWS_EVENTv0
　　UPDATE_ROWS_EVENTv0
　　DELETE_ROWS_EVENTv0
　　WRITE_ROWS_EVENTv1
　　UPDATE_ROWS_EVENTv1
　　DELETE_ROWS_EVENTv1
　　这些都是row event 当主库变动的时候，如果复制方式是row，那么就会产生这些event
　　payload
　　一：header
　　6个字节的表id
　　2个字节的flags
　　二：body
　　不定长度的列数量
　　记录变化之前的掩码（如果之前存在该条记录），表明各列是不是存在的，长度为(num of columns+7)/8 暂记为p_bi
　　记录变化之后的掩码（如果之后存在该条记录），表明各列是不是存在的，长度为(num of columns+7)/8 暂记为p_ai
　　三：rows 可能有多条
　　null map 记录变化之前各列是不是为null，为null的话该列记为1，map的长度为（p_bi中相对应的列1的数量+7）/8
　　变化前的记录 可以通过TABLE_MAP_EVENT中的信息进行解析
　　null map 记录变化之后各列是不是为null，为null的话该列记为1，map的长度为（p_ai中相对应的列1的数量+7）/8
　　变化后的记录 可以通过TABLE_MAP_EVENT中的信息进行解析
　　现在分析上面例子：
　　hexdump -C mysql-bin.000015 -s 469
　　000001d5  20 c0 b3 53 17 01 00 00  00 2e 00 00 00 03 02 00  | ..S............|
　　000001e5  00 00 00 29 00 00 00 00  00 01 00 04 ff f0 01 00  |...)............|
　　000001f5  00 00 02 62 6f 02 68 75  04 00 74 61 6e 67 20 c0  |...bo.hu..tang .|
　　00000205  b3 53 10 01 00 00 00 1b  00 00 00 1e 02 00 00 00  |.S..............|
　　00000215  00 77 00 00 00 00 00 00  00                       |.w.......|
　　前面19个字节是公共头，忽略，0x000000000029是表id，0x0001是flags，04是列数量,0xff是p_ai，四列都是1，都存在，0xf0表明前四列不为null，都需要进行解析，后面就是各列数据：第第一列是MYSQL_TYPE_LONG，四个字节，读取0x00000001，就是1
　　第二列是MYSQL_TYPE_STRING，读取一个字节的长度，0x02, 再读取0x02个字节，0x626f，就是“bo”
　　第三列是MYSQL_TYPE_VARCHAR，读取一个字节的长度，0x02,再读取0x02个字节，0x6875，就是“hu”
　　第三列是MYSQL_TYPE_BLOB，读取两个字节的长度，0x0004，再读取0x04个字节，0x74616e67，就是“tang”
　　分析结束
　　INTVAR_EVENT
　　payload
　　1个字节的type
　　8个字节的value
　　当type为 0x02时，说明该event是 INSERT_ID_EVENT
　　在用statement作复制的时候，该event很重要，在自增主键的情况下，需要在每次插入记录的时候，设置一下INSERT_ID(set session insert_id=xxx)，这样才能保证主从库的主键一致。有一种情况，主库有个自增主键id,有个unique key是name，当插入一条记录导致name重复的话，id还是加了1，这样的话，主库就会有一个缺失的主键，但是binlog在从库执行的时候，肯定会成功，不会出现这样的缺失主键情况，导致主从主键不一致。
　　PS：在进行statement的主从复制的时候，如果语句里有now()的话，主从数据可能不一致，一种解决方法是在每次在从库执行query的之前，set session timestamp=xxx，这样主从的时间就一致了
　　Rand()的情况怎么解决还不清楚。