JIYIK CN >

We will learn how to integrate ElasticSearch with MySQL. We will also look at some of the key differences between JSON format databases and relational databases.

Integrating ElasticSearch with MySQL

As we all know, MySQL is a relational-based data management system that stores data in schemas.

On the other hand, Elastic Search (ES) is a NoSQL based data store. It is an open source distributed search engine.

It provides real-time data analysis. Hence it is more efficient than RDBMS in some cases.

It searches websites/applications, collects and analyzes log data, and performs data analysis and visualization. The basic terminology difference between JSON format DB and relational database is: database equals cluster, table equals index (data organization mechanism, storing complex data structures in JSON doc), row equals document, column equals field.

A collection of nodes is called a cluster; shards are pieces of data. The prerequisite here is that we need to install MySQL.

The open source version can be found in the MySQL Community Server section, which you can find at the MySQL download site.

Next, we create a table in MySQL.

CREATE DATABASE info_cus;
DROP TABLE IF EXISTS info_cus;
CREATE TABLE info_cus(
  id integer NOT NULL,
  PRIMARY KEY (id),
  employe_n VARCHAR(32) NOT NULL,
  update_time datetime NOT NULL
);

Insert Statement

Now we go to the MySQL server and insert these records:

INSERT INTO info_cus (id, employe_n,update_time)
VALUES (1,'ali',CURDATE()),
(2,'Ayesha',CURDATE()),
(3,'Rizwan',CURDATE()),
(4,'Tanveer',CURDATE());

Now we check:

select * from info_cus;

Output:

id    employe_n    update_time
1    ali            2022-11-13 15:31:05
2    Ayesha        2022-11-13 15:31:05
3    Rizwan        2022-11-13 15:31:05
4    Tanveer        2022-11-13 15:31:05

If we get the right record sheet, we can start.

Since ES is Java based, we need to download the JDBC connector to connect the two. This connection will help in extracting the required data from the data store and move it to Elastic Search.

To download the JDBC connector, go to this link. There will be a jar file; because it is generic, it should run on any operating system - Windows, macOS, etc.

Next, we extract the files and make sure we use the bin.jar file, no matter what version you have installed, copy this file to the root directory with the name jdbc.jar./logstash-7.12.0/, generate a new text file named jdbc_conn.conf.

Use the code given below in this new text file. It will create a Logstash pipeline through JDBC plugin.

input {
  jdbc {
    jdbc_driver_library => "<driverpath>/mysql-connector-java-<versionNumber>.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://<MySQL host>:3306/es_db"
    jdbc_user => "<myusername>"
    jdbc_password => "<mypassword>"
    jdbc_paging_enabled => true
    tracking_column => "unix_ts_in__seconds"
    use_column_value => true
    tracking_column_type => "numeric"
    schedule => "*/5 * * * * *"
    statement => "select *, unix_timestamp AS unix_ts_in__seconds FROM elas_table where (unix_timestamp > :sql_last_value AND modification_time < NOW()) ORDER BY modification_time ASC"
  }
}
filter {
  mutate {
    copy => { "id" => "[@metadata][_id]"}
    remove_field => ["id", "@version", "unix_ts_in__seconds"]
  }
}

Output:

{
  stdout { codec =>  "rubydebug"}
}

Now, open it with the new JDBC_conn configuration file Logstash：bin/logstash -f jdbc_conn.conf. Finally, we go to Logstash to move the data and log in to Elastic Search to authenticate the data in Kibana.