flume采集mysql数据库增量数据说明

• flume依赖包

1、https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.48.tar.gz

2、flume-ng-sql-source-1.5.3.jar

• flume配置文件

  agent.sources = sqlSource
  agent.sinks = k1
  agent.channels = c1
  
  agent.sources.sqlSource.start_time =17:00:00
  agent.sources.sqlSource.channels = c1
  agent.sources.sqlSource.cycle_time =5*60*1000
  agent.sources.sqlSource.is_select_table =true
  agent.sources.sqlSource.is_up_than_field =true
  agent.sources.sqlSource.identity_field =id
  
  agent.sources.sqlSource.type = org.keedio.flume.source.SQLSource
  agent.sources.sqlSource.hibernate.connection.url = jdbc:mysql://172.16.1.180:3306/zabbix?useSSL=false
  # Hibernate Database connection properties
  agent.sources.sqlSource.hibernate.connection.user = zabbix
  agent.sources.sqlSource.hibernate.connection.password = zabbix
  agent.sources.sqlSource.hibernate.connection.autocommit = true
  agent.sources.sqlSource.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
  agent.sources.sqlSource.hibernate.connection.driver_class = com.mysql.jdbc.Driver
  agent.sources.sqlSource.status.file.path = /opt/
  agent.sources.sqlSource.status.file.name = sqlSource.status
  # Custom query
  agent.sources.sqlSource.custom.query = select * from abc where id > \'$@$\'
  agent.sources.sqlSource.hibernate.connection.provider_class = org.hibernate.connection.C3P0ConnectionProvider
  
  agent.sources.sqlSource.where =
  agent.sources.sqlSource.up_columns = id
  agent.sources.sqlSource.as_columns = id
  agent.sources.sqlSource.column_type=integer
  agent.sources.sqlSource.time_type=date
  agent.sources.sqlSource.time_type_type=timestamp
  agent.sources.sqlSource.start.from = 0
  agent.sources.sqlSource.begin_time =2020-09-01 14:48:56
  agent.sources.sqlSource.end_time =2020-10-02 14:48:56
  agent.sources.sqlSource.max.rows = 500
  agent.sources.sqlSource.run.query.delay=180000
  agent.sources.sqlSource.run.query.delayTimes=180000
  agent.sources.sqlSource.interceptors=i1
  agent.sources.sqlSource.interceptors.i1.type = static
  agent.sources.sqlSource.interceptors.i1.key=key
  agent.sources.sqlSource.interceptors.i1.value={"vendor":"McAfee","product":"防病毒","agent_ip":"192.2.17.35","agent_name":"mcafee","topicName":"flume1", "hostIP":"191.2.8.2"}
  
  agent.channels.c1.type = memory
  agent.channels.c1.capacity = 100000
  agent.channels.c1.transactionCapacity = 10000
  
  agent.sinks.k1.type = thrift
  agent.sinks.k1.channel=c1
  agent.sinks.k1.hostname = 172.16.1.180
  agent.sinks.k1.port = 5330
  agent.sinks.k1.connect.timeout = 0
  agent.sinks.k1.request.timeout = 0

• 配置文件注意事项
  1. 状态文件和路径不能出现-或者_，否则启动可能报错
  2. 状态文件路径如果不存在，请新建路径，状态文件可以不新建
  3. sql语句请指定自增字段，否则会重复读取全量数据
• 测试样例
• 数据库原始数据：
• 

• 测试效果：

  

   

  测试结论：

  当前配置，可以实现数据增量获取。

  当数据新增时，重启flume或者等几分钟，可以获取新增数据。