DTS數(shù)據傳輸同步MySQL至Elasticsearch實戰(zhàn)
作者：Tinywan
本章節(jié)通過RDS MySQL中的生產數(shù)據實時同步到阿里云Elasticsearch中進行搜索查詢，通過數(shù)據傳輸服務DTS（Data Transmission Service）進行數(shù)據同步操作。
概述
數(shù)據傳輸服務DTS（Data Transmission Service）是阿里云提供的實時數(shù)據流服務，支持關系型數(shù)據庫（RDBMS）、非關系型的數(shù)據庫（NoSQL）、數(shù)據多維分析（OLAP）等數(shù)據源間的數(shù)據交互，集數(shù)據同步、遷移、訂閱、集成、加工于一體，助您構建安全、可擴展、高可用的數(shù)據架構。其底層基礎設施采用阿里雙11異地多活架構，為數(shù)千下游應用提供實時數(shù)據流，已在線上穩(wěn)定運行7年之久。
本章節(jié)通過RDS MySQL中的生產數(shù)據實時同步到阿里云Elasticsearch中進行搜索查詢，通過數(shù)據傳輸服務DTS（Data Transmission Service）進行數(shù)據同步操作。
前提條件
- 已創(chuàng)建源RDS MySQL實例，詳情請參見快速創(chuàng)建RDS MySQL實例。
- 已創(chuàng)建目標Elasticsearch實例，詳情請參見創(chuàng)建阿里云Elasticsearch實例。
- 目標Elasticsearch實例的存儲空間須大于源RDS MySQL實例占用的存儲空間。
概念對應關系
圖片
創(chuàng)建DTS實例
創(chuàng)建同步任務
圖片
選擇需要同步的RDS MySQL表
圖片
任務同步進展
第一次同步是全量同步
驗證數(shù)據同步結果
默認情況下，您還需要同時選中庫表結構同步和全量同步。預檢查完成后，DTS會將源實例中待同步對象的全量數(shù)據在目標集群中初始化，作為后續(xù)增量同步數(shù)據的基線數(shù)據。
待全量同步完成，增量同步進行中時，您即可在Elasticsearch中查看同步成功的數(shù)據。
圖片
數(shù)據同步完成后，我們通過Kibana訪問實例進行數(shù)據驗證。
圖片
- 在Kibana區(qū)域，單擊公網入口
- 在登錄頁面輸入賬號和密碼，單擊登錄
- 單擊Kibana頁面左上角的image圖標，選擇Management > 開發(fā)工具（Dev Tools）。
- 在Console頁簽下，執(zhí)行如下命令訪問Elasticsearch實例
全局查詢
GET /mall_category/_search搜索成功后，返回結果如下，以下結果表示全量同步到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" : "發(fā)飾",
"parent_id" : 1258,
"is_show" : 1,
"sort_order" : 0,
"create_time" : 0,
"update_time" : 0,
"delete_time" : 0
}
}
]
}
}條件查詢
GET /mall_category/_search
{
"query": {
"match": {
"name": "有商品無法刪2"
}
}
}搜索成功后，返回結果如下：
{
"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
}
}
]
}
}精準查詢
GET /mall_category/_search
{
"query": {
"match_phrase": {
"name": "有商品無法刪2"
}
}
}搜索成功后，返回結果如下：
{
"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
}
}
]
}
}增量同步
在MySQL中插入一條數(shù)據，在Elasticsearch中查看增量數(shù)據同步結果。例如通過以下SQL語句插入一條數(shù)據。
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 );這里通過精準查詢方式查詢
GET /mall_category/_search
{
"query": {
"match_phrase": {
"name": "開源技術小棧"
}
}
}在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
}
}
]
}
}
圖片
責任編輯：武曉燕
來源：
開源技術小棧
