canal 配置MySQL数据同步

jingleli21

已于 2022-07-13 15:08:32 修改

阅读量2k

点赞数

分类专栏：学习问题总结文章标签：服务器运维

于 2022-07-11 16:28:58 首次发布

原文链接：https://blog.csdn.net/ss810540895/article/details/115912317

版权

学习问题总结专栏收录该内容

35 篇文章 0 订阅

订阅专栏

注意几个点：
1.同一台服务器安装完canal，如果下次删除重新安装，会发现启动报错，说明没删除干净，换台服务器安装
2.每次测试完了记得stop服务，还有删除元数据
3.数据库和表的匹配正则不支持换行
4.canal服务端启动之后需要等一会，直到example日志中出现’the next step is binlog dump’，然后启动客户端
————————————————
版权声明：本文为CSDN博主「帅大大的架构之路」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/ss810540895/article/details/115912317

部署canal服务端

部署mysql

mkdir -p /data/mysql/conf/
cd /data/mysql/conf/

vim my.cnf

[mysqld]
log_bin=master-binlog
binlog_format=ROW
server-id=1
sync-binlog=1
expire_logs_days=1
log_slave_updates=1

检查mysql配置

docker exec -it mysql /bin/bash
mysql -uroot -p123456

mysql> select version();
+------------+
| version() |
+------------+
| 5.7.28-log |
+------------+
1 row in set (0.00 sec)

mysql> create database test;
mysql> use test;
mysql> create table t1(id int,name varchar(30));

创建canal mysql用户并授权访问binlog
mysql> create user canal identified by 'canal';
Query OK, 0 rows affected (1.02 sec)

mysql> grant select, replication slave, replication client on *.* to 'canal'@'%' ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql8.0以上需要修改密码模式

mysql8.0前的版本修改密码的命令：
update mysql.user set authentication_string=password("新密码") where user="root";

mysql8.0以后的版本修改密码的命令：
ALTER USER 'canal'@'%' IDENTIFIED WITH mysql_native_password BY '新密码'

安装canal-deployer
https://github.com/alibaba/canal/releases/tag/canal-1.1.4

mkdir /opt/canal/{canal-deployer,canal-adapter} -p
cd /opt/canal/canal-deployer
wget https://github.com/alibaba/canal/releases/download/canal-1.1.4/canal.deployer-1.1.4.tar.gz
tar xf canal.deployer-1.1.4.tar.gz

cd conf/
vim canal.properties
canal.destinations = example,example2

cp -r example example2

cd example
vim instance.properties

canal.instance.master.address=192.168.146.128:3306
canal.instance.dbUsername=canal
canal.instance.dbPassword=canal
canal.instance.filter.regex=chen.t1,chen.t2,chen.t3
#canal.mq.topic=example

cd example2 同上

启动canal-deployer
cd /opt/canal/canal-deployer/bin
./start.sh

tailf /opt/canal/canal-deployer/logs/canal/canal.log

2021-04-20 14:41:50.683 [main] INFO com.alibaba.otter.canal.deployer.CanalLauncher - ## set default uncaught exception handler
2021-04-20 14:41:50.712 [main] INFO com.alibaba.otter.canal.deployer.CanalLauncher - ## load canal configurations
2021-04-20 14:41:50.725 [main] INFO com.alibaba.otter.canal.deployer.CanalStarter - ## start the canal server.
2021-04-20 14:41:50.755 [main] INFO com.alibaba.otter.canal.deployer.CanalController - ## start the canal server[172.0.129.1(172.0.129.1):11111]
2021-04-20 14:41:51.742 [main] INFO com.alibaba.otter.canal.deployer.CanalStarter - ## the canal server is running now ......

192.168.146.129
使用canal-adapter同步数据
安装mysql
安装同上
mysql> create database shuai;
mysql> use shuai;
mysql> create table t1(id int,name varchar(30));
mysql> create database a;
mysql> create database b;
mysql> use a;
mysql> create table t2(id int,name varchar(30));
mysql> use b;
mysql> create table t3(id int,name varchar(30));

https://github.com/alibaba/canal/releases/tag/canal-1.1.4

cd /opt/canal/canal-adapter
wget https://github.com/alibaba/canal/releases/download/canal-1.1.4/canal.adapter-1.1.4.tar.gz
tar xf canal.deployer-1.1.4.tar.gz
cd conf/

vim 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 # kafka rocketMQ
canalServerHost: 60.216.20.210:11111
# zookeeperHosts: slave1:2181
# mqServers: 127.0.0.1:9092 #or rocketmq
# flatMessage: true
batchSize: 500
syncBatchSize: 1000
retries: 0
timeout:
accessKey:
secretKey:
srcDataSources:
defaultDS:
url: jdbc:mysql://192.168.7.208:3306/test?useUnicode=true&&useSSL=false
#url: jdbc:mysql://192.168.7.208:3306/lecturer_manage?useUnicode=true&&character_set_server=utf8mb4&useSSL=false&&allowMultiQueries=true&&rewriteBatchedStatements=true&&v
erifyServerCertificate=false&&autoReconnect=true&&serverTimezone=GMT%2B8

username: canal
password: Canal@@123

canalAdapters:
- instance: example # canal instance Name or mq topic name
groups:
- groupId: g1
outerAdapters:
- name: logger
- name: rdb
key: mysql1
properties:
jdbc.driverClassName: com.mysql.jdbc.Driver
jdbc.url: jdbc:mysql://192.168.7.209:3307/ceshi?useUnicode=true
jdbc.username: root
jdbc.password: Mysql@@123
# canalAdapters:
- instance: example2 # canal instance Name or mq topic name
groups:
- groupId: g2
outerAdapters:
- name: logger
- name: rdb
key: mysql2
properties:
jdbc.driverClassName: com.mysql.jdbc.Driver
jdbc.url: jdbc:mysql://192.168.7.206:3306/ceshi?useUnicode=true
jdbc.username: root
jdbc.password: 19n2E7&*#

修改两个Mysql服务表映射配置文件

cd /opt/canal/canal-adapter/conf/rdb mv mytest_user.yml test1.yml cp -r test1.yml test2.yml

vim test1.yml

dataSourceKey: defaultDS
destination: example2
groupId: g2
outerAdapterKey: mysql2
concurrent: true
dbMapping:
database: zhubaoe_common
table: zby_auth_rule
targetTable: zhubaoe1.zby_auth_rule
targetPk:
id: id
# mapAll: true
targetColumns:
id:
name:
title:
type:
status:
tag:
pid:
icon:
sort:
is_auth:
# etlCondition: "where c_time>={}"
commitBatch: 3000 # 批量提交的大小