logstash 同步 mysql 增量

此例为从 mysql 数据库同步到 mysql

------------------ sql 必须排序 ------------------
select u.ID_ userId,u.ACCOUNT_ userAccount,u.FULLNAME_ userFullname,u.CREATE_TIME_,o.ID_ orgId,o.CODE_ orgCode
,o.NAME_ orgName,o.GRADE_ orgGrade, UNIX_TIMESTAMP(DATE_FORMAT(u.CREATE_TIME_,‘%Y-%m-%d %h:%i:%s’))*1000 createTime
from sys_user u left join sys_org_user ou on u.ID_ = ou.USER_ID_
left join sys_org o on o.ID_ = ou.ORG_ID_ where UNIX_TIMESTAMP(DATE_FORMAT(u.CREATE_TIME_,‘%Y-%m-%d %h:%i:%s’))*1000 > :sql_last_value
order by UNIX_TIMESTAMP(DATE_FORMAT(u.CREATE_TIME_,‘%Y-%m-%d %h:%i:%s’))*1000 asc

------------------ logstash.conf ------------------
input {
stdin {
}
jdbc {
jdbc_connection_string => “jdbc:mysql://192.168.5.184:3306/nqm_dev?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=round”
jdbc_user => “root”
jdbc_password => “my123”
jdbc_driver_library => “D:\code\logstash-8.1.1\lib\mysql-connector-java-8.0.28.jar”
jdbc_driver_class => “com.mysql.cj.jdbc.Driver”
#jdbc_paging_enabled => “true”
#jdbc_page_size => “50”
#此时该参数就要为 true. 否则默认 track 的是 timestamp 的值.
use_column_value => true
#是否记录上次执行结果, 如果为真,将会把上次执行到的 tracking_column 字段的值记录下来,保存到 last_run_metadata_path 指定的文件中
record_last_run => “true”
#如果 use_column_value 为真,需配置此参数. 这个参数就是数据库给出的一个字段名称。当然该字段必须是递增的，可以是 数据库的数据时间这类的
tracking_column => “createTime”
# Value can be any of: numeric,timestamp，Default value is “numeric”
tracking_column_type => numeric
# record_last_run上次数据存放位置
record_last_run => true
# 只需要在 SQL 语句中 WHERE MY_ID > :last_sql_value 即可. 其中 :sql_last_value 取得就是该文件中的值
#上一个sql_last_value值的存放文件路径, 必须要在文件中指定字段的初始值
last_run_metadata_path => “D:\code\logstash-8.1.1\data\mysql_last_run_test.txt”
# 是否将字段名转换为小写，默认true（如果有数据序列化、反序列化需求，建议改为false）
lowercase_column_names => false
# 是否清除last_run_metadata_path的记录，需要增量同步时此字段必须为false；
clean_run => false
statement_filepath => “D:\code\logstash-8.1.1\sql\mysql\test_userOrg_input.sql”
# 每间隔一分钟执行一次
schedule => “*/1 * * * *”
type => “jdbc”
jdbc_default_timezone =>“Asia/Shanghai”
}
}

output {
jdbc {
driver_jar_path=>“D:\code\logstash-8.1.1\lib\mysql-connector-java-8.0.28.jar”
driver_class => “com.mysql.cj.jdbc.Driver”
connection_string => “jdbc:mysql://192.168.5.184:3306/nqm_dev?user=root&password=my123”
statement => ["insert into tmp_userorg (userId, userAccount, userFullname, orgId, orgCode, orgName, orgGrade ) values (?, ?, ?, ?, ?, ?, ? ) on duplicate key update
userId = values(userId), userAccount = values(userAccount), userFullname = values(userFullname), orgId = values(orgId), orgCode = values(orgCode), orgName = values(orgName),orgGrade = values(orgGrade) ",
“userId”, “userAccount”, “userFullname”, “orgId”, “orgCode”, “orgName”, “orgGrade” ]
}
stdout {
codec => json_lines
}
}

------------------ 安装 output-jdbc，bin 目录下 ------------------
logstash-plugin install logstash-output-jdbc
------------------ 启动logstash ------------------
logstash -w 1 -f D:\code\logstash-8.1.1\config\logstash.conf

------------------ 建表sql ------------------
DROP TABLE IF EXISTS tmp_userorg;
CREATE TABLE tmp_userorg (
userId varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
userAccount varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
userFullname varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
orgId varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
orgCode varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
orgName varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
orgGrade varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;