MYSQL 多实例数据同步到ES

边城cn

已于 2023-08-08 10:36:40 修改

阅读量332

点赞数 1

分类专栏：数据仓库文章标签： adb android

于 2023-07-31 14:36:42 首次发布

本文链接：https://blog.csdn.net/miyatang/article/details/132021836

版权

数据仓库专栏收录该内容

5 篇文章 2 订阅

订阅专栏

文章描述了如何使用Canal将两个不同MySQL实例（std_goods_test和std_goods_dev）中的std_goods表数据同步到同一个Elasticsearch（ES）环境中。配置涉及canal.deployer和canal.adapter的设置，包括数据库连接信息、表过滤规则、MQ主题以及ES的映射和同步条件。此外，还提到了ES索引的创建、查看和删除操作。

摘要由CSDN通过智能技术生成

在业务中，往往需要把多个不同业务，或不同环境的的MYSQL 数据源，同步到同一个ES 环境中。
为了实现此功能，在canal 中，需要在配置文件中手工添加相关数据源，以下是一个在同一个MYSQL实例中，
不同shcema的数据，同步到同一个ES环境中。

具体安装步聚不再累赘说明。

实例数据同步示意图：

MySQL -> ES 数据同步

std_goods_test.std_goods  --> ES
std_goods_dev.std_goods  --> ES


mysql://192.168.1.240:33306  -->  http://192.168.1.240:9200

1.canal.deployer 配置1.canal.deployer 配置

配置文件：canal.properties:

canal.destinations = std_goods_test,std_goods_dev

** #配置了2个不同的数据库实例名称，

** #同时要建立2个实例名称的子目录，并配置同不的实例数据库

配置文件：
std_goods_dev/instance.properties

# username/password
canal.instance.dbUsername=canal
canal.instance.dbPassword=123456
canal.instance.connectionCharset = UTF-8
# table regex
canal.instance.filter.regex=std_goods_dev.t_goods
# mq config
canal.mq.topic=SYNC_ES_STD_DEV	#注意这个topic

std_goods_test/instance.properties

# username/password
canal.instance.dbUsername=canal
canal.instance.dbPassword=123456
canal.instance.connectionCharset = UTF-8
# table regex
canal.instance.filter.regex=std_goods_test.t_goods
# mq config
canal.mq.topic=SYNC_ES_STD_TEST	#注意这个topic

2.canal.adapter 配置

application.yml 配置文件

srcDataSources:

    defaultDS_test:	#测试库DS
      url: jdbc:mysql://192.168.1.240:33306/std_goods_test?useUnicode=true
      username: canal
      password: 123456


  srcDataSources_dev:
    defaultDS_dev:
      url: jdbc:mysql://192.168.1.240:33306/std_goods_dev?useUnicode=true
      username: canal
      password: 123456

  canalAdapters:
  - instance: SYNC_ES_STD_TEST #测试库topic一致  :topic  std_goods_test/instance.properties   
    groups:
    - groupId: g_test
      outerAdapters:
      - name: es7
        key: exampleKey_test
        hosts: http://192.168.1.240:9200 # 127.0.0.1:9200 for rest mode
        properties:
          mode: rest # or rest
          cluster.name: elasticsearch

#  canalAdapters:
  - instance: SYNC_ES_STD_DEV # 开发库topic一致:topic: std_goods_dev/instance.properties 
    groups:
    - groupId: g_dev
      outerAdapters:
      - name: es7
        key: exampleKey_dev
        hosts: http://192.168.1.240:9200 # 127.0.0.1:9200 for rest mode
        properties:
          mode: rest # or rest
          cluster.name: elasticsearch

3.需要同步的表的查询文件：

/opt/app/canal.adapter/conf/es7/std_goods_dev.yml

/opt/app/canal.adapter/conf/es7/std_goods_test.yml

#文件中的每一项参数，必须与上面 canal.adapter application.yml文件中一致。

#没有一项参数是多余的。必须一致。

3.1 yml file

[root@std-uat es7]# cat std_goods_dev.yml

dataSourceKey: defaultDS_dev
destination: SYNC_ES_STD_DEV
outerAdapterKey: exampleKey_dev
groupId: g_dev
esMapping:
  _index: std_goods_dev
  _id: id
  sql: "select id,goods_id,cate_id,platform_cate_id,brand_id,brand_name,goods_no,goods_name,goods_subtitle,goods_img,sale,waterflood_sale,sort,is_show,del_flag,show_time,hide_time,market_price,platform_price,business_id,business_name,create_time,update_time from t_goods g"
  etlCondition: "where del_flag=0  and g.id>={0} and g.id<={1}"
  commitBatch: 3000

[root@std-uat es7]# cat std_goods_test.yml

dataSourceKey: defaultDS
destination: SYNC_ES_STD_TEST
outerAdapterKey: exampleKey_test
groupId: g_test
esMapping:
  _index: std_goods_test
  _id: id
  sql: "select id,goods_id,cate_id,platform_cate_id,brand_id,brand_name,goods_no,goods_name,goods_subtitle,goods_img,sale,waterflood_sale,sort,is_show,del_flag,show_time,hide_time,market_price,platform_price,business_id,business_name,create_time,update_time from t_goods g"
  etlCondition: "where del_flag=0  and g.id>={0} and g.id<={1}"
  commitBatch: 3000

[root@std-uat es7]#

3.2 json file
#建立es index 对应的json 文件：
[root@std-uat es_index_create]# cat std_goods_test.json

{
  "mappings":{
    "std_goods_doc":{
    "properties":{
        "id": {"type": "integer"},
        "goods_id": {"type": "keyword"},
        "cate_id": {"type": "integer"},
	"platform_cate_id": {"type": "integer"},
        "brand_id": {"type": "integer"},
        "brand_name": {"type": "text","analyzer":"ik_max_word","search_analyzer":"ik_max_word"},
        "goods_no": {"type": "keyword"},
        "goods_name": {"type": "text"},
        "goods_subtitle": {"type": "keyword"},
        "goods_img": {"type": "keyword"},
        "sale": {"type": "integer"},
        "waterflood_sale":{"type":"integer"},
        "sort": {"type":"integer"},
        "is_show":{"type":"integer"},
	"del_flag":{"type":"integer"},
        "show_time":{"type":"date"},
        "hide_time":{"type":"date"},
        "market_price":{"type":"double"},
        "platform_price":{"type":"double"},
        "business_id":{"type":"integer"},
        "business_name":{"type":"keyword"},
        "create_time":{"type":"date"},
        "update_time":{"type":"date"}

                 }
                }
        }
}

4.相关ES index 操作命令

4.1建立索引(在std_goods_dev.json 文件当前目录下执行)：

	curl -XPUT -H "Content-Type: application/json" http://127.0.0.1:9200/std_goods_dev?include_type_name=true -d  "@std_goods_dev.json"

	curl -XPUT -H "Content-Type: application/json" http://127.0.0.1:9200/std_goods_test?include_type_name=true -d  "@std_goods_test.json"

4.2查看ES中的索引

	curl -XGET http://127.0.0.1:9200/_cat/indices?v

4.3删除ES中的索引

#delete index.
#curl -XDELETE http://127.0.0.1:9200/std_goods_dev
#curl -XDELETE http://127.0.0.1:9200/std_goods_test
#warning:delete command.

4.4全量同步数据到ES

#注意：exampleKey_test 必须和前面配置文件中的key 一致。


curl -X POST http://127.0.0.1:8081/etl/es7/exampleKey_test/std_goods_test.yml
curl -X POST http://127.0.0.1:8081/etl/es7/exampleKey_dev/std_goods_dev.yml