DTS数据传输同步MySQL至Elasticsearch实战-益华科技

概述

数据传输服务DTS（Data Transmission Service）是数输同实战阿里云提供的实时数据流服务，支持关系型数据库（RDBMS）、据传非关系型的数输同实战数据库（NoSQL）、数据多维分析（OLAP）等数据源间的据传数据交互，集数据同步、数输同实战迁移、据传订阅、数输同实战集成、据传加工于一体，数输同实战助您构建安全、据传可扩展、数输同实战高可用的据传数据架构。其底层基础设施采用阿里双11异地多活架构，数输同实战为数千下游应用提供实时数据流，据传已在线上稳定运行7年之久。数输同实战

本章节通过RDS MySQL中的云服务器生产数据实时同步到阿里云Elasticsearch中进行搜索查询，通过数据传输服务DTS（Data Transmission Service）进行数据同步操作。

前提条件

已创建源RDS MySQL实例，详情请参见快速创建RDS MySQL实例。已创建目标Elasticsearch实例，详情请参见创建阿里云Elasticsearch实例。目标Elasticsearch实例的存储空间须大于源RDS MySQL实例占用的存储空间。

概念对应关系

图片

创建DTS实例

创建同步任务

图片

选择需要同步的RDS MySQL表

图片

任务同步进展

第一次同步是全量同步

验证数据同步结果

默认情况下，您还需要同时选中库表结构同步和全量同步。预检查完成后，DTS会将源实例中待同步对象的全量数据在目标集群中初始化，作为后续增量同步数据的基线数据。

待全量同步完成，增量同步进行中时，b2b信息网您即可在Elasticsearch中查看同步成功的数据。

图片

数据同步完成后，我们通过Kibana访问实例进行数据验证。

图片

在Kibana区域，单击公网入口在登录页面输入账号和密码，单击登录单击Kibana页面左上角的image图标，选择Management > 开发工具（Dev Tools）。在Console页签下，执行如下命令访问Elasticsearch实例

全局查询

复制GET /mall_category/_search1.

搜索成功后，返回结果如下，以下结果表示全量同步到Elasticsearch成功。

复制{ "took" : 4, "timed_out" : false, "_shards" : { "total" : 5, "successful" : 5, "skipped" : 0, "failed" : 0 }, "hits" : { "total" : { "value" : 1041, "relation" : "eq" }, "max_score" : 1.0, "hits" : [ { "_index" : "mall_category", "_type" : "mall_category", "_id" : "2513", "_score" : 1.0, "_source" : { "id" : 2513, "name" : "有商品无法删2", "parent_id" : 2512, "is_show" : 1, "sort_order" : 0, "create_time" : 1679537485, "update_time" : 1679537485, "delete_time" : 1679542632 } }, .... { "_index" : "mall_category", "_type" : "mall_category", "_id" : "1310", "_score" : 1.0, "_source" : { "id" : 1310, "name" : "发饰", "parent_id" : 1258, "is_show" : 1, "sort_order" : 0, "create_time" : 0, "update_time" : 0, "delete_time" : 0 } } ] } }1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.

条件查询

复制GET /mall_category/_search { "query": { "match": { "name": "有商品无法删2" } } }1.2.3.4.5.6.7.8.

搜索成功后，返回结果如下：

复制{ "took" : 3, "timed_out" : false, "_shards" : { "total" : 5, "successful" : 5, "skipped" : 0, "failed" : 0 }, "hits" : { "total" : { "value" : 59, "relation" : "eq" }, "max_score" : 21.80254, "hits" : [ { "_index" : "mall_category", "_type" : "mall_category", "_id" : "2513", "_score" : 21.80254, "_source" : { "id" : 2513, "name" : "有商品无法删2", "parent_id" : 2512, "is_show" : 1, "sort_order" : 0, "create_time" : 1679537485, "update_time" : 1679537485, "delete_time" : 1679542632 } }, { "_index" : "mall_category", "_type" : "mall_category", "_id" : "2512", "_score" : 16.643557, "_source" : { "id" : 2512, "name" : "有商品无法删1", "parent_id" : 2511, "is_show" : 1, "image_url" : "", "sort_order" : 0, "create_time" : 1679537458, "update_time" : 1679537458, "delete_time" : 1679554114 } }, { "_index" : "mall_category", "_type" : "mall_category", "_id" : "2511", "_score" : 15.356989, "_source" : { "id" : 2511, "name" : "测试有商品无法删", "parent_id" : 0, "is_show" : 1, "image_url" : "", "sort_order" : 0, "create_time" : 1679537448, "update_time" : 1679537448, "delete_time" : 1679554191 } }, { "_index" : "mall_category", "_type" : "mall_category", "_id" : "2508", "_score" : 4.6345234, "_source" : { "id" : 2508, "name" : "无糖代糖", "parent_id" : 0, "is_show" : 1, "sort_order" : 0, "create_time" : 1679534815, "update_time" : 1679534815, "delete_time" : 1679642540 } }, { "_index" : "mall_category", "_type" : "mall_category", "_id" : "2045", "_score" : 4.5389233, "_source" : { "id" : 2045, "name" : "无痕塑身", "parent_id" : 1350, "is_show" : 1, "sort_order" : 0, "create_time" : 0, "update_time" : 0, "delete_time" : 0 } }, { "_index" : "mall_category", "_type" : "mall_category", "_id" : "2240", "_score" : 4.3105736, "_source" : { "id" : 2240, "name" : "无人机", "parent_id" : 1363, "is_show" : 1, "sort_order" : 0, "create_time" : 0, "update_time" : 0, "delete_time" : 0 } } ] } }1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.90.91.92.93.94.95.96.97.98.99.100.101.102.103.104.105.106.107.108.109.110.111.112.113.114.115.116.117.

精准查询

复制GET /mall_category/_search { "query": { "match_phrase": { "name": "有商品无法删2" } } }1.2.3.4.5.6.7.8.

搜索成功后，返回结果如下：

复制{ "took" : 2, "timed_out" : false, "_shards" : { "total" : 5, "successful" : 5, "skipped" : 0, "failed" : 0 }, "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : 21.80254, "hits" : [ { "_index" : "mall_category", "_type" : "mall_category", "_id" : "2513", "_score" : 21.80254, "_source" : { "id" : 2513, "name" : "有商品无法删2", "parent_id" : 2512, "is_show" : 1, "sort_order" : 0, "create_time" : 1679537485, "update_time" : 1679537485, "delete_time" : 1679542632 } } ] } }1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.

增量同步

在MySQL中插入一条数据，在Elasticsearch中查看增量数据同步结果。例如通过以下SQL语句插入一条数据。

复制INSERT INTO `mall_category` ( `name`, `parent_id`, `is_show`, `image_url`, `sort_order`, `create_time`, `update_time`, `delete_time` ) VALUES ( 开源技术小栈, 1361, 1, https://img.tinywan.com/shop/img/2024-12/3a5cbd823.png, 0, 0, 0, 0 );1.2.3.4.5.6.

这里通过精准查询方式查询

复制GET /mall_category/_search { "query": { "match_phrase": { "name": "开源技术小栈" } } }1.2.3.4.5.6.7.8.

在Elasticsearch中查看结果，预期结果如下：

复制{ "took" : 1, "timed_out" : false, "_shards" : { "total" : 5, "successful" : 5, "skipped" : 0, "failed" : 0 }, "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : 21.59761, "hits" : [ { "_index" : "mall_category", "_type" : "mall_category", "_id" : "2537", "_score" : 21.59761, "_source" : { "update_time" : 0, "delete_time" : 0, "create_time" : 0, "image_url" : "https://img.tinywan.com/shop/img/2024-12/3a5cbd823.png", "parent_id" : 1361, "name" : "开源技术小栈", "id" : 2537, "sort_order" : 0, "is_show" : 1 } } ] } }1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.

图片

服务器托管