本章节通过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://www.528045.com/file/upload/202411/29/pklbko0cils.png",
"parent_id" : 1361,
"name" : "开源技术小栈",
"id" : 2537,
"sort_order" : 0,
"is_show" : 1
}
}
]
}
}图片
