canal和maxwell的安装配置及区别

亦凉 2022-12-02 13:38 239阅读 0赞

## 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**

## 1、canal的单机配置 ##

### 1.1 基本配置 ###

**canal.properties**

![format_png][]

![format_png 1][]

**conf/example/instance.properties**

![format_png 2][]

![format_png 3][]

![format_png 4][]

### 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 多分区配置 ###

![format_png 5][]

![format_png 6][]

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

![format_png 7][]

![format_png 8][]

![format_png 9][]

## 2、maxwell的配置 ##

### 2.1 基本配置 ###

复制config.properties.example为maxwell.properties

![format_png 10][]

### 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 多分区配置 ###

![format_png 11][]

![format_png 12][]

![format_png 13][]

## 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`\r\nMODIFY 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更简洁。

[format_png]: /images/20221123/5fc662ad8f9d4a66929a432a3dce8eaf.png
[format_png 1]: /images/20221123/f7a7c8a2820043288124d691396d2c36.png
[format_png 2]: /images/20221123/7a43b6fd503342d0a3e134f5e2c640be.png
[format_png 3]: /images/20221123/8fcde67fc67248bba3ac2b0cb988f76a.png
[format_png 4]: /images/20221123/763f0b7ce1f44140b4f55e8ca4f37d1a.png
[format_png 5]: /images/20221123/cef8d40680664842b1173f52c86b605f.png
[format_png 6]: /images/20221123/170afb3304f2418191564a531b1da4bd.png
[format_png 7]: /images/20221123/0d2f6ecdacb743a2b0d8051531832c34.png
[format_png 8]: /images/20221123/bd9ff81731de45cba0c8b693121bbfbf.png
[format_png 9]: /images/20221123/81866420c86946a48607ec583a0ca4d8.png
[format_png 10]: /images/20221123/2080998b05f54b3893aa00f1ea0fd760.png
[format_png 11]: /images/20221123/47394c1e919948d8901435542d41c281.png
[format_png 12]: /images/20221123/5e4445c9cc314045bc124c2c16d72f5a.png
[format_png 13]: /images/20221123/acaaca41f34741c3939a3e24cf1bd4b1.png