canal同步mysql指定表到mysql
canal部署
实现同步mysql-node-1节点上的chen.t1,chen.t2,chen.t3到mysql-node-2节点上的shuai.t1,a.t2,b.t3（环境与参数可能有差异）
注意⼏个点：
1.同⼀台服务器安装完canal，如果下次删除重新安装，会发现启动报错，说明没删除⼲净，换台服务器安装
2.每次测试完了记得stop服务，还有删除元数据
3.数据库和表的匹配正则不⽀持换⾏
4.canal服务端启动之后需要等⼀会，直到example⽇志中出现’the next step is binlog dump’，然后启动客户端
192.168.146.128
部署canal服务端
部署mysql
mkdir -p /data/mysql/conf/
cd /data/mysql/conf/
vim myf
[mysqld]
log_bin=master-binlog
binlog_format=ROW
server-id=1
sync-binlog=1
expire_logs_days=1
log_slave_updates=1
docker run -itd -p 3306:3306 --privileged=true -v /data/mysql/conf/myf:/etc/myf -v /data/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=12 3456 --name mysql mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci
检查mysql配置
docker exec -it mysql /bin/bash
mysql -uroot -p123456
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin      | ON    |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.01 sec)
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.28-log |
+------------+
1 row in set (0.00 sec)
mysql> create database chen;
mysql> use chen;
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'@'%' identified by 'canal'; Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
安装canal-deployer
mkdir /opt/canal/{canal-deployer,canal-adapter} -p
cd /opt/canal/canal-deployer
wget github/alibaba/canal/releases/download/canal-1.1.4/canal.deployer-1.1.
tar xf canal.deployer-1.1.
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.=chen.t1,chen.t2,chen.t3
#pic=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  canal.deployer.CanalLauncher - ## set default uncaught exception handler
2021-04-20 14:41:50.712 [main] INFO  canal.deployer.CanalLauncher - ## load canal configurations
2021-04-20 14:41:50.725 [main] INFO  canal.deployer.CanalStarter - ## start the canal server.
2021-04-20 14:41:50.755 [main] INFO  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  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));
cd /opt/canal/canal-adapter
wget github/alibaba/canal/releases/download/canal-1.1.4/canal.adapter-1.1. tar xf canal.deployer-1.1.
cd conf/
l
srcDataSources:
defaultDS:
url: jdbc:mysql://42.19.17.21:3333/zhubaoe_common?useUnicode=true
username: canal
password: canal@zhubaoe
canalAdapters:
- instance: example # canal instance Name or mq topic name
groups:
- groupId: g1
outerAdapters:
- name: logger
- name: rdb
key: mysql1
properties:
jdbc.driverClassName: sql.jdbc.Driver
jdbc.url: jdbc:mysql://tcdb:524/zhubaoe?useUnicode=true
jdbc.username: root
jdbc.password: zby123456
-mysql下载starting the server
instance: example2 # canal instance Name or mq topic name
groups:
- groupId: g2
outerAdapters:
- name: logger
- name: rdb
key: mysql2
properties:
jdbc.driverClassName: sql.jdbc.Driver
jdbc.url: jdbc:mysql://tcdb:524/zhubaoe1?useUnicode=true
jdbc.username: root
jdbc.password: zby123456
修改两个Mysql服务表映射配置⽂件
cd /opt/canal/canal-adapter/conf/rdb
mv l
cp -l
cp -l
cp -l
cp -l
cp -l
vim  l
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:
name:
title:
type:
status:
tag:
pid:
icon:
sort:
is_auth:
#  etlCondition: "where c_time>={}"
commitBatch: 3000 # 批量提交的⼤⼩
l 同上
l 同上
l
dataSourceKey: defaultDS
destination: example
groupId: g1
outerAdapterKey: mysql1
concurrent: true
dbMapping:
database: zhubaoe_common
table: zby_auth_rule
targetTable: zhubaoe.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 # 批量提交的⼤⼩
l 同上
l 同上
启动canal-adapter
cd /opt/canal/canal-adapter/bin
./startup.sh
tailf logs/adapter/adapter.log
2021-04-20 15:31:41.672 [main] INFO  http11.Http11NioProtocol - Starting ProtocolHandler ["http-nio-8081"]
2021-04-20 15:31:41.707 [main] INFO  at.util.NioSelectorPool - Using a shared selector for servlet write/read
2021-04-20 15:31:41.765 [main] INFO  o.s.at.TomcatWebServer - Tomcat started on port(s): 8081 (http) with context path '' 2021-04-20 15:31:41.773 [main] INFO  canal.adapter.launcher.CanalAdapterApplication - Started CanalAdapterApplication in 85.405 seconds (JV M running for 85.914)
2021-04-20 15:31:41.792 [Thread-4] INFO  anal.adapter.launcher.loader.CanalAdapterWorker - =============> Start to subscribe destination: ex ample <=============
2021-04-20 15:31:41.833 [Thread-4] INFO  anal.adapter.launcher.loader.CanalAdapterWorker - =============> Subscribe destination: example s ucceed <=============

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。