Docker下同步Mysql数据到Es

ELK基本介绍及安装部分请看上篇文章：Docker安装ELK

一般存储数据都是基于数据库，但数据量大了以后需要搜索，就需要引入ElasticSearch了。这就涉及到双方数据如何同步的问题。
有几种方案：

业务调接口主动推送（耦合度高）
logstash定时同步（灵活）
canal基于binlog订阅同步（不方便join）

由于canal不太方便跨表join，今天我们先实践下logstash的同步方案。

Mysql测试

sql语句如下：

 CREATE TABLE `attendee`  (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `full_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '名称',
  `mobile` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '手机',
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '邮箱',
  `position` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '职务',
  `company` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '公司',
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '简介',
  `is_deleted` tinyint(0) NULL DEFAULT NULL COMMENT '是否删除',
  `created_date` datetime(0) NULL DEFAULT NULL COMMENT '创建日期',
  `created_user_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '创建人',
  `lastest_update_date` datetime(0) NULL DEFAULT NULL COMMENT '修改日期',
  `lastest_update_user_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '最后修改人',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
insert into attendee(`id`,`full_name`,`mobile`,`email`,`position`,`company`,`description`,`is_deleted`,`created_date`,`created_user_id`,`lastest_update_date`,`lastest_update_user_id`) values(uuid(),'张三1','13000000001','13000000001@qq.com','工程师','虎虎生威',null,0,now(),null,now(),null);
 
insert into attendee(`id`,`full_name`,`mobile`,`email`,`position`,`company`,`description`,`is_deleted`,`created_date`,`created_user_id`,`lastest_update_date`,`lastest_update_user_id`) values(uuid(),'李四1','13000000002','13000000002@qq.com','设计师','虎虎生威',null,0,now(),null,now(),null);

select * from attendee

LogStash同步

ELK安装部分请移架Docker安装ELK，这里不再赘述。

logstash目录下新建mysql目录，并下载mysql-connector-java包
包地址列表：mysql-connector-java

cd /data/elk/logstash
mkdir mysql& cd mysql
wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.27/mysql-connector-java-8.0.27.jar

如遇到网速问题，可先下载下来，然后用rz 命令上传到linux。

在/data/elk/logstash/logstash.conf

  
input {
  jdbc {
    jdbc_driver_library => "app/mysql-connector-java-8.0.27.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://10.0.2.55:3306/es_demo_lib"
    jdbc_user => "root"
    jdbc_password => "yourpassword"
    jdbc_paging_enabled => true
    tracking_column => "unix_ts_in_secs"
    use_column_value => true
    tracking_column_type => "numeric"
    schedule => "*/5 * * * * *"
    statement => "SELECT *, UNIX_TIMESTAMP(lastest_update_date) AS unix_ts_in_secs FROM attendee WHERE (UNIX_TIMESTAMP(lastest_update_date)) > :sql_last_value AND lastest_update_date < NOW() ORDER BY lastest_update_date asc"
  }
}
filter {
  mutate {
    copy => { "id" => "[@metadata][_id]"}
    remove_field => ["id", "@version", "unix_ts_in_secs"]
  }
}
output {
  elasticsearch {
      hosts => ["elasticsearch:9200"]
      index => "attendee_idx"
      document_id => "%{[@metadata][_id]}"
  }
}

在上述管道中，应该重点强调几个区域：

tracking_column：此字段会指定 “unix_ts_in_secs” 字段（用于跟踪 Logstash 从 MySQL 读取的最后一个文档，下面会进行描述），其存储在 .logstash_jdbc_last_run 中的磁盘上。该值将会用来确定 Logstash 在其轮询循环的下一次迭代中所请求文档的起始值。在 .logstash_jdbc_last_run 中所存储的值可以作为 “:sql_last_value” 通过 SELECT 语句进行访问。

unix_ts_in_secs：这是一个由上述 SELECT 语句生成的字段，包含可作为标准 Unix 时间戳（自 Epoch 起秒数）的 “modification_time”。我们刚讨论的 “tracking column” 会引用该字段。Unix 时间戳用于跟踪进度，而非作为简单的时间戳；如将其作为简单时间戳，可能会导致错误，因为在 UMT 和本地时区之间正确地来回转换是一个十分复杂的过程。

sql_last_value：这是一个内置参数，包括 Logstash 轮询循环中当前迭代的起始点，上面 JDBC 输入配置中的 SELECT 语句便会引用这一参数。该字段会设置为 “unix_ts_in_secs”（读取自 .logstash_jdbc_last_run）的最新值。在 Logstash 轮询循环内所执行的 MySQL 查询中，其会用作所返回文档的起点。通过在查询中加入这一变量，能够确保不会将之前传播到 Elasticsearch 的插入或更新内容重新发送到 Elasticsearch。

schedule：其会使用 cron 语法来指定 Logstash 应当以什么频率对 MySQL 进行轮询以查找变更。这里所指定的 "*/5 * * * * *" 会告诉 Logstash 每 5 秒钟联系一次 MySQL。
modification_time < NOW()：SELECT 中的这一部分是一个较难解释的概念，我们会在下一部分详加解释。

filter：在这一部分，我们只需简单地将 MySQL 记录中的 “id” 值复制到名为 “_id” 的元数据字段，因为我们之后输出时会引用这一字段，以确保写入 Elasticsearch 的每个文档都有正确的 “_id” 值。通过使用元数据字段，可以确保这一临时值不会导致创建新的字段。我们还从文档中删除了 “id”、“@version” 和 “unix_ts_in_secs” 字段，因为我们不希望将这些字段写入到 Elasticsearch 中。

output：在这一部分，我们指定每个文档都应当写入 Elasticsearch，还需为其分配一个 “_id”（需从我们在筛选部分所创建的元数据字段提取出来）。还会有一个包含被注释掉代码的 rubydebug 输出，启用此输出后能够帮助您进行故障排查。

错误问题
1.配置文件格式问题排查

docker logs elk_logstash

  Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:main, :exception=>"LogStash::ConfigurationError", :message=>"Expected one of [ \\t\\r\\n], \"#\", \"input\", \"filter\", \"output\" at line 1, column 1 (byte 1)", :backtrace=>["/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:32:in `compile_imperative'", "org/logstash/execution/AbstractPipelineExt.java:187:in `initialize'", "org/logstash/execution/JavaBasePipelineExt.java:72:in `initialize'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:47:in `initialize'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline_action/create.rb:52:in `execute'", "/usr/share/logstash/logstash-core/lib/logstash/agent.rb:383:in `block in converge_state'"]}

参考：https://stackoverflow.com/questions/64372405/expected-one-of-t-r-n-logstash-config-error
2.Docker安装ELK中提到的docker-compose需要变更，logstash需要依赖mysql启动之后才执行

 version: '2.2'
services:
  elasticsearch:
    image: elasticsearch:7.16.3  #镜像
    container_name: elk_elasticsearch  #定义容器名称
    restart: always  #开机启动，失败也会一直重启
    environment:
      - "cluster.name=elasticsearch" #设置集群名称为elasticsearch
      - "discovery.type=single-node" #以单一节点模式启动
      - "ES_JAVA_OPTS=-Xms256m -Xmx256m" #设置使用jvm内存大小
    volumes:
      - /data/elk/elasticsearch/plugins:/usr/share/elasticsearch/plugins #插件文件挂载
      - /data/elk/elasticsearch/data:/usr/share/elasticsearch/data #数据文件挂载
    ports:
      - 9200:9200
    networks:
      - elastic 
  kibana:
    image: kibana:7.16.3
    container_name: elk_kibana
    restart: always
    depends_on:
      - elasticsearch #kibana在elasticsearch启动之后再启动
    environment:
      - ELASTICSEARCH_URL=http://elasticsearch:9200 #设置访问elasticsearch的地址
    ports:
      - 5601:5601
    networks:
      - elastic 
  
  mysql:
    restart: always
    image: mysql:8.0
    container_name: mysqllabel
    volumes:
      - /apps/mysql/mydir:/mydir
      - /apps/mysql/datadir:/var/lib/mysql
      - /apps/mysql/conf/my.cnf:/etc/my.cnf
      # 数据库还原目录 可将需要还原的sql文件放在这里
      - /apps/mysql/source:/docker-entrypoint-initdb.d
    environment:
      - "MYSQL_ROOT_PASSWORD=uu001"
      - "MYSQL_DATABASE=uudemo"
      - "TZ=Asia/Shanghai"
    ports:
      # 使用宿主机的3306端口映射到容器的3306端口
      # 宿主机：容器
      - 3306:3306
  logstash:
    image: logstash:7.16.3
    container_name: elk_logstash
    restart: always
    volumes:
      - /data/elk/logstash/logstash.conf:/usr/share/logstash/pipeline/logstash.conf #挂载logstash的配置文件
      - /data/elk/logstash/mysql/mysql-connector-java-8.0.27.jar:/app/mysql-connector-java-8.0.27.jar #jar包
    depends_on:
      - elasticsearch #logstash在elasticsearch启动之后再启动
      - mysql #logstash在mysql启动之后再启动
    links:
      - elasticsearch:es #可以用es这个域名访问elasticsearch服务
    ports:
      - 4560:4560
    networks:
      - elastic 
 
networks:
  elastic:
    driver: bridge

3.连不上数据库

Communications link failure，The last packet successfully received from the server was
Unable to connect to database.
Caused by: java.net.UnknownHostException: mysqllabel

解决：一般是链接字符串错误，或者容器内配置的ip无法访问或者jar包版本不对。
logstash.conf里面mysql换成ip地址。(里面是我虚拟机的ip)

查看同步效果

logstash观察日志

docker logs elk_logstash

kibana查看es索引

 GET attendee_idx/_search
{
  "query":{
    "match_phrase_prefix": {
      "full_name": "张"
    }
  }
}

输出

 {
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 1.2039728,
    "hits" : [
      {
        "_index" : "attendee_idx",
        "_type" : "_doc",
        "_id" : "af6a7380-7f82-11ec-8c33-0242ac180002",
        "_score" : 1.2039728,
        "_source" : {
          "description" : null,
          "mobile" : "13000000001",
          "full_name" : "张三1",
          "created_date" : "2022-01-27T23:06:20.000Z",
          "lastest_update_date" : "2022-01-27T23:06:20.000Z",
          "company" : "虎虎生威",
          "position" : "工程师",
          "is_deleted" : 0,
          "created_user_id" : null,
          "@timestamp" : "2022-01-27T17:41:45.948Z",
          "lastest_update_user_id" : null,
          "email" : "13000000001@qq.com"
        }
      }
    ]
  }
}

mysql执行插入一条语句

 insert into attendee(`id`,`full_name`,`mobile`,`email`,`position`,`company`,`description`,`is_deleted`,`created_date`,`created_user_id`,`lastest_update_date`,`lastest_update_user_id`) values(uuid(),'张三2','14000000001','14000000002@qq.com','工程师','虎虎生威',null,0,now(),null,now(),null);

可以看到结果已生效。

总结

（待后面补充）

参考资料

配置 logstash 将mysql多表数据全量增量同步到es
Logstash实时同步MySQL数据到ElasticSearch的经验总结
 如何通过 Docker 部署 Logstash 同步 Mysql 数据库数据到 ElasticSearch
how to keep elasticsearch synchronized with a relational database using logstash

posted @ 2022-01-27 21:23 从此启程阅读(785) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· Docker搭建ELK

· 聊一聊异构系统间数据一致性

· docker搭建Elasticsearch、Kibana、Logstash 同步mysql数据到ES

· 使用logstash同步mysql到ES

阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？

公告

昵称：从此启程
园龄： 13年
粉丝： 212
关注： 38

+加关注

2025年3月

日

一

二

三

四

五

六

从此启程

勤于思考，乐于实践。研究并分享.NetCore知识

Docker下同步Mysql数据到Es

Mysql测试

LogStash同步

查看同步效果

总结

参考资料

公告

最新随笔

积分与排名

随笔分类

随笔档案

文章分类

文章档案

阅读排行榜

评论排行榜

推荐排行榜

最新评论

	CREATE TABLE `attendee` (
	`id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
	`full_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '名称',
	`mobile` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '手机',
	`email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '邮箱',
	`position` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '职务',
	`company` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '公司',
	`description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '简介',
	`is_deleted` tinyint(0) NULL DEFAULT NULL COMMENT '是否删除',
	`created_date` datetime(0) NULL DEFAULT NULL COMMENT '创建日期',
	`created_user_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '创建人',
	`lastest_update_date` datetime(0) NULL DEFAULT NULL COMMENT '修改日期',
	`lastest_update_user_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '最后修改人',
	PRIMARY KEY (`id`) USING BTREE
	) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
	insert into attendee(`id`,`full_name`,`mobile`,`email`,`position`,`company`,`description`,`is_deleted`,`created_date`,`created_user_id`,`lastest_update_date`,`lastest_update_user_id`) values(uuid(),'张三1','13000000001','13000000001@qq.com','工程师','虎虎生威',null,0,now(),null,now(),null);

	insert into attendee(`id`,`full_name`,`mobile`,`email`,`position`,`company`,`description`,`is_deleted`,`created_date`,`created_user_id`,`lastest_update_date`,`lastest_update_user_id`) values(uuid(),'李四1','13000000002','13000000002@qq.com','设计师','虎虎生威',null,0,now(),null,now(),null);


	input {
	jdbc {
	jdbc_driver_library => "app/mysql-connector-java-8.0.27.jar"
	jdbc_driver_class => "com.mysql.jdbc.Driver"
	jdbc_connection_string => "jdbc:mysql://10.0.2.55:3306/es_demo_lib"
	jdbc_user => "root"
	jdbc_password => "yourpassword"
	jdbc_paging_enabled => true
	tracking_column => "unix_ts_in_secs"
	use_column_value => true
	tracking_column_type => "numeric"
	schedule => "/5 * * * *"
	statement => "SELECT *, UNIX_TIMESTAMP(lastest_update_date) AS unix_ts_in_secs FROM attendee WHERE (UNIX_TIMESTAMP(lastest_update_date)) > :sql_last_value AND lastest_update_date < NOW() ORDER BY lastest_update_date asc"
	}
	}
	filter {
	mutate {
	copy => { "id" => "[@metadata][_id]"}
	remove_field => ["id", "@version", "unix_ts_in_secs"]
	}
	}
	output {
	elasticsearch {
	hosts => ["elasticsearch:9200"]
	index => "attendee_idx"
	document_id => "%{[@metadata][_id]}"
	}
	}

	version: '2.2'
	services:
	elasticsearch:
	image: elasticsearch:7.16.3 #镜像
	container_name: elk_elasticsearch #定义容器名称
	restart: always #开机启动，失败也会一直重启
	environment:
	- "cluster.name=elasticsearch" #设置集群名称为elasticsearch
	- "discovery.type=single-node" #以单一节点模式启动
	- "ES_JAVA_OPTS=-Xms256m -Xmx256m" #设置使用jvm内存大小
	volumes:
	- /data/elk/elasticsearch/plugins:/usr/share/elasticsearch/plugins #插件文件挂载
	- /data/elk/elasticsearch/data:/usr/share/elasticsearch/data #数据文件挂载
	ports:
	- 9200:9200
	networks:
	- elastic
	kibana:
	image: kibana:7.16.3
	container_name: elk_kibana
	restart: always
	depends_on:
	- elasticsearch #kibana在elasticsearch启动之后再启动
	environment:
	- ELASTICSEARCH_URL=http://elasticsearch:9200 #设置访问elasticsearch的地址
	ports:
	- 5601:5601
	networks:
	- elastic

	mysql:
	restart: always
	image: mysql:8.0
	container_name: mysqllabel
	volumes:
	- /apps/mysql/mydir:/mydir
	- /apps/mysql/datadir:/var/lib/mysql
	- /apps/mysql/conf/my.cnf:/etc/my.cnf
	# 数据库还原目录可将需要还原的sql文件放在这里
	- /apps/mysql/source:/docker-entrypoint-initdb.d
	environment:
	- "MYSQL_ROOT_PASSWORD=uu001"
	- "MYSQL_DATABASE=uudemo"
	- "TZ=Asia/Shanghai"
	ports:
	# 使用宿主机的3306端口映射到容器的3306端口
	# 宿主机：容器
	- 3306:3306
	logstash:
	image: logstash:7.16.3
	container_name: elk_logstash
	restart: always
	volumes:
	- /data/elk/logstash/logstash.conf:/usr/share/logstash/pipeline/logstash.conf #挂载logstash的配置文件
	- /data/elk/logstash/mysql/mysql-connector-java-8.0.27.jar:/app/mysql-connector-java-8.0.27.jar #jar包
	depends_on:
	- elasticsearch #logstash在elasticsearch启动之后再启动
	- mysql #logstash在mysql启动之后再启动
	links:
	- elasticsearch:es #可以用es这个域名访问elasticsearch服务
	ports:
	- 4560:4560
	networks:
	- elastic

	networks:
	elastic:
	driver: bridge

	GET attendee_idx/_search
	{
	"query":{
	"match_phrase_prefix": {
	"full_name": "张"
	}
	}
	}

	{
	"took" : 2,
	"timed_out" : false,
	"_shards" : {
	"total" : 1,
	"successful" : 1,
	"skipped" : 0,
	"failed" : 0
	},
	"hits" : {
	"total" : {
	"value" : 1,
	"relation" : "eq"
	},
	"max_score" : 1.2039728,
	"hits" : [
	{
	"_index" : "attendee_idx",
	"_type" : "_doc",
	"_id" : "af6a7380-7f82-11ec-8c33-0242ac180002",
	"_score" : 1.2039728,
	"_source" : {
	"description" : null,
	"mobile" : "13000000001",
	"full_name" : "张三1",
	"created_date" : "2022-01-27T23:06:20.000Z",
	"lastest_update_date" : "2022-01-27T23:06:20.000Z",
	"company" : "虎虎生威",
	"position" : "工程师",
	"is_deleted" : 0,
	"created_user_id" : null,
	"@timestamp" : "2022-01-27T17:41:45.948Z",
	"lastest_update_user_id" : null,
	"email" : "13000000001@qq.com"
	}
	}
	]
	}
	}

勤于思考，乐于实践。 研究并分享.NetCore知识

Mysql测试

LogStash同步

查看同步效果

总结

参考资料

公告

最新随笔

积分与排名

随笔档案

文章档案

勤于思考，乐于实践。研究并分享.NetCore知识