无需Schedule参数：MySQL新增数据触发Logstash同步至Elastic Stack方案咨询

Hey there! Let's work through how to fix that resource-heavy polling issue you're facing with Logstash and MySQL sync. Your current setup works, but constant polling can definitely drag down system scalability when data grows—so let's look at better alternatives, plus ways to optimize your existing setup if you need a quick win.

方案1：用CDC（变更数据捕获）实现实时触发（推荐） #

This is the industry-standard approach for real-time MySQL-to-Elasticsearch sync without polling. Tools like Debezium capture MySQL's binlog events directly, so it only sends data to Logstash when actual changes (inserts/updates/deletes) happen. No wasted cycles checking for new data when there's nothing to sync.

基本实现步骤： #

1. Enable MySQL Binlog: Edit your MySQL config (my.cnf or my.ini) to turn on binlog with row-level formatting:

   log_bin = mysql-bin
   server-id = 1
   binlog_format = ROW
   binlog_do_db = testdb
   

   Restart MySQL after making changes.

2. Set up Debezium: You can use Debezium with Kafka Connect (most common) or Debezium Server. Configure it to monitor your brainplay table—Debezium will push change events to a Kafka topic.

3. Logstash Consumes from Kafka: Update your Logstash config to pull events from Kafka instead of polling MySQL:

   input {
     kafka {
       bootstrap_servers => "your-kafka-ip:9092"
       topics => "mysql.testdb.brainplay"
       codec => json
     }
   }
   
   output {
     stdout { codec => json_lines }
     elasticsearch {
       hosts => "localhost:9200"
       index => "test-migrate"
       document_type => "data"
       document_id => "%{payload.personid}"
     }
   }
   

   This setup is fully real-time, scales well, and only processes actual data changes—no unnecessary database queries.

方案2：自定义Webhook触发同步 #

If you don't want to add Kafka/Debezium to your stack, you can set up a trigger in MySQL to notify Logstash when new data is inserted.

实现步骤： #

1. Add an HTTP Input to Logstash: Let Logstash listen for webhook requests to trigger a sync:

   input {
     http {
       host => "0.0.0.0"
       port => 8080
       add_field => { "trigger_sync" => "true" }
     }
     # Keep your JDBC input as a fallback, but set a very long schedule
     jdbc {
       jdbc_connection_string => "jdbc:mysql://localhost:3306/testdb"
       jdbc_user => "root"
       jdbc_password => ""
       jdbc_driver_library => "/home/Downloads/mysql-connector-java-5.1.38.jar"
       jdbc_driver_class => "com.mysql.jdbc.Driver"
       schedule => "@daily" # Only run once a day as backup
       use_column_value => true
       tracking_column => 'EVENT_TIME_OCCURRENCE_FIELD'
       statement => "SELECT * FROM brainplay WHERE EVENT_TIME_OCCURRENCE_FIELD > :sql_last_value"
     }
   }
   

2. Create a MySQL Trigger: When a new row is inserted, send an HTTP request to Logstash's endpoint. Note: You'll need MySQL to support executing shell commands (via sys_exec UDF or similar):

   DELIMITER //
   CREATE TRIGGER trigger_after_insert_brainplay
   AFTER INSERT ON brainplay
   FOR EACH ROW
   BEGIN
     SET @webhook_url = 'http://your-logstash-ip:8080';
     SET @cmd = CONCAT('curl -X POST ', @webhook_url, ' -d "{}"');
     SELECT sys_exec(@cmd);
   END //
   DELIMITER ;
   

   ⚠️ Warning: This can slow down MySQL writes since the trigger waits for the HTTP request to complete. For better performance, send the trigger event to a lightweight message queue first, then have a consumer notify Logstash.

方案3：优化现有轮询配置（过渡方案） #

If you need to stick with polling for now, you can tweak your setup to reduce resource usage:

• Index your tracking column: Make sure EVENT_TIME_OCCURRENCE_FIELD has a database index. This makes the WHERE EVENT_TIME_OCCURRENCE_FIELD > :sql_last_value query lightning fast, even on large tables.
• Adjust the schedule: Widen the polling interval if real-time sync isn't critical (e.g., */30 * * * * instead of every 15 minutes).
• Limit result size: Add a LIMIT clause to your SQL to avoid pulling thousands of rows at once:

  SELECT * FROM brainplay WHERE EVENT_TIME_OCCURRENCE_FIELD > :sql_last_value LIMIT 1000
  

• Persist sql_last_value: Ensure Logstash uses last_run_metadata_path to save the last tracked value—this prevents it from re-querying all data after a restart.

总结 #

For long-term scalability and real-time performance, Debezium's CDC approach is the best choice—it eliminates polling entirely and only processes actual data changes. The webhook method works for small-scale use cases but has tradeoffs with MySQL performance. Optimizing your existing polling setup is a quick fix, but it's still a band-aid compared to CDC.

内容的提问来源于stack exchange，提问作者ankitkhandelwal185