# Fluentd integration with mySQL and Grafana
###### tags: `By_Ivan`

This is testing for data process pipeline. The goal is send data from FluentD to Grafana, creating report for the end users.

Each service is designed to run on different machine.

skinparam activity {
    BackgroundColor<< Machine >> Lightblue
    BackgroundColor DarkKhaki

    FontColor white
    FontName Aapex
}


partition "CentOS VM" {
        partition Grafana #yellowgreen {
            temp->WebService
        }
        partition Docker #lightgray {
            partition mySQL #orange {
                
                Database->[result]WebService
            }
        }
        WebService-up>[query]Database
        partition Fluentd #lightblue {
            exec_plugin-right>[json]Database
        } 
}
hide temp

## Fluentd write to mySQL

#### Main applications:
1. mySQL (docker environment)
2. Fluentd (gem environment)

#### Requirements:
1. mysql-devel (yum)
2. mysql2 adapter (gem)
3. tzinfo (gem) (downgrade to 1.2.7)
4. fluentd plugin sql

#### Instructions:

- install

$ yum install mysql-devel
$ fluent-gem install mysql2
$ fluent-gem install fluent-plugin-sql

- config

<match *>
        @type sql
        host #mysql_ip
        port #3306
        username +++++
        password *****
        database -----
        adapter mysql2
        socket  /var/run/mysqld/mysqld.sock
        <table>
                table #tablename
                column_mapping 'keyA:indexA,keyB:indexB,...'
        </table>
</match>

:::info quote
Fluentd store its data in json format.</br>
Use the argument "column_mapping" to map the keys of json to the database column.
:::
___

#### mySQL Instructions:

setup in docker

$ docker run -it --name mysql_container -d mysql
$ docker container inspect mysql_container # get it's ip

create table and database

$ docker exec -it mysql_container mysql -p #access mysql

### Result

mysql> USE test_db
Database changed
mysql> SELECT * FROM cpu where type like "%idle";
+-----+------+----------+-------------------------+---------+-------+-------+
| id  | host | interval | timestamp               | cpu_num | type  | pct   |
+-----+------+----------+-------------------------+---------+-------+-------+
|   6 | elk  |      600 | 2020-09-07 16:10:12 UTC | all     | %idle | 99.97 |
|  12 | elk  |      600 | 2020-09-07 16:10:12 UTC | cpu0    | %idle | 99.97 |
|  18 | elk  |      600 | 2020-09-07 16:10:12 UTC | cpu1    | %idle | 99.99 |
|  24 | elk  |      600 | 2020-09-07 16:10:12 UTC | cpu2    | %idle | 99.98 |
|  30 | elk  |      600 | 2020-09-07 16:10:12 UTC | cpu3    | %idle | 99.99 |
|  36 | elk  |      600 | 2020-09-07 16:10:12 UTC | cpu4    | %idle | 99.98 |
|  42 | elk  |      600 | 2020-09-07 16:10:12 UTC | cpu5    | %idle | 99.92 |
|  48 | elk  |      600 | 2020-09-07 16:10:12 UTC | cpu6    | %idle | 99.98 |
|  54 | elk  |      600 | 2020-09-07 16:10:12 UTC | cpu7    | %idle | 99.96 |
+-----+------+----------+-------------------------+---------+---------+-------+

## mySQL with Grafana

installing grafana

https://devconnected.com/how-to-install-grafana-on-centos-8/