《果然新鲜》电商项目（44）- 利用Logstash自动同步数据库内容到ES

《果然新鲜》电商项目（44）- 利用Logstash自动同步数据库内容到ES

文章目录

​​1. 如何实现数据库与ES同步​​​​2.数据库数据录入​​​​3. logstash同步数据库内容到ES​​

​​3.1 logstash安装插件​​

​​3.1.1 logstash-input-jdbc插件安装​​​​3.1.2 logstash-output-elasticsearch插件安装​​

​​3.2 logstash配置​​

​​3.2.1 logstash配置文件讲解​​​​3.2.2 上传配置文件​​

​​4. 测试​​​​5. 总结​​

1. 如何实现数据库与ES同步

我们都知道ES是搜索引擎，它的搜索速度比数据库快很多，那如何将数据库里面的内容同步到ES呢？这个时候就需要用到中间件​​Logstash​​了。

Logstash是一个开源数据收集引擎，具有实时管道功能，Logstash可以动态地将来自不同数据源的数据统一起来，并将数据标准化到你所选择的目的地。

1.配置MySQL信息有：

数据库连接信息数据库查询最新数据SQL

2.配置定时器：

配置定时器的定时规则（如：每分钟从数据库里定时一次）

3.配置ES：

配置ES连接信息ES文档id等信息

2.数据库数据录入

数据库的表设计已经在前面的《商品搜索服务数据库设计》已经讲解过了，为了本文有演示效果和以后的博客讲解，先插入假数据

category表：

INSERT INTO `guoranxinxian-goods`.`category`(`ID`, `PARENT_ID`, `NAME`, `STATUS`, `SORT_ORDER`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (1, 0, '新鲜水果', 0, 0, NULL, NULL, '2020-03-02 15:00:57', '2020-03-02 15:00:57', '2020-03-02 15:00:57');INSERT INTO `guoranxinxian-goods`.`category`(`ID`, `PARENT_ID`, `NAME`, `STATUS`, `SORT_ORDER`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (2, 1, '热销水果', 0, 0, NULL, NULL, '2020-03-02 15:02:08', '2020-03-02 15:02:08', '2020-03-02 15:02:08');INSERT INTO `guoranxinxian-goods`.`category`(`ID`, `PARENT_ID`, `NAME`, `STATUS`, `SORT_ORDER`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (3, 1, '时令水果', 0, 0, NULL, NULL, '2020-03-02 15:02:08', '2020-03-02 15:02:08', '2020-03-02 15:02:08');INSERT INTO `guoranxinxian-goods`.`category`(`ID`, `PARENT_ID`, `NAME`, `STATUS`, `SORT_ORDER`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (4, 1, '热带水果', 0, 0, NULL, NULL, '2020-03-02 15:02:08', '2020-03-02 15:02:08', '2020-03-02 15:02:08');INSERT INTO `guoranxinxian-goods`.`category`(`ID`, `PARENT_ID`, `NAME`, `STATUS`, `SORT_ORDER`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (5, 1, '地标水果', 0, 0, NULL, NULL, '2020-03-02 15:02:08', '2020-03-02 15:02:08', '2020-03-02 15:02:08');INSERT INTO `guoranxinxian-goods`.`category`(`ID`, `PARENT_ID`, `NAME`, `STATUS`, `SORT_ORDER`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (6, 2, '苹果', 0, 0, NULL, NULL, '2020-03-02 15:03:19', '2020-03-02 15:03:19', '2020-03-02 15:03:19');INSERT INTO `guoranxinxian-goods`.`category`(`ID`, `PARENT_ID`, `NAME`, `STATUS`, `SORT_ORDER`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (7, 2, '橙子', 0, 0, NULL, NULL, '2020-03-02 15:03:19', '2020-03-02 15:03:19', '2020-03-02 15:03:19');INSERT INTO `guoranxinxian-goods`.`category`(`ID`, `PARENT_ID`, `NAME`, `STATUS`, `SORT_ORDER`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (8, 0, '海鲜水产', 0, 0, NULL, NULL, '2020-03-02 15:28:39', '2020-03-02 15:28:39', '2020-03-02 15:28:39');INSERT INTO `guoranxinxian-goods`.`category`(`ID`, `PARENT_ID`, `NAME`, `STATUS`, `SORT_ORDER`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (9, 8, '鱼类', 0, 0, NULL, NULL, '2020-03-02 15:28:49', '2020-03-02 15:28:49', '2020-03-02 15:28:49');INSERT INTO `guoranxinxian-goods`.`category`(`ID`, `PARENT_ID`, `NAME`, `STATUS`, `SORT_ORDER`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (10, 8, '虾类', 0, 0, NULL, NULL, '2020-03-02 15:28:49', '2020-03-02 15:28:49', '2020-03-02 15:28:49');INSERT INTO `guoranxinxian-goods`.`category`(`ID`, `PARENT_ID`, `NAME`, `STATUS`, `SORT_ORDER`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (11, 9, '三文鱼', 0, 0, NULL, NULL, '2020-03-02 15:30:10', '2020-03-02 15:30:10', '2020-03-02 15:30:10');INSERT INTO `guoranxinxian-goods`.`category`(`ID`, `PARENT_ID`, `NAME`, `STATUS`, `SORT_ORDER`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (12, 9, '鳕鱼', 0, 0, NULL, NULL, '2020-03-02 15:30:10', '2020-03-02 15:30:10', '2020-03-02 15:30:10');

attribute_key表：

INSERT INTO `guoranxinxian-goods`.`attribute_key`(`ID`, `CATEGORY_ID`, `ATTRIBUTE_NAME`, `NAME_SORT`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (1, 6, '重量', '0', 1, NULL, '2020-03-02 15:34:35', '2020-03-02 15:34:35', '2020-03-02 15:34:35');INSERT INTO `guoranxinxian-goods`.`attribute_key`(`ID`, `CATEGORY_ID`, `ATTRIBUTE_NAME`, `NAME_SORT`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (2, 6, '类别', '0', 1, NULL, '2020-03-02 15:34:35', '2020-03-02 15:34:35', '2020-03-02 15:34:35');INSERT INTO `guoranxinxian-goods`.`attribute_key`(`ID`, `CATEGORY_ID`, `ATTRIBUTE_NAME`, `NAME_SORT`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (3, 6, '原产地', '0', 1, NULL, '2020-03-02 15:34:35', '2020-03-02 15:34:35', '2020-03-02 15:34:35');INSERT INTO `guoranxinxian-goods`.`attribute_key`(`ID`, `CATEGORY_ID`, `ATTRIBUTE_NAME`, `NAME_SORT`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (4, 6, '售卖方式', '0', 1, NULL, '2020-03-02 15:34:35', '2020-03-02 15:34:35', '2020-03-02 15:34:35');

attribute_vallue表：

INSERT INTO `guoranxinxian-goods`.`attribute_value`(`ID`, `ATTRIBUTE_ID`, `ATTRIBUTE_VALUE`, `VALUE_SORT`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (1, '1', '3kg-4kg', '0', 1, NULL, '2020-03-02 15:36:27', '2020-03-02 15:36:27', '2020-03-02 15:36:27');INSERT INTO `guoranxinxian-goods`.`attribute_value`(`ID`, `ATTRIBUTE_ID`, `ATTRIBUTE_VALUE`, `VALUE_SORT`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (2, '1', '1000g以下', '0', 1, NULL, '2020-03-02 15:36:42', '2020-03-02 15:36:42', '2020-03-02 15:36:42');INSERT INTO `guoranxinxian-goods`.`attribute_value`(`ID`, `ATTRIBUTE_ID`, `ATTRIBUTE_VALUE`, `VALUE_SORT`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (3, '1', '1000-1999g', '0', 1, NULL, '2020-03-02 15:36:43', '2020-03-02 15:36:43', '2020-03-02 15:36:43');INSERT INTO `guoranxinxian-goods`.`attribute_value`(`ID`, `ATTRIBUTE_ID`, `ATTRIBUTE_VALUE`, `VALUE_SORT`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (4, '1', '2000-3999g', '0', 1, NULL, '2020-03-02 15:36:43', '2020-03-02 15:36:43', '2020-03-02 15:36:43');INSERT INTO `guoranxinxian-goods`.`attribute_value`(`ID`, `ATTRIBUTE_ID`, `ATTRIBUTE_VALUE`, `VALUE_SORT`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (5, '2', '红富士', '0', 1, NULL, '2020-03-02 15:38:55', '2020-03-02 15:38:55', '2020-03-02 15:38:55');INSERT INTO `guoranxinxian-goods`.`attribute_value`(`ID`, `ATTRIBUTE_ID`, `ATTRIBUTE_VALUE`, `VALUE_SORT`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (6, '2', '雪莲果', '0', 1, NULL, '2020-03-02 15:38:55', '2020-03-02 15:38:55', '2020-03-02 15:38:55');INSERT INTO `guoranxinxian-goods`.`attribute_value`(`ID`, `ATTRIBUTE_ID`, `ATTRIBUTE_VALUE`, `VALUE_SORT`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (7, '2', '徐香', '0', 1, NULL, '2020-03-02 15:38:55', '2020-03-02 15:38:55', '2020-03-02 15:38:55');INSERT INTO `guoranxinxian-goods`.`attribute_value`(`ID`, `ATTRIBUTE_ID`, `ATTRIBUTE_VALUE`, `VALUE_SORT`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (8, '3', '陕西', '0', 1, NULL, '2020-03-02 15:38:57', '2020-03-02 15:38:57', '2020-03-02 15:38:57');INSERT INTO `guoranxinxian-goods`.`attribute_value`(`ID`, `ATTRIBUTE_ID`, `ATTRIBUTE_VALUE`, `VALUE_SORT`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (9, '3', '四川', '0', 1, NULL, '2020-03-02 15:38:57', '2020-03-02 15:38:57', '2020-03-02 15:38:57');INSERT INTO `guoranxinxian-goods`.`attribute_value`(`ID`, `ATTRIBUTE_ID`, `ATTRIBUTE_VALUE`, `VALUE_SORT`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (10, '3', '泰国', '0', 1, NULL, '2020-03-02 15:38:57', '2020-03-02 15:38:57', '2020-03-02 15:38:57');INSERT INTO `guoranxinxian-goods`.`attribute_value`(`ID`, `ATTRIBUTE_ID`, `ATTRIBUTE_VALUE`, `VALUE_SORT`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (11, '3', '海南', '0', 1, NULL, '2020-03-02 15:38:59', '2020-03-02 15:38:59', '2020-03-02 15:38:59');INSERT INTO `guoranxinxian-goods`.`attribute_value`(`ID`, `ATTRIBUTE_ID`, `ATTRIBUTE_VALUE`, `VALUE_SORT`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (12, '3', '山东', '0', 1, NULL, '2020-03-02 15:38:59', '2020-03-02 15:38:59', '2020-03-02 15:38:59');INSERT INTO `guoranxinxian-goods`.`attribute_value`(`ID`, `ATTRIBUTE_ID`, `ATTRIBUTE_VALUE`, `VALUE_SORT`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (13, '3', '广西', '0', 1, NULL, '2020-03-02 15:38:59', '2020-03-02 15:38:59', '2020-03-02 15:38:59');INSERT INTO `guoranxinxian-goods`.`attribute_value`(`ID`, `ATTRIBUTE_ID`, `ATTRIBUTE_VALUE`, `VALUE_SORT`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (14, '3', '越南', '0', 1, NULL, '2020-03-02 15:38:59', '2020-03-02 15:38:59', '2020-03-02 15:38:59');

product表：

INSERT INTO `guoranxinxian-goods`.`product`(`ID`, `CATEGORY_ID`, `NAME`, `SUBTITLE`, `MAIN_IMAGE`, `SUB_IMAGES`, `DETAIL`, `ATTRIBUTE_LIST`, `PRICE`, `STOCK`, `STATUS`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (1, 6, '烟台红富士苹果', '红富士苹果', ''{\"imgages\":[{\"'烟台红富士苹果 12个 净重2.6kg以上', '1,2,3,4', NULL, NULL, 0, 1, NULL, '2020-03-02 16:02:40', NULL, '2020-03-02 16:02:40');

product_specs表：

INSERT INTO `guoranxinxian-goods`.`product_specs`(`ID`, `PRODUCT_ID`, `PRODUCT_SPECS`, `SPECS_SEQ`, `PRODUCT_STOCK`, `PRODUCT_PRICE`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (1, 1, '{\"内存\":\"4G\",\"颜色\":\"红色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', 0, 30, 3699.00000000, 1, NULL, '2019-03-02 15:50:04', '2019-03-02 15:50:04', '2019-03-02 15:50:04');INSERT INTO `guoranxinxian-goods`.`product_specs`(`ID`, `PRODUCT_ID`, `PRODUCT_SPECS`, `SPECS_SEQ`, `PRODUCT_STOCK`, `PRODUCT_PRICE`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (2, 1, '{\"内存\":\"8G\",\"颜色\":\"白色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', 0, 30, 3899.00000000, 1, NULL, '2019-03-02 15:50:04', '2019-03-02 15:50:04', '2019-03-02 15:50:04');INSERT INTO `guoranxinxian-goods`.`product_specs`(`ID`, `PRODUCT_ID`, `PRODUCT_SPECS`, `SPECS_SEQ`, `PRODUCT_STOCK`, `PRODUCT_PRICE`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (3, 1, '{\"内存\":\"16G\",\"颜色\":\"白色\",\"年份\":\"2019\",\"尺寸\":\"16寸\"}', 0, 30, 4199.00000000, 1, NULL, '2019-03-02 15:50:04', '2019-03-02 15:50:04', '2019-03-02 15:50:04');

3. logstash同步数据库内容到ES

3.1 logstash安装插件

3.1.1 logstash-input-jdbc插件安装

如果要要实现logstash同步MySQL的数据到ES，首先的前提是要让logstash与Mysql建立连接，那该如何建立呢？其实是需要安装jdbc插件的，安装方法如下：

1.启动logstash：

docker start logstash

2.进入logstash插件目录：

docker exec -it logstash /bin/bashcd /usr/share/logstash/bin

3.在线安装jdbc插件：

./logstash-plugin install logstash-input-jdbc

3.1.2 logstash-output-elasticsearch插件安装

要让logstash与es能进行信息交互，还需要安装“logstash-output-elasticsearch”插件。 进入logstash目录后，执行安装命令：

./logstash-plugin install logstash-output-elasticsearch

3.2 logstash配置

3.2.1 logstash配置文件讲解

先贴上logstash配置内容（mysql.conf）:

input { jdbc { jdbc_driver_library => "/usr/share/logstash/lib/mysql-connector-java-5.1.46.jar" jdbc_driver_class => "com.mysql.jdbc.Driver" jdbc_connection_string => "jdbc:mysql://192.168.18.166:3306/guoranxinxian-goods" jdbc_user => "root" jdbc_password => "123456" schedule => "* * * * *" statement => "SELECT * FROM product WHERE UPDATED_TIME >= :sql_last_value" use_column_value => true tracking_column_type => "timestamp" tracking_column => "updated_time" last_run_metadata_path => "syncpoint_table" }}output { elasticsearch { hosts => ["192.168.162.134:9200"] index => "product" document_id => "%{id}" document_type => "product" } stdout { codec => json_lines }}

3.2.2 上传配置文件

注意：上面的配置文件里面配置了mysql 的驱动包，所以要-驱动包，-：测试

1.启动elasticsearch，成功：

docker start elasticsearch

docker start kibana

## 进入容器docker exec -it logstash /bin/bashcd /usr/share/logstash/config/## 修改mysql.conf名字mv mysql.conf logstash.conf## 替换默认启动配置文件rm -rf /usr/share/logstash/pipeline/logstash.conf mv logstash.conf /usr/share/logstash/pipelineexitdocker restart logstash

5.查看日志：

docker logs -f logstash

GET product/_search

7.本地数据库再次插入一条数据

INSERT INTO `guoranxinxian-goods`.`product`(`ID`, `CATEGORY_ID`, `NAME`, `SUBTITLE`, `MAIN_IMAGE`, `SUB_IMAGES`, `DETAIL`, `ATTRIBUTE_LIST`, `PRICE`, `STOCK`, `STATUS`, `REVISION`, `CREATED_BY`, `CREATED_TIME`, `UPDATED_BY`, `UPDATED_TIME`) VALUES (2, 6, '赣南脐橙', '甜橙子', ''{\"imgages\":[{\"'赣南脐橙 鲜甜橙子 2.5kg装 单果160g-200g 生鲜自营新鲜水果', '1,2,3,4', NULL, NULL, 0, 1, NULL, '2020-03-31 17:00:40', NULL, '2020-03-31 17:00:40');

5. 总结

本文主要讲解“如何利用Logstash自动同步数据库内容到ES”，说明了原理和注意的细节。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。