MySQL row Format Binlog parsing (1)

replication with the MySQL row format slave often encounter replication errors 1062 and 1032 error, usually mirror abnormal downtime caused by the master-slave replication data inconsistency, but some libraries themselves are large, the cost of reconstruction is very large, And the data consistency of these libraries users may not be too concerned about, so the previous approach is generally encountered primary key conflicts on the skip, encountered the key can not find the Mysqlbinlog to parse the data out, but this method too human words, processing is very slow, So before doing a tool for automatic data repair, it is also parsing the Binlog log, and then generating SQL statements to execute.
do this tool has another use, since can parse Binlog log, this can also do based on binlog recovery, of course, this function before Ali has been done, but do the method is not the same, you can refer to the MySQL flashback.

parsing binlog need to know a few points:

1. Start-position of abnormal occurrences
2. Abnormal end of End-position

because it is running on the slave machine, all of the parsing here is relay log.

Through show slave status, you can clearly know

• Start-psoition:relay_log_pos
• End-position: This information can not be obtained directly from the show slave status, this is calculated, the calculation method:

end-position = Relay_log_pos + (end_log_pos-exec_master_log_pos)

(End_log_pos-exec_master_log_pos) This represents the number of bytes that this thing executes from start to error.

End_log_pos in show slave status error message inside also, take out to be able.

knowing the starting position and the end position, the rest can be resolved. If you are not sure about the Binlog event, you can look at the official documentation:
http://dev.mysql.com/doc/internals/en/binlog-event.htmlThe documentation was also extensively referenced in the process of writing the code.


I use Python to parse, in fact, in what language is the same, the steps are as follows:      

1. First seek to start position
2. Parse the first event type, if it is table_map_event open parsing, if not just skip, because if it is a normal statement in the row format is recorded in the way is definitely table_map_event + specific operation, so if the first is not a table_ Map_event then either the Binlog is damaged or the starting position is incorrect.
3. If it resolves to tabl_map_event, the following types of event are useful for us: Insert Event/update Event/delete event, when parsing this event we can basically piece together a SQL statement.
4. And then continue parsing the next table_map_evnt and so on, you can parse out a lot of SQL statements.

The basic process is this, so how to parse it?
first talk about the event:
basically all of the event formats are the same, and are composed of header + data two parts.
the header is fixed, with different event variables in the data.

data is divided into fixed part and variable part.
Header format
| timestamp (0:4) | Type_code (4:1) | server_id (5:4) | event_length (9:4) | next_position (13:4) | flags (17:2) | extr A_header (19:x-19) |
Data format
|fix_data x:y | Variable_data

Header Description:

• Timestamp time stamp
• Type_code Event Type is useful
• SERVER_ID's no use.
• Event_lenght entire event length including header
• Next_position the position of the next event is very useful.
• Flags are useless.
• Extra_header, this is no use at the moment.

so the entire header is 19 bytes, the data portion takes up bytes: event_length-19

here are a few of the binlog formats that are used:

• Table_map_event:19
• Write_rows_event:23
• Update_rows_event:24
• Delete_rows_event:25

here to note that the type of event started from MySQL5.6.2, so the need to change 5.6, 5.6 of I have not been tested in detail, do not know what the pit.

• #mysql5.6.2 Events
• # write_rows_event= 30
• # update_rows_event= 31
• # delete_rows_event= 32
• # incident_event= 26

     
because the header parts are the same, they say the data section directly.

table_map_event :
Data: Fixed Data:

• table_id (6bytes)
• Extra not use (2bytes)

Variable Data

• Schema_length (1bytes)
• Schame name (schema_length size)
• 0//null character takes up one byte
• Table Length (1bytes)
• Table name (table length size)
• 0//null character takes up one byte
• Column size (variable length, up to 8 bytes, maximum 2 64-square)
• COULMN type (column size bytes)

What can we get from table_map_event?

Yes, the main thing to get is the library name and the table name and the field type.

Show and library name good, directly read out without turning, field type MySQL also has a special conversion table, each byte corresponds to a field type.
Http://dev.mysql.com/doc/internals/en/com-query-response.html#packet-Protocol::MYSQL_TYPE_DATETIME
so now the Table table library Name field types have, feel less the name of the fields, field names go directly to MySQL to check.
Sql=("Select* fromInformation_schema.columnswhereTable_schema='%s ' "
"And Table_name='%S ' ")In this way, we have the metadata information, we need to parse the specific data, and then put the parsed data directly into it.

So here's just how to parse the other event format.

start with the write_rows_event first:
Write_rows_event is actually an insert operation, the front also said that each EVENT header is the same, but the data section has a distinction, so write_rows_event is the same, The header section is 19 bytes and the data department is event_length-19 bytes.

So what are the fixed data and variables data in the data section?

Fixed Data:

• table_id (6bytes)
• Extra not use (2bytes)

Variables Data:

• Col_num (dynamic), typically 1bytes.
• Map_col (table bitmap)
• Record

you can see that the fixed data section does not have much useful information, possibly table_id, and is important in the Variables data section.

• Col_num represents the number of fields in bytes is variable, generally one byte is enough, 1 bytes/2 of eight is generally difficult to have a table with hundreds of fields.
• Map_col this represents whether the field is empty
• Record this is the specific recording content.
• 
  

How to parse a record?

the format of the record is the structure of the Bit_map + record.
Bit_map Each bit represents whether the field is empty, so the number of bytes Bit_map occupies is: ((columns+7)/8)
For example, 1100 represents a total of four fields, the first two fields are non-empty, the latter two are null, and the null data is not recorded in the record.
So, first of all, according to bitmap to determine whether the field is empty, if not empty the program continues to scan, if empty, determine whether the next field is empty.
at this point an INSERT statement is basically resolved.
In addition, Delete_rows_event is basically the same as insert.
update_rows_event a little bit special, update_rows_event is also bitmap + record, but the update contains the pre-and post-image, so an update format is
bitmap + record (front image) + Bitnap + record (after image)

basically a simple statement is this:
Table-map-event + insert/update/delete Event
Then there may be situations such as:
Insert Values (), (), () or delete ID in (...)
A SQL operation is a case of many records.
then the format of this record becomes record = (bitmap + data) + (bitmap + data): Until the parsing is complete

So you must scan this event when scanning, or it is easy to make mistakes.

Here is a brief introduction to how to parse the Binlog bar, the following will be detailed to resolve how to parse the different types of fields.