使用logstash同步MySQL数据到ES

使用logstash同步MySQL数据到ES

版权声明：【分享也是一种提高】个人转载请在正文开头明显位置注明出处，未经作者同意禁止企业/组织转载，禁止私自更改原文，禁止用于商业目的。 https://blog.csdn.net/u010887744/article/details/86708490

概述：

在生成业务常有将MySQL数据同步到ES的需求，如果需要很高的定制化，往往需要开发同步程序用于处理数据。但没有特殊业务需求，官方提供的logstash就很有优势了。

在使用logstash我们赢先了解其特性，再决定是否使用：

• 无需开发，仅需安装配置logstash即可；
• 凡是SQL可以实现的logstash均可以实现（本就是通过sql查询数据）
• 支持每次全量同步或按照特定字段（如递增ID、修改时间）增量同步；
• 同步频率可控，最快同步频率每分钟一次（如果对实效性要求较高，慎用）；
• 不支持被物理删除的数据同步物理删除ES中的数据（可在表设计中增加逻辑删除字段IsDelete标识数据删除）。

1、安装

前往官网下载logstash，下载地址https://www.elastic.co/downloads/logstash，zip压缩包大约160M；

程序目录：【windows】G:ELKlogstash-6.5.4；【linux】/tomcat/logstash/logstash-6.5.4。

下文统一以【程序目录】表示不同环境的安装目录。

2、配置

2.1、新建目录存放配置文件及mysql依赖包

在【程序目录】目录（in同级）新建mysql目录，将下载好的mysql-connector-java-5.1.34.jar放入此目录；

在【程序目录】mysql目录新建jdbc.conf文件，此文件将配置数据库连接信息、查询数据sql、分页信息、同步频率等核心信息。

注意事项请查看注释信息。

2.2、单表同步配置

1.  
   input {
2.  
   stdin {}
3.  
   jdbc {
4.  
   type => "jdbc"
5.  
   # 数据库连接地址
6.  
   jdbc_connection_string => "jdbc:mysql://192.168.1.1:3306/TestDB?characterEncoding=UTF-8&autoReconnect=true""
7.  
   # 数据库连接账号密码；
8.  
   jdbc_user => "username"
9.  
   jdbc_password => "pwd"
10.  
    # MySQL依赖包路径；
11.  
    jdbc_driver_library => "mysql/mysql-connector-java-5.1.34.jar"
12.  
    # the name of the driver class for mysql
13.  
    jdbc_driver_class => "com.mysql.jdbc.Driver"
14.  
    # 数据库重连尝试次数
15.  
    connection_retry_attempts => "3"
16.  
    # 判断数据库连接是否可用，默认false不开启
17.  
    jdbc_validate_connection => "true"
18.  
    # 数据库连接可用校验超时时间，默认3600S
19.  
    jdbc_validation_timeout => "3600"
20.  
    # 开启分页查询（默认false不开启）；
21.  
    jdbc_paging_enabled => "true"
22.  
    # 单次分页查询条数（默认100000,若字段较多且更新频率较高，建议调低此值）；
23.  
    jdbc_page_size => "500"
24.  
    # statement为查询数据sql，如果sql较复杂，建议配通过statement_filepath配置sql文件的存放路径；
25.  
    # sql_last_value为内置的变量，存放上次查询结果中最后一条数据tracking_column的值，此处即为ModifyTime；
26.  
    # statement_filepath => "mysql/jdbc.sql"
27.  
    statement => "SELECT KeyId,TradeTime,OrderUserName,ModifyTime FROM `DetailTab` WHERE ModifyTime>= :sql_last_value order by ModifyTime asc"
28.  
    # 是否将字段名转换为小写，默认true（如果有数据序列化、反序列化需求，建议改为false）；
29.  
    lowercase_column_names => false
30.  
    # Value can be any of: fatal,error,warn,info,debug，默认info；
31.  
    sql_log_level => warn
32.  
    #
33.  
    # 是否记录上次执行结果，true表示会将上次执行结果的tracking_column字段的值保存到last_run_metadata_path指定的文件中；
34.  
    record_last_run => true
35.  
    # 需要记录查询结果某字段的值时，此字段为true，否则默认tracking_column为timestamp的值；
36.  
    use_column_value => true
37.  
    # 需要记录的字段，用于增量同步，需是数据库字段
38.  
    tracking_column => "ModifyTime"
39.  
    # Value can be any of: numeric,timestamp，Default value is "numeric"
40.  
    tracking_column_type => timestamp
41.  
    # record_last_run上次数据存放位置；
42.  
    last_run_metadata_path => "mysql/last_id.txt"
43.  
    # 是否清除last_run_metadata_path的记录，需要增量同步时此字段必须为false；
44.  
    clean_run => false
45.  
    #
46.  
    # 同步频率(分 时 天 月 年)，默认每分钟同步一次；如果是每10分钟执行一下 */10 即可 https://www.cnblogs.com/superman66/p/4565723.html
47.  
    schedule => "* * * * *"
48.  
    }
49.  
    }
50.  
     
51.  
    filter {
52.  
    json {
53.  
    source => "message"
54.  
    remove_field => ["message"]
55.  
    }
56.  
    # convert 字段类型转换，将字段TotalMoney数据类型改为float；
57.  
    mutate {
58.  
    convert => {
59.  
    "TotalMoney" => "float"
60.  
    }
61.  
    }
62.  
    }
63.  
    output {
64.  
    elasticsearch {
65.  
    # host => "192.168.1.1"
66.  
    # port => "9200"
67.  
    # 配置ES集群地址
68.  
    hosts => ["192.168.1.1:9200", "192.168.1.2:9200", "192.168.1.3:9200"]
69.  
    # 索引名字，必须小写
70.  
    index => "consumption"
71.  
    # 数据唯一索引（建议使用数据库KeyID）
72.  
    document_id => "%{KeyId}"
73.  
    }
74.  
    stdout {
75.  
    codec => json_lines
76.  
    }
77.  
    }

2.3、多表同步

多表配置和单表配置的区别在于input模块的jdbc模块有几个type，output模块就需对应有几个type；

1.  
   input {
2.  
   stdin {}
3.  
   jdbc {
4.  
   # 多表同步时，表类型区分，建议命名为“库名_表名”，每个jdbc模块需对应一个type；
5.  
   type => "TestDB_DetailTab"
6.  
    
7.  
   # 其他配置此处省略，参考单表配置
8.  
   # ...
9.  
   # ...
10.  
    # record_last_run上次数据存放位置；
11.  
    last_run_metadata_path => "mysqllast_id.txt"
12.  
    # 是否清除last_run_metadata_path的记录，需要增量同步时此字段必须为false；
13.  
    clean_run => false
14.  
    #
15.  
    # 同步频率(分 时 天 月 年)，默认每分钟同步一次；
16.  
    schedule => "* * * * *"
17.  
    }
18.  
    jdbc {
19.  
    # 多表同步时，表类型区分，建议命名为“库名_表名”，每个jdbc模块需对应一个type；
20.  
    type => "TestDB_Tab2"
21.  
    # 多表同步时，last_run_metadata_path配置的路径应不一致，避免有影响；
22.  
    # 其他配置此处省略
23.  
    # ...
24.  
    # ...
25.  
    }
26.  
    }
27.  
     
28.  
    filter {
29.  
    json {
30.  
    source => "message"
31.  
    remove_field => ["message"]
32.  
    }
33.  
    }
34.  
     
35.  
    output {
36.  
    # output模块的type需和jdbc模块的type一致
37.  
    if [type] == "TestDB_DetailTab" {
38.  
    elasticsearch {
39.  
    # host => "192.168.1.1"
40.  
    # port => "9200"
41.  
    # 配置ES集群地址
42.  
    hosts => ["192.168.1.1:9200", "192.168.1.2:9200", "192.168.1.3:9200"]
43.  
    # 索引名字，必须小写
44.  
    index => "detailtab1"
45.  
    # 数据唯一索引（建议使用数据库KeyID）
46.  
    document_id => "%{KeyId}"
47.  
    }
48.  
    }
49.  
    if [type] == "TestDB_Tab2" {
50.  
    elasticsearch {
51.  
    # host => "192.168.1.1"
52.  
    # port => "9200"
53.  
    # 配置ES集群地址
54.  
    hosts => ["192.168.1.1:9200", "192.168.1.2:9200", "192.168.1.3:9200"]
55.  
    # 索引名字，必须小写
56.  
    index => "detailtab2"
57.  
    # 数据唯一索引（建议使用数据库KeyID）
58.  
    document_id => "%{KeyId}"
59.  
    }
60.  
    }
61.  
    stdout {
62.  
    codec => json_lines
63.  
    }
64.  
    }

3、启动运行

在【程序目录】目录执行以下命令启动：

1.  
   【windows】binlogstash.bat -f mysqljdbc.conf
2.  
   【linux】nohup ./bin/logstash -f mysql/jdbc_jx_moretable.conf &

可新建脚本配置好启动命令，后期直接运行即可。

在【程序目录】logs目录会有运行日志。

Note：

6.X版本需要jdk8支持，如果默认jdk版本不是jdk8，那么需要在logstash或logstash.lib.sh的行首位置添加两个环境变量：

1.  
   export JAVA_CMD="/usr/tools/jdk1.8.0_162/bin"
2.  
   export JAVA_HOME="/usr/tools/jdk1.8.0_162/"

开机自启动：

windows开机自启：

• 方案1：使用windows自带的任务计划；
• 方案2：nssm注册windows服务，https://blog.csdn.net/u010887744/article/details/53957713

linux开机自启：

• CentOS 7将linux服务加入系统启动 systemd service，https://blog.csdn.net/u010887744/article/details/53957647

4、问题及解决方案

4.1、数据同步后，ES没有数据

output.elasticsearch模块的index必须是全小写；

4.2、增量同步后last_run_metadata_path文件内容不改变

如果lowercase_column_names配置的不是false，那么tracking_column字段配置的必须是全小写。

4.3、提示找不到jdbc_driver_library

2032 com.mysql.jdbc.Driver not loaded. Are you sure you've included the correct jdbc driver in :jdbc_driver_library?

检测配置的地址是否正确，如果是linux环境，注意路径分隔符是“/”，而不是“”。

 

4.4、数据丢失

statement配置的sql中，如果比较字段使用的是大于“>”，可能存在数据丢失。

假设当同步完成后last_run_metadata_path存放的时间为2019-01-30 20:45:30，而这时候新入库一条数据的更新时间也为2019-01-30 20:45:30，那么这条数据将无法同步。

解决方案：将比较字段使用 大于等于“>=”。

4.5、数据重复更新

上一个问题“数据丢失”提供的解决方案是比较字段使用“大于等于”，但这时又会产生新的问题。

假设当同步完成后last_run_metadata_path存放的时间为2019-01-30 20:45:30，而数据库中更新时间最大值也为2019-01-30 20:45:30，那么这些数据将重复更新，直到有更新时间更大的数据出现。

当上述特殊数据很多，且长期没有新的数据更新时，会导致大量的数据重复同步到ES。

何时会出现以上情况呢：①比较字段非“自增”；②比较字段是程序生成插入。

解决方案：

①比较字段自增保证不重复或重复概率极小（比如使用自增ID或者数据库的timestamp），这样就能避免大部分异常情况了；

②如果确实存在大量程序插入的数据，其更新时间相同，且可能长期无数据更新，可考虑定期更新数据库中的一条测试数据，避免最大值有大量数据。

4.6、容灾

logstash本身无法集群，我们常使用的组合ELK是通过kafka集群变相实现集群的。

可供选择的处理方式：①使用任务程序推送数据到kafaka，由kafka同步数据到ES，但任务程序本身也需要容灾，并需要考虑重复推送的问题；②将logstash加入守护程序，并辅以第三方监控其运行状态。具体如何选择，需要结合自身的应用场景了。

4.7、海量数据同步

为什么会慢？logstash分页查询使用临时表分页，每条分页SQL都是将全集查询出来当作临时表，再在临时表上分页查询。这样导致每次分页查询都要对主表进行一次全表扫描。

SELECT * FROM (SELECT * FROM `ImageCN1` WHERE ModifyTime>= '1970-01-01 08:00:00' order by ModifyTime asc) AS `t1` LIMIT 5000 OFFSET 10000000;

数据量太大，首次同步如何安全过渡同步？

可考虑在statement对应的sql中加上分页条件，比如ID在什么范围，修改时间在什么区间，将单词同步的数据总量减少。先少量数据同步测试验证，再根据测试情况修改区间条件启动logstash完成同步。比如将SQL修改为：

SELECT * FROM `ImageCN1` WHERE ModifyTime<'2018-10-10 10:10:10' AND ModifyTime>= '1970-01-01 08:00:00' order by ModifyTime asc

这样需要每次同步后就修改sql，线上运营比较繁琐，是否可以不修改sql，同时保证同步效率呢？SQL我们可以再修改下：

SELECT * FROM `ImageCN1` WHERE ModifyTime>= '1970-01-01 08:00:00' order by ModifyTime asc limit 100000

 这样就能保证每次子查询的数据量不超过10W条，实际测试发现，数据量很大时效果很明显。

1.  
   [SQL]USE XXXDataDB;
2.  
   受影响的行: 0
3.  
   时间: 0.001s
4.  
    
5.  
   [SQL]
6.  
   SELECT * FROM (SELECT * FROM `ImageCN1` WHERE ModifyTime>= '1970-01-01 08:00:00' order by ModifyTime asc ) AS `t1` LIMIT 5000 OFFSET 900000;
7.  
   受影响的行: 0
8.  
   时间: 7.229s
9.  
    
10.  
    [SQL]
11.  
     
12.  
    SELECT * FROM (SELECT * FROM `ImageCN1` WHERE ModifyTime>= '2018-07-18 19:35:10' order by ModifyTime asc limit 100000) AS `t1` LIMIT 5000 OFFSET 90000
13.  
     
14.  
    受影响的行: 0
15.  
    时间: 1.778s

 测试可以看出，SQL不加limit 10W时，越往后分页查询越慢，耗时达到8S，而加了limit条件的SQL耗时稳定在2S以内。

1.  
   欢迎个人转载，但须在文章页面明显位置给出原文连接；
2.  
   未经作者同意必须保留此段声明、不得随意修改原文、不得用于商业用途，否则保留追究法律责任的权利。
3.  
    
4.  
   【 CSDN 】：csdn.zxiaofan.com
5.  
   【GitHub】：github.zxiaofan.com
6.  
    
7.  
   如有任何问题，欢迎留言。祝君好运！
8.  
   Life is all about choices！
9.  
   将来的你一定会感激现在拼命的自己！