Detailed explanation of Binlog format as ROW and problems encountered

Detailed explanation of Binlog format as ROW

1. Row Replication Based on RBR is related to the record location. The binlog records only the data of Columns with changes in the relevant table, and introduces four events: table_map, Write_rows, Update_rows, and Delete_rows.

2. After a statement is executed on the master database, it is recorded in binlog:

Table_map event (containing the table ID, table name, and columnType, no column name), Followed by three events, and the end mark is STMT_END_F.

Is the result of mysqlbinlog:

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/211221B21-0.png "title =" a.png "alt =" 150241622.png"/>

Show binlog eventsin 'mysql-bin.001430 'from 12893999 limit 4 \ G

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/211221F44-1.png "title =" B .png "alt =" 150257617.png"/>

The execution process of the ROW-format binlog on the slave end:

1. the SQL process reads events from relaylog

2. For Table_map events, the SQL process extracts table information and saves table definitions.

3. Lock the table to be modified and check whether the structure on the master and slave is consistent.

4. If the table structure is inconsistent, the replication will be stopped. Otherwise, the replication will continue until STMT_END_F ends.

For Update_rows and Delete_rows events, the SQL process must first locate the Specific Row. The search procedure is as follows:

1. primary key priority

The primary key on the slave is installed first. When a matched primary key value is found, the matched row is found, and the content of other columns in the row is not matched.

2. Non-empty unique index scan, only compare key values

3. Other index or full table scan: At this time, compare whether the data of the entire row is consistent on the master and slave to ensure the consistency of master-slave replication, but it is also the slowest ).

Recently, the company created a slave database to back up data (inconsistent data). As a result, after the slave database runs for a period of time, an error is reported, resulting in SQL process interruption:

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/2112211960-2.png "title =" c.png "alt =" 150313723.png"/>

Last_Errno: 1032Last_Error: Could not execute Delete_rows event on table mdb.item_info; Can't find record in 'item_info', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.001430, end_log_pos 12893999

This article is from My DBA life blog, please be sure to keep this source http://huanghualiang.blog.51cto.com/6782683/1298730