Mysql master-slave Replication-skipping error log entries from the slave database (this article is reprinted and hereby declares) during MYSQL Replication, sometimes the POS points on the master-slave end are different, an error occurs in the copied SQL process, leading to master-slave replication failure. For example, if you copy an ID of 100 to the slave end on the master end, the slave end already has an ID100
Mysql master-slave Replication-skipping error log entries from the slave database (this article is reprinted and hereby declares) during MYSQL Replication, sometimes the POS points on the master-slave end are different, an error occurs in the copied SQL process, leading to master-slave replication failure. For example, if you copy an ID of 100 to the slave end on the master end, the slave end already has the ID = 100
Mysql master-slave replication-skipping error log entries from the slave Database
(This article is reproduced and hereby declares)
During MYSQL Replication, the Replication SQL process may encounter errors due to different POS points on the master and slave sides, resulting in 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. The method is as follows:
1: Stop the SLAVE Service
Mysql> stop slave;
2: set the number of skipped events
Mysql> set global SQL _SLAVE_SKIP_COUNTER = 1;
3: Start the SLAVE Service
Mysql> start slave;
The meaning of N is described below.
As we all know, when an slave error occurs, you can skip the error through set global SQL _slave_skip_counter = N, but what does this N actually mean? At the beginning,
An error occurred while thinking that for transaction type, N represents N transactions, not transaction type, 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 is, he skips N events. The most important thing here is to understand the meaning of the 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 following example shows the meaning of the REAL event:
On slave
Show slave status
Last_errno.: 1062
Last_Error: Error 'duplicate entry '000000' for key 'primary' 'on query. Default database: 'ssldb'. Query: 'insert slave_no_skip1 values (193, 'y10 ')'
Skip_Counter: 0
On the master, run
Mysql> show binlog events in 'mysql-bin.000010' from 46755013;
+ ------------------ + ---------- + ------------ + ----------- + ------------- + ------------------------------------------------------ +
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+ ------------------ + ---------- + ------------ + ----------- + ------------- + ------------------------------------------------------ +
| 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 | queries | 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 */|
+ ------------------ + ---------- + ------------ + ----------- + ------------- + ------------------------------------------------------ +
24 rows in set (0.00 sec)
The error indicates that the statement "use 'ssldb'; insert slave_no_skip1 values (193, 'y10')" causes an error.
If we want to skip to the last statement "use 'ssldb'; insert slave_no_skip1 values (190, 'y17')", we must simply 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)
Run show slave status on slave 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
It can be seen that he has jumped to use 'ssldb'; insert slave_no_skip1 values (190, 'y17') as I wish.
