[logstash]往es里面导数据时候的日期格式问题和mysql同步的关键步骤

随手一笔，遇到一个奇葩数据库，日期格式丰富多变，以下记录一种简单的解决方式，格式上可以作为日后的启发。

#注意两个date filter plugin即可
filter {
  date {
      match => ["pzrq","yyyy-MM-dd","yyyy/MM/dd","yyyy.MM.dd","yyyy-M-d","yyyy/M/d","yyyy.M.d"]
      target => "pzrq"
  }
  date {
      match => ["yxqz","yyyy-MM-dd","yyyy/MM/dd","yyyy.MM.dd","yyyy-M-d","yyyy/M/d","yyyy.M.d"]
      target => "yxqz"
  }
  mutate {
    copy => { "id" => "[@metadata][_id]"}
    remove_field => [ "@version","@timestamp"]
  }
}

建立对应的增删改journal mysql表格，这个表是采用trigger的方式，自己加入数据的。以下主要是语法相关的提示。

DELIMITER ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_bin */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `server_after_insert` AFTER INSERT ON `reg_server_items` FOR EACH ROW INSERT INTO server_test.server_journal
	SET
		action_type = 'create',
		ZCZBHHZBAPZBH = NEW.ZCZBHHZBAPZBH,
		action_time = now() */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_bin */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `server_after_update` AFTER UPDATE ON `reg_server_items` FOR EACH ROW IF NEW.ZCZBHHZBAPZBH = OLD.ZCZBHHZBAPZBH THEN
		INSERT INTO server_test.server_journal
		SET action_type = 'update',
			ZCZBHHZBAPZBH = OLD.ZCZBHHZBAPZBH,
			action_time = NOW();
	ELSE
		-- Set old one as deleted
		INSERT INTO server_test.server_journal
		SET action_type = 'delete',
			ZCZBHHZBAPZBH = OLD.ZCZBHHZBAPZBH,
			action_time = NOW();
		-- AND NEW one created
		INSERT INTO server_test.server_journal
		SET action_type = 'create',
			ZCZBHHZBAPZBH = NEW.ZCZBHHZBAPZBH,
			action_time = NOW();
	END IF */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_bin */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `server_after_delete` AFTER DELETE ON `reg_server_items` FOR EACH ROW INSERT INTO server_test.server_journal
	SET action_type = 'delete',
		ZCZBHHZBAPZBH = OLD.ZCZBHHZBAPZBH,
		action_time = now() */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

完事儿在jdbc config里面，使用filter plugin去改es的动作。

filter {  
  if [action_type] == "create" or [action_type] == "update" {
    mutate { add_field => { "[@metadata][action]" => "index" } }
  } else if [action_type] == "delete" {
    mutate { add_field => { "[@metadata][action]" => "delete" } }
  }
}