canal和maxwell的安装配置及区别

Canal地址:https://github.com/alibaba/canal

Maxwell地址:https://github.com/zendesk/maxwell

Maxwell官网地址:http://maxwells-daemon.io/

0、mysql开启binglog

在mysql的配置文件(Linux: /etc/my.cnf , Windows: my.ini)下,修改配置

在[mysqld] 区块（没有就创建）

设置/添加

（1）log-bin=mysql-bin

这个表示binlog日志的前缀是mysql-bin ，以后生成的日志文件就是 mysql-bin.123456 的文件后面的数字按顺序生成。每次mysql重启或者到达单个文件大小的阈值时，新生一个文件，按顺序编号。

（2）binlog_format=row

mysql binlog的格式，那就是有三种，分别是STATEMENT,MIXED,ROW

1） statement
语句级，binlog会记录每次一执行写操作的语句。
相对row模式节省空间，但是可能产生不一致性，比如
update tt set create_date=now()
如果用binlog日志进行恢复，由于执行时间不同可能产生的数据就不同。
优点：节省空间
缺点：有可能造成数据不一致。
2） row
行级， binlog会记录每次操作后每行记录的变化。
优点：保持数据的绝对一致性。因为不管sql是什么，引用了什么函数，他只记录执行后的效果。
缺点：占用较大空间。

3） mixed
statement的升级版，一定程度上解决了，因为一些情况而造成的statement模式不一致问题
在某些情况下譬如：
1. 当函数中包含 UUID() 时；
2. 包含 AUTO_INCREMENT 字段的表被更新时；
3. 执行 INSERT DELAYED 语句时；
4. 用 UDF 时；
会按照 ROW的方式进行处理
优点：节省空间，同时兼顾了一定的一致性。
缺点：还有些极个别情况依旧会造成不一致，另外statement和mixed对于需要对binlog的监控的情况都不方便。

（3）binlog-do-db=test_db

要监听的数据库

（4）server-id=1

唯一ID

（5）重启mysql

（6）查看binlog

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

1、canal的单机配置

1.1 基本配置

canal.properties

conf/example/instance.properties

1.2 mysql创建用户赋予权限

mysql> CREATE USER canal IDENTIFIED BY 'canal'; 
mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
mysql> FLUSH PRIVILEGES; 
mysql> show grants for 'canal' ;
+----------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for canal@%                                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%' IDENTIFIED BY PASSWORD '*E3619321C1A937C46A0D8BD1DAC39F93B27D4458' |
+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

给的权限只有，查，slave，和客户端，不允许增删等操作

1.3 多分区配置

所有库，所有表，按主键分区

2、maxwell的配置

2.1 基本配置

复制config.properties.example为maxwell.properties

2.2 mysql创建用户赋予权限

mysql> CREATE DATABASE maxwell ;
mysql> GRANT ALL   ON maxwell.* TO 'maxwell'@'%' IDENTIFIED BY 'maxwell';
mysql> GRANT  SELECT ,REPLICATION SLAVE , REPLICATION CLIENT  ON *.* TO maxwell@'%'
mysql> FLUSH PRIVILEGES;

2.3 多分区配置

2.4 maxwell-bootstrap

针对数据初始化的问题，Maxwell 提供了一个命令工具 maxwell-bootstrap 帮助我们完成数据初始化，maxwell-bootstrap 是基于 SELECT * FROM table 的方式进行全量数据初始化，不会产生多余的binlog！

参数	说明
–log_level LOG_LEVEL	日志级别（DEBUG, INFO, WARN or ERROR）
–user USER	mysql 用户名
–password PASSWORD	mysql 密码
–host HOST	mysql 地址
–port PORT	mysql 端口
–database DATABASE	要bootstrap的表所在的数据库
–table TABLE	要引导的表
–where WHERE_CLAUSE	设置过滤条件
–client_id CLIENT_ID	指定执行引导操作的Maxwell实例

bin/maxwell-bootstrap --user maxwell --password 123456 --host 127.0.0.1 --database test --table test --client_id maxwell

其中client_id 是指另一个已启动的maxwell监控进程的client_id

注意：--bootstrapper=sync 时，在处理bootstrap时，会阻塞正常的binlog解析；--bootstrapper=async 时，不会阻塞。

3、canal和maxwell对比

3.1 执行insert操作

insert into student values(4,"张三",100),(5,"李思",80),(6,"王五",50)

canal

{"data":null,"database":"gmall","es":1598770894000,"id":6439,"isDdl":true,"mysqlType":null,"old":null,"pkNames":null,"sql":"ALTER TABLE `student`
MODIFY COLUMN `id`  int(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST","sqlType":null,"table":"student","ts":1598770895098,"type":"ALTER"}
{"data":[{"id":"4","name":"张三","age":"100"}],"database":"gmall","es":1598770932000,"id":6440,"isDdl":false,"mysqlType":{"id":"int(11) unsigned","name":"varchar(20)","age":"int"},"old":null,"pkNames":["id"],"sql":"","sqlType":{"id":4,"name":12,"age":4},"table":"student","ts":1598770932288,"type":"INSERT"}
{"data":[{"id":"5","name":"李思","age":"80"}],"database":"gmall","es":1598770932000,"id":6440,"isDdl":false,"mysqlType":{"id":"int(11) unsigned","name":"varchar(20)","age":"int"},"old":null,"pkNames":["id"],"sql":"","sqlType":{"id":4,"name":12,"age":4},"table":"student","ts":1598770932288,"type":"INSERT"}
{"data":[{"id":"6","name":"王五","age":"50"}],"database":"gmall","es":1598770932000,"id":6440,"isDdl":false,"mysqlType":{"id":"int(11) unsigned","name":"varchar(20)","age":"int"},"old":null,"pkNames":["id"],"sql":"","sqlType":{"id":4,"name":12,"age":4},"table":"student","ts":1598770932288,"type":"INSERT"}

maxwell更简洁

{"database":"gmall","table":"student","type":"insert","ts":1598770932,"xid":261875,"commit":true,"data":{"id":6,"name":"王五","age":50}}
{"database":"gmall","table":"student","type":"insert","ts":1598770932,"xid":261875,"xoffset":1,"data":{"id":5,"name":"李思","age":80}}
{"database":"gmall","table":"student","type":"insert","ts":1598770932,"xid":261875,"xoffset":0,"data":{"id":4,"name":"张三","age":100}}

3.2 执行update操作

UPDATE student SET age='88' WHERE id IN(5,4)

maxwell

{"database":"gmall","table":"student","type":"update","ts":1598771585,"xid":270573,"xoffset":0,"data":{"id":4,"name":"张三","age":88},"old":{"age":100}}
{"database":"gmall","table":"student","type":"update","ts":1598771585,"xid":270573,"commit":true,"data":{"id":5,"name":"李思","age":88},"old":{"age":80}}

canal

{"data":[{"id":"4","name":"张三","age":"88"}],"database":"gmall","es":1598771585000,"id":6444,"isDdl":false,"mysqlType":{"id":"int(11) unsigned","name":"varchar(20)","age":"int"},"old":[{"age":"100"}],"pkNames":["id"],"sql":"","sqlType":{"id":4,"name":12,"age":4},"table":"student","ts":1598771585168,"type":"UPDATE"}
{"data":[{"id":"5","name":"李思","age":"88"}],"database":"gmall","es":1598771585000,"id":6444,"isDdl":false,"mysqlType":{"id":"int(11) unsigned","name":"varchar(20)","age":"int"},"old":[{"age":"80"}],"pkNames":["id"],"sql":"","sqlType":{"id":4,"name":12,"age":4},"table":"student","ts":1598771585168,"type":"UPDATE"}

3.3 执行delete操作

maxwell

{"database":"gmall","table":"student","type":"delete","ts":1598771670,"xid":271637,"xoffset":0,"data":{"id":4,"name":"张三","age":88}}
{"database":"gmall","table":"student","type":"delete","ts":1598771670,"xid":271637,"commit":true,"data":{"id":5,"name":"李思","age":88}}

canal

{"data":[{"id":"5","name":"李思","age":"88"}],"database":"gmall","es":1598771670000,"id":6445,"isDdl":false,"mysqlType":{"id":"int(11) unsigned","name":"varchar(20)","age":"int"},"old":null,"pkNames":["id"],"sql":"","sqlType":{"id":4,"name":12,"age":4},"table":"student","ts":1598771670306,"type":"DELETE"}
{"data":[{"id":"4","name":"张三","age":"88"}],"database":"gmall","es":1598771670000,"id":6445,"isDdl":false,"mysqlType":{"id":"int(11) unsigned","name":"varchar(20)","age":"int"},"old":null,"pkNames":["id"],"sql":"","sqlType":{"id":4,"name":12,"age":4},"table":"student","ts":1598771670306,"type":"DELETE"}

3.4 对比

Maxwell 没有 Canal那种server+client模式，只有一个server把数据发送到消息队列或redis。

Maxwell 有一个亮点功能，就是Canal只能抓取最新数据，对已存在的历史数据没有办法处理。而Maxwell有一个bootstrap功能，可以直接引导出完整的历史数据用于初始化，非常好用。

Maxwell不能直接支持HA，但是它支持断点还原，即错误解决后重启继续上次点儿读取数据。

Maxwell只支持json格式，而Canal如果用Server+client模式的话，可以自定义格式。

Maxwell比Canal更加轻量级。

3.5 总结

日志结构

canal 每一条SQL会产生一条日志，如果该条Sql影响了多行数据，则已经会通过集合的方式归集在这条日志中。（即使是一条数据也会是数组结构）

maxwell 以影响的数据为单位产生日志，即每影响一条数据就会产生一条日志。如果想知道这些日志是否是通过某一条sql产生的可以通过xid进行判断，相同的xid的日志来自同一sql。

数字类型

当原始数据是数字类型时,maxwell会尊重原始数据的类型不增加双引，变为字符串。

canal一律转换为字符串。

带原始数据字段定义

canal数据中会带入表结构。

maxwell更简洁。