关于mysql主备切换canal出现的问题解决

2023-07-10 21:39:03 267

通过配置VIP，在进行主备切换时，出现的报错信息：

1.当主备节点当前binlog文件名称相同时，原主节点的position小于主备切换后的position，出现如下报错：

2020-07-0215:08:09,332INFO[destination=1-236,address=/192.168.3.100:3306,EventParser]MysqlConnection:293|RegisterslaveRegisterSlaveCommandPacket[reportHost=192.168.3.1,reportPort=63292,reportUser=canal_repl_user,reportPasswd=111111,serverId=10236,command=21]
2020-07-0215:08:21,227INFO[destination=1-236,address=/192.168.3.100:3306,EventParser]MysqlConnection:321|COM_BINLOG_DUMPwithposition:BinlogDumpCommandPacket[binlogPosition=1104,slaveServerId=10236,binlogFileName=mysql-bin.000002,command=18]
2020-07-0215:08:24,979INFO[destination=1-236,address=/192.168.3.100:3306,EventParser]LogEvent:122|common_header_len=19,number_of_event_types=38
2020-07-0215:08:24,983ERROR[destination=1-236,address=/192.168.3.100:3306,EventParser]DirectLogFetcher:163|I/Oerrorwhilereadingfromclientsocket
java.io.IOException:Receivederrorpacket:errno=1236,sqlstate=HY000errmsg=logevententryexceededmax_allowed_packet;Increasemax_allowed_packetonmaster;thefirstevent'mysql-bin.000002'at1104,thelasteventreadfrom'/usr/local/mysql/logs/mysql-bin.000002'at123,thelastbytereadfrom'/usr/local/mysql/logs/mysql-bin.000002'at1123.
atcom.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102)
atcom.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:169)
atcom.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:279)
atjava.lang.Thread.run(Thread.java:748)
2020-07-0215:08:24,989ERROR[destination=1-236,address=/192.168.3.100:3306,EventParser]MysqlEventParser:301|dumpaddress192.168.3.100/192.168.3.100:3306hasanerror,retrying.causedby
java.io.IOException:Receivederrorpacket:errno=1236,sqlstate=HY000errmsg=logevententryexceededmax_allowed_packet;Increasemax_allowed_packetonmaster;thefirstevent'mysql-bin.000002'at1104,thelasteventreadfrom'/usr/local/mysql/logs/mysql-bin.000002'at123,thelastbytereadfrom'/usr/local/mysql/logs/mysql-bin.000002'at1123.
atcom.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102)
atcom.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:169)
atcom.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:279)
atjava.lang.Thread.run(Thread.java:748)
2020-07-0215:08:24,994ERROR[destination=1-236,address=/192.168.3.100:3306,EventParser]LogAlarmHandler:19|destination:1-236[java.io.IOException:Receivederrorpacket:errno=1236,sqlstate=HY000errmsg=logevententryexceededmax_allowed_packet;Increasemax_allowed_packetonmaster;thefirstevent'mysql-bin.000002'at1104,thelasteventreadfrom'/usr/local/mysql/logs/mysql-bin.000002'at123,thelastbytereadfrom'/usr/local/mysql/logs/mysql-bin.000002'at1123.
atcom.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102)
atcom.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:169)
atcom.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:279)
atjava.lang.Thread.run(Thread.java:748)
]

此类报错和max_allowed_packet相关。首先max_allowed_packet控制着主从复制过程中单个语句产生的二进制binlogevent大小，它的值必须是1024的倍数。出现此类错误的常见原因：

1>.该参数在主备库的配置大小不一致。从主库传递到备库的binlogevent大小超过了主库或者备库的max_allowed_packet大小。可以查看变量值：SHOWGLOBALVARIABLESLIKE"%max_allowed_packet%";看两个库的值是否一致。

2>.在对应个binlog文件中找不到对应的偏移量position，可以通过mysqlbinlog命令查看，发现没有要报错要找的1104(position)，可以切换到存在的position位点

mysqlbinlogmysql-bin.000002--stop-position=1200

/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER/*!*/;
#at4
#20063016:24:37serverid2end_log_pos123CRC320x87e4bed6Start:binlogv4,serverv5.7.28-logcreated20063016:24:37
#Warning:thisbinlogiseitherinuseorwasnotclosedproperly.
BINLOG'
xfb6Xg8CAAAAdwAAAHsAAAABAAQANS43LjI4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
Ada+5Ic=
'/*!*/;
#at123
#20063016:24:37serverid2end_log_pos234CRC320xd95db8f4Previous-GTIDs
#b3a0925e-b78b-11ea-9b67-000c2915fd70:51-55,
#b85582c3-14d9-11ea-a64a-000c29ab1835:40-52
#at234
#20063016:25:23serverid1end_log_pos299CRC320x0ed285dbGTIDlast_committed=0sequence_number=1rbr_only=yes
/*!50718SETTRANSACTIONISOLATIONLEVELREADCOMMITTED*//*!*/;
SET@@SESSION.GTID_NEXT='b85582c3-14d9-11ea-a64a-000c29ab1835:53'/*!*/;
#at299
#20063016:25:23serverid1end_log_pos362CRC320x34ec0ffbQuerythread_id=11exec_time=0error_code=0
SETTIMESTAMP=1593505523/*!*/;
SET@@session.pseudo_thread_id=11/*!*/;
SET@@session.foreign_key_checks=1,@@session.sql_auto_is_null=0,@@session.unique_checks=1,@@session.autocommit=1/*!*/;
SET@@session.sql_mode=524288/*!*/;
SET@@session.auto_increment_increment=1,@@session.auto_increment_offset=1/*!*/;
/*!\Cutf8*//*!*/;
SET@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET@@session.lc_time_names=0/*!*/;
SET@@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
#at362
#20063016:25:23serverid1end_log_pos427CRC320x62a09b2fTable_map:`test`.`test_canal_2_hive`mappedtonumber114
#at427
#20063016:25:23serverid1end_log_pos492CRC320x0f349879Write_rows:tableid114flags:STMT_END_F

BINLOG'
8/b6XhMBAAAAQQAAAKsBAAAAAHIAAAAAAAEABHRlc3QAEXRlc3RfY2FuYWxfMl9oaXZlAAMIDxED
UAAABi+boGI=
8/b6Xh4BAAAAQQAAAOwBAAAAAHIAAAAAAAEAAgAD//iYAAAAAAAAABB6aGFuZ3Nhbi0wMS1zMTI5
Xvr283mYNA8=
'/*!*/;
#at492
#20063016:25:23serverid1end_log_pos523CRC320x9d38dbb3Xid=542
COMMIT/*!*/;
#at523
#20063016:31:33serverid1end_log_pos588CRC320x7a71df00GTIDlast_committed=1sequence_number=2rbr_only=yes
/*!50718SETTRANSACTIONISOLATIONLEVELREADCOMMITTED*//*!*/;
SET@@SESSION.GTID_NEXT='b85582c3-14d9-11ea-a64a-000c29ab1835:54'/*!*/;
#at588
#20063016:31:33serverid1end_log_pos651CRC320xec353d4aQuerythread_id=11exec_time=0error_code=0
SETTIMESTAMP=1593505893/*!*/;
BEGIN
/*!*/;
#at651
#20063016:31:33serverid1end_log_pos716CRC320x0309e1d5Table_map:`test`.`test_canal_2_hive`mappedtonumber114
#at716
#20063016:31:33serverid1end_log_pos781CRC320xb7ac4767Write_rows:tableid114flags:STMT_END_F

BINLOG'
Zfj6XhMBAAAAQQAAAMwCAAAAAHIAAAAAAAEABHRlc3QAEXRlc3RfY2FuYWxfMl9oaXZlAAMIDxED
UAAABtXhCQM=
Zfj6Xh4BAAAAQQAAAA0DAAAAAHIAAAAAAAEAAgAD//iZAAAAAAAAABB6aGFuZ3Nhbi0wMS1zMTI5
Xvr4ZWdHrLc=
'/*!*/;
#at781
#20063016:31:33serverid1end_log_pos812CRC320xa8c3ce12Xid=550
COMMIT/*!*/;
#at812
#20063016:52:25serverid1end_log_pos877CRC320x8e7366eeGTIDlast_committed=2sequence_number=3rbr_only=no
SET@@SESSION.GTID_NEXT='b85582c3-14d9-11ea-a64a-000c29ab1835:55'/*!*/;
#at877
#20063016:52:25serverid1end_log_pos1122CRC320xb5c35333Querythread_id=11exec_time=0error_code=0
use`test`/*!*/;
SETTIMESTAMP=1593507145/*!*/;
SET@@session.sql_mode=1436549152/*!*/;
GRANTREPLICATIONSLAVE,REPLICATIONCLIENTON*.*TO'canal_repl_user'@'%'IDENTIFIEDWITH'mysql_native_password'AS'*FD571203974BA9AFE270FE62151AE967ECA5E0AA'
/*!*/;
#at1122
#20063016:52:29serverid1end_log_pos1187CRC320x2f039a0cGTIDlast_committed=3sequence_number=4rbr_only=no
SET@@SESSION.GTID_NEXT='b85582c3-14d9-11ea-a64a-000c29ab1835:56'/*!*/;
#at1187
#20063016:52:29serverid1end_log_pos1278CRC320x0348011dQuerythread_id=11exec_time=0error_code=0
SETTIMESTAMP=1593507149/*!*/;
SET@@session.time_zone='SYSTEM'/*!*/;
flushprivileges
/*!*/;
SET@@SESSION.GTID_NEXT='AUTOMATIC'/*addedbymysqlbinlog*//*!*/;
DELIMITER;
#Endoflogfile
/*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;

2.当mysql主备切换时，无论binlog文件名是否相同，如果原主节点position大于主备切换后主库当前binlog的position，会报如下错误：

2020-07-0214:51:16,671INFO[destination=1-236,address=/192.168.3.100:3306,EventParser]MysqlConnection:293|RegisterslaveRegisterSlaveCommandPacket[reportHost=192.168.3.1,reportPort=60838,reportUser=canal_repl_user,reportPasswd=111111,serverId=10236,command=21]
2020-07-0214:51:16,671INFO[destination=1-236,address=/192.168.3.100:3306,EventParser]MysqlConnection:321|COM_BINLOG_DUMPwithposition:BinlogDumpCommandPacket[binlogPosition=10262,slaveServerId=10236,binlogFileName=mysql-bin.000002,command=18]
2020-07-0214:51:16,672ERROR[destination=1-236,address=/192.168.3.100:3306,EventParser]DirectLogFetcher:163|I/Oerrorwhilereadingfromclientsocket
java.io.IOException:Receivederrorpacket:errno=1236,sqlstate=HY000errmsg=Clientrequestedmastertostartreplicationfromposition>filesize
atcom.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102)
atcom.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:169)
atcom.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:279)
atjava.lang.Thread.run(Thread.java:748)
2020-07-0214:51:16,673ERROR[destination=1-236,address=/192.168.3.100:3306,EventParser]MysqlEventParser:301|dumpaddress192.168.3.100/192.168.3.100:3306hasanerror,retrying.causedby
java.io.IOException:Receivederrorpacket:errno=1236,sqlstate=HY000errmsg=Clientrequestedmastertostartreplicationfromposition>filesize
atcom.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102)
atcom.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:169)
atcom.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:279)
atjava.lang.Thread.run(Thread.java:748)
2020-07-0214:51:16,769ERROR[destination=1-236,address=/192.168.3.100:3306,EventParser]LogAlarmHandler:19|destination:1-236[java.io.IOException:Receivederrorpacket:errno=1236,sqlstate=HY000errmsg=Clientrequestedmastertostartreplicationfromposition>filesize
atcom.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102)
atcom.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:169)
atcom.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:279)
atjava.lang.Thread.run(Thread.java:748)
]

从错误信息可以看出，复制时其实position大于当前binlog文件的大小

3.mysql主备切换时，原主库binlog名称序号大于切换后主库binlog名称序号，会报如下错误：

2020-07-0611:35:07,977INFO[destination=1-236,address=/192.168.3.100:3306,EventParser]MysqlConnection:293|RegisterslaveRegisterSlaveCommandPacket[reportHost=192.168.3.1,reportPort=59469,reportUser=canal_repl_user,reportPasswd=111111,serverId=10236,command=21]
2020-07-0611:35:07,978INFO[destination=1-236,address=/192.168.3.100:3306,EventParser]MysqlConnection:321|COM_BINLOG_DUMPwithposition:BinlogDumpCommandPacket[binlogPosition=1411,slaveServerId=10236,binlogFileName=mysql-bin.000003,command=18]
2020-07-0611:35:07,979ERROR[destination=1-236,address=/192.168.3.100:3306,EventParser]DirectLogFetcher:163|I/Oerrorwhilereadingfromclientsocket
java.io.IOException:Receivederrorpacket:errno=1236,sqlstate=HY000errmsg=Couldnotfindfirstlogfilenameinbinarylogindexfile
atcom.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102)
atcom.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:169)
atcom.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:279)
atjava.lang.Thread.run(Thread.java:748)
2020-07-0611:35:07,980ERROR[destination=1-236,address=/192.168.3.100:3306,EventParser]MysqlEventParser:301|dumpaddress192.168.3.100/192.168.3.100:3306hasanerror,retrying.causedby
java.io.IOException:Receivederrorpacket:errno=1236,sqlstate=HY000errmsg=Couldnotfindfirstlogfilenameinbinarylogindexfile
atcom.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102)
atcom.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:169)
atcom.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:279)
atjava.lang.Thread.run(Thread.java:748)
2020-07-0611:35:07,987ERROR[destination=1-236,address=/192.168.3.100:3306,EventParser]LogAlarmHandler:19|destination:1-236[java.io.IOException:Receivederrorpacket:errno=1236,sqlstate=HY000errmsg=Couldnotfindfirstlogfilenameinbinarylogindexfile
atcom.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102)
atcom.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:169)
atcom.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:279)
atjava.lang.Thread.run(Thread.java:748)
]

意思是在新主库上由于binlog名称翻转次数较少，binlog名称需要比原主库少，在新主库上找不到对应名称的binlog

3.使用删除的binlog文件名称，报如下错误：

2020-07-0318:07:53,443WARN[destination=1-236,address=/192.168.3.100:3306,EventParser]MysqlEventParser:456|preparetofindstartpositionmysql-bin.000001:4:1593507861000
2020-07-0318:07:53,443WARN[destination=1-236,address=/192.168.3.100:3306,EventParser]MysqlEventParser:205|--->findstartpositionsuccessfully,EntryPosition[included=false,journalName=mysql-bin.000001,position=4,serverId=,gtid=,timestamp=1593507861000]cost:40ms,thenextstepisbinlogdump
2020-07-0318:07:53,444INFO[destination=1-236,address=/192.168.3.100:3306,EventParser]MysqlConnector:101|disConnectMysqlConnectionto/192.168.3.100:3306...
2020-07-0318:07:53,446INFO[destination=1-236,address=/192.168.3.100:3306,EventParser]MysqlConnector:79|connectMysqlConnectionto/192.168.3.100:3306...
2020-07-0318:07:53,447INFO[destination=1-236,address=/192.168.3.100:3306,EventParser]MysqlConnector:182|handshakeinitializationpacketreceived,preparetheclientauthenticationpackettosend
2020-07-0318:07:53,447INFO[destination=1-236,address=/192.168.3.100:3306,EventParser]MysqlConnector:199|clientauthenticationpacketissentout.
2020-07-0318:07:53,458INFO[destination=1-236,address=/192.168.3.100:3306,EventParser]MysqlConnection:293|RegisterslaveRegisterSlaveCommandPacket[reportHost=192.168.3.1,reportPort=49875,reportUser=canal_repl_user,reportPasswd=111111,serverId=10236,command=21]
2020-07-0318:07:53,459INFO[destination=1-236,address=/192.168.3.100:3306,EventParser]MysqlConnection:321|COM_BINLOG_DUMPwithposition:BinlogDumpCommandPacket[binlogPosition=4,slaveServerId=10236,binlogFileName=mysql-bin.000001,command=18]
2020-07-0318:07:53,460ERROR[destination=1-236,address=/192.168.3.100:3306,EventParser]DirectLogFetcher:163|I/Oerrorwhilereadingfromclientsocket
java.io.IOException:Receivederrorpacket:errno=1236,sqlstate=HY000errmsg=Couldnotfindfirstlogfilenameinbinarylogindexfile
atcom.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102)
atcom.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:169)
atcom.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:279)
atjava.lang.Thread.run(Thread.java:748)
2020-07-0318:07:53,462ERROR[destination=1-236,address=/192.168.3.100:3306,EventParser]MysqlEventParser:301|dumpaddress/192.168.3.100:3306hasanerror,retrying.causedby
java.io.IOException:Receivederrorpacket:errno=1236,sqlstate=HY000errmsg=Couldnotfindfirstlogfilenameinbinarylogindexfile
atcom.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102)
atcom.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:169)
atcom.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:279)
atjava.lang.Thread.run(Thread.java:748)
2020-07-0318:07:53,463ERROR[destination=1-236,address=/192.168.3.100:3306,EventParser]LogAlarmHandler:19|destination:1-236[java.io.IOException:Receivederrorpacket:errno=1236,sqlstate=HY000errmsg=Couldnotfindfirstlogfilenameinbinarylogindexfile
atcom.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102)
atcom.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:169)
atcom.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:279)
atjava.lang.Thread.run(Thread.java:748)
]

可以通过在主库上通过"flushlogs"命令重新生成信息binlog，然后使用"showmasterstatus"查询信息位点，重新使用“CHANGEMASTERTOMASTER_LOG_FILE='log-bin.00000xx',MASTER_LOG_POS=xxx;”重新同步binlog。

总结

到此这篇关于mysql主备切换canal出现的一些问题解决的文章就介绍到这了,更多相关mysql主备切换canal问题内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！

关于mysql主备切换canal出现的问题解决

热门推荐

随机推荐