mysql主从同步出现异常语句跳过错误处理

1.跳过操作：

mysql>slave stop;

mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1        跳过一个事务

mysql>slave start

2.SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1，跳过一个事务的概念。

 在mysql中，对于sql的 binary log 他实际上是由一连串的event组成的一个组，即事务组。
我们在master上可以通过
SHOW BINLOG EVENTS 来查看一个sql里有多少个event。

 例如：

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000003' from 9508\G
*************************** 1. row ***************************                                                     一个row代表一个事务组
Log_name: mysql-bin.000003
Pos: 9508
Event_type: Query
Server_id: 2
End_log_pos: 9944
Info: use `BK`; CREATE DEFINER=`root`@`%` PROCEDURE `zoucm`( in Spwd VARCHAR(20), in Npwd varchar(20), in YH VARCHAR(20))
pwd_s:
BEGIN
DECLARE Pid int;
select count(*) into Pid from users where user = YH and PWD = Spwd;
if Pid=1 THEN
update users set PWD=Npwd where user = YH and PWD = Spwd;
select 0 state ;
LEAVE pwd_s;
ELSE
select 1 as state ;
end if;
end pwd_s
*************************** 2. row ***************************
Log_name: mysql-bin.000003
Pos: 9944
Event_type: Query
Server_id: 2
End_log_pos: 10080
Info: use `liguanjia_cn`; CREATE TABLE `sss` (
`ds` int(11) NULL ,
PRIMARY KEY (`ds`)
)
*************************** 3. row ***************************
Log_name: mysql-bin.000003
Pos: 10080
Event_type: Query
Server_id: 2
End_log_pos: 10214
Info: use `liguanjia_cn`; CREATE TABLE `dd` (
`ss` double NULL ,
PRIMARY KEY (`ss`)
)

3。实例出错解决：

slave出错信息：

 

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: XXXXXX
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 14413
Relay_Log_File: LNMP3-relay-bin.000004
Relay_Log_Pos: 2782
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: liguanjia_cn.%,liguanjia_com.%
Replicate_Wild_Ignore_Table: 
Last_Errno: 1305
Last_Error: Error 'PROCEDURE BK.zoucm does not exist' on query. Default database: 'BK'. Query: 'drop procedure zoucm'
Skip_Counter: 0
Exec_Master_Log_Pos: 13973
Relay_Log_Space: 4472
Until_Condition: None
Until_Log_File: 
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File: 
Master_SSL_CA_Path: 
Master_SSL_Cert: 
Master_SSL_Cipher: 
Master_SSL_Key: 
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 1305
Last_SQL_Error: Error 'PROCEDURE BK.zoucm does not exist' on query. Default database: 'BK'. Query: 'drop procedure zoucm'
1 row in set (0.00 sec)

master从出错开始binlog日志事务组列表：

 

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000003' from 13973\G
*************************** 1. row ***************************
Log_name: mysql-bin.000003
Pos: 13973
Event_type: Query
Server_id: 2
End_log_pos: 14054
Info: use `BK`; drop procedure zoucm
*************************** 2. row ***************************
Log_name: mysql-bin.000003
Pos: 14054
Event_type: Query
Server_id: 2
End_log_pos: 14162
Info: use `liguanjia_cn`; INSERT INTO `dd` (`ss`) VALUES ('dd')
*************************** 3. row ***************************
Log_name: mysql-bin.000003
Pos: 14162
Event_type: Query
Server_id: 2
End_log_pos: 14299
Info: use `liguanjia_cn`; ALTER TABLE `dd`
MODIFY COLUMN `ss` int NOT NULL DEFAULT 0 FIRST
*************************** 4. row ***************************
Log_name: mysql-bin.000003
Pos: 14299
Event_type: Query
Server_id: 2
End_log_pos: 14413
Info: use `liguanjia_cn`; UPDATE `dd` SET `ss`='123' WHERE (`ss`='0')
4 rows in set (0.00 sec)

 

操作目标：

"drop procedure zoucm " 该语句是出错源头。

如果我们想直接跳到 “UPDATE `dd` SET `ss`='123' WHERE (`ss`='0')”  改语句，它们之间隔着3个事务组。

mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 3 就之间从“UPDATE `dd` SET `ss`='123' WHERE (`ss`='0')该语句开始同步

转载于:https://my.oschina.net/u/214948/blog/87846