1.slave stop;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
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')該語句開始同步