分布式搜索引擎ElasticSearch-Elasticsearch与MySQL数据同步

Logstash

什么是Logstash

Logstash是一款轻量级的日志搜集处理框架，可以方便的把分散的、多样化的日志搜集起来，并进行自定义的处理，然后传输到指定的位置，比如某个服务器或者文件。

Logstash安装与测试

解压，进入bin目录

[[e-mail] bin]# pwd
/application/logstash-6.1.1/bin
[[e-mail] bin]# ./logstash -e 'input { stdin { } } output { stdout {} }'

控制台输入字符，随后就有日志输出

[[e-mail] bin]# ./logstash -e 'input { stdin { } } output { stdout {} }'
aaaaaaabbbbbbbbbb
2019-05-29T22:26:49.655Z discovery aaaaaaabbbbbbbbbb
abcdefg
2019-05-29T22:26:52.815Z discovery abcdefg

命令说明

1）stdin，表示输入流，指从键盘输入
2）stdout，表示输出流，指从显示器输出
3）命令行参数:
3.1）-e 执行
3.2）–config 或 -f 配置文件，后跟参数类型可以是一个字符串的配置或全路径文件名或全路径路径(如：/etc/logstash.d/，logstash会自动读取/etc/logstash.d/目录下所有*.conf 的文本文件，然后在自己内存里拼接成一个完整的大配置文件再去执行)

MySQL数据导入Elasticsearch

（1）MySQL中先插入几条模拟数据

insert into  tb_article (id,title,content) values('11', 'title-11', 'content-11')
insert into  tb_article (id,title,content) values('12', 'title-12', 'content-12')

（2）创建logstash配置文件目录

在logstash-6.1.1安装目录下创建文件夹mysqletc （名称随意）

（3）创建mysql导入配置文件

文件夹下创建mysql.conf （名称随意） ，内容如下：

[[e-mail] mysqletc]# cat mysql.conf 
input {
    jdbc {
	# mysql jdbc connection string to our backup databse 后面的test 对应mysql中的test数据库
       jdbc_connection_string => "jdbc:mysql://192.168.2.10:3306/tensquare_article?characterEncoding=UTF8"
		
	# the user we wish to excute our statement as
        jdbc_user => "root"
        jdbc_password => "888888"
		
	# the path to our downloaded jdbc driver  
        jdbc_driver_library => "/application/logstash-6.1.1/mysqletc/mysql-connector-java-5.1.46.jar"
		
	# the name of the driver class for mysql
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        jdbc_paging_enabled => "true"
        jdbc_page_size => "50000"
		
	#以下对应着要执行的sql的绝对路径。
        statement => "select id,title,content from tb_article"
	#定时字段 各字段含义（由左至右）分、时、天、月、年，全部为*默认含义为每分钟都更新
        schedule => "* * * * *"
  }
}

output {
    elasticsearch {
	#ESIP地址与端口
        hosts => "192.168.2.10:9200" 
	#ES索引名称（自己定义的）
        index => "tensquare_article"
	#自增ID编号
        document_id => "%{id}"
        document_type => "article"
    }

    stdout {
	#以JSON格式输出
        codec => json_lines
    }
}
[[e-mail] mysqletc]# 

（4）拷贝mysql驱动

将mysql驱动包mysql-connector-java-5.1.46.jar拷贝至/application/logstash-6.1.1/mysqletc/下 。/application/logstash-6.1.1/是你的安装目录

[[e-mail] ~]# cd /application/logstash-6.1.1/mysqletc/
[[e-mail] mysqletc]# ll
total 988
-rw-r--r--. 1 root root    1268 May 30 10:15 mysql.conf
-rw-r--r--. 1 root root 1004838 Mar  5 19:38 mysql-connector-java-5.1.46.jar

（5）启动logstash

命令行下执行

/application/logstash-6.1.1/bin/logstash -f /application/logstash-6.1.1/mysqletc/mysql.conf 

（6）观察日志

观察控制台输出，每间隔1分钟就执行一次sql查询。

......
[2019-05-30T10:19:00,196][INFO ][logstash.inputs.jdbc     ] (0.000879s) SELECT version()
[2019-05-30T10:19:00,209][INFO ][logstash.inputs.jdbc     ] (0.000911s) SELECT count(*) AS `count` FROM (select id,title,content from tb_article) AS `t1` LIMIT 1
[2019-05-30T10:19:00,220][INFO ][logstash.inputs.jdbc     ] (0.000910s) SELECT * FROM (select id,title,content from tb_article) AS `t1` LIMIT 50000 OFFSET 0
{"title":"title-11","id":"11","@version":"1","@timestamp":"2019-05-30T02:19:00.223Z","content":"content-11"}
{"title":"title-12","id":"12","@version":"1","@timestamp":"2019-05-30T02:19:00.224Z","content":"content-12"}
Thu May 30 10:20:00 CST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
[2019-05-30T10:20:00,109][INFO ][logstash.inputs.jdbc     ] (0.001034s) SELECT version()
[2019-05-30T10:20:00,134][INFO ][logstash.inputs.jdbc     ] (0.001124s) SELECT count(*) AS `count` FROM (select id,title,content from tb_article) AS `t1` LIMIT 1
[2019-05-30T10:20:00,155][INFO ][logstash.inputs.jdbc     ] (0.004251s) SELECT * FROM (select id,title,content from tb_article) AS `t1` LIMIT 50000 OFFSET 0
{"title":"title-11","id":"11","@version":"1","@timestamp":"2019-05-30T02:20:00.157Z","content":"content-11"}
{"title":"title-12","id":"12","@version":"1","@timestamp":"2019-05-30T02:20:00.158Z","content":"content-12"}
......

（7）刷新elasticsearch-head

再次刷新elasticsearch-head的数据显示，看是否也更新了数据。