Logstash input JDBC plugin and MariaDB

[vasek (Václav Šulc)]

Hi Elasticians,
I prepared example How to connect to MariaDB using Logstash JDBC input plugin.

I hope it will save some time

Servers

• ES node - 10.88.88.231
• MariaDB server - 10.88.88.150

[10.88.88.231] Downloading and installing jdbc driver library (mariadb-java-client-2.4.2.jar)

wget https://downloads.mariadb.com/Connectors/java/connector-java-2.4.2/mariadb-java-client-2.4.2.jar
mkdir /etc/logstash/java-libs/
mv mariadb-java-client-2.4.2.jar $_
chown -R logstash: /etc/logstash/

[10.88.88.150] Installing MariaDB server
Install MariaDB server on virtual machine with IP 10.88.88.150.

yum install mysql_secure_installation
mysql -u root -p

[10.88.88.150] Granting privileges

GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.88.88.%' IDENTIFIED BY 'your-password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

[10.88.88.150] Enabling network communication on FW
On database server 10.88.88.150:
Allow communication from ES node 10.88.88.231to mariadb server listening on port 3306.

firewall-cmd --add-rich-rule='rule family="ipv4" source address="10.88.88.231/32" port port=3306 protocol="tcp" accept'

[10.88.88.150] Creating testing DATABASE and TABLE.

DROP DATABASE IF EXISTS test_database;
CREATE DATABASE test_database ;
USE test_database ;

CREATE TABLE logs (id INT NOT NULL AUTO_INCREMENT,
       timestamp TIMESTAMP NOT NULL,
       severity VARCHAR(40) NOT NULL,
       message VARCHAR(1024) NOT NULL,
       marked_by_logstash BOOLEAN NOT NULL DEFAULT false,
       PRIMARY KEY ( id )
       );

[10.88.88.150] Inserting one row to table

INSERT INTO logs (timestamp, severity, message) VALUES ('2019-06-22 12:15:12', 'info', 'obsah zpravy');

[10.88.88.150] Showing all rows from table

SELECT * from logs ;

[10.88.88.150] Generating some rows to table using Bash script

for i in {1..10}
do
 generated_date=$(date --date="+$i hour" "+%Y-%m-%dT%H:%M:%S+02:00")
 mysql -u"root" -p"your-password" -D "test_database" -e "INSERT INTO logs (timestamp, severity, message) VALUES ('$generated_date', 'info', 'obsah zpravy');"
done

[10.88.88.231] Configuration of Logstash Mariadb pipeline

input {
  jdbc {
    jdbc_validate_connection => true
    jdbc_driver_library => "/etc/logstash/java-libs/mariadb-java-client-2.4.2.jar"
    jdbc_driver_class => "Java::org.mariadb.jdbc.Driver"
    jdbc_connection_string => "jdbc:mariadb://10.88.88.150:3306/test_database"
    jdbc_user => "root"
    jdbc_password => "your-password"
    schedule => "*/1 * * * *"
    statement => "SELECT * from logs"
    sql_log_level => "debug"
    record_last_run => true
    last_run_metadata_path => "/etc/logstash/jdbc_last_run_metadata_path/mariadb"
  }
}

output {
  elasticsearch {
      hosts => ["localhost:9200"]
      index => "mariadb-%{+YYYY.MM}"
  }
}

[10.88.88.231] Applying configuration of Logstash

systemctl restart logstash

[10.88.88.231] Setting level of logging of jdbc input plugin

curl -XPUT 'localhost:9600/_node/logging?pretty' -H 'Content-Type: application/json' -d'
{
    "logger.logstash.inputs.jdbc" : "DEBUG"
}'

[10.88.88.231] Checking whether index was created

 curl --silent -XGET localhost:9200/_cat/indices/mariadb*?h=i,dc
 mariadb-2019.06 93

[10.88.88.231] Showing documents

curl -XGET localhost:9200/mariadb*/_search?pretty
....
{
        "_index" : "mariadb-2019.06",
        "_type" : "_doc",
        "_id" : "TGsSg2sBm8_n6AiG3gzi",
        "_score" : 1.0,
        "_source" : {
          "id" : 5,
          "severity" : "info",
          "@version" : "1",
          "marked_by_logstash" : false,
          "@timestamp" : "2019-06-23T06:43:00.284Z",
          "message" : "obsah zpravy",
          "timestamp" : "2019-06-23T10:40:37.000Z"
        }
      }

Use the saved time to another activities

Vasek