使用logstash-input-jdbc插件同步mysql数据到elasticsearch中

插件文档：https://www.elastic.co/guide/en/logstash/6.7/plugins-inputs-jdbc.html

插件下载地址：https://www.elastic.co/cn/downloads/logstash

使用logstash-input-jdbc插件来同步mysql数据到elasticsearch中

第一步：下载logstash插件并解压

第二步：解压之后进入bin执行命令

./logstash-plugin install logstash-input-jdbc

第三步：配置文件

在logstash的bin目录下创建一个文件（config-mysql,文件名自定义），该文件里面创建一个.conf的文件，我创建的为mysql.conf文件，信息如下：

jdbc_driver_library:为存放mysql驱动jar的路径，

schedule:为定时器，定时监测数据

statement：sql语句，

input {
  jdbc {
    jdbc_driver_library => "/usr/soft/logstash/logstash-6.7.0/mysql-Driver/mysql-connector-java-5.1.12.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/hospital"
    jdbc_user => "****"
    jdbc_password => "****"
    schedule => "* * * * *"
    statement => "select * from oplogin"
  }
}
output {
    elasticsearch {
        hosts => "192.168.2.106:9200"
        index => "hospital"
        document_id => "%{id}"
        document_type => "oplogin"
    }
    stdout {
        codec => json_lines
    }
}

第四步：执行该配置文件

./logstash -f config-mysql/mysql.conf

当有多个表需要同步到elasticsearch中时对应的配置文件为：

input {
  jdbc {
    jdbc_driver_library => "/usr/soft/logstash/logstash-6.7.0/mysql-Driver/mysql-connector-java-5.1.12.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/hospital"
    jdbc_user => "****"
    jdbc_password => "****"
    schedule => "* * * * *"
    statement => "select * from oplogin"
	type => "oplogin"
  }
  jdbc {
    jdbc_driver_library => "/usr/soft/logstash/logstash-6.7.0/mysql-Driver/mysql-connector-java-5.1.12.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/hospital"
    jdbc_user => "****"
    jdbc_password => "****"
    schedule => "* * * * *"
    statement => "select * from areainfo"
	type => "areainfo"
  }
}
output {
	if[type] == "oplogin" {
		elasticsearch {
			hosts => "192.168.2.106:9200"
			index => "hospital"
			document_id => "%{id}"
			document_type => "oplogin"
		}
	}   
	if[type] == "areainfo" {
		elasticsearch {
			hosts => "192.168.2.106:9200"
			index => "areainfo"
			document_id => "%{areaID}"
			document_type => "areainfo"
		}
	}   
    stdout {
        codec => json_lines
    }
}

但这里还是有一个问题，就是每次同步的时候都是从头开始，这种解决为：

 

input {
  jdbc {
    jdbc_driver_library => "/usr/soft/logstash/logstash-6.7.0/mysql-Driver/mysql-connector-java-5.1.12.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/iotdb_prod"
    jdbc_user => "****"
    jdbc_password => "****"
    schedule => "* * * * *"
	clean_run => "false"
	record_last_run => "true"
	use_column_value => "true"
	tracking_column => "electmsgid"
    statement_filepath => "/usr/soft/logstash/logstash-6.7.0/sqlfile/electricAllMsg.sql"
	last_run_metadata_path => "/usr/soft/logstash/lastval/electricAllMsg.txt"
	lowercase_column_names => "true"
	type => "electricAllMsg"
  }
}
output {
	if[type] == "electricAllMsg" {
		elasticsearch {
			hosts => "192.168.2.106:9200"
			index => "electric"
			document_id => "%{electmsgid}"
			document_type => "electricAllMsg"
		}
	}   
    stdout {
        codec => json_lines
    }
}

electricAllMsg.sql文件内容

select electricallmsginfo.*,electricfireinfo.devNumber,electricfireinfo.loopDevType,electricfireinfo.EUI,electricfireinfo.loopNo,electricfireinfo.unit,electricfireinfo.setTopLimit,electricfireinfo.setBtmLimit,
devicebase.deviceId,deviceTypeCode,devicebase.protocolType,devicebase.devEUI,devicebase.deviceSN,devicebase.deviceQRCode,devicebase.deviceImeiCode,devicebase.deviceSimNo,devicebase.deviceNumber,devicebase.mcuIDHex,devicebase.workState,
devicebase.isValid,devicebase.deviceAddr,devicebase.deviceGpsLati,devicebase.deviceGpsLong,devicebase.deviceGpsAlti,
devicebase.deviceIccidCode,area.*,
orgbaseinfo.orgId,orgbaseinfo.orgName,orgbaseinfo.orgNational,orgbaseinfo.industryClass,orgbaseinfo.parentOrgId,orgbaseinfo.orgType,orgbaseinfo.orgContact,orgbaseinfo.orgContactPhone,orgbaseinfo.orgLati,orgbaseinfo.orgLong,orgbaseinfo.orgProName,orgbaseinfo.orgCityName,orgbaseinfo.orgDistName,orgbaseinfo.address 
from electricallmsginfo inner join electricfireinfo on electricallmsginfo.electricId=electricfireinfo.electId
inner join devicebase on electricfireinfo.EUI=devicebase.devEUI
inner join areadevicemapinfo on devicebase.deviceId=areadevicemapinfo.deviceId
inner join (select areaID,parentAreaID,areaNo,areaName,areaType,areaLocDetail,areaLocProv,areaLocCity,areaLocDist,areaIDPath,SUBSTRING_INDEX(areaIDPath,"_",1) topId from areainfo) area on areadevicemapinfo.areaid=area.areaID
inner join customerareamapinfo on area.areaID=customerareamapinfo.areaID
inner join orgbaseinfo on customerareamapinfo.customerID=orgbaseinfo.orgId
where customerareamapinfo.custType=2 and customerareamapinfo.bindType=01 and areadevicemapinfo.bindType=01
and electmsgid >:sql_last_value
limit 0,100000