canal 同步mysql指定表到mysql

canal部署192.168.146.128部署canal服务端部署mysqlmkdir -p /data/mysql/conf/cd /data/mysql/conf/vim my.cnf[mysqld]log_bin=master-binlogbinlog_format=ROWserver-id=1sync-binlog=1expire_logs_days=1log_slave_updates

帅大大的架构之路

4129人浏览 · 2021-04-20 16:34:47

帅大大的架构之路 · 2021-04-20 16:34:47 发布

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 my.cnf

[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/my.cnf:/etc/my.cnf -v /data/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 --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

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

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.5

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

  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: com.mysql.jdbc.Driver
          jdbc.url: jdbc:mysql://6n5krq9t.sql.tencentcdb.com:524/zhubaoe?useUnicode=true
          jdbc.username: root
          jdbc.password: zby123456
  - 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://6n5krq9t.sql.tencentcdb.com:524/zhubaoe1?useUnicode=true
          jdbc.username: root
          jdbc.password: zby123456

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

cd /opt/canal/canal-adapter/conf/rdb
mv mytest_user.yml test1.yml
cp -r test1.yml test2.yml
cp -r test1.yml test3.yml
cp -r test1.yml test4.yml
cp -r test1.yml test5.yml
cp -r test1.yml test6.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 # 批量提交的大小


vim test2.yml	同上
vim test3.yml	同上

vim test4.yml

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 # 批量提交的大小

vim test5.yml	同上
vim test6.yml	同上

启动canal-adapter

cd /opt/canal/canal-adapter/bin
./startup.sh


tailf logs/adapter/adapter.log

2021-04-20 15:31:41.672 [main] INFO  org.apache.coyote.http11.Http11NioProtocol - Starting ProtocolHandler ["http-nio-8081"]
2021-04-20 15:31:41.707 [main] INFO  org.apache.tomcat.util.net.NioSelectorPool - Using a shared selector for servlet write/read
2021-04-20 15:31:41.765 [main] INFO  o.s.boot.web.embedded.tomcat.TomcatWebServer - Tomcat started on port(s): 8081 (http) with context path ''
2021-04-20 15:31:41.773 [main] INFO  c.a.otter.canal.adapter.launcher.CanalAdapterApplication - Started CanalAdapterApplication in 85.405 seconds (JVM running for 85.914)
2021-04-20 15:31:41.792 [Thread-4] INFO  c.a.o.canal.adapter.launcher.loader.CanalAdapterWorker - =============> Start to subscribe destination: example <=============
2021-04-20 15:31:41.833 [Thread-4] INFO  c.a.o.canal.adapter.launcher.loader.CanalAdapterWorker - =============> Subscribe destination: example succeed <=============

仅记录（自己看的）

############### 部署



mkdir /soft/canal/{canal-deployer,canal-adapter} -p

yum install java -y


# canal-deployer
cd /soft/canal/canal-deployer/
wget https://github.com/alibaba/canal/releases/download/canal-1.1.5/canal.deployer-1.1.5.tar.gz
tar xf canal.deployer-1.1.5.tar.gz

[root@jg48 canal-deployer]# cd conf/
[root@jg48 conf]# ls
canal_local.properties  canal.properties  es  example  logback.xml  metrics  spring
[root@jg48 conf]# vim canal.properties 

canal.destinations = example

cd example/
vim instance.properties

canal.instance.master.address=4.1.1.2:3333
canal.instance.dbUsername=canal
canal.instance.dbPassword=canal
canal.instance.filter.regex=zhubaoe_common.zby_merchant,zhubaoe_common.zby_auth_group

启动
/soft/canal/canal-deployer/bin/startup.sh
tail -f /soft/canal/canal-deployer/logs/example/example.log



# canal-adapter
cd /soft/canal/canal-adapter/
wget https://github.com/alibaba/canal/releases/download/canal-1.1.5/canal.adapter-1.1.5.tar.gz

cd conf/
vim application.yml
  # 源数据库
  srcDataSources:
    defaultDS:
      url: jdbc:mysql://4.1.1.2:3333/zhubaoe_common?useUnicode=true
      username: canal
      password: canal
  # 目标数据库
  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://gz-t.sql.tencentcdb.com:58824/zhubaoe1?useUnicode=true
          jdbc.username: root
          jdbc.password: 123456
      - name: rdb
        key: mysql2
        properties:
          jdbc.driverClassName: com.mysql.jdbc.Driver
          jdbc.url: jdbc:mysql://gt.sql.tencentcdb.com:58824/zhubaoe2?useUnicode=true
          jdbc.username: root
          jdbc.password: 123456
	  # 目标es
      - name: es7
        hosts: 1.7.1.2:9220 # 127.0.0.1:9200 for rest mode
        properties:
          mode: rest #transport # or rest
          # security.auth: test:123456 #  only used for rest mode
          cluster.name: docker-cluster

cd rdb/
vim z1.yml

dataSourceKey: defaultDS
destination: example
groupId: g1
outerAdapterKey: mysql1
concurrent: true
dbMapping:
  database: zhubaoe_common
  table: zby_auth_rule
  targetTable: zby_auth_rule
  targetPk:
    id: id
  mapAll: true
  targetKeywordsIdentifier:  # 新增配置项 关键字、保留字标识符(对表名无效)
    prefix: '`'              # 默认值为 `
    suffix: '`'
  commitBatch: 3000 # 批量提交的大小

cd es7
vim mytest_user.yml

dataSourceKey: defaultDS
destination: example
groupId: g1
esMapping:
  _index: zby_brand
  _id: _id
  upsert: true
#  pk: id
  sql: "select a.id as _id, a.name, a.short_name,a.code,a.create_date,a.update_date from zby_brand a"
#  objFields:
#    _labels: array:;
#  etlCondition: "where a.c_time>={}"
  commitBatch: 3000

启动
/soft/canal/canal-adapter/bin/startup.sh
tail -f /soft/canal/canal-adapter/logs/adapter/adapter.log




# 过程中遇到的问题：
1.
Could not resolve placeholder 'HOSTNAME%%.*'

解决：

vim /root/.bash_profile
export PROMPT_COMMAND=""

2.
invalid internal transport message format, got

解决：
这是由于es那边索引字段的类型不对，或者格式不对，
这样定义就可以了 "type": "date"

3.
com.alibaba.druid.pool.DruidDataSource cannot be cast to com.alibaba.druid.pool.DruidDataSource

解决：
下载https://github.com/alibaba/canal/releases/download/canal-1.1.5-alpha-2/canal.adapter-1.1.5-SNAPSHOT.tar.gz
这个alpha-2/canal.adapter版本，然后把plugin里的es7的jar包替换掉


4.Illegal character in scheme name at index 0
# 目标es
      - name: es7
        hosts: 1.7.1.2:9220 # 127.0.0.1:9200 for rest mode
这里的hosts需要加上http://
（我第一次同步的时候没加也没报这个错）

向您推荐>>Eolink开发者社区

权威｜前沿｜技术｜干货｜国内首个API全生命周期开发者社区

更多推荐

ELK实现containerd的容器日志采集展示【基于logging的全栈监测】

企业级ELK Stack构建介绍

云原生

深入理解 Mocha 测试框架：从零实现一个 Mocha

前言什么是自动化测试自动化测试在很多团队中都是Devops环节中很难执行起来的一个环节，主要原因在于测试代码的编写工作很难抽象，99%的场景都需要和业务强绑定，而且写测试代码的编写工作量往往比编写实际业务代码的工作量更多。在一些很多业务场景中投入产出比很低，适合写自动化测试的应该是那些中长期业务以及一些诸如组件一样的基础库。自动化测试是个比较大的概念，其中分类也比较多，比如单元测试，端对端测试，集

云原生

(20200916 Solved)docker-compose up创建容器自动退出

问题描述如题，创建容器后自动退出了。并且docker start container无效解决方案原因是缺失了控制终端的配置，需要在docker-compose.yml中增加tty:true ，有时候这样也不行，需要再增加一个command:/bin/bash，命令不一定是这个，需要是一个不会退出的命令，然后用-d后台启动容器。Referencesdocker-compose启动容器后自动退出...