mysql_CDC方法maxwell基本使用和配置

maxwell 读取binlog

参考网址

docker pull zendesk/maxwell

在docker的镜像是zendesk/maxwell:latest
lmages_id:824058dff591

docker 下创建容器mysql_cdc 3123：3306

配置参数文件

进入容器 vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
server-id=1
log-bin=master
binlog_format=row

授予maxwell权限，如果maxwell用户不存在，会自动创建。

mysql> GRANT ALL on maxwell.* to 'maxwell'@'%' identified by 'XXXXXX';

mysql> GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE on . to 'maxwell'@'%';

启动maxwell

docker run -it --rm zendesk/maxwell bin/maxwell --user='maxwell' --password='XXXXXX' --host=172.17.0.5 --producer=stdout

07:11:43,445 INFO BinlogConnectorLifecycleListener - Binlog connected.

测试数据与输出

相关字段解释

• json字段解释
  database -- the database name
  table -- the table name
  type -- this can be "insert", "update", or "delete". In all 3 event types cases, the JSON stream will include a full row. If the row is a delete, Maxwell will output the last value of the row before it was deleted.
  ts -- the timestamp the row was stored in the binlog.
  data -- the good stuff.

• create db-----
  07:12:21,876 INFO AbstractSchemaStore - storing schema @Position[BinlogPosition[master.000001:29604], lastHeartbeat=1511766736046] after applying "create database testduliyan" to testduliyan, new schema id is 2

• create table ------
  07:13:13,080 INFO AbstractSchemaStore - storing schema @Position[BinlogPosition[master.000001:34197], lastHeartbeat=1511766788077] after applying "create table duliyan(id int , name varchar(50), mark varchar(50))" to testduliyan, new schema id is 3

• insert table ---

• {"database":"testduliyan","table":"duliyan","type":"insert","ts":1511768435,"xid":1045,"commit":true,"data":{"id":1,"name":"aa","mark":"aa"}} {"database":"testduliyan","table":"duliyan","type":"insert","ts":1511768546,"xid":1084,"commit":true,"data":{"id":2,"name":"bb","mark":"bb"}}

• update table ---update duliyan set name ='cc' where id =2;
  {"database":"testduliyan","table":"duliyan","type":"update","ts":1511768575,"xid":1094,"commit":true,"data":{"id":2,"name":"cc","mark":"bb"},"old":{"name":"bb"}}

• update duliyan set name ='ll' where id =2 and name ='cc';
  {"database":"testduliyan","table":"duliyan","type":"update","ts":1511772519,"xid":2301,"commit":true,"data":{"id":2,"name":"ll","mark":"bb"},"old":{"name":"cc"}}

• delete table ----
  {"database":"testduliyan","table":"duliyan","type":"delete","ts":1511768728,"xid":1151,"commit":true,"data":{"id":4,"name":"ee","mark":"ee"}}

rowid
mysql不存在类似与oracle的rowid伪列

开发者介绍

maxwell导出信息到rabbitMQ

• 创建容器docker 镜像需要management版本
  docker run -d --hostname my-rabbit --name some-rabbit -p 1234:15672 rabbitmq:3.6.10-management

• 然后在浏览器登陆http://10.0.0.96:1234/#/ userguest：password guest

修改用户名和密码
docker run -d --hostname my-rabbit --name some-rabbit -e RABBITMQ_DEFAULT_USER=user -e RABBITMQ_DEFAULT_PASS=password rabbitmq:3.6.10-management

• docker start af2 --启动rabbitmq

• docker exec -it af2 /bin/bash

• apt-get update

• apt-get install net-tools

• ifconfig

• 在主机 ping 172.17.0.8

• telnet 172.17.0.8 15672

• maxwell 启动
  docker run -it --rm zendesk/maxwell bin/maxwell --user='maxwell' --password='XXXXXX' --host=172.17.0.5 --producer=rabbitmq --rabbitmq_host=172.17.0.8

• 出现如下内容启动成功
  01:26:24,694 INFO BinlogConnectorReplicator - Setting initial binlog pos to: master.000001:476435173
  01:26:24,832 INFO BinaryLogClient - Connected to 172.17.0.5:3306 at master.000001/476435173 (sid:6379, cid:130)
  01:26:24,832 INFO BinlogConnectorLifecycleListener - Binlog connected.

• 在http://10.0.0.96:1234/#/queues 中查看duilie消息

maxwell required

JRE 7 or above
mysql 5.1, 5.5, 5.6, 5.7
kafka 0.8.2 or greater

maxwell最新版v1.10.9已经支持5.7的JSON数据类型，也支持GTID

v1.7.0: "lucky me, lucky mud"
Maxwell 1.7 brings 2 major new, alpha features. The first is Mysql 5.7 support, including JSON column type support and handling of 5.7 SQL, but not including GTID support yet. This is based on porting Maxwell to Stanley Shyko's binlog-connector library.
v1.8.0: "upbeat, honest, contradictory"
In version 1.8.0 Maxwell gains alpha support for GTID-based positions!