canal 笔记

(1) 配置MySQL

(1.1) 修改MySQL配置文件

1
2
3
4
[mysqld]  
log-bin=mysql-bin
binlog-format=ROW
server_id=1

(1.2) MySQL添加对应用户

1
2
3
4
5
6

CREATE USER canal IDENTIFIED BY 'canal';

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';

FLUSH PRIVILEGES;

(1.3) 校验用户对应权限

1、 show master status ;
如果正常显示binlog,则没问题,如果提示 Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation ,则没有对应 REPLICATION CLIENT 权限

2、 show slave status ;
如果提示 Access denied; you need (at least one of) the REPLICATION SLAVE privilege(s) for this operation ,则没有对应 REPLICATION SLAVE 权限

(2) 启动canal

执行 ./bin/startup.sh 即可启动

(2.1) canal启动时canal.log

1
2
3
4
5
6
7
8
2018-07-23 20:27:47.576 [main] INFO  com.alibaba.otter.canal.deployer.CanalLauncher - ## the canal server is running now ......
2018-07-23 20:27:47.721 [canal-instance-scan-0] INFO c.a.o.canal.deployer.monitor.SpringInstanceConfigMonitor - auto notify start xxx1 successful.
2018-07-23 20:27:47.802 [canal-instance-scan-0] INFO c.a.o.canal.deployer.monitor.SpringInstanceConfigMonitor - auto notify start xxx2 successful.
2018-07-23 20:27:47.862 [canal-instance-scan-0] INFO c.a.o.canal.deployer.monitor.SpringInstanceConfigMonitor - auto notify start xxx3 successful.
2018-07-23 20:27:47.921 [canal-instance-scan-0] INFO c.a.o.canal.deployer.monitor.SpringInstanceConfigMonitor - auto notify start xxx4 successful.
2018-07-23 20:27:47.987 [canal-instance-scan-0] INFO c.a.o.canal.deployer.monitor.SpringInstanceConfigMonitor - auto notify start xxx5 successful.
2018-07-23 20:27:48.044 [canal-instance-scan-0] INFO c.a.o.canal.deployer.monitor.SpringInstanceConfigMonitor - auto notify start xxx6 successful.
2018-07-23 20:27:48.094 [canal-instance-scan-0] INFO c.a.o.canal.deployer.monitor.SpringInstanceConfigMonitor - auto notify start xxx7 successful.

(2.2) canal正常启动时对应的日志

1
2
3
4
5
6
2018-07-23 20:27:47.429 [canal-instance-scan-0] INFO  c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [canal.properties]
2018-07-23 20:27:47.436 [canal-instance-scan-0] INFO c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [xxx/instance.properties]
2018-07-23 20:27:47.444 [canal-instance-scan-0] WARN org.springframework.beans.TypeConverterDelegate - PropertyEditor [com.sun.beans.editors.EnumEditor] found through deprecated global PropertyEditorManager fallback - consider using a more isolated form of registration, e.g. on the BeanWrapper/BeanFactory!
2018-07-23 20:27:47.451 [canal-instance-scan-0] INFO c.a.otter.canal.instance.spring.CanalInstanceWithSpring - start CannalInstance for 1-xxx
2018-07-23 20:27:47.453 [canal-instance-scan-0] INFO c.a.otter.canal.instance.core.AbstractCanalInstance - start successful....
2018-07-23 20:27:47.666 [destination = xxx , address = /127.0.0.1:3306 , EventParser] WARN c.a.otter.canal.parse.inbound.mysql.MysqlEventParser - prepare to find start position just show master status

(3) canal复制原理

复制如何工作,整体上来说,复制有3个步骤:

(1) master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件,binary log events);

(2) slave将master的binary log events复制到它的中继日志(relay log)中;

(3) slave读取中继日志中的事件,将其重放到备库数据之上。

下图描述了复制的过程:

canal复制

(4) 遇到的问题

(4.1) Error When doing Client Authentication:ErrorPacket

1
2
3
4
5
6
7
Caused by: java.io.IOException: connect /127.0.0.1:3306 failure:java.io.IOException: Error When doing Client Authentication:ErrorPacket [errorNumber=1045, fieldCount=-1, message=Access denied for user 'canal'@'localhost' (using password: YES), sqlState=28000, sqlStateMarker=#]
at com.alibaba.otter.canal.parse.driver.mysql.MysqlConnector.negotiate(MysqlConnector.java:208)
at com.alibaba.otter.canal.parse.driver.mysql.MysqlConnector.connect(MysqlConnector.java:71)
at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.connect(MysqlConnection.java:56)
at com.alibaba.otter.canal.parse.inbound.mysql.MysqlEventParser.preDump(MysqlEventParser.java:86)
at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:157)
at java.lang.Thread.run(Thread.java:748)

1、 用户名密码不正确

(4.2) Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation

1
2
3
4
ERROR com.alibaba.otter.canal.common.alarm.LogAlarmHandler - destination:xx
[com.alibaba.otter.canal.parse.exception.CanalParseException: command : 'show master status' has an error!
Caused by: java.io.IOException: ErrorPacket [errorNumber=1227, fieldCount=-1, message=Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation, sqlState=42000, sqlStateMarker=#]
with command: show master status

用canal账户登录后发现可以查看对应数据库对应表的数据,但是 show master status 提示 Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation

1、instance.properties配置文件里配置的用户没有REPLICATION权限
2、canal instance.properties 配置错误
3、配置文件里用户名密码不正确
4、MySQL对应用户不存在
5、MySQL配置不对

给canal用户对应的replication权限
grant replication client on *.* to 'canal'@'%';
flush privileges

(4.3) Access denied; you need (at least one of) the REPLICATION SLAVE privilege(s) for this operation

1
2
[destination = xxx , address = /127.0.0.1:3306 , EventParser] ERROR com.alibaba.otter.canal.common.alarm.LogAlarmHandler - destination:xx[java.io.IOException: Received error packet: errno = 1227, sqlstate = 42000 errmsg = Access denied; you need (at least one of) the REPLICATION SLAVE privilege(s) for this operation
at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:95)

REPLICATION SLAVE 常用于建立复制时所需要用到的用户权限,也就是slave server必须被master server授权具有该权限的用户,才能通过该用户复制。
并且”SHOW SLAVE HOSTS”这条命令和REPLICATION SLAVE权限有关,否则执行时会报错:

REPLICATION CLIENT 不可用于建立复制,有该权限时,只是多了可以使用如”SHOW SLAVE STATUS”、”SHOW MASTER STATUS”等命令。
在5.6.6版本以后,也可以使用”SHOW BINARY LOGS”。

权限问题
GRANT REPLICATION SLAVE ON *.* TO 'canal'@'%'
flush privileges

References

[1] canal-AdminGuide
[2] canal配置使用
[3] Mysql 普通账户授权replication client后登录失败问题
[4] REPLICATION SLAVE 与 REPLICATION CLIENT 权限
[5] 对replication slave,replication client的一点说明
[6] MySQL 5.6 Reference Manual – 6.2.1 Privileges Provided by MySQL