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