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更简洁。