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' from13973 \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')该语句开始同步
