CentOS7下Elasticsearch抽取MySQL数据遇index_not_found_exception错误求助

Hey there, let's tackle that index_not_found_exception error you're hitting when syncing MySQL data to Elasticsearch 6.2.4 with the JDBC plugin. Here are the most actionable fixes to work through:

First, confirm the index name you're trying to write to (check your JDBC configuration script for the es.index value). Run this command to check if it exists:

curl -XGET 'http://localhost:9200/your_target_index_name?pretty'

• If you get a 404 response, the index doesn't exist. You can create it manually with a command like this (adjust mappings to match your MySQL test table fields):

  curl -XPUT 'http://localhost:9200/your_target_index_name' -H 'Content-Type: application/json' -d '{
    "settings": {"number_of_shards": 1},
    "mappings": {
      "_doc": {
        "properties": {
          "id": {"type": "integer"},
          "your_field1": {"type": "text"},
          "your_field2": {"type": "date"}
        }
      }
    }
  }'
  

  Note: Elasticsearch 6.x only allows one type per index, so use _doc as the default type.

Open your elasticsearch-jdbc execution script and verify these critical parameters:

• es.index: Must match the exact name of the index you created (case-sensitive!)
• es.type: Set to _doc to align with ES 6.x rules
• MySQL connection string: Ensure it points to your ElasticSearchDatabase and uses valid credentials
• SQL query: Confirm it correctly targets the test table (select * from test should work if permissions are set)

Example of a valid configuration snippet:

cd /etc/elasticsearch/elasticsearch-jdbc-2.3.4.1
./bin/elasticsearch-jdbc \
  --url 'jdbc:mysql://localhost:3306/ElasticSearchDatabase?useSSL=false' \
  --user 'your_mysql_username' \
  --password 'your_mysql_password' \
  --sql 'select * from test' \
  --es.index 'your_target_index_name' \
  --es.type '_doc' \
  --es.host 'localhost' \
  --es.port 9200

You're using elasticsearch-jdbc-2.3.4.1 with Elasticsearch 6.2.4 — these versions are completely incompatible. The old JDBC plugin was built for ES 2.x and won't work reliably with ES 5.x or later.

The official, supported alternative for MySQL sync is the Logstash JDBC Input Plugin. Here's a quick setup guide:

1. Install Logstash 6.2.4 (match your ES version for best compatibility)
2. Download the MySQL JDBC driver (e.g., mysql-connector-java-5.1.49.jar) and place it in Logstash's vendor/jars directory
3. Create a Logstash config file (e.g., mysql_to_es.conf):

   input {
     jdbc {
       jdbc_connection_string => "jdbc:mysql://localhost:3306/ElasticSearchDatabase?useSSL=false"
       jdbc_user => "your_mysql_username"
       jdbc_password => "your_mysql_password"
       jdbc_driver_library => "/path/to/mysql-connector-java-5.1.49.jar"
       jdbc_driver_class => "com.mysql.jdbc.Driver"
       schedule => "* * * * *" # Sync every minute (adjust as needed)
       statement => "SELECT * FROM test"
     }
   }
   
   output {
     elasticsearch {
       hosts => ["localhost:9200"]
       index => "your_target_index_name"
       document_type => "_doc"
       document_id => "%{id}" # Use MySQL primary key to avoid duplicate docs
     }
     stdout { codec => json_lines } # Print sync logs to console for debugging
   }
   

4. Start Logstash:

   bin/logstash -f mysql_to_es.conf
   

• Check the elasticsearch-jdbc logs in /etc/elasticsearch/logs/ — look for hints like invalid index names, failed MySQL connections, or permission issues
• Also review Elasticsearch's own logs in /var/log/elasticsearch/ to see if it's rejecting requests for specific reasons

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