使用logstash实现mysql到ES的数据迁移

前言

es在作为数据检索的能力是业界公认的，当我么的服务数据体量增大后，使用mysql存储大文本、或者文本检索就不够用了，那就上es，那么就涉及到旧数据迁移的问题，本文介绍es提供的logstash实现数据迁移。

1、安装logstash

版本选择 （logstash、es、kibana版本要一致）

Docker安装

docker pull logstash:8.5.3
2、配置文件拷贝出来
mkdir /localLogstash
docker cp logstash:/usr/share/logstash /localLogstash
3、授予权限
chmod 777 -R /localLogstash/logstash
3、安装mysql-connector-java-8.0.19.jar
wget https://link.zhihu.com/?target=https%3A//cdn.mysql.com/archives/mysql-connector-java-8.0/mysql-connector-java-8.0.19.zip

unzip mysql-connector-java-8.0.19.zip
4、配置映射关系 (重点)

主要是statement这个配置，sql语句获取数据源

我这里直接原数据迁移，所以只配置了input output，如果需要做中间数据转化，增加filter配置，这里我就不详细说明了。

具体可以查阅该博文Logstash配置详解

# Sample Logstash configuration for creating a simple
# Beats -> Logstash -> Elasticsearch pipeline.

input {
    jdbc{
    # mysql 数据库链接
    jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/worklink?useUnicode=true&characterEncoding=utf-8&useSSL=false"
    # 用户名和密码
    jdbc_user => "user"
    jdbc_password => "password"
    #驱动 刚刚解压的地址
    jdbc_driver_library => "/logstash/mysql-connector-java-8.0.19/mysql-connector-java-8.0.19.jar"
    # 驱动类名
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_paging_enabled => "true"
    # 分页
    jdbc_page_size => "50000"
    jdbc_default_timezone =>"Asia/Shanghai"
    # mysql文件, 也可以直接写SQL语句在此处，如下：
    statement => "select a,b,c form d"
    # 这里类似crontab,可以定制定时操作，比如每分钟执行一次同步(分 时 天 月 年)
    #schedule => "* * * * *"
    # type => "jdbc"
    # 是否记录上次执行结果, 如果为真,将会把上次执行到的 tracking_column 字段的值记录下来,保存到 last_run_metadata_path 指定的文件中
    #record_last_run => true
    # 是否需要记录某个column 的值,如果record_last_run为真,可以自定义我们需要 track 的 column 名称，此时该参数就要为 true. 否则默认 track 的是 timestamp 的值.
    # use_column_value => true
    # 如果 use_column_value 为真,需配置此参数. track 的数据库 column 名,该 column 必须是递增的. 一般是mysql主键
    # tracking_column => "update_time"
    # tracking_column_type => "timestamp"
    # last_run_metadata_path => "./logstash_capital_bill_last_id"
    # 是否清除 last_run_metadata_path 的记录,如果为真那么每次都相当于从头开始查询所有的数据库记录
    # clean_run => false
    # 是否将 字段(column) 名称转小写
    lowercase_column_names => false
  }
}

output {
  elasticsearch {
    hosts => ["http://127.0.0.1:9200"]
    index => "revision_record"
    user => "user"
    password => "passwd"
  }
  # 可以在控制台先看下查询结果
  #stdout {
  #   codec => json_lines
  #}   
}
5、执行脚本

我只做一次性的数据迁移，所以直接运行脚本

如果是周期性的数据迁移，就需要启动docker，配置yml配置文件，这里就不细说了。

bin/logstash -f config/mylogstash.conf
6、问题解决
6.1、当mysql需要迁移的数据量超过100W条之后，logstash的mysql分页语句过深的导致迁移缓慢

在logstash的导入conf配置文件中的statement代码

SELECT * FROM xxx

Logstash的实际执行代码（设置了jdbc的分页大小为10000）

SELECT * FROM (SELECT * FROM xxx) AS `t1` LIMIT 50000 OFFSET 1000000

解决思路是通过记录上一次sql结果集最后一条数据的条件值，循环执行脚本

SELECT * from xxx where createTime > :sql_last_value limit 10000

我这里使用创建时间戳createTime作为分割条件tracking_column，:sql_last_value会取最后一条的数据的createTime循环执行sql。

这里需要注意tracking_column的选择需要符合两个条件：全局唯一、可排序

配置如下（这里只贴必要配置）

input {
    jdbc{
    # :sql_last_value为变量,表示上次结果最后的值 初始值为0
    statement => "SELECT * from xxx where id > :sql_last_value limit 10000"
    # 这里类似crontab,可以定制定时操作，比如每分钟执行一次同步(分 时 天 月 年)
    schedule => "* * * * *"
    # 是否记录上次执行结果, 如果为真,将会把上次执行到的 tracking_column 字段的值记录下来,保存到 last_run_metadata_path 指定的文件中
    record_last_run => true
    # 是否需要记录某个column 的值,如果record_last_run为真,可以自定义我们需要 track 的 column 名称，此时该参数就要为 true. 否则默认 track 的是 timestamp 的值.
    use_column_value => true
    # 如果 use_column_value 为真,需配置此参数. track 的数据库 column 名,该 column 必须是递增的. 一般是mysql主键
    tracking_column => "createTime"
    # tracking_column_type => "timestamp"
    last_run_metadata_path => "./createTime"
    # 是否清除 last_run_metadata_path 的记录,如果为真那么每次都相当于从头开始查询所有的数据库记录
    # clean_run => false
  }
}