DTS數據傳輸同步MySQL至Elasticsearch實戰

作者：Tinywan 2024-08-05 09:31:00

本章節通過RDS MySQL中的生產數據實時同步到阿里云Elasticsearch中進行搜索查詢，通過數據傳輸服務DTS（Data Transmission Service）進行數據同步操作。

概述

數據傳輸服務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會將源實例中待同步對象的全量數據在目標集群中初始化，作為后續增量同步數據的基線數據。

待全量同步完成，增量同步進行中時，您即可在Elasticsearch中查看同步成功的數據。

圖片

數據同步完成后，我們通過Kibana訪問實例進行數據驗證。

圖片

在Kibana區域，單擊公網入口
在登錄頁面輸入賬號和密碼，單擊登錄
單擊Kibana頁面左上角的image圖標，選擇Management > 開發工具（Dev Tools）。
在Console頁簽下，執行如下命令訪問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" : "發飾",
          "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中插入一條數據，在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 );

這里通過精準查詢方式查詢

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
        }
      }
    ]
  }
}

圖片

責任編輯：武曉燕來源：開源技術小棧