Methods for skipping errors during MYSQL Database Replication

During MYSQL database Replication, the incorrect method is skipped. During MYSQL Replication, sometimes the Replication SQL process is incorrect due to different POS points on the master and slave sides, leading to master-slave Replication failure. For example, if you copy an ID of 100 to the slave end on the master end, And the slave end already has the ID = 100 record for some reason, during the INSERT operation, the primary key will be repeated and insertion will fail. Skip this error. Method 1: stop slave service mysql> stop slave; 2: SET the number of skipped events mysql> set global SQL _SLAVE_SKIP_COUNTER = 1; www.2cto.com 3: START SLAVE service mysql> START SLAVE; the meaning of N is explained below. We all know that when slave encounters an error, we can skip the error by setting GLOBAL SQL _slave_skip_counter = N. But what does this N actually mean, at the beginning, I got an error. I thought that for the transaction type, N represents N transactions, not transactions, and represents an SQL statement. After being guided by linuxtone Cao Ge, I found that this is not the case.
The document has an introduction (http://dev.mysql.com/doc/refman... e-skip-counter.html): This statement skips the next N events from the master that he is skipping N events, the most important thing here is to understand the meaning of event in mysql, the binary log of SQL is actually a group composed of a series of events, that is, a transaction group. We can use
Show binlog events to check the number of EVENTS in an SQL statement. The example shows the meaning of the REAL event: show slave status Last_Errno: 1062 Last_Error: Error 'duplicate entry '000000' for key 'primary'' on query on slave. default database: 'ssldb '. query: 'insert slave_no_skip1 values (193, 'y10') 'Skip _ Counter: 0 www.2cto.com on the master, run mysql> show binlog events in 'mysql-bin.000010' from 46755013; + domains + ---------- + ------------ + ----------- + ------------- + domains + | Log_name | Pos | Event_type | Server_id | region | Info | + region + ---------- + ------------ + ----------- + region + | mysql-bin.000010 | 46755013 | Query | 1 | 46755082 | BEGIN | 1 | mysql-bin.000010 | 46755082 | Query | 1 | 46755187 | use 'ssldb '; insert slave_no_skip1 values (193, 'y10 ') | 2 | mysql-bin.000010 | 46755187 | Xid | 1 | 46755214 | COMMIT/* xid = 4529451 */| 3 | mysql-bin.000010 | 46755214 | Query | 1 | 46755283 | BEGIN | 4 | mysql-bin.000010 | 46755283 | Query | 1 | 46755387 | use 'ssldb '; insert slave_no_skip1 values (194, 'y11 ') | 5 | mysql-bin.000010 | 46755387 | Xid | 1 | 46755414 | COMMIT/* xid = 4529452 */| 6 | mysql-bin.000010 | 46755414 | Query | 1 | 46755483 | BEGIN | 7 | mysql-bin.000010 | 46755483 | Query | 1 | 46755587 | use 'ssldb '; insert slave_no_skip1 values (195, 'y12 ') | 8 | mysql-bin.000010 | 46755587 | Xid | 1 | 46755614 | COMMIT/* xid = 4529453 */| 9 | mysql-bin.000010 | 46755614 | Query | 1 | 46755683 | BEGIN | 10 | mysql-bin.000010 | 46755683 | Query | 1 | 46755788 | use 'ssldb '; insert slave_no_skip1 values (196, 'y13 ') | 11 | mysql-bin.000010 | 46755788 | Xid | 1 | 46755815 | COMMIT/* xid = 4529454 */| 12 | mysql-bin.000010 | 46755815 | Query | 1 | 46755884 | BEGIN | 13 | mysql-bin.000010 | 46755884 | Query | 1 | 46755989 | use 'ssldb '; insert slave_no_skip1 values (197, 'y14 ') | 14 | mysql-bin.000010 | 46755989 | Xid | 1 | 46756016 | COMMIT/* xid = 4529455 */| 15 | mysql-bin.000010 | 46756016 | Query | 1 | 46756085 | BEGIN | 16 | mysql-bin.000010 | 46756085 | Query | 1 | 46756190 | use 'ssldb '; insert slave_no_skip1 values (198, 'y15 ') | 17 | mysql-bin.000010 | 46756190 | Xid | 1 | 46756217 | COMMIT/* xid = 4529456 */| 18 | mysql-bin.000010 | 46756217 | Query | 1 | 46756286 | BEGIN | 19 | mysql-bin.000010 | 46756286 | Query | 1 | 46756391 | use 'ssldb '; insert slave_no_skip1 values (199, 'y16 ') | 20 | mysql-bin.000010 | 46756391 | Xid | 1 | 46756418 | COMMIT/* xid = 4529457 */| 21 | mysql-bin.000010 | 46756418 | Query | 1 | 46756487 | BEGIN | mysql-bin.000010 | 46756487 | Query | 1 | 46756592 | use 'ssldb '; insert slave_no_skip1 values (190, 'y17 ') | mysql-bin.000010 | 46756592 | Xid | 1 | 46756619 | COMMIT/* xid = 4529458 */| + -------------------- + ---------- + ------------ + ----------- + rows + 24 rows in set (0.00 sec) www.2cto.com indicates by mistake that it is use 'ssldb'; insert slave_no_skip1 values (193, 'y10'). If we want to skip to the last statement "use 'ssldb '; insert slave_no_skip1 values (190, 'y17') ", we must calculate the number of events in the middle.
Obviously, it is 21, so we can execute set global SQL _slave_skip_counter = 21 (here you can set global SQL _slave_skip_counter = 19 or 20) in slave, execute show slave status to view Last_Errno: 1062 Last_Error: Error 'duplicate entry '000000' for key 'primary' 'on query. default database: 'ssldb '. query: 'insert slave_no_skip1 values (190, 'y17') 'Skip _ Counter: 0. as I expected, he jumped to use 'ssldb'; insert slave_no_skip1 values (190, 'y17.