用过 Solr 的朋友都知道，Solr 可以直接在配置文件中配置数据库连接从而完成索引的同步创建，但是 ElasticSearch 本身并不具备这样的功能，那如何建立索引呢？方法其实很多，可以使用 Java API 的方式建立索引，也可以通过 Logstash 的插件 logstash-input-jdbc 完成，今天来探讨下如何使用 logstash-input-jdbc 完成全量同步以及增量同步。

环境

本文以及后续 es 系列文章都基于 5.5.3 这个版本的 elasticsearch ，这个版本比较稳定，可以用于生产环境。

默认你已经搭建好 es 的基础环境，如还未搭建好，请参考前文。接下来只讲解 logstash 的安装使用。本文使用最新版本的 logstash ，版本号为 6.4.0。

系列文章

数据准备

如图所示，如果要实现这个搜索，首先要创建相关的索引，筛选条件有男生/女生，还有分类，属性，字数，连载状态，品质等，排序条件有人气，时间，字数，收藏，推荐，点击。

这些数据正常都不会再同一张表当中，而是分布于不同的表中，但这些数据都与作品的 id 紧紧关联。本文为了方便演示，把这些数据都放在同一张表当中，如果在实际使用的过程当中，如果遇到多张表的情况，可以写 sql 进行联合查询，同样也可以建立索引，实现方式详见下文。

创建表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
CREATE TABLE `book`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  `status` tinyint(11) NULL DEFAULT NULL,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `intro` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL,
  `icon` int(11) NULL DEFAULT NULL,
  `author` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `words` int(11) NULL DEFAULT NULL,
  `collection` int(11) NULL DEFAULT NULL,
  `goods` int(11) NULL DEFAULT NULL,
  `click` int(11) NULL DEFAULT NULL,
  `site` tinyint(11) NULL DEFAULT NULL,
  `sort` tinyint(11) NULL DEFAULT NULL,
  `vip` tinyint(11) NULL DEFAULT NULL,
  `popularity` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB  CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ;

导入测试数据

注：测试数据是通过爬虫在盗版网站抓的，个别数据胡乱填写的。

 INSERT INTO `book`(`id`, `create_time`, `update_time`, `status`, `name`, `intro`, `icon`, `author`, `words`, `collection`, `goods`, `click`, `site`, `sort`, `vip`, `popularity`) VALUES (1, '2017-12-27 20:53:01', '2018-09-10 20:53:46', 0, '火爆娱乐天王', '  有人说：“他是至高无上的音乐教皇！”有人说：“他是无人能及的影视国王！”还有人说：“他是神级作家、话剧大师、伟大的音乐家……”他使华夏元素狂暴冲击西方文化，却又能做出令西方人也不及的欧美音乐影视。他造就了无数的歌星影星，创作出无数的经典，建立了一个庞大的娱乐帝国。当所有人称颂他膜拜他的时候，只有他自己知道，他不过是一个意外来自异时空的文化使者。  ', 1, '茶与酒之歌', 230000, 4234, 42315, 523, 2, 9, 1, 12423);
INSERT INTO `book`(`id`, `create_time`, `update_time`, `status`, `name`, `intro`, `icon`, `author`, `words`, `collection`, `goods`, `click`, `site`, `sort`, `vip`, `popularity`) VALUES (2, '2017-12-27 20:53:01', '2017-12-27 20:53:03', 0, '无限平行进化', '  日复一日的枯燥生活让廖宇开始产生了难以抑制的厌倦。沉重庞大的经济负担使他的生活开始产生了变化。历史的缺失更是成为了他心中不散的谜团。某一天突如其来的陌生信息终于给他打开了一扇门。性格各异的X战警与复杂庞大的复仇者联盟会再度爆发怎样的战争。加勒比海上是否还会扬起第四艘传奇战舰的旗帜。铺天盖地的宇宙虫族与冰冷机械的人工智能谁才是最具有侵略性的文明。最终廖宇会如何选择，向左还是向右…… 各位书友要是觉得《无限平行进化》还不错的话请不要忘记向您QQ群和微博里的朋友推荐哦！无限平行进化最新章节,无限平行进化无弹窗,无限平行进化全文阅读.  ', 0, '雪色银狼CS', 555555, 450000, 13412, 555, 2, 9, 1, 342);
INSERT INTO `book`(`id`, `create_time`, `update_time`, `status`, `name`, `intro`, `icon`, `author`, `words`, `collection`, `goods`, `click`, `site`, `sort`, `vip`, `popularity`) VALUES (3, '2017-12-27 20:53:02', '2018-09-15 20:53:55', 0, '逆天药神', '  一个意外，让他在星空的彼岸两地相望。三人携手，杀出了这片大陆的四方威名。他，张凡，既然获得新生，就绝不再做弱者。既然要成为强者，就必须让这片星空颤抖！“既然是我的未来，那便只能由我主宰”  ', 1, '枫叶', 340000, 213334, 3421, 42314, 2, 9, 1, 52315);
INSERT INTO `book`(`id`, `create_time`, `update_time`, `status`, `name`, `intro`, `icon`, `author`, `words`, `collection`, `goods`, `click`, `site`, `sort`, `vip`, `popularity`) VALUES (4, '2017-12-27 20:53:02', '2018-08-16 20:53:59', 0, '锦绣风华之第一农家女1', '  前世她是铁血手腕的帝国集团总裁，却被心爱之人设计，魂归天国。 再次睁眼，眼前的三间茅草屋，一对小瘦猴。 就算是她定力再强悍，在他们喊出那声“娘”的时候，还是让她差点没跳起来。 前世活到28还是清白如兰，一个穿越就让她勉强算是B的身材，孕育出一对儿女？ 当然，这还是其次，最重要的是她丫的居然是未婚生子，这在现代都遭人白眼的事情，那个天杀的能告诉她，这个身体的原主，是不是太牛叉了，居然没有被浸猪笼。 只是，当这对瘦的皮包骨的小包子在她跟前，肿着两对眼泡忍者泪花跑前跑后，就算是她再不想面对现实，也无法坐视不理。 既然让她再次重生，她势必要左手挥舞锄头，右手执笔算盘，带着一对可爱的包子，发家致富。 购田地，建豪宅，买下人，顾长工，一切都再朝着让她满意的方向前进，而那些眼红嫉妒之辈，完全都是她业余之时的消遣，根本就不是一个等级。 但是，常在河边走，哪有不湿鞋，这个道理她明白，却没想到那鞋子会湿的这么快，面对那个如同妖孽般，表面谦谦公子，风华无双，实则腹黑狡诈，怎么坑死她怎么来，还让她有火没处发。 精彩小剧场 当一对粉雕玉琢的包子被一个风华绝代的男人一手一个抱进来，君瑶真心的黑面了，泪奔了。 他们这两个没良心的到底明不明白，什么是引狼入室啊。 “多谢宁公子送他们回来，您看如今天色已晚，为了宁公子的名声，小妇人也不敢久留，宁公子请回吧。”她快步上前，一把一个把小包子从那个男人怀里蒿出来，快言快语的下了逐客令。 男人好看的眉毛微挑，随后一模情绪从眸中迅速划过，快的难以捕捉。 “无妨，我来陪陪这两个小家伙，君娘子不必多心。” “我…”她差点没被噎死，她有什么好多心的，就冲着他觊觎她的孩子，就不能让她留下。 只是她的话没有说完，就被男人贴面而来的俊彦吓得向后退去，而男人含笑的黑眸和清淡的话语，却让她差点怒火狂飙，“还是你想我把他们带回去？” 君瑶大惊，带回去？那是绝对不可能的，她的儿女，谁敢打主意，谁就没活路，话虽然很直白，却独独对他不管用。  ', 1, '席妖妖', 444902, 23, 5353, 21323, 2, 9, 1, 314);
INSERT INTO `book`(`id`, `create_time`, `update_time`, `status`, `name`, `intro`, `icon`, `author`, `words`, `collection`, `goods`, `click`, `site`, `sort`, `vip`, `popularity`) VALUES (5, '2017-12-27 20:53:02', '2018-09-05 20:54:05', 0, '伏魔', '  富家子弟墨浞因为发现了村子中的秘密，在良心与亲情的折磨下，逃到了边境小城。因为一个香-艳而又恐怖的梦，墨浞经历了一些诡异的事，从而得知自己的前世与今生的使命。踏上藏地，历经磨难，克服了自己的心魔，战胜了...  ', 1, '一叶style', 490000, 526, 7687, 9, 2, 29, 1, 41516);		  

创建索引

使用 postman 等工具创建 es 索引，其中使用到了 ik 以及 pinyin 分词器，具体配置可以参考我前面的文章。

PUT: http://192.168.200.192:9200/novel

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
 
{
    "settings":{
        "number_of_shards":5,
        "number_of_replicas":1,
        "analysis":{
            "analyzer":{
                "pinyin_analyzer":{
                    "tokenizer":"ik_smart",
                    "filter":[
                        "full_pinyin_no_space",
                        "full_pinyin_with_space",
                        "first_letter_pinyin"
                    ]
                }
            },
            "filter":{
                "full_pinyin_no_space":{
                    "type":"pinyin",
                    "first_letter":"none",
                    "padding_char":""
                },
                "full_pinyin_with_space":{
                    "type":"pinyin",
                    "first_letter":"none",
                    "padding_char":" "
                },
                "first_letter_pinyin":{
                    "type":"pinyin",
                    "first_letter":"only",
                    "padding_char":""
                }
            }
        }
    },
    "mappings":{
        "book":{
            "properties":{
                "id":{
                    "type":"integer"
                },
                "words":{
                    "type":"integer"
                },
                "intro":{
                    "analyzer":"ik_smart",
                    "search_analyzer":"ik_smart",
                    "type":"text"
                },
                "name":{
                    "analyzer":"pinyin_analyzer",
                    "search_analyzer":"pinyin_analyzer",
                    "type":"text"
                },
                "sort":{
                    "type":"integer"
                },
                "updatetime":{
                    "type":"date",
                    "format":"yyyy-MM-dd HH:mm:ss"
                },
                "vip":{
                    "type":"boolean"
                },
                "site":{
                    "type":"integer"
                },
                "author":{
                    "analyzer":"pinyin_analyzer",
                    "search_analyzer":"pinyin_analyzer",
                    "type":"text"
                },
                "collection":{
                    "type":"integer"
                },
                "click":{
                    "type":"integer"
                },
                "popularity":{
                    "type":"integer"
                },
                "goods":{
                    "type":"integer"
                },
                "status":{
                    "type":"integer"
                }
            }
        }
    }
}

注意，这里索引的所有字段都是小写的，不要包含大写，否则后续会出现问题。
索引创建好了之后，安装 logstash。

安装 logstash

Tips: 因为 logstash 启动的时候，会占用较高的 CPU ，建议不要放在 es 集群的服务器上，最好换一台服务器进行安装。

1
2
3
 
wget https://artifacts.elastic.co/downloads/logstash/logstash-6.4.0.tar.gz

tar -xvf logstash-6.4.0.tar.gz

因为建立索引需要使用到 logstash-input-jdbc，所以先配置这个插件，这个插件在 logstash 5.0 之后就默认自带了，无需再次安装。

配置 logstash同步之全量同步

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
## 进入 logstash 根目录
cd /usr/local/es/logstash-6.4.0

## 创建存放 logstash-input-jdbc 相关配置以及依赖的目录
mkdir logstash-input-jdbc

cd logstash-input-jdbc

## 创建两个目录
mkdir lib & mkdir conf

## 下载 jdbc 所需的 mysql 驱动到 lib 目录
wget http://central.maven.org/maven2/mysql/mysql-connector-java/6.0.6/mysql-connector-java-6.0.6.jar -P lib/

## 进入 conf/ 目录
cd conf/

## 创建两个文件，一个是 jdbc 的 sql，另一个是 logstash-input-jdbc 的配置文件

vim mysql2es.sql

首先编写 sql， 构造索引所需的数据。由于是第一次同步到 es,所以进行全量同步，不过这里的全量也并非是一次性把查出库内所有数据，而是伪全量的增量同步。

mysql2es.sql 内容为

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
SELECT
	`id`,
	IFNULL( `update_time`, '1970-01-01 08:00:00' ) AS `updatetime`,
	`status`,
	`name`,
	IFNULL( `intro`, '' ) AS `intro`,
	`author`,
	`words`,
	`collection`,
	`goods`,
	`click`,
	`site`,
	`sort`,
	`vip`,
	`popularity` 
FROM
	book 
WHERE
	id >= :sql_last_value and id < :sql_last_value + 11

注意为了避免一次性查询，对数据库造成太大压力，因此这里使用了增量的方式来完成初始化同步到 es，这里的 :sql_last_value 是 logstash 上一步同步的最后的值，这里为 id，也可以是时间。因为演示用的测试数据较少，所以每次只同步了 10 条记录，如果实际使用，一次同步 1000 条比较合适，即 :sql_last_value + 1001

sql 查询出的字段名要与索引字段名称对应上，否则无法映射同步。

这里的 mysql2es.sql 将在 logstash-input-jdbc 中用到，创建文件 mysql2es.conf

1
 
vim mysql2es.conf

mysql2es.conf 的内容是：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
 
input {
    stdin {
    }
    jdbc {
      # 数据库
      jdbc_connection_string => "jdbc:mysql://192.168.199.192:3306/novel"
      # 用户名密码
      jdbc_user => "root"
      jdbc_password => "123456"
      # jar包的位置
      jdbc_driver_library => "/usr/local/es/logstash-6.4.0/logstash-input-jdbc/lib/mysql-connector-java-6.0.6.jar"
      # mysql的Driver
      jdbc_driver_class => "com.mysql.jdbc.Driver"
      # 读取这个sql
      statement_filepath => "/usr/local/es/logstash-6.4.0/logstash-input-jdbc/conf/mysql2es.sql"
      # 指定时区
      jdbc_default_timezone => "Asia/Shanghai"
      # 每分钟执行一次同步(分 时 天 月 年)，比如每十分钟(*/10 * * * *)
      schedule => "* * * * *"
      #索引的类型
      type => "book"

      use_column_value => "true"
      #tracking_column_type: 递增字段的类型，numeric 表示数值类型, timestamp 表示时间戳类型
      tracking_column_type => "numeric"
      # 递增字段
      tracking_column => "id"
      # 保存每次同步时递增字段的最后一个值到这个文件
      last_run_metadata_path => "/usr/local/es/logstash-6.4.0/logstash-input-jdbc/conf/full_sync"
    }
}

filter {

    json {
        source => "message"
        remove_field => ["message"]
    }
}

output {
    elasticsearch {
        hosts => "192.168.199.192:9200"
        # index 索引名
        index => "novel"
        # 需要关联的数据库中有有一个id字段，对应索引的id号
        document_id => "%{id}"
    }
    stdout {
        codec => json_lines
    }
}

启动logstash同步

1
 
/usr/local/es/logstash-6.4.0/bin/logstash -f /usr/local/es/logstash-6.4.0/logstash-input-jdbc/conf/mysql2es.conf

启动后稍等一会儿，如果配置正确会打印出执行的 sql:

1
 
SELECT `id`,IFNULL(`update_time`,'1970-01-01 08:00:00') AS `updatetime`,`status`,`name`,IFNULL(`intro`,'') AS `intro`,`author`,`words`,`collection`,`goods`,`click`,`site`,`sort`,`vip`,`popularity` FROM book WHERE id> 0 AND id< 0+11

查看 ElasticSearchHead 控制台，发现也已经有了 10 条索引数据。
由于每次同步 10 条，每分钟同步一次，5分钟后，测试的 50条记录也已经全部被同步到 es 里了。

ctrl + c 停止同步进程，查看文件

1
 
cat /usr/local/es/logstash-6.4.0/logstash-input-jdbc/conf/full_sync

文件中的数字正是上次最后同步的 id

logstash增量同步

根据 id 进行同步，如果对数据库中已被同步到 es 的数据进行了修改，这个数据也不会被同步更新到 es 当中去。

根据 id 每次同步 1000 条，这种同步方式也只适合第一次全量进行初始化时候使用，后续增量同步最好根据时间戳的方式完成。

修改 mysql2es.sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
SELECT
	`id`,
	IFNULL( `update_time`, '1970-01-01 08:00:00' ) AS `updatetime`,
	`status`,
	`name`,
	IFNULL( `intro`, '' ) AS `intro`,
	`author`,
	`words`,
	`collection`,
	`goods`,
	`click`,
	`site`,
	`sort`,
	`vip`,
	`popularity` 
FROM
	book 
WHERE
	update_time >= convert_tz(:sql_last_value, '+00:00','-08:00')
	order by update_time asc

注意这里使用到了 convert_tz 这个函数，原因是 logstash 会在同步时在最后同步时间增加 8 个小时，因此需要使用函数，减去 8 个小时才是正确的时间。 因为logstash 记录最后一次同步的值是最后一条记录的，所以，最好根据 update_time 进行升序排序，即取的值是离现在最近的时间。

修改 mysql2es.conf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
 
input {
    stdin {
    }
    jdbc {
      # 数据库
      jdbc_connection_string => "jdbc:mysql://192.168.199.192:3306/novel"
      # 用户名密码
      jdbc_user => "root"
      jdbc_password => "123456"
      # jar包的位置
      jdbc_driver_library => "/usr/local/es/logstash-6.4.0/logstash-input-jdbc/lib/mysql-connector-java-6.0.6.jar"
      # mysql的Driver
      jdbc_driver_class => "com.mysql.jdbc.Driver"
      # 读取这个sql
      statement_filepath => "/usr/local/es/logstash-6.4.0/logstash-input-jdbc/conf/mysql2es.sql"
      # 指定时区
      jdbc_default_timezone => "Asia/Shanghai"
      # 每分钟
      schedule => "* * * * *"
      #索引的类型
      type => "book"

      use_column_value => "true"
      #tracking_column_type: 递增字段的类型，numeric 表示数值类型, timestamp 表示时间戳类型
      tracking_column_type => "timestamp"
      # 递增字段
      tracking_column => "updatetime"
      # 保存每次同步时递增字段的最后一个值到这个文件
      last_run_metadata_path => "/usr/local/es/logstash-6.4.0/logstash-input-jdbc/conf/incr_sync"
    }
}

filter {

    json {
        source => "message"
        remove_field => ["message"]
    }
}

output {
    elasticsearch {
        hosts => "192.168.199.192:9200"
        # index 索引名
        index => "novel"
        # 需要关联的数据库中有有一个id字段，对应索引的id号
        document_id => "%{id}"
    }
    stdout {
        codec => json_lines
    }
}

修改增量的字段为 updatetime，且字段类型设置为 timestamp，
指定记录最后同步时间的文件为 incr_sync ，同时在 conf 目录下创建这个文件 incr_sync，设置一下初始值，即同步大于设置这个值的数据。

1
2
3
4
5
6
7
8
9
 
cd /usr/local/es/logstash-6.4.0/logstash-input-jdbc/conf/
vim incr_conf

# 文件内容如下，这里具体时间根据你的情况自行修改

--- !ruby/object:DateTime '2018-09-04 16:47:14.000000000 Z'


## 注意修改只需要改 2018-09-04 16:47:14 这一部分，其他地方就不要改，改了格式可能会不对。

保存退出，执行同步命令

1
 
/usr/local/es/logstash-6.4.0/bin/logstash -f /usr/local/es/logstash-6.4.0/logstash-input-jdbc/conf/mysql2es.conf

过一会儿，正常输出 sql 语句

1
 
SELECT `id`,IFNULL(`update_time`,'1970-01-01 08:00:00') AS `updatetime`,`status`,`name`,IFNULL(`intro`,'') AS `intro`,`author`,`words`,`collection`,`goods`,`click`,`site`,`sort`,`vip`,`popularity` FROM book WHERE update_time> '2018-09-04 16:47:14' order by update_time asc

incr_sync 文件中的值，会因为每次同步同步取的是最后一条记录的值，所以最好对时间进行排序

配置文件中，当在input的jdbc下，增加type属性时，会导致该索引下增加type字段。所以sql查询出的字段不要用type，如果有，as成其他的名字，不然的话，这里判断会有异常

logstash 同步后台运行

上面的运行命令如果推出终端或者按下 ctrl + c，同步就会终止，所以我们要让同步任务在后台运行。

1
 
nouhup /usr/local/es/logstash-6.4.0/bin/logstash -f /usr/local/es/logstash-6.4.0/logstash-input-jdbc/conf/mysql2es.conf &

使用 nohup 和 * 号将要执行的语句包裹起来，就可以实现后台运行了，同时会在当前执行命令的目录生成一个 nohup.out 的文件，这里 logstash 的运行日志会被写入到这个文件当中。

1
 
tail -fn300 nohup.out

参考