MySQL Replication--binlog (2)

MySQL replication is the most widely used set up, the previous section has simply said some of the use of replication and the principle of replication, know that we can quickly build a copy of the platform, but only know that these are not enough, many times is not smooth sailing, there will always be a short period of time, Or there will always be a variety of problems that come up a few times. What should we do when something goes wrong?

Let's take a look at some common problems with MySQL replication, and the corresponding solutions; Can we consider doing better, providing automated or semi-automatic tools to help us solve problems faster and better?

OK, first of all, let's take a look at the problems we often encounter in replication.

The most classic of the two errors, is also a more troublesome two:

Errorno	Describe	Common Solutions
1062	Due to a primary key or a unique build conflict	You can erase this record from the library.
1032	No records found	Analysis Binlog The preceding paragraph, fill the gas record

Now that the problem has come, we now know the cause of the problem and how to deal with it. So what if this problem happens often? Business has a relatively strong reliance on the standby, and how to do it?

Our idea is to be able to do an automated or semi-automatic tool or step to help us find and solve problems in a timely manner. Think about it if you want to do this, then the first thing we have to do is how to find the right record, and then how to delete it, or to fill up the preceding paragraph. This is what we are going to do, after we find these records we can restart slave; And then copy it to work properly.

How to find these records before we combine the previous content and begin to further understand Binlog(ROW format of Binlog):

First each event has an Event _header header:

The event header has a size of 4 bytes, an event type of 1 bytes, a server ID of4 bytes, an event length of 4 bytes, and the next event start position 4 Byte and a 2- byte flag, the popular point is:

+---------+---------+---------+------------+-----------+-------+

|timestamp|type Code|server_id|event_length|end_log_pos|flags |

|4 bytes | | Byte |4 bytes |4 bytes |4 bytes |

+---------+---------+---------+------------+-----------+-------+
For event types, see the binlog_event.h#245 line begins with an enumeration type:

I have a 5.7.6 version of the event header information, because this version event_type more ( Nine), we will focus on the first moment:
rotate_event= 4,

format_description_event= ,

Table_map_event = +,

Write_rows_event =

Update_rows_event = -

Delete_rows_event = +,

then each event will have a event_body event Body:

The event body is composed of three parts: header, post-header,payload , but usually we put Post-header and Payload are all attributed to the event body, in fact post-header storage is some fixed length of data, we usually do not need special care.

So the popular version of the event body we describe as follows:

+=====================================+

|event | Fixed part (Post-header) |

|data +-------------------------------------------------------+

| | variable part (payload) |

+=====================================+

The reason we say post-header is fixed is because in the formatdescription event, the length has been stipulated. The first event in the document .

finally One event is the rotat event log rotation events:

By the front we know rotate_event 's type_code is 4, its so post-header length 8 .

OK, These basic information we all know, then we will start to write a program to parse our Binlog bar, let our program in processing replication is to help us!

about how to parse I put in the next section, the content is a bit more , and then write in here also not very suitable.

Report:

Eventheader Description: https://dev.mysql.com/doc/internals/en/event-header-fields.html

MySQL Replication--binlog (2)