通过配置vip，在进行主备切换时，出现的报错信息：
1.当主备节点当前binlog文件名称相同时，原主节点的position小于主备切换后的position，出现如下报错：
2020-07-02 15:08:09,332 info [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] mysqlconnection:293 | register slave registerslavecommandpacket[reporthost=192.168.3.1,reportport=63292,reportuser=canal_repl_user,reportpasswd=111111,serverid=10236,command=21] 2020-07-02 15:08:21,227 info [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] mysqlconnection:321 | com_binlog_dump with position:binlogdumpcommandpacket[binlogposition=1104,slaveserverid=10236,binlogfilename=mysql-bin.000002,command=18] 2020-07-02 15:08:24,979 info [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] logevent:122 | common_header_len= 19, number_of_event_types= 38 2020-07-02 15:08:24,983 error [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] directlogfetcher:163 | i/o error while reading from client socket java.io.ioexception: received error packet: errno = 1236, sqlstate = hy000 errmsg = log event entry exceeded max_allowed_packet; increase max_allowed_packet on master; the first event 'mysql-bin.000002' at 1104, the last event read from '/usr/local/mysql/logs/mysql-bin.000002' at 123, the last byte read from '/usr/local/mysql/logs/mysql-bin.000002' at 1123. at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.directlogfetcher.fetch(directlogfetcher.java:102) at com.alibaba.otter.canal.parse.inbound.mysql.mysqlconnection.dump(mysqlconnection.java:169) at com.alibaba.otter.canal.parse.inbound.abstracteventparser$3.run(abstracteventparser.java:279) at java.lang.thread.run(thread.java:748) 2020-07-02 15:08:24,989 error [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] mysqleventparser:301 | dump address 192.168.3.100/192.168.3.100:3306 has an error, retrying. caused by java.io.ioexception: received error packet: errno = 1236, sqlstate = hy000 errmsg = log event entry exceeded max_allowed_packet; increase max_allowed_packet on master; the first event 'mysql-bin.000002' at 1104, the last event read from '/usr/local/mysql/logs/mysql-bin.000002' at 123, the last byte read from '/usr/local/mysql/logs/mysql-bin.000002' at 1123. at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.directlogfetcher.fetch(directlogfetcher.java:102) at com.alibaba.otter.canal.parse.inbound.mysql.mysqlconnection.dump(mysqlconnection.java:169) at com.alibaba.otter.canal.parse.inbound.abstracteventparser$3.run(abstracteventparser.java:279) at java.lang.thread.run(thread.java:748) 2020-07-02 15:08:24,994 error [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] logalarmhandler:19 | destination:1-236[java.io.ioexception: received error packet: errno = 1236, sqlstate = hy000 errmsg = log event entry exceeded max_allowed_packet; increase max_allowed_packet on master; the first event 'mysql-bin.000002' at 1104, the last event read from '/usr/local/mysql/logs/mysql-bin.000002' at 123, the last byte read from '/usr/local/mysql/logs/mysql-bin.000002' at 1123. at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.directlogfetcher.fetch(directlogfetcher.java:102) at com.alibaba.otter.canal.parse.inbound.mysql.mysqlconnection.dump(mysqlconnection.java:169) at com.alibaba.otter.canal.parse.inbound.abstracteventparser$3.run(abstracteventparser.java:279) at java.lang.thread.run(thread.java:748) ]
此类报错和max_allowed_packet相关。首先max_allowed_packet控制着主从复制过程中单个语句产生的二进制binlog event大小，它的值必须是1024的倍数 。出现此类错误的常见原因：
1>.该参数在主备库的配置大小不一致。 从主库传递到备库的binlog event大小超过了主库或者备库的max_allowed_packet大小。可以查看变量值：show global variables like “%max_allowed_packet%”; 看两个库的值是否一致。
2>.在对应个binlog文件中找不到对应的偏移量position，可以通过mysqlbinlog命令查看，发现没有要报错要找的1104(position)，可以切换到存在的position位点
mysqlbinlog mysql-bin.000002 --stop-position=1200 /*!50530 set @@session.pseudo_slave_mode=1*/; /*!50003 set @old_completion_type=@@completion_type,completion_type=0*/; delimiter /*!*/; # at 4 #200630 16:24:37 server id 2 end_log_pos 123 crc32 0x87e4bed6 start: binlog v 4, server v 5.7.28-log created 200630 16:24:37 # warning: this binlog is either in use or was not closed properly. binlog ' xfb6xg8caaaadwaaahsaaaabaaqans43lji4lwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaezgnaagaegaebaqeegaaxwaegggaaaaicagcaaaacgokkioaejqa ada+5ic= '/*!*/; # at 123 #200630 16:24:37 server id 2 end_log_pos 234 crc32 0xd95db8f4 previous-gtids # b3a0925e-b78b-11ea-9b67-000c2915fd70:51-55, # b85582c3-14d9-11ea-a64a-000c29ab1835:40-52 # at 234 #200630 16:25:23 server id 1 end_log_pos 299 crc32 0x0ed285db gtid last_committed=0 sequence_number=1 rbr_only=yes /*!50718 set transaction isolation level read committed*//*!*/; set @@session.gtid_next= 'b85582c3-14d9-11ea-a64a-000c29ab1835:53'/*!*/; # at 299 #200630 16:25:23 server id 1 end_log_pos 362 crc32 0x34ec0ffb query thread_id=11 exec_time=0 error_code=0 set timestamp=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/*!*/; /*!\c utf8 *//*!*/; 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 /*!*/; # at 362 #200630 16:25:23 server id 1 end_log_pos 427 crc32 0x62a09b2f table_map: `test`.`test_canal_2_hive` mapped to number 114 # at 427 #200630 16:25:23 server id 1 end_log_pos 492 crc32 0x0f349879 write_rows: table id 114 flags: stmt_end_f binlog ' 8/b6xhmbaaaaqqaaaksbaaaaahiaaaaaaaeabhrlc3qaexrlc3rfy2fuywxfml9oaxzlaamidxed uaaabi+bogi= 8/b6xh4baaaaqqaaaowbaaaaahiaaaaaaaeaagad//iyaaaaaaaaabb6agfuz3nhbi0wms1zmti5 xvr283myna8= '/*!*/; # at 492 #200630 16:25:23 server id 1 end_log_pos 523 crc32 0x9d38dbb3 xid = 542 commit/*!*/; # at 523 #200630 16:31:33 server id 1 end_log_pos 588 crc32 0x7a71df00 gtid last_committed=1 sequence_number=2 rbr_only=yes /*!50718 set transaction isolation level read committed*//*!*/; set @@session.gtid_next= 'b85582c3-14d9-11ea-a64a-000c29ab1835:54'/*!*/; # at 588 #200630 16:31:33 server id 1 end_log_pos 651 crc32 0xec353d4a query thread_id=11 exec_time=0 error_code=0 set timestamp=1593505893/*!*/; begin /*!*/; # at 651 #200630 16:31:33 server id 1 end_log_pos 716 crc32 0x0309e1d5 table_map: `test`.`test_canal_2_hive` mapped to number 114 # at 716 #200630 16:31:33 server id 1 end_log_pos 781 crc32 0xb7ac4767 write_rows: table id 114 flags: stmt_end_f binlog ' zfj6xhmbaaaaqqaaamwcaaaaahiaaaaaaaeabhrlc3qaexrlc3rfy2fuywxfml9oaxzlaamidxed uaaabtxhcqm= zfj6xh4baaaaqqaaaa0daaaaahiaaaaaaaeaagad//izaaaaaaaaabb6agfuz3nhbi0wms1zmti5 xvr4zwdhrlc= '/*!*/; # at 781 #200630 16:31:33 server id 1 end_log_pos 812 crc32 0xa8c3ce12 xid = 550 commit/*!*/; # at 812 #200630 16:52:25 server id 1 end_log_pos 877 crc32 0x8e7366ee gtid last_committed=2 sequence_number=3 rbr_only=no set @@session.gtid_next= 'b85582c3-14d9-11ea-a64a-000c29ab1835:55'/*!*/; # at 877 #200630 16:52:25 server id 1 end_log_pos 1122 crc32 0xb5c35333 query thread_id=11 exec_time=0 error_code=0 use `test`/*!*/; set timestamp=1593507145/*!*/; set @@session.sql_mode=1436549152/*!*/; grant replication slave, replication client on *.* to 'canal_repl_user'@'%' identified with 'mysql_native_password' as '*fd571203974ba9afe270fe62151ae967eca5e0aa' /*!*/; # at 1122 #200630 16:52:29 server id 1 end_log_pos 1187 crc32 0x2f039a0c gtid last_committed=3 sequence_number=4 rbr_only=no set @@session.gtid_next= 'b85582c3-14d9-11ea-a64a-000c29ab1835:56'/*!*/; # at 1187 #200630 16:52:29 server id 1 end_log_pos 1278 crc32 0x0348011d query thread_id=11 exec_time=0 error_code=0 set timestamp=1593507149/*!*/; set @@session.time_zone='system'/*!*/; flush privileges /*!*/; set @@session.gtid_next= 'automatic' /* added by mysqlbinlog */ /*!*/; delimiter ; # end of log file /*!50003 set completion_type=@old_completion_type*/; /*!50530 set @@session.pseudo_slave_mode=0*/;
2.当mysql主备切换时，无论binlog文件名是否相同，如果原主节点position大于主备切换后主库当前binlog的position，会报如下错误：
2020-07-02 14:51:16,671 info [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] mysqlconnection:293 | register slave registerslavecommandpacket[reporthost=192.168.3.1,reportport=60838,reportuser=canal_repl_user,reportpasswd=111111,serverid=10236,command=21] 2020-07-02 14:51:16,671 info [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] mysqlconnection:321 | com_binlog_dump with position:binlogdumpcommandpacket[binlogposition=10262,slaveserverid=10236,binlogfilename=mysql-bin.000002,command=18] 2020-07-02 14:51:16,672 error [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] directlogfetcher:163 | i/o error while reading from client socket java.io.ioexception: received error packet: errno = 1236, sqlstate = hy000 errmsg = client requested master to start replication from position > file size at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.directlogfetcher.fetch(directlogfetcher.java:102) at com.alibaba.otter.canal.parse.inbound.mysql.mysqlconnection.dump(mysqlconnection.java:169) at com.alibaba.otter.canal.parse.inbound.abstracteventparser$3.run(abstracteventparser.java:279) at java.lang.thread.run(thread.java:748) 2020-07-02 14:51:16,673 error [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] mysqleventparser:301 | dump address 192.168.3.100/192.168.3.100:3306 has an error, retrying. caused by java.io.ioexception: received error packet: errno = 1236, sqlstate = hy000 errmsg = client requested master to start replication from position > file size at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.directlogfetcher.fetch(directlogfetcher.java:102) at com.alibaba.otter.canal.parse.inbound.mysql.mysqlconnection.dump(mysqlconnection.java:169) at com.alibaba.otter.canal.parse.inbound.abstracteventparser$3.run(abstracteventparser.java:279) at java.lang.thread.run(thread.java:748) 2020-07-02 14:51:16,769 error [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] logalarmhandler:19 | destination:1-236[java.io.ioexception: received error packet: errno = 1236, sqlstate = hy000 errmsg = client requested master to start replication from position > file size at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.directlogfetcher.fetch(directlogfetcher.java:102) at com.alibaba.otter.canal.parse.inbound.mysql.mysqlconnection.dump(mysqlconnection.java:169) at com.alibaba.otter.canal.parse.inbound.abstracteventparser$3.run(abstracteventparser.java:279) at java.lang.thread.run(thread.java:748) ]
从错误信息可以看出，复制时其实position大于当前binlog文件的大小
3.mysql主备切换时，原主库binlog名称序号大于切换后主库binlog名称序号，会报如下错误：
2020-07-06 11:35:07,977 info [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] mysqlconnection:293 | register slave registerslavecommandpacket[reporthost=192.168.3.1,reportport=59469,reportuser=canal_repl_user,reportpasswd=111111,serverid=10236,command=21] 2020-07-06 11:35:07,978 info [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] mysqlconnection:321 | com_binlog_dump with position:binlogdumpcommandpacket[binlogposition=1411,slaveserverid=10236,binlogfilename=mysql-bin.000003,command=18] 2020-07-06 11:35:07,979 error [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] directlogfetcher:163 | i/o error while reading from client socket java.io.ioexception: received error packet: errno = 1236, sqlstate = hy000 errmsg = could not find first log file name in binary log index file at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.directlogfetcher.fetch(directlogfetcher.java:102) at com.alibaba.otter.canal.parse.inbound.mysql.mysqlconnection.dump(mysqlconnection.java:169) at com.alibaba.otter.canal.parse.inbound.abstracteventparser$3.run(abstracteventparser.java:279) at java.lang.thread.run(thread.java:748) 2020-07-06 11:35:07,980 error [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] mysqleventparser:301 | dump address 192.168.3.100/192.168.3.100:3306 has an error, retrying. caused by java.io.ioexception: received error packet: errno = 1236, sqlstate = hy000 errmsg = could not find first log file name in binary log index file at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.directlogfetcher.fetch(directlogfetcher.java:102) at com.alibaba.otter.canal.parse.inbound.mysql.mysqlconnection.dump(mysqlconnection.java:169) at com.alibaba.otter.canal.parse.inbound.abstracteventparser$3.run(abstracteventparser.java:279) at java.lang.thread.run(thread.java:748) 2020-07-06 11:35:07,987 error [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] logalarmhandler:19 | destination:1-236[java.io.ioexception: received error packet: errno = 1236, sqlstate = hy000 errmsg = could not find first log file name in binary log index file at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.directlogfetcher.fetch(directlogfetcher.java:102) at com.alibaba.otter.canal.parse.inbound.mysql.mysqlconnection.dump(mysqlconnection.java:169) at com.alibaba.otter.canal.parse.inbound.abstracteventparser$3.run(abstracteventparser.java:279) at java.lang.thread.run(thread.java:748) ]
意思是在新主库上由于binlog名称翻转次数较少，binlog名称需要比原主库少，在新主库上找不到对应名称的binlog
3.使用删除的binlog文件名称，报如下错误：
2020-07-03 18:07:53,443 warn [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] mysqleventparser:456 | prepare to find start position mysql-bin.000001:4:1593507861000 2020-07-03 18:07:53,443 warn [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] mysqleventparser:205 | ---> find start position successfully, entryposition[included=false,journalname=mysql-bin.000001,position=4,serverid=<null>,gtid=<null>,timestamp=1593507861000] cost : 40ms , the next step is binlog dump 2020-07-03 18:07:53,444 info [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] mysqlconnector:101 | disconnect mysqlconnection to /192.168.3.100:3306... 2020-07-03 18:07:53,446 info [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] mysqlconnector:79 | connect mysqlconnection to /192.168.3.100:3306... 2020-07-03 18:07:53,447 info [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] mysqlconnector:182 | handshake initialization packet received, prepare the client authentication packet to send 2020-07-03 18:07:53,447 info [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] mysqlconnector:199 | client authentication packet is sent out. 2020-07-03 18:07:53,458 info [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] mysqlconnection:293 | register slave registerslavecommandpacket[reporthost=192.168.3.1,reportport=49875,reportuser=canal_repl_user,reportpasswd=111111,serverid=10236,command=21] 2020-07-03 18:07:53,459 info [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] mysqlconnection:321 | com_binlog_dump with position:binlogdumpcommandpacket[binlogposition=4,slaveserverid=10236,binlogfilename=mysql-bin.000001,command=18] 2020-07-03 18:07:53,460 error [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] directlogfetcher:163 | i/o error while reading from client socket java.io.ioexception: received error packet: errno = 1236, sqlstate = hy000 errmsg = could not find first log file name in binary log index file at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.directlogfetcher.fetch(directlogfetcher.java:102) at com.alibaba.otter.canal.parse.inbound.mysql.mysqlconnection.dump(mysqlconnection.java:169) at com.alibaba.otter.canal.parse.inbound.abstracteventparser$3.run(abstracteventparser.java:279) at java.lang.thread.run(thread.java:748) 2020-07-03 18:07:53,462 error [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] mysqleventparser:301 | dump address /192.168.3.100:3306 has an error, retrying. caused by java.io.ioexception: received error packet: errno = 1236, sqlstate = hy000 errmsg = could not find first log file name in binary log index file at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.directlogfetcher.fetch(directlogfetcher.java:102) at com.alibaba.otter.canal.parse.inbound.mysql.mysqlconnection.dump(mysqlconnection.java:169) at com.alibaba.otter.canal.parse.inbound.abstracteventparser$3.run(abstracteventparser.java:279) at java.lang.thread.run(thread.java:748) 2020-07-03 18:07:53,463 error [destination = 1-236 , address = /192.168.3.100:3306 , eventparser] logalarmhandler:19 | destination:1-236[java.io.ioexception: received error packet: errno = 1236, sqlstate = hy000 errmsg = could not find first log file name in binary log index file at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.directlogfetcher.fetch(directlogfetcher.java:102) at com.alibaba.otter.canal.parse.inbound.mysql.mysqlconnection.dump(mysqlconnection.java:169) at com.alibaba.otter.canal.parse.inbound.abstracteventparser$3.run(abstracteventparser.java:279) at java.lang.thread.run(thread.java:748) ]
可以通过在主库上通过”flush logs”命令重新生成信息binlog，然后使用”show master status”查询信息位点，重新使用“change master to master_log_file=’log-bin.00000xx’,master_log_pos=xxx;”重新同步binlog。
总结
到此这篇关于mysql主备切换canal出现的一些问题解决的文章就介绍到这了,更多相关mysql主备切换canal问题内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！