文章目录


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

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

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

我自己画了一张数据库与ES同步的原理图,如下:
《果然新鲜》电商项目(44)- 利用Logstash自动同步数据库内容到ES_数据
首先在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, '烟台红富士苹果', '红富士苹果', 'https://ss1.bdstatic.com/70cFvXSh_Q1YnxGkpoWK1HF6hhy/it/u=1296845473,2218916964&fm=26&gp=0.jpg', '{\"imgages\":[{\"https://ss1.bdstatic.com/70cFuXSh_Q1YnxGkpoWK1HF6hhy/it/u=3443692772,1819655971&fm=26&gp=0.jpg\"},{\"https://ss0.bdstatic.com/70cFuHSh_Q1YnxGkpoWK1HF6hhy/it/u=1435166030,2305236542&fm=26&gp=0.jpg\"}]}', '烟台红富士苹果 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/bash
cd /usr/share/logstash/bin

3.在线安装jdbc插件:

./logstash-plugin install logstash-input-jdbc

《果然新鲜》电商项目(44)- 利用Logstash自动同步数据库内容到ES_database_02
最后一句的中文翻译是:“错误:安装终止,插件“ logstash-input-jdbc” 已经被’logstash-integration-jdbc’支持”。表示已经安装了,无需再安装。

3.1.2 logstash-output-elasticsearch插件安装

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

./logstash-plugin install logstash-output-elasticsearch

《果然新鲜》电商项目(44)- 利用Logstash自动同步数据库内容到ES_数据库_03
安装成功!

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

配置字段字段解析:
《果然新鲜》电商项目(44)- 利用Logstash自动同步数据库内容到ES_数据库_04
《果然新鲜》电商项目(44)- 利用Logstash自动同步数据库内容到ES_database_05

3.2.2 上传配置文件

注意:上面的配置文件里面配置了mysql 的驱动包,所以要下载驱动包,下载地址:https://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.46/mysql-connector-java-5.1.46.jar

首先上传msql驱动包到logstash的lib目录​​/usr/share/logstash/lib​​​《果然新鲜》电商项目(44)- 利用Logstash自动同步数据库内容到ES_elasticsearch_06
然后上传配置文件mysql.conf到​​/usr/share/logstash/config​​目录(方法同上):
《果然新鲜》电商项目(44)- 利用Logstash自动同步数据库内容到ES_database_07

4. 测试

1.启动elasticsearch,成功:

docker start elasticsearch

《果然新鲜》电商项目(44)- 利用Logstash自动同步数据库内容到ES_数据库_08
2.启动kibana,成功:

docker start kibana

《果然新鲜》电商项目(44)- 利用Logstash自动同步数据库内容到ES_数据_09
3.在kibana控制台,搜索全部,可以看到一共搜索出3条数据:
《果然新鲜》电商项目(44)- 利用Logstash自动同步数据库内容到ES_数据_10
4.修改logstash.conf,退出并重启logstash

## 进入容器
docker exec -it logstash /bin/bash
cd /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/pipeline

exit
docker restart logstash

5.查看日志:

docker logs -f logstash

耐心等候,可以看到定时去查询数据库的内容,如下图:
《果然新鲜》电商项目(44)- 利用Logstash自动同步数据库内容到ES_database_11
6.查看es,可以看到已经查询出来了:

GET product/_search

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

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, '赣南脐橙', '甜橙子', 'https://ss1.bdstatic.com/70cFvXSh_Q1YnxGkpoWK1HF6hhy/it/u=1296845473,2218916964&fm=26&gp=0.jpg', '{\"imgages\":[{\"https://ss1.bdstatic.com/70cFuXSh_Q1YnxGkpoWK1HF6hhy/it/u=3443692772,1819655971&fm=26&gp=0.jpg\"},{\"https://ss0.bdstatic.com/70cFuHSh_Q1YnxGkpoWK1HF6hhy/it/u=1435166030,2305236542&fm=26&gp=0.jpg\"}]}', '赣南脐橙 鲜甜橙子 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');

《果然新鲜》电商项目(44)- 利用Logstash自动同步数据库内容到ES_database_13
日志可以看到查询到新的数据
《果然新鲜》电商项目(44)- 利用Logstash自动同步数据库内容到ES_mysql_14
es也能看到有新的数据写入:
《果然新鲜》电商项目(44)- 利用Logstash自动同步数据库内容到ES_数据_15

5. 总结

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