首页 / MYSQL / mysql主备切换canal出现的问题解析

mysql主备切换canal出现的问题解析

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql主备切换canal出现的问题解析，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含17605字，纯文字阅读大概需要26分钟。

内容图文

通过配置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*/;

View Code

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出现的问题解析所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/868984.html

来源：【匿名】

【上一篇】mysql 5.8安装【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【mysql主备切换canal出现的问题解析】教程文章相关的互联网学习教程文章

解析MySQL binlog --（4）TABLE_MAP_EVENT【代码】【图】

关于MySQL DNS解析探究之一：DNS解析确实在起作用【代码】

机器A --> 192.168.103.94 -- 服务器机器B --> 192.168.4.2 -- 客户端机器A =>装上mysql-server，启动；使用root用户登录，查看DNS解析是否开启，执行如下命令：mysql> show variables like ‘skip_name_resolve%‘; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | skip_name_resolve | OFF | +-------------------+-------+ 1 row in set (0.00 sec)说明DNS解析是开启...

解析Mysql备份与恢复简单总结与tee命令的使用介绍

备份数据方法：一：sql语句。LOCKS TABLES tablename READ;//读锁定尝试锁定表之前，LOCK TABLES不是事务安全型的，会隐含地提交所有活性事务,同时，会隐含地开始一项事务（例如，使用START TRANSACTION），所以，对事务表（如InnoDB）使用LOCK TABLES的正确方法是，设置AUTOCOMMIT=0FLUSH TABLES,SELECT * INTO OUTFILE ‘data_bck.sql‘ FIELDS TERMINATED BY ‘,‘ FROM tablename;UNLOCK TABLES;二：mysqldump工具。完全备份数...

MySQL 选择数据库：数据库选择语法解析【代码】

MySQL 选择数据库在你连接到 MySQL 数据库后，可能有多个可以操作的数据库，所以你需要选择你要操作的数据库。从命令提示窗口中选择MySQL数据库在 mysql> 提示窗口中可以很简单的选择特定的数据库。你可以使用SQL命令来选择指定的数据库。实例以下实例选取了数据库 RUNOOB:[root@host]# mysql -u root -p Enter password:****** mysql> use RUNOOB; Database changed mysql> 执行以上命令后，你就已经成功选择了 RUNOOB 数据库，在...

MySQL的sql解析【代码】【图】

首先看一下示例语句SELECT DISTINCT < select_list > FROM < left_table > < join_type > JOIN < right_table > ON < join_condition > WHERE < where_condition > GROUP BY < group_by_list > HAVING < having_condition > ORDER BY < order_by_condition > LIMIT < limit_number > 然而它的执行顺序是这样的1 FROM <left_table> 2 ON <join_condition> 3 <join_type> JOIN <right_table> 第二步和第三步会循环执行 4 WHE...

mysql invalid json value 以及传到前端的json因为多了反斜杠导致无法解析的问题【代码】

mysql json字段添加如下json导致报错，其实是因为children节点下的meta元素后面多了个逗号（因为还没有加入下一个元素）：[{"name": "Test_It","path": "/test","component": "Layout","children": [{"name": "Test_It_c","path": "index","component": "Test","meta": {"icon": "example","title": "后台"},}]}] 这样就可以了：[{"name": "Test_It","path": "/test","component": "Layout","children": [{"name": "Test_It_c","pa...

不使用MySQL数据库的五个给力理由解析

在我们实际工作中，往往会能听到很多关于不使用MySQL数据库的理由，当然也有一些是对MySQL(和PHP搭配之最佳组合)的误解，下面我将讲述的是5个不使用MySQL(和PHP搭配之最佳组合)的响亮理由。首先我们要知道，或许有一项技术存在很多理由让我们可以选择使用它，但是让我们不使用它往往只要有一个理由就足够了。选择一个软件产品同样也是如此。 MySQL数据库虽然应用很广泛，受到大家的青睐，但MySQL数据库也有负面的作用，下面就介绍五...

解析mysql中如何获得数据库的大小

1.查看mysql数据库大小SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH)FROM information_schema.TABLES where TABLE_SCHEMA=‘数据库名‘;得到的结果是以字节为单位，除1024为K，除1048576(=1024*1024)为M。2.查看表的最后mysql修改时间select TABLE_NAME,UPDATE_TIME from INFORMATION_SCHEMA.tables where TABLE_SCHEMA=‘数据库名‘;可以通过查看数据库中表的mysql修改时间，来确定mysql数据库是否已经长期不再使用。原文：http://w...

（转）MySQL join语法解析与性能分析【代码】【图】

文章转载的：http://www.cnblogs.com/BeginMan/p/3754322.html一、join语法概述　　join用于多表中字段之间的联系，语法如下：... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona　　table1：左表；table2：右表　　JOIN按照功能大致分为如下三类：　　（1）INNER JOIN（内连接或等值连接）：取得两个表中存在连接匹配关系的记录。　　（2）LEFT JOIN（左连接）：取得左表（table1）完全记录，即是右表（table2）并无对应...

mysql导入导出命令解析【代码】

mysqldump是mysql用于转存储数据库的实用程序。它主要产生一个SQL脚本，其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等。下面就让我们一起学习吧！一、mysqldump：数据库备份程序有3种方式来调用mysqldump： mysqldump [options] db_name [tables] mysqldump [options] ---database DB1 [DB2 DB3...] mysqldump [options] --all--database如果没有指定任何表或使用了---database或--all--database选项，则转储整个...

mysql交互协议解析——mysql包基础数据、mysql包基本格式

mysql交互协议是开发mysql周边组件常用的协议，如JDBC,libmysql等等。在此我们要认识到mysql交互协议其实是半双工的交互协议，至于为什么，这里就先挖个小坑，以后再填。在探讨mysql交互协议的格式之前，我们要理清一个小问题，就是mysql交互协议会用到的基本数据类型。1.整数类型这里的整数类型可以用来表示mysql数据类型中的整数类型、日期时间类型、DOUBLE、FLOAT类型、比特类型以及枚举类型等等。所有的mysql整数类型都是小端的...

MySQL的sql_mode解析与设置，sql文件导入报错解决【图】

在往MySQL数据库中插入一组数据时，出错了！数据库无情了给我报了个错误：ERROR 1365（22012）：Division by 0；意思是说：你不可以往数据库中插入一个除数为0的运算的结果。于是乎去谷歌了一番，总算是明白了其中的原因：是因为MySQL的sql_mode 模式限制着一些所谓的‘不合法’的操作。解析这个sql_mode,简而言之就是：它定义了你MySQL应该支持的sql语法，对数据的校验等等。。一、如何查看当前数据库使用的sql_mode： mysql> se...

修改mysql默认字符集的两种方法详细解析

(1) 最简单的修改方法，就是修改mysql的my.ini文件中的字符集键值，如 default-character-set = utf8 character_set_server = utf8修改完后，重启mysql的服务，service mysql restart使用 mysql> SHOW VARIABLES LIKE ‘character%‘;查看，发现数据库编码均已改成utf8复制代码代码如下:+--------------------------+---------------------------------+ | Variable_name | Value | +--------------------------+-------------...

MySQL插入语句解析【代码】

1.INSERT INTO　　最常用简单的插入语句，可以有以下两种用法　　1> INSERT INTO tb_user(id, name, age) VALUES (100022, ‘Tom‘, 25); // 只给指定的列赋值　　2> INSERT INTO tb_user VALUES (100022, ‘Tom‘, 25);　　// 必须给所有列赋值　　注： INTO关键字可以省略 2. INSERT INTO ... SELECT　　可以将查询结果插入表中　　insert into t2same(name,age) select name, age from t2;　　insert into t2same select id...

MySQL执行计划explain的key_len解析

作者：沃趣科技高级数据库专家邱文辉前言当用Explain查看SQL的执行计划时，里面有列显示了 key_len 的值，根据这个值可以判断索引的长度，在组合索引里面可以更清楚的了解到了哪部分字段使用到了索引。下面演示中，表结构的合理性这边暂且不说，只是证明一下索引长度的计算方法。目前大部分博文是字符类型的索引长度计算方法，下面列举几个类型的索引长度计算方法：1、整数类型(dg1)root@127.0.0.1 [mytest]> desc table_key;+-...

MYSQL - 技术教程分类

MySQL 教程 MySQL 安装 MySQL 管理 MySQL PHP 语法 MySQL 连接 MySQL 创建数据库 MySQL 删除数据库 MySQL 选择数据库 MySQL 数据类型 MySQL 创建数据表 MySQL 删除数据表 MySQL 插入数据 MySQL 查询数据 MySQL WHERE 子句 MySQL UPDATE 更新 MySQL DELETE 语句 MySQL LIKE 子句 MySQL UNION MySQL 排序 MySQL 分组 MySQL 连接的使用 MySQL NULL 值处理 MySQL 事务 MySQL ALTER命令 MySQL 索引 MySQL 临时表 MySQL 复制表 MySQL 元数据 MySQL 序列使用 MySQL 处理重复数据 MySQL 及 SQL 注入 MySQL 导出数据 MySQL 导入数据 MySQL 函数 MySQL 运算符 mysql 全部

MYSQL - 最热教程

sql分组取最大记录方法 mysql如何设置默认值 mysql创建数据表时指定默认值教程 MySQL 5.7 的初始化操作（root初始密码...Oracle中合并数据集(多行变一行)mysql 相同内容的字段合并为一条的方法解决mysql设置时区时的错误Unknown or ...解决ubuntu下mysql的'Access denied fo...ubuntu系统中MysqlERROR1045(28000)报错...mysql数据库设置不区分大小写

首页 / MYSQL / mysql主备切换canal出现的问题解析

mysql主备切换canal出现的问题解析

内容导读

内容图文

内容总结

内容备注

内容手机端

【mysql主备切换canal出现的问题解析】教程文章相关的互联网学习教程文章

解析MySQL binlog --（4）TABLE_MAP_EVENT【代码】【图】

关于MySQL DNS解析探究之一：DNS解析确实在起作用【代码】

解析Mysql备份与恢复简单总结与tee命令的使用介绍

MySQL 选择数据库：数据库选择语法解析【代码】

MySQL的sql解析【代码】【图】

mysql invalid json value 以及传到前端的json因为多了反斜杠导致无法解析的问题【代码】

不使用MySQL数据库的五个给力理由解析

解析mysql中如何获得数据库的大小

（转）MySQL join语法解析与性能分析【代码】【图】

mysql导入导出命令解析【代码】

mysql交互协议解析——mysql包基础数据、mysql包基本格式

MySQL的sql_mode解析与设置，sql文件导入报错解决【图】

修改mysql默认字符集的两种方法详细解析

MySQL插入语句解析【代码】

MySQL执行计划explain的key_len解析

MYSQL - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程