# Logstash input JDBC plugin and MariaDB

**URL:** <https://discuss.elastic.co/t/logstash-input-jdbc-plugin-and-mariadb/186985>\
**Category:** Logstash\
**Created:** [June 23, 2019, 6:51am UTC](https://discuss.elastic.co/t/logstash-input-jdbc-plugin-and-mariadb/186985 "2019-06-23T06:51:43Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![vasek](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vasek/32/136636_2.png) [@vasek](https://discuss.elastic.co/u/vasek)\
**Post date:** [June 23, 2019, 6:51am UTC](https://discuss.elastic.co/t/logstash-input-jdbc-plugin-and-mariadb/186985/1 "2019-06-23T06:51:43Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 21, 2019, 6:51am UTC](https://discuss.elastic.co/t/logstash-input-jdbc-plugin-and-mariadb/186985/2 "2019-07-21T06:51:44Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.