canal+kafka同步数据库说明

最近使用canal+kafka同步数据库的数据，在此记录一下配置修改

确保mysql开启bin-log，模式为ROW；

创建canal用户： CREATE USER canal IDENTIFIED BY ‘1qaz@WSX’; GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON . TO ‘canal’@’%’; FLUSH PRIVILEGES; ALTER USER ‘canal’@’%’ IDENTIFIED WITH mysql_native_password BY ‘1qaz@WSX@’;

修改canal.properties： 去掉canal.instance.parser.parallelThreadSize = 16的配置项注释 canal.serverMode=kafka canal.mq.servers的值需要指定为Kafka服务或者集群Broker的地址

修改instance.properties 配置canal.instance.mysql.slaveId，注意：需要配置一个和Master节点的服务ID完全不同的值 配置canal.instance.master.address为需要被同步数据的数据库地址，如：127.0.0.1:3306 配置canal.instance.dbUsername=canal 配置canal.instance.dbPassword=1qaz@WSX 新增canal.instance.defaultDatabaseName配置，值为同步数据的数据库名称 配置canal.mq.topic，指向一个静态topic，如：test 配置canal.mq.partition=0

附：数据格式

// 建库脚本 {“data”:null,“database”:"test",“es”:1583143732000,“id”:1,“isDdl”:false,“mysqlType”:null,“old”:null,“pkNames”:null,“sql”:“CREATE DATABASE test CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci“,“sqlType”:null,“table”:””,“ts”:1583143930177,“type”:“QUERY”}

// 建表 {“data”:null,“database”:“test”,“es”:1583143957000,“id”:2,“isDdl”:true,“mysqlType”:null,“old”:null,“pkNames”:null,“sql”:“CREATE TABLE order\n(\n id BIGINT UNIQUE PRIMARY KEY AUTO_INCREMENT COMMENT ‘主键’,\n order_id VARCHAR(64) NOT NULL COMMENT ‘订单ID’,\n amount DECIMAL(10, 2) NOT NULL DEFAULT 0 COMMENT ‘订单金额’,\n create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,\n UNIQUE uniq_order_id (order_id)\n) COMMENT ‘订单表’”,“sqlType”:null,“table”:“order”,“ts”:1583143958045,“type”:“CREATE”}

// INSERT {“data”:[{“id”:“1”,“order_id”:“10086”,“amount”:“999.0”,“create_time”:“2020-03-02 05:12:49”}],“database”:“test”,“es”:1583143969000,“id”:3,“isDdl”:false,“mysqlType”:{“id”:“BIGINT”,“order_id”:“VARCHAR(64)”,“amount”:“DECIMAL(10,2)”,“create_time”:“DATETIME”},“old”:null,“pkNames”:[“id”],“sql”:"",“sqlType”:{“id”:-5,“order_id”:12,“amount”:3,“create_time”:93},“table”:“order”,“ts”:1583143969460,“type”:“INSERT”}

// UPDATE {“data”:[{“id”:“1”,“order_id”:“10086”,“amount”:“10087.0”,“create_time”:“2020-03-02 05:12:49”}],“database”:“test”,“es”:1583143974000,“id”:4,“isDdl”:false,“mysqlType”:{“id”:“BIGINT”,“order_id”:“VARCHAR(64)”,“amount”:“DECIMAL(10,2)”,“create_time”:“DATETIME”},“old”:[{“amount”:“999.0”}],“pkNames”:[“id”],“sql”:"",“sqlType”:{“id”:-5,“order_id”:12,“amount”:3,“create_time”:93},“table”:“order”,“ts”:1583143974870,“type”:“UPDATE”}

// DELETE {“data”:[{“id”:“1”,“order_id”:“10086”,“amount”:“10087.0”,“create_time”:“2020-03-02 05:12:49”}],“database”:“test”,“es”:1583143980000,“id”:5,“isDdl”:false,“mysqlType”:{“id”:“BIGINT”,“order_id”:“VARCHAR(64)”,“amount”:“DECIMAL(10,2)”,“create_time”:“DATETIME”},“old”:null,“pkNames”:[“id”],“sql”:"",“sqlType”:{“id”:-5,“order_id”:12,“amount”:3,“create_time”:93},“table”:“order”,“ts”:1583143981091,“type”:“DELETE”}