一、修改MYSQL配置文件my.ini
[mysqld]
#选择ROW(行)模式
binlog-format=row
#打开binlog
log_bin=mysql-bin
server_id=1 # 配置 MySQL replaction 需要定义,不要和 canal 的 slaveId 重复
show variables like ‘binlog_format’; //查看日志模式
show variables like ‘log_bin’;二进制日志是否开启
二、添加MYSQL账号
CREATE USER canal IDENTIFIED BY 'canal';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
-- GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ;
FLUSH PRIVILEGES;
由于MySQL 8默认使用了新的认证插件,Canal连接时可能需要额外配置,不然连不上数据库。
ALTER USER 'canal'@'%' IDENTIFIED WITH mysql_native_password BY 'yourpassword';
FLUSH PRIVILEGES;
三、下载canal文件
地址:https://github.com/alibaba/canal/releases
四、安装服务端 canal-deployer
解压文件,修改配置文件canal.properties
#实例名称,与conf下文件名相同
canal.destinations = example
修改conf\example下实例instance.properties配置:
canal.instance.master.address=127.0.0.1:3306
canal.instance.dbUsername=canal
canal.instance.dbPassword=canal
canal.instance.defaultDatabaseName=farm_platform
bin/startup.bat 启动canal服务。
启动成功:
五、集成springboot
注:如果不需要集成springboot项目中,可跳过次步骤。
添加pom
<dependency>
<groupId>top.javatool</groupId>
<artifactId>canal-spring-boot-starter</artifactId>
<version>1.2.1-RELEASE</version>
<exclusions>
<exclusion>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
</exclusion>
</exclusions>
</dependency>
添加yml注解
canal:
server: 127.0.0.1:11111 # Canal服务器地址和端口
destination: example # Canal目的地名称
添加类
(value = "all")
public class DefaultEntryHandler implements EntryHandler<Map<String, String>> {
public void insert(Map<String, String> map) {
log.info("insert message {}", map);
}
public void update(Map<String, String> before, Map<String, String> after) {
log.info("update before {} ", before);
log.info("update after {}", after);
}
public void delete(Map<String, String> map) {
log.info("delete {}", map);
}
}
六、安装Web端canal-admin
解压admin文件;
首先将sql文件导入数据库中
然后修改配置文件:application.yml;
server:
port: 8089
spring:
jackson:
date-format: yyyy-MM-dd HH:mm:ss
time-zone: GMT+8
#canal 数据库配置
spring.datasource:
address: 192.168.9.113:3306
database: canal_manager
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://${spring.datasource.address}/${spring.datasource.database}?useUnicode=true&characterEncoding=UTF-8&useSSL=false
hikari:
maximum-pool-size: 30
minimum-idle: 1
#canal admin账号和密码
canal:
adminUser: admin
adminPasswd: 123456
点击bin/startup.bat启动;
启动成功后,访问:http://127.0.0.1:8089/,输入yml配置的账号admin/123456
修改deployer配置文件,让服务端注册到admin:
备份canal.properties,将canal-local.properties修改为canal.properties文件,修改配置
# 当前节点ip
canal.register.ip = 127.0.0.1
# admin的地址和账号密码
canal.admin.manager = 192.168.9.113:8089
canal.admin.port = 11110
canal.admin.user = admin
# admin密码,使用了mysql的password加密后的密码,与admin的conf/applicaiton.yml中设置的密码对应
canal.admin.passwd = 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
# 开启自动注册模式
canal.admin.register.auto = true
# 指定注册的集群名
canal.admin.register.cluster =
# 注册到admin上的服务名,默认为当前ip
canal.admin.register.name = server_01
如果要调整admin密码,可以通过如下mysql指令设置:
# mysql5.0
select password('xxx')
# mysql8.0
select upper(sha1(unhex(sha1('xxx'))))
重新启动deployer, 就会自动注册到admin中。
新建instance,配置方法与之前instance.properties一样。
七、安装客户端adapter
解压adapter文件;配置/conf下bootstrap.yml文件:
canal:
manager:
#admin数据库
jdbc:
url: jdbc:mysql://127.0.0.1:3306/canal_manager?useUnicode=true&characterEncoding=UTF-8
username: root
password: 870121ss..
配置/conf下application.yml文件:
server:
port: 8081
spring:
jackson:
date-format: yyyy-MM-dd HH:mm:ss
time-zone: GMT+8
default-property-inclusion: non_null
canal.conf:
mode: tcp #tcp kafka rocketMQ rabbitMQ
flatMessage: true
zookeeperHosts:
syncBatchSize: 1000
retries: -1
timeout:
accessKey:
secretKey:
consumerProperties:
# canal tcp consumer
canal.tcp.server.host: 127.0.0.1:11111
canal.tcp.zookeeper.hosts:
canal.tcp.batch.size: 500
canal.tcp.username:
canal.tcp.password:
# kafka consumer
kafka.bootstrap.servers: 127.0.0.1:9092
kafka.enable.auto.commit: false
kafka.auto.commit.interval.ms: 1000
kafka.auto.offset.reset: latest
kafka.request.timeout.ms: 40000
kafka.session.timeout.ms: 30000
kafka.isolation.level: read_committed
kafka.max.poll.records: 1000
# rocketMQ consumer
rocketmq.namespace:
rocketmq.namesrv.addr: 127.0.0.1:9876
rocketmq.batch.size: 1000
rocketmq.enable.message.trace: false
rocketmq.customized.trace.topic:
rocketmq.access.channel:
rocketmq.subscribe.filter:
# rabbitMQ consumer
rabbitmq.host:
rabbitmq.virtual.host:
rabbitmq.username:
rabbitmq.password:
rabbitmq.resource.ownerId:
srcDataSources:
zhsw:
url: jdbc:mysql://192.168.3.195:3306/sc_pipe_network?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false&useAffectedRows=true&allowPublicKeyRetrieval=true
username: root
password: flzx3qc!
local:
url: jdbc:mysql://192.168.9.113:3306/farm_platform?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false&useAffectedRows=true&allowPublicKeyRetrieval=true
username: root
password: root
canalAdapters:
- instance: zhsw # canal instance Name or mq topic name
groups:
- groupId: zhsw
outerAdapters:
- name: rdb
key: zhsw
properties:
jdbc.driverClassName: com.mysql.cj.jdbc.Driver
jdbc.url: jdbc:mysql://192.168.3.203:3306/zhch_water?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false&useAffectedRows=true&allowPublicKeyRetrieval=true
jdbc.username: root
jdbc.password: root
druid.stat.enable: false
druid.stat.slowSqlMillis: 1000
- instance: local # canal instance Name or mq topic name
groups:
- groupId: local
outerAdapters:
- name: rdb
key: local
properties:
jdbc.driverClassName: com.mysql.cj.jdbc.Driver
jdbc.url: jdbc:mysql://192.168.3.203:3306/zhch_park?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false&useAffectedRows=true&allowPublicKeyRetrieval=true
jdbc.username: root
jdbc.password: root
druid.stat.enable: false
druid.stat.slowSqlMillis: 1000
配置conf\rdb下yml文件,一张表配置一个文件。
dataSourceKey: zhsw
destination: zhsw
groupId: zhsw
outerAdapterKey: zhsw
concurrent: true
dbMapping:
database: sc_pipe_network
table: sys_notice
targetTable: sys_notice
targetPk:
id: id
mapAll: true
dataSourceKey: zhsw
destination: zhsw
groupId: zhsw
outerAdapterKey: zhsw
concurrent: true
dbMapping:
database: sc_pipe_network
table: d_news_info
targetTable: d_news_info
targetPk:
id: id
也可以配置全库,farm_platform必须源库和目标库都存在,且表都建好。
dataSourceKey: local
destination: local
groupId: local
outerAdapterKey: local
concurrent: true
dbMapping:
#同步数据库DDL语句create-drop-alert,同步DDL必须配置这个
mirrorDb: true
# 监听的数据库名
database: farm_platform
启动bin/startup.bat
八、可能遇到的问题
1、解决canal在window环境下运行出现config dir not found 的问题
把startup.bat 用记事本或者其他工具打开,将classpath用分号隔开的两个值调换一下顺序。
切记不要文件名不要使用中文。
2、数据库连接不上,检查使用的lib包与数据库版本是否一致。