docker-compose logstash jdbc同步数据到 elasticsearch

docker-compose logstash jdbc同步数据到 elasticsearch

在生成订单后，需要将通过 es 查询订单，这里采用 logstash 同步数据到 es。 也可以采用 Cannal

工程目录

[root@blue elk-demo]# tree
.
├── docker-compose.yml
└── logstash
    ├── config
    │   ├── lib
    │   │   └── mysql-connector-java-8.0.22.jar
    │   ├── script
    │   │   └── user_statement.sql
    │   └── template
    │       └── user.json
    └── logstash.conf

5 directories, 5 files

docker-compose.yml 配置文件

version: '3'
services:
  elasticsearch:
    image: elasticsearch:7.9.0
    container_name: elasticsearch
    environment:
      - discovery.type=single-node
      - http.port=9200
      - http.cors.enabled=true
      - http.cors.allow-origin=*
      - http.cors.allow-headers=X-Requested-With,X-Auth-Token,Content-Type,Content-Length,Authorization
      - http.cors.allow-credentials=false
      - bootstrap.memory_lock=true
      - 'ES_JAVA_OPTS=-Xms512m -Xmx512m'
    volumes:
      - $PWD/data:/usr/share/elasticsearch/data
      - /etc/localtime:/etc/localtime:ro
    ports:
      - '9200:9200'
      - '9300:9300'
  kibana:
    image: kibana:7.9.0
    ports:
      - "5601:5601"
    volumes:
      - /etc/localtime:/etc/localtime:ro
    links:
      - elasticsearch
  logstash:
    image: registry.cn-hangzhou.aliyuncs.com/logss/logstash:7.4.1
    container_name: logstash
    hostname: logstash
    volumes:
      - /etc/localtime:/etc/localtime:ro
      - $PWD/logstash/logstash.conf:/usr/share/logstash/pipeline/logstash.conf
      - $PWD/logstash/config/script:/usr/share/logstash/pipeline/script
      - $PWD/logstash/config/template:/usr/share/logstash/pipeline/template
      - $PWD/logstash/config/lib/mysql-connector-java-8.0.22.jar:/usr/share/logstash/logstash-core/lib/jars/mysql-connector-java-8.0.22.jar
    links:
      - elasticsearch
    depends_on:
      - elasticsearch
    ports:
      - "5044:5044"

logstash.conf 配置文件

[root@blue logstash]# cat logstash.conf 

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

input {
  jdbc {
    jdbc_driver_library => "mysql-connector-java-8.0.22.jar"
    jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://172.xx.35.140:3306/logstash"
    jdbc_user => "root"
    jdbc_password => "root"
    jdbc_paging_enabled => true
    jdbc_page_size => "10000"
    schedule => "* * * * *"
    statement_filepath => "/usr/share/logstash/pipeline/script/user_statement.sql"
    lowercase_column_names => "false"
    record_last_run => true
    last_run_metadata_path => "config/logstash_metadata"
    type => "t_user"  
  }
  
}

output {  
  if [type] == "t_user"{
    elasticsearch {
		hosts => ["172.xx.35.140:9200"]
		index => "t_user"
		document_id =>"%{id}"

		template_overwrite => true
        	template_name => tpl_user
	        template => "/usr/share/logstash/pipeline/template/user.json"
	}
   }
   if [type] == "t_comment"{
    elasticsearch {
		hosts => ["172.xx.35.140:9200"]
		index => "t_comment"
		document_id =>"%{id}"
	}
   }

   stdout{
        codec=>rubydebug
    }
}

user_statement.sql

[root@blue script]# cat user_statement.sql 
select * from t_user where update_time > :sql_last_value

user.json es 映射文件

[root@blue template]# cat user.json 
{
  "template": {
    "mappings": {
      "properties": {
        "id": {
          "type": "integer"
        },
        "user_name": {
          "type": "string"
        },
        "age": {
          "type": "integer"
        },
        "create_time": {
          "type": "date"
        },
        "update_time": {
          "type": "date"
        }
      }
    }
  }
}

t_user 表

CREATE TABLE `t_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

启动

docker-compose up -d

如果看到下面，就算成功了

logstash         | [2021-08-11T01:14:00,150][INFO ][logstash.inputs.jdbc     ] (0.001373s) SELECT version()
logstash         | [2021-08-11T01:14:00,156][INFO ][logstash.inputs.jdbc     ] (0.002462s) 
					select * from t_user where update_time > '2021-08-11 01:13:00'
logstash         | 
elasticsearch    | {"type": "deprecation", "timestamp": "2021-08-11T01:14:00,334Z", "level": "WARN", "component": 
			"o.e.d.a.b.BulkRequestParser", "cluster.name": "docker-cluster", "node.name": "fa122fa948a4", 
			"message": "[types removal] Specifying types in bulk requests is deprecated.", 
			"cluster.uuid": "8VnrYaJ_T3GICBOqblq7IA", "node.id": "Gfa_3MEoRm2MCzSsOs7B-w"  }
logstash         | {
logstash         |              "id" => 34,
logstash         |        "@version" => "1",
logstash         |            "type" => "t_user",
logstash         |             "age" => 15,
logstash         |     "create_time" => 2021-08-11T20:30:58.000Z,
logstash         |     "update_time" => 2021-08-11T20:31:32.000Z,
logstash         |      "@timestamp" => 2021-08-11T01:14:00.209Z,
logstash         |       "user_name" => "showme"
logstash         | }