canal实现缓存一致性
1、windows mysql安装（额外整理）
从 https://downloads.mysql.com/archives/community/ 下载安装包
本次下载 mysql-5.7.25-winx64.zip
以管理员身份（否则会出错）打开cmd，
切换目录到解压目录
D:\workSpace\mysql-5.7.25-winx64下，
分别执行：
# 指定默认启动配置文件
mkdir data
进入D:\workSpace\mysql-5.7.25-winx64\bin
mysqld.exe --install mysql --defaults-file="D:\workSpace\mysql-5.7.25-winx64\my.ini"
net start mysql
后续可使用net start/stop mysql
维护mysql
执行
mysql -u root -p
免密首次登陆mysql，并设置对应mysql密码格式，设置响应账号密码
my.ini参考
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir= D:\workSpace\mysql-5.7.25-winx64
# 设置mysql数据库的数据的存放目录
datadir= D:\workSpace\mysql-5.7.25-winx64\data
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
server-id=1
# 经确认,配置是生效的，但这个指定目录不生效，不重要，故不深究
log-bin= D:\workSpace\mysql-5.7.25-winx64\mysql-bin
expire-logs-days = 14
max-binlog-size = 512M
2、canal安装
官方git
https://github.com/alibaba/canal
下载地址
https://github.com/alibaba/canal/releases
github下载加速器
两个github下载中间站，速度很nice，分享一波
https://www.offcloud.com/
https://toolwa.com/github/
授权mysql账号
前文指定配置文件已经开启了mysql bin-log，如果使用的阿里商业库，默认是开启bin-log的。
注意Bin-log的级别需要为row。
创建mysql账号用户canal使用：
CREATE USER canal IDENTIFIED BY 'canal';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
以下是canal详细配置详解
canal基础使用核心有两个配置文件
canal\conf\canal.properties
canal核心服务配置，如端口，bin-log同步形式等，另还有canal-admin相关配置
canal\conf\example\canal.properties
canal相关监听配置，如数据库、过滤、数据库连接等等
canal.properties
转载一个
https://blog.csdn.net/liujun19921020/article/details/100575195
instance.properties
再转载一个
https://blog.csdn.net/liujun19921020/article/details/100575231
附两份配置参考(rocketMq消费消息处理)：
canal.properties
#################################################
######### common argument #############
#################################################
# tcp bind ip
canal.ip =
# register ip to zookeeper
canal.register.ip =
canal.port = 11111
canal.metrics.pull.port = 11112
# canal instance user/passwd
# canal.user = canal
# canal.passwd = E3619321C1A937C46A0D8BD1DAC39F93B27D4458
# canal admin config
#canal.admin.manager = 127.0.0.1:8089
canal.admin.port = 11110
canal.admin.user = admin
canal.admin.passwd = 123456
canal.zkServers =
# flush data to zk
canal.zookeeper.flush.period = 1000
canal.withoutNetty = false
# tcp, kafka, RocketMQ
canal.serverMode = RocketMQ
# flush meta cursor/parse position to file
canal.file.data.dir = ${canal.conf.dir}
canal.file.flush.period = 1000
## memory store RingBuffer size, should be Math.pow(2,n)
canal.instance.memory.buffer.size = 16384
## memory store RingBuffer used memory unit size , default 1kb
canal.instance.memory.buffer.memunit = 1024
## meory store gets mode used MEMSIZE or ITEMSIZE
canal.instance.memory.batch.mode = MEMSIZE
canal.instance.memory.rawEntry = true
## detecing config
canal.instance.detecting.enable = false
#canal.instance.detecting.sql = insert into retl.xdual values(1,now()) on duplicate key update x=now()
canal.instance.detecting.sql = select 1
canal.instance.detecting.interval.time = 3
canal.instance.detecting.retry.threshold = 3
canal.instance.detecting.heartbeatHaEnable = false
# support maximum transaction size, more than the size of the transaction will be cut into multiple transactions delivery
canal.instance.transaction.size = 1024
# mysql fallback connected to new master should fallback times
canal.instance.fallbackIntervalInSeconds = 60
# network config
canal.instance.network.receiveBufferSize = 16384
canal.instance.network.sendBufferSize = 16384
canal.instance.network.soTimeout = 30
# binlog filter config
canal.instance.filter.druid.ddl = true
canal.instance.filter.query.dcl = false
canal.instance.filter.query.dml = false
canal.instance.filter.query.ddl = false
canal.instance.filter.table.error = false
canal.instance.filter.rows = false
canal.instance.filter.transaction.entry = false
# binlog format/image check
canal.instance.binlog.format = ROW,STATEMENT,MIXED
canal.instance.binlog.image = FULL,MINIMAL,NOBLOB
# binlog ddl isolation
canal.instance.get.ddl.isolation = false
# parallel parser config
canal.instance.parser.parallel = true
## concurrent thread number, default 60% available processors, suggest not to exceed Runtime.getRuntime().availableProcessors()
#canal.instance.parser.parallelThreadSize = 16
## disruptor ringbuffer size, must be power of 2
canal.instance.parser.parallelBufferSize = 256
# table meta tsdb info
canal.instance.tsdb.enable = true
canal.instance.tsdb.dir = ${canal.file.data.dir:../conf}/${canal.instance.destination:}
canal.instance.tsdb.url = jdbc:h2:${canal.instance.tsdb.dir}/h2;CACHE_SIZE=1000;MODE=MYSQL;
canal.instance.tsdb.dbUsername = canal
canal.instance.tsdb.dbPassword = canal
# dump snapshot interval, default 24 hour
canal.instance.tsdb.snapshot.interval = 24
# purge snapshot expire , default 360 hour(15 days)
canal.instance.tsdb.snapshot.expire = 360
# aliyun ak/sk , support rds/mq
#此处有发生修改
canal.aliyun.accessKey = ****
canal.aliyun.secretKey = ****
#################################################
######### destinations #############
#################################################
canal.destinations = example
# conf root dir
canal.conf.dir = ../conf
# auto scan instance dir add/remove and start/stop instance
canal.auto.scan = true
canal.auto.scan.interval = 5
canal.instance.tsdb.spring.xml = classpath:spring/tsdb/h2-tsdb.xml
#canal.instance.tsdb.spring.xml = classpath:spring/tsdb/mysql-tsdb.xml
canal.instance.global.mode = spring
canal.instance.global.lazy = false
canal.instance.global.manager.address = ${canal.admin.manager}
#canal.instance.global.spring.xml = classpath:spring/memory-instance.xml
canal.instance.global.spring.xml = classpath:spring/file-instance.xml
#canal.instance.global.spring.xml = classpath:spring/default-instance.xml
##################################################
######### MQ #############
##################################################
#此处有发生修改
canal.mq.servers = http://111:80
canal.mq.retries = 0
canal.mq.batchSize = 16384
canal.mq.maxRequestSize = 1048576
canal.mq.lingerMs = 100
canal.mq.bufferMemory = 33554432
canal.mq.canalBatchSize = 50
canal.mq.canalGetTimeout = 100
canal.mq.parallelThreadSize = 8
canal.mq.flatMessage = true
canal.mq.compressionType = none
canal.mq.acks = all
#canal.mq.properties. =
#此处有发生修改 （没找到哪里能指定tag）
canal.mq.producerGroup = GID-CANAL
# Set this value to "cloud", if you want open message trace feature in aliyun.
canal.mq.accessChannel = cloud
# aliyun mq namespace
#canal.mq.namespace =
canal.mq.vhost=
canal.mq.exchange=
canal.mq.username=
canal.mq.password=
canal.mq.aliyunuid=
##################################################
######### Kafka Kerberos Info #############
##################################################
canal.mq.kafka.kerberos.enable = false
canal.mq.kafka.kerberos.krb5FilePath = "../conf/kerberos/krb5.conf"
canal.mq.kafka.kerberos.jaasFilePath = "../conf/kerberos/jaas.conf"
instance.properties
#################################################
## mysql serverId , v1.0.26+ will autoGen
# canal.instance.mysql.slaveId=0
# enable gtid use true/false
canal.instance.gtidon=false
# position info
#此处有发生修改
canal.instance.master.address=192.168.0.64:3306
canal.instance.master.journal.name=
#指定起始搜索binlog文件
canal.instance.master.position=
#毫秒级别
canal.instance.master.timestamp=
canal.instance.master.gtid=
# rds oss binlog
canal.instance.rds.accesskey=
canal.instance.rds.secretkey=
canal.instance.rds.instanceId=
# table meta tsdb info
#canal.instance.tsdb.enable=true
#canal.instance.tsdb.url=jdbc:mysql://127.0.0.1:3306/canal_tsdb
#canal.instance.tsdb.dbUsername=canal
#canal.instance.tsdb.dbPassword=canal
canal.instance.standby.address =
canal.instance.standby.journal.name =
canal.instance.standby.position =
canal.instance.standby.timestamp =
canal.instance.standby.gtid=
# username/password
#此处有发生修改
canal.instance.dbUsername=root
canal.instance.dbPassword=123456
canal.instance.connectionCharset = UTF-8
# enable druid Decrypt database password
canal.instance.enableDruid=false
#canal.instance.pwdPublicKey=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALK4BUxdDltRRE5/zXpVEVPUgunvscYFtEip3pmLlhrWpacX7y7GCMo2/JM6LeHmiiNdH1FWgGCpUfircSwlWKUCAwEAAQ==
# table regex
#canal.instance.filter.regex=sale.user_attribute,sale.user_expand,sale.user,
#监听表','间隔
canal.instance.filter.regex=sale.sheet1
# table black regex
#排除表，谨记排除mysql相关权限，角色，表结构等表
canal.instance.filter.black.regex=mysql\..*
# table field filter(format: schema1.tableName1:field1/field2,schema2.tableName2:field1/field2)
#canal.instance.filter.field=test1.t_product:id/subject/keywords,test2.t_company:id/name/contact/ch
# table field black filter(format: schema1.tableName1:field1/field2,schema2.tableName2:field1/field2)
#canal.instance.filter.black.field=test1.t_product:subject/product_image,test2.t_company:id/name/contact/ch
# mq config
#默认使用topic，如db未在后续指定，会自动走这个top
canal.mq.topic=canal
# dynamic topic route by schema or table regex
#指定topic,可使topic单独处理多个表或一个表，以下给出单个表案例（这个正则，真心不好用）
#canal.mq.dynamicTopic=user-canal:sale\\.user;user-expand-canal:sale\\.user_expand;user-attribute-canal:sale\\.user_attribute;
canal.mq.partition=0
# hash partition config
#canal.mq.partitionsNum=3
#canal.mq.partitionHash=test.table:id^name,.*\\..*
#################################################
这里给一个mq消费处理的案例
此处因为一定原因，只给一个消费分发的代码，给个思路，自己写后面吧
package com.1111;
import com.alibaba.fastjson.JSONObject;
import com.aliyun.openservices.ons.api.Action;
import com.aliyun.openservices.ons.api.ConsumeContext;
import com.aliyun.openservices.ons.api.Message;
import com.1111.CanalMsg;
import com.1111.MqEnum;
import com.1111.SqlType;
import com.1111.TransEnum;
import com.1111.HandleService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.context.annotation.Configuration;
import java.nio.charset.StandardCharsets;
/**
* @ProjectName: canal
* @Description: 普通canal消费端
* @Date: 2020/6/5 11:47
* @Version: 1.0
*/
@Slf4j
@Configuration
public class CanalConsumer extends AbstractConsumer implements InitializingBean {
@Autowired
private ApplicationContext applicationContext;
@Override
public Action doConsumer(Message message, ConsumeContext consumeContext) {
try {
log.info("canal数据同步messageId-->{},", message.getMsgID());
String messageJson = new String(message.getBody(), StandardCharsets.UTF_8);
CanalMsg canalMsg = JSONObject.parseObject(messageJson, CanalMsg.class);
SqlType sqlType = SqlType.loadByType(canalMsg.getType());
if (null == sqlType) {
log.info("sql类型不在统计范围内:type-->{},messageJson-->{}", canalMsg.getType(), messageJson);
return Action.CommitMessage;
}
TransEnum transEnum = TransEnum.loadByTable(canalMsg.getTable());
if (null == transEnum) {
if (canalMsg.isDdl()) {
log.error("请及时处理ddl语句:table-->{},sql-->{}", canalMsg.getTable(), canalMsg.getSql());
}
log.info("未提供有效转换类：tableName-->{},messageJson-->{}", canalMsg.getTable(), messageJson);
return Action.CommitMessage;
}
HandleService hadle = (HandleService) applicationContext.getBean(transEnum.getAClass());
boolean handle = hadle.handle(sqlType, transEnum, canalMsg);
if (!handle) {
log.info("canal数据同步失败:" + messageJson);
}
} catch (Exception e) {
log.error("canal数据同步异常----->{}", e);
return Action.ReconsumeLater;
}
return Action.CommitMessage;
}
@Override
public void afterPropertiesSet() {
try {
super.listener(MqEnum.CANAL);
} catch (Exception e) {
log.info(MqEnum.CANAL.getMessage() + "----------------消费者监听失败-------------------");
}
}
}
package com.111.vo;
import com.alibaba.fastjson.JSONObject;
import lombok.Data;
import java.util.List;
@Data
public class CanalMsg {
/**
* data : [{"id":"2","name":"asd"}]
* database : cannel
* es : 1591239268000
* id : 1
* isDdl : false
* mysqlType : {"id":"int(20)","name":"varchar(20)"}
* old : null
* pkNames : ["id"]
* sql :
* sqlType : {"id":4,"name":12}
* table : canel
* ts : 1591239464269
* type : INSERT
*/
private String database;
private long es;
private int id;
private boolean isDdl;
private JSONObject mysqlType;
private List<JSONObject> old;
private String sql;
private Object sqlType;
private String table;
private long ts;
private String type;
private List<JSONObject> data;
private List<String> pkNames;
}
一些使用心得
canal我这边引用的目的有两个:
一个是做数据库日志，重要的mysql表操作需做详细记录;阿里商业库虽然有binlog日志;但是解析麻烦，查看麻烦，还需要定期清除，麻烦;
另一个是做缓存强一致性，常规做缓存有两种方法，一种使用bean形式加载缓存，如果发生更新重新加载缓存（代码侵入性强，使用内存缓存，影响项目启动时间，容易出错，没有通用性）；还有一种是双写，简单说就是发生curd同时写入到redis或内存，这种是目前使用最普遍的，但是问题也很明显，代码侵入性强，因为事务的关系会有各种问题；
使用canal后很明显的就变了，可以使用canal对各种配置表做全套缓存到redis，可以将重要信息日志按自己的安排写到任意位置，如果使用了mq，那么甚至可以做简单的事务，来控制自己的缓存；如果封装得当，加个缓存就是改个配置的事，配套自己开发出缓存组件就好用了；
缓存组件给个思路，加个注解，切面到缓存库，如果库存在，就返回内容，不存在就穿透到库，参考@Cacheable实现；
说一个使用误区：使用canal后其实类似于异步写入缓存，合理控制消费速度，尽量是消费超过写入;这样避免一些要求特别高及时性的配置生效过慢；当然，其实读写分离也有延迟，看个人理解；
强调
D:\workSpace\canal\conf\example
目录下会生成 canal的Index记录文件：meta.dat，h2.mv.db
meda.dat会记录上次指定到的binlog下标；
如果canal停止过长时间，请及时记录timestamp；供下次查询使用；
如果bin-log有自动清除，建议不要长时间停止canal，如果上次处理结束的binlog丢失，请删除meta.dat，h2.mv.db，
重启配置好canal(将重新定位position);
并：手动同步初始缓存
709
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
