logstash全量和增量同步数据到mysql

一、场景

在mysql数据同步到ES中，发现第一次同步时需要全量的数据，之后则需要定时去同步增量数据，使用logstash需要了解一下事项（注意：更新周期最快是1分钟）

1、凡是SQL可以实现的logstash均可以实现（本就是通过sql查询数据）

2、支持每次全量同步或按照特定字段（如递增ID、修改时间）增量同步；

3、同步频率可控，最快同步频率每分钟一次（如果对实效性要求较高，慎用）；

4、不支持被物理删除的数据同步物理删除ES中的数据（可在表设计中增加逻辑删除字段IsDelete标识数据删除）。

二、全量更新

1、前提：

     数据库名为:test 
     test下表名为:student
     表中数据如下图：

2.在logstash-6.5.1文件夹下创建totalJdbc.conf，totalStudent.sql两个文件

totalJdbc.conf文件如下：

input {
    stdin {
    }
    jdbc {
      # 驱动
      jdbc_driver_library => "/home/mysql-connector-java-5.1.42.jar"
      # 驱动类名
      jdbc_driver_class => "com.mysql.jdbc.Driver"
      # mysql 数据库链接,shop为数据库名
      jdbc_connection_string => "jdbc:mysql://192.168.31.240:3306/test"
      # 用户名和密码
      jdbc_user => "root"
      jdbc_password => "Aa123456~"
      jdbc_paging_enabled => "true"
      jdbc_page_size => "50000"
      # 执行的sql 文件路径+名称
      statement_filepath => "/home/logstash-6.5.1/totalStudent.sql"
      # 设置监听间隔  各字段含义（由左至右）分、时、天、月、年，全部为*默认含义为每分钟都更新
      schedule => "* * * * *"
      # 索引类型
      type => "student_type"
    }
}

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

output {
    elasticsearch {
        hosts => ["192.168.31.232:9200","192.168.31.233:9200"]
        index => "studentIndex"
        document_id => "%{sid}"
    }
    stdout {
        codec => json_lines
    }
}

totalStudent.sql文件内容如下：

select * from student

3、启动logstash，命令：

./bin/logstash -f ./totalJdbc.conf

出现下图，表示全量更新成功

三、增量更新

1、前提，在增加两条数据

insert into student(sid,name,sex)
values (5,'xiaoming5','男'),(6,'xiaoming6','男');

2、在logstash-6.5.1文件夹下创建addJdbc.conf，addStudent.sql两个文件

addJdbc.conf文件内容如下：

input {
  jdbc {
    jdbc_driver_library => "/home/mysql-connector-java-5.1.42.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    # 数据库相关配置
    jdbc_connection_string => "jdbc:mysql://192.168.31.240:3306/test?characterEncoding=UTF-8&autoReconnect=true"
    jdbc_user => "root"
    jdbc_password => "Aa123456~"
    # 数据库重连尝试次数
    connection_retry_attempts => "3"
    # 数据库连接可用校验超时时间，默认3600S
    jdbc_validation_timeout => "3600"
    # 开启分页查询（默认false不开启）；
    jdbc_paging_enabled => "true"
    # 单次分页查询条数（默认100000,若字段较多且更新频率较高，建议调低此值）；
    jdbc_page_size => "500"
    # 如果sql较复杂，建议配通过statement_filepath配置sql文件的存放路径；
    statement_filepath => "/home/logstash-6.5.1/addStudent.sql"
    # 需要记录查询结果某字段的值时，此字段为true，否则默认tracking_column为timestamp的值；
    use_column_value => true
    # 是否将字段名转换为小写，默认true（如果有数据序列化、反序列化需求，建议改为false）；
    lowercase_column_names => false
    # 需要记录的字段，用于增量同步，需是数据库字段
    tracking_column => sid
    # Value can be any of: numeric,timestamp，Default value is "numeric"
    tracking_column_type => numeric
    # record_last_run上次数据存放位置；
    record_last_run => true
    #上一个sql_last_value值的存放文件路径, 必须要在文件中指定字段的初始值
    last_run_metadata_path => "/home/logstash-6.5.1/config/station_parameter.txt"
    # 是否清除last_run_metadata_path的记录，需要增量同步时此字段必须为false；
    clean_run => false
    # 同步频率(分 时 天 月 年)，默认每分钟同步一次；
    schedule => "* * * * *"
    # 索引类型
    type => "student_type"
  }
}

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

output {
  stdout {
    codec => rubydebug
  }
  elasticsearch {
    hosts => ["192.168.31.232:9200","192.168.31.233:9200"]
    #将mysql数据加入myindex索引下，会自动创建
    index => "student"
    # 自增ID 需要关联的数据库中有有一个id字段，对应索引的id号
    document_id => "%{sid}"
  }
}

addStudent.sql文件内容如下；

select * from student where sid > :sql_last_value

3、启动logshtash

./bin/logstash -f ./addJdbc.conf 

启动成功如下图：

四、多表同步

配置文件:

input {
	stdin {}
	jdbc {
		 # 多表同步时，表类型区分，建议命名为“库名_表名”，每个jdbc模块需对应一个type；
		type => "TestDB_DetailTab"
		
		 # 其他配置此处省略，参考单表配置
		 # ...
		 # ...
		 # record_last_run上次数据存放位置；
		last_run_metadata_path => "mysql\last_id.txt"
		 # 是否清除last_run_metadata_path的记录，需要增量同步时此字段必须为false；
		clean_run => false
		 #
		 # 同步频率(分 时 天 月 年)，默认每分钟同步一次；
		schedule => "* * * * *"
	}
	jdbc {
		 # 多表同步时，表类型区分，建议命名为“库名_表名”，每个jdbc模块需对应一个type；
		type => "TestDB_Tab2"
		# 多表同步时，last_run_metadata_path配置的路径应不一致，避免有影响；
		 # 其他配置此处省略
		 # ...
		 # ...
	}
}
 
filter {
	json {
		source => "message"
		remove_field => ["message"]
	}
}
 
output {
	# output模块的type需和jdbc模块的type一致
	if [type] == "TestDB_DetailTab" {
		elasticsearch {
			 # host => "192.168.1.1"
			 # port => "9200"
			 # 配置ES集群地址
			hosts => ["192.168.1.1:9200", "192.168.1.2:9200", "192.168.1.3:9200"]
			 # 索引名字，必须小写
			index => "detailtab1"
			 # 数据唯一索引（建议使用数据库KeyID）
			document_id => "%{KeyId}"
		}
	}
	if [type] == "TestDB_Tab2" {
		elasticsearch {
			# host => "192.168.1.1"
			# port => "9200"
			# 配置ES集群地址
			hosts => ["192.168.1.1:9200", "192.168.1.2:9200", "192.168.1.3:9200"]
			# 索引名字，必须小写
			index => "detailtab2"
			# 数据唯一索引（建议使用数据库KeyID）
			document_id => "%{KeyId}"
		}
	}
	stdout {
		codec => json_lines
	}
}

五、注意事项

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

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

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

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