欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-03-16 09:59:26
...

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