​

A,Canal and Maxwell contrast their choices

Canal (a middleware component of Ali)

Github.com/alibaba/can… (Server side, need a client to cooperate)

Github.com/Hackeruncle… (Client)

Maxwell (written by a foreigner, open source)

Github.com/zendesck/ma…

Liverpoolfc.tv: maxwells – daemon. IO

Scenario: Data in the MySQL database is stored in HBase or Kudu after a series of operations

For example: MySQL–> middleware –>kafka—–>… —–> Store HBase or Kudu, etc. Data migration involves both full (historical) and incremental data, and full data requires Bootstrap.

Today’s introduction is middleware:

  1. The following comparison is from an article on bigdatadecode.club/, but there are some corrections to be made, highlighted in red
Canal(server) Maxwell(Server + Client)
language Java Java
active active active
HA support Custom, but breakpoint restoration is supported
Data to the ground custom Be born to Kafka
partition support support
The Bootstrap (guide) Does not support support
The data format Free format Json (fixed format) Spark Json –> DF
The document More detailed More detailed
Random read support support

2. Maxwell is recommended

Cause: a. Lightweight server and client

B. Support: Breakpoint recovery + Bootstrap + JSON

C. Maxwell is more active than Canal

Maxwell’s original design idea was MySQL+Kafka

​

Function:

  • can do SELECT * from table(bootstrapping) initial loads of a table.
  • supports automatic position recover on master promotion.
  • flexible partitioning schemes for kafka – by databse,table,primary key,or column.
  • maxwell pulls all this off by acting as a full mysql replica,including a SQL parser for create/alter/drop statements (nope,there was no other way).

Maxwell Installation and Deployment & Use cases

(1) MySQL deployment, here will not be repeated, I have in the previous document.

Check whether binlog_format is ROW, server_id is 1, log_bin is ON.

​

​

If you do not change the value of Maxwell, an error occurs when you start Maxwell:

​

​

If not, modify:

vim /etc/my.cnf
[mysql_d]
server_id=1
log-bin=master
binlog_format=row
Copy the code

MySQL > alter database;

service mysqld restart
Copy the code

(2) Maxwell deployment: refer to maxwells-daemon. IO /quickstart/ for a simple explanation

Maxwell’s download:

Github.com/zendesk/max…

Extract:

Tar -zxvf maxwell-1.25.1.tar.gz -c /opt/module/maxwell/Copy the code

​

Create Maxwell’s DB and user

mysql> CREATE database maxwell;
mysql> CREATE USER 'maxwell'@'%' IDENTIFIED BY 'maxwell';
mysql> GRANT ALL ON maxwell.* TO 'maxwell'@'%';
mysql> GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'maxwell'@'%';
mysql> flush privileges;
Copy the code

​

Create a table and insert test data:

​

Start the Maxwell:

[root@node4 maxwell-1.25.1]# bin/maxwell --user='maxwell' --password='maxwell' --host='127.0.0.1' --producer=stdout
Copy the code

​

Insert a piece of data:

mysql> use zyg_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into user_info (name,age,address) values ('zyg',20,'www.google.com');
Query OK, 1 row affected (0.00 sec)
Copy the code

​

Maxwell displays:

​

{
    "database": "zyg_test",
    "table": "user_info",
    "type": "insert",
    "ts": 1587893666,
    "xid": 699,
    "commit": true,
    "data": {
        "name": "zyg",
        "age": 20,
        "address": "www.google.com"
    }
}
Copy the code

Analysis: use online json editor tool www.bejson.com/jsoneditoro…

​

Update the data:

mysql> update user_info set age = 19 where name= 'allen'; Query OK, 1 row affected (0.01sec) Rows matched: 1 Changed: 1 Warnings: 0Copy the code

​

{
    "database": "zyg_test",
    "table": "user_info",
    "type": "update",
    "ts": 1587894286,
    "xid": 1507,
    "commit": true,
    "data": {
        "name": "allen",
        "age": 19,
        "address": "www.baibu.com"
    },
    "old": {
        "age": 18
    }
}
Copy the code

​

Maxwells-daemon. IO /config/

1. Kafka version problems

​

The Kafka version cannot be modified in the configuration file. Maxwell 1.25.1 is used by default

Using kafka version: 1.0.0

​

You need to add the locally installed version of Kafka at runtime:

Bin /maxwell --user='maxwell' --password='XXXXXX' --host='127.0.0.1' \ --producer=kafka --kafka.bootstrap.servers=localhost:9092 --kafka_topic=Maxwell kafka_version = xxxCopy the code

  1. Case problem:

Mysql is case insensitive, but Maxwell is case sensitive when filtering

include_dbs = test

include_tables = USER_INFO

My MySQL table: test.user_info will not be filtered

  1. Maxwell Indicates the location where data synchronization starts

​

If the MySQL binlog’s pos position reaches 100000, Maxwell wants to specify that the pos position is 999. If you do not specify init_position, the default is 100000.

​