Mysql misunderstandings about the set global SQL _slave_skip_counter = N command

Mysql is confused about the set global SQL _slave_skip_counter = N command. In the maintenance of the slave database, set global SQL _slave_skip_counter = N is a common command. This article describes this point in detail, this helps us to understand it in depth. Background 1: During master-slave database maintenance, you sometimes need to skip a command that cannot be executed. You need to execute set global SQL _slave_skip_counter = N in the stop State of slave to skip the command. N = 1 is a common case that is not easy to use, but N> 1 is not as the name suggests. This article details the meaning of N and precautions for use. Background 2: MySQL copies the binlog file content from the master database and runs it locally. Commands on binlog exist in the form of events. Not a command corresponds to an event. Taking an insert statement as an example (engine InnoDB and binglog_format = statement), there are actually three events in binlog, which are begin \ insert \ commit respectively. The command type is Query_log_event. set global SQL _slave_skip_counter = N, meaning that N events are skipped from the current position when starting slave. Every time an event is skipped, N --. is not consistent with the actual situation? If you see this, you will ask. This is a problem. If the current execution position is the beginning of an insert statement, then using N = 1 actually starts from the second entry of begin \ insert \ commit. Can this insert statement still not be skipped? In fact, there are two other policies: 1. If N = 1 and the current event is BEGIN, N remains unchanged and the current event is skipped to continue. 2. If N = 1 and the current event is in a transaction (after BEGIN, before COMMIT), N remains unchanged and the current event is skipped to continue. Note: In fact, when N = 1, several events will be skipped until the current transaction ends. Of course, if N> 1, N --. Command is required for every skipped event. For example, the most common N = 1 is the next transaction. Assume that a Pos is followed by the following command (engine InnoDB, binglog_format = statement), insert into t values (x1); begin; insert into t values (x2 ); insert into t values (x3); commit; insert into t values (x4); your slave database stop on the Pos, if you want to skip the previous commands and directly insert x4, you can set N to 4, 5, 6, or 7. (The X1 statement has three events.) other instructions: The above examples show that it is in the innodb engine and statement mode. Other situations are different: 1. If the engine is myisam (and other engines that do not support transactions), and under statement, there will be no begin or commit in the binlog, and each command is an event; 2. In the binlog in row mode, an insert statement is actually two events (Table_map_event and Row_log_event). The calculation should be different from statement. 3. In row mode, no matter whether the engine supports transactions or not, an insert statement will add BEGIN and commit, that is, four events. 4. insert/delete/update operations based on InnoDB Engine tables all have explicit BEGIN/COMMIT. in the preceding example, if the row mode is used, the N to be set for executing the X4 statement is 5 ~ 10. Conclusion: 1. N in set global SQL _slave_skip_counter = N indicates skipping N events. 2. The best note is that when N is set to 1, the effect skips the next transaction. 3. After skipping the nth event, if the position falls inside a transaction, the entire transaction will be skipped. 4. An insert/update/delete operation does not necessarily correspond to only one event, determined by the engine and log format