Logstash同步MySQL数据到Elasticsearch

前言

很多时候，在项目初期都是仅采用MySQL数据库作为业务数据库，但是随着数据的增长，当单表的数据超过千万级后，在怎么对查询SQL语句进行优化性能都不理想。这种情况下，我们就可以考虑通过Elasticsearch来实现项目的读写分离：写操作对Mysql库进行操作，读操作采用ES。那么我们应该如何保证Elasticsearch和MySQL的数据同步呢？本文给大家介绍通过Logstash实现MySQL数据定时增量同步到Elasticsearch。

1、环境准备

2、下载安装包

3、配置

3.1、新建sync文件夹

3.2、logstash-db-sync.conf文件内容

3.3、logstash.sql文件类容

3.4、track_time文件内容

4、启动

5、一对多数据如何同步

1、环境准备

JDK：1.8 64位 下载 & 配置

2、下载安装包

下载安装包，并解压到指定目录，如/home/xsaas/

https://artifacts.elastic.co/downloads/logstash/logstash-7.17.2-linux-x86_64.tar.gz

其他版本可在https://www.elastic.co/cn/downloads/past-releases#logstash选择下载，最好保证版本号和es完全一致

解压：

cd /home/xsaas/
tar -xvf logstash-7.17.2-linux-x86_64.tar.gz
           

3、配置

3.1、新建sync文件夹

cd /home/xsaas/logstash-7.17.2
#新建文件夹sync
mkdir sync&&cd sync
           

在sync创建文件logstash-db-sync.conf、logstash.sql、track_time，然后把数据库驱动移到该文件夹下

3.2、logstash-db-sync.conf文件内容

input {
    jdbc {
        # 设置 MySql/MariaDB 数据库url以及数据库名称
        jdbc_connection_string => "jdbc:mysql://ip:3306/foodie?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true"
        # 用户名和密码
        jdbc_user => "root"
        jdbc_password => "qqqqqqqqqqq"
        # 数据库驱动所在位置，可以是绝对路径或者相对路径
        jdbc_driver_library => "/home/xsaas/logstash-7.17.2/sync/mysql-connector-java-5.1.41.jar"
        # 驱动类名 mysql8用com.mysql.cj.jdbc.Driver，mysql5用com.mysql.jdbc.Driver
        jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
        # 开启分页
        jdbc_paging_enabled => "true"
        # 分页每页数量，可以自定义
        jdbc_page_size => "1000"
        # 执行的sql文件路径
        statement_filepath => "/home/xsaas/logstash-7.17.2/sync/logstash.sql"
        # 设置定时任务间隔  含义：分、时、天、月、年，全部为*默认含义为每分钟跑一次任务
        schedule => "* * * * *"
        # 索引类型
        type => "_doc"
        # 是否开启记录上次追踪的结果，也就是上次更新的时间，这个会记录到 last_run_metadata_path 的文件
        use_column_value => true
        # 记录上一次追踪的结果值
        last_run_metadata_path => "/home/xsaas/logstash-7.17.2/sync/track_time"
        # 如果 use_column_value 为true， 配置本参数，追踪的 column 名，可以是自增id或者时间
        tracking_column => "updated_time"
        # tracking_column 对应字段的类型
        tracking_column_type => "timestamp"
        # 是否清除 last_run_metadata_path 的记录，true则每次都从头开始查询所有的数据库记录
        clean_run => false
        # 数据库字段名称大写转小写
        lowercase_column_names => false
    }
}
output {
    elasticsearch {
        # es地址
        hosts => ["ip:9200"]
        # es账号名
        user => "root"
        # es密码
        password => "123456"
        # 同步的索引名
        index => "foodie-items"
        # 设置_docID和数据相同
        document_id => "%{itemId}"
    }
    # 日志输出:
    stdout {
        codec => json_lines
    }
}
           

3.3、logstash.sql文件类容

即你要同步的数据

SELECT
	i.id as itemId,
	i.item_name as itemName,
	i.sell_counts as sellCounts,
	ii.url as imgUrl,
	tempSpec.price_discount as price,
	i.updated_time as updated_time
FROM
	items i
LEFT JOIN
	items_img ii
on
	i.id = ii.item_id
LEFT JOIN
	(SELECT item_id,MIN(price_discount) as price_discount FROM items_spec) tempSpec
on 
	i.id = tempSpec.item_id
WHERE
	ii.is_main = 1
	and
	i.updated_time >= :sql_last_value   -- 这个时间是从track_time获取
           

3.4、track_time文件内容

直接新建即可，文件内容为空。主要作用为记录上一次追踪的结果值，比如配置追踪的是id，则该文件会记录上次查询中最后一行的id值。

#新建track_time文件
touch track_time
           

4、启动

cd /home/xsaas/logstash-7.17.2
sh bin/logstash -w 1 -f sync/logstash-db-sync.conf

#后台启动
nohup sh bin/logstash -w 1 -f sync/logstash-db-sync.conf > logstash.log 2>&1 &
           

命令解释：

-w 1：表示单线程运行，主要是为了解决Logstash Aggregate使用聚合时数组中的数据会错乱覆盖。原因是因为Logstash默认多线程运行，所以数据聚合时数据会混乱，如果你同步的数据没涉及到数据聚合（一对多），则可以删除该参数

5、一对多数据如何同步

敬请期待