Answers

• 

  Anurag Srivastava

  Oct 3, 2020, 10:58:55 AM | Share

  • Facebook
  • Twitter
  • WhatsApp
  • Linkedin
  • Copy Link

  We can migrate the MySQL data to Elasticsearch using Logstash and for that, we first need to create the Logstash

  configuration file inside /etc/logstash/conf.d/ directory. So let us create a file as blog.conf and write the following code:
  
  

  input {
      jdbc {
          jdbc_driver_library => "/usr/share/logstash/mysql-connector-java-5.1.23-bin.jar"
          jdbc_driver_class => "com.mysql.jdbc.Driver"
          # mysql jdbc connection string to our database, mydb
           jdbc_connection_string => "jdbc:mysql://url-of-db:3306/db_name?zeroDateTimeBehavior=convertToNull"
          # The user we wish to execute our statement as
          jdbc_user => "username"
          jdbc_password => "password"
          schedule => "* * * * *"
          # our query to fetch blog details
          statement => "SELECT blg.*, concat(au.first_name, ' ',au.last_name) as name,au.email as email, 
  cc.category_name, cc.category_image  FROM `blog_blogs` as blg left join auth_user as au on au.id = 
  blg.author_id left join category_category as cc on cc.id = blg.category_id where blg.id > :sql_last_value 
  order by blg.create_date"
          use_column_value => true
          tracking_column => id
          tracking_column_type => "numeric"
      }
  }
  output {
     elasticsearch {
          hosts => "http://127.0.0.1:9200"
          index => "bqstack"
          document_type => "blogs"
          }
  }
  

  
  

  After doing all those configuration changes we need to execute Logstash configuration using the following command on

  Ubuntu:

  /usr/share/logstash/bin/logstash -f /etc/logstash/conf.d/blog.conf --path.data=/tmp/bq

  
  This is a one time command and after running it, the scheduler will start working as per our scheduler entry, the query

  will be executed every second. In this way, we can configure Logstash using the JDBC input plugin to read the RDBMS

  data and put it into Elasticsearch.

  If you want more details then please refer to my blog: Configuring Logstash to push MySQL data into Elasticsearch

  Let me know in case if you have any further doubt.
  

  
  

  0 | 0