I am taking the example of a
Once we are getting the data from MySQL database into Elasticsearch, we can create the dashboards in Kibana as per our requirement. I 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 code as follows:
# file: blog.conf
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"
hosts => "http://127.0.0.1:9200"
index => "bqstack"
document_type => "blogs"
In the above Logstash configuration
Once these database connection related parameters are set we can set the
"* * * * * " => runs every second
"30 2 * * *" => runs as 2:30AM
"10 22 * * *" => runs at 10:10PM
Statement parameter is there to write the query, in the above query I have compared the blog id with sql_last_value which is dynamic and refer to the id column of the table. After each query execution, the value of sql_last_value is updated and set in the file. Next time when the query is executed this value is picked from the file. We are providing the data type of the tracking column along with the tracking column name, as
After doing all those configuration changes we need to execute
/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 and as per our scheduler entry, the query will be executed every second. We can do the
Above command opens the crontab file where we can write the following entry to ensure that after machine restart Logstash configuration is executed and JDBC input plugin scheduler starts executing the queries to fetch the data from the database.
@reboot /usr/share/logstash/bin/logstash -f /etc/logstash/conf.d/blog.conf --path.data=/tmp/bq
Above crontab entry starts with @reboot which works after each machine restart after that we have given the expression to execute the Logstash configuration.
In this way, we can configure Logstash using JDBC input plugin to read the RDBMS data and put it into Elasticsearch. Once data is there in Elasticsearch we can create the visualizations and dashboards using Kibana.
Other Blogs on Elastic Stack:
Introduction to Elasticsearch
Elasticsearch Installation and Configuration on Ubuntu 14.04
Log analysis with Elastic stack
Elasticsearch Rest API
Basics of Data Search in Elasticsearch
Elasticsearch Rest API
Wildcard and Boolean Search in Elasticsearch
Configure Logstash to push MySQL data into Elasticsearch
Metrics Aggregation in Elasticsearch
Bucket Aggregation in Elasticsearch
How to create Elasticsearch Cluster
Author | Blogger | Tech Lead | Elastic Stack | Innovator |View Profile
May 31, 2019, 4:54:54 AM
Can I pass more than two columns into the tracking_column because i want to track the two column value change
Jun 5, 2019, 11:41:42 AM
same question, can you please help us
Jun 5, 2019, 12:08:20 PM
Jun 5, 2019, 12:11:33 PM
@Amitav, @Jitender: No you can not use more than one column here and the reason behind it is that we want to track the change in the table which can easily be done using the auto increment field or timestamp field.
Leave a comment
Sep 16, 2019, 5:55:06 AM | Anurag Srivastava
Oct 13, 2018, 8:49:59 PM | Anurag Srivastava
Mar 9, 2019, 8:20:38 AM | Anurag Srivastava
Apr 6, 2019, 8:41:41 PM | Anurag Srivastava
Apr 14, 2018, 1:18:05 PM | Anurag Srivastava
Jan 31, 2018, 6:11:29 AM | Anurag Srivastava
Feb 9, 2019, 6:34:22 PM | Anurag Srivastava
Jun 21, 2018, 4:58:11 AM | Anurag Srivastava
Jun 26, 2018, 4:50:18 PM | Anurag Srivastava
Jul 16, 2018, 5:00:02 PM | Anurag Srivastava
Jul 31, 2018, 6:16:42 PM | Anurag Srivastava
Jun 2, 2018, 10:49:54 AM | Anurag Srivastava
May 26, 2018, 6:42:02 PM | Anurag Srivastava
Aug 10, 2018, 7:14:40 PM | Anurag Srivastava