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