Database Monitoring with Elasticsearch, Kibana, Fleet, and MySQL Agent

Agent

In this blog, I’ll walk you through how I set up database monitoring using the Elastic Stack (Elasticsearch, Kibana, Fleet) along with a MySQL agent. By the end, you'll have a fully operational setup that collects MySQL metrics and visualizes them in Kibana dashboards.

🛠️ Prerequisites

Before starting, make sure you have the following installed on your system:

• Docker & Docker Compose

• Basic knowledge of Docker networks and containers

🔗 Step 1: Create a Docker Network

Since we’ll be running multiple services that need to talk to each other, let’s first create a Docker network:

docker network create efk-net

📦 Step 2: Run Elasticsearch & Kibana

Now, let’s spin up Elasticsearch and Kibana using Docker Compose.

👉 Create a file called docker-compose.yml and add the following:

version: "3.8"
services:
  elasticsearch:
    image: docker.elastic.co/elasticsearch/elasticsearch:8.15.0
    container_name: elasticsearch
    environment:
      - discovery.type=single-node
      - xpack.security.enabled=true
      - xpack.security.http.ssl.enabled=false
      - xpack.security.transport.ssl.enabled=false
      - xpack.security.enrollment.enabled=true 
    ports:
      - "9200:9200"
    volumes:
      - es_data:/usr/share/elasticsearch/data
    networks:
      - efk-net
  kibana:
    image: docker.elastic.co/kibana/kibana:8.15.0
    container_name: kibana
    environment:
      - ELASTICSEARCH_HOSTS=http://elasticsearch:9200
    ports:
      - "5601:5601"
    depends_on:
      - elasticsearch
    volumes:
      - ./kibana.yml:/usr/share/kibana/config/kibana.yml:ro
    networks:
      - efk-net

volumes:
  es_data:
networks:
  efk-net:
    external: true

Now, run:

docker compose up -d elasticsearch

📝 Step 3: Configure Kibana

Create a kibana.yml file in the same folder:

server.host: "0.0.0.0"
elasticsearch.hosts: ["http://elasticsearch:9200"]
elasticsearch.serviceAccountToken: "YOUR_TOKEN"

We will set token later.

🔑 Step 4: Generate Authentication

Elastic 8.x comes with security enabled by default. We need to set up authentication for Kibana and for ourselves as admins.

4.1 Generate a Kibana Service Token

Run the following inside Elasticsearch:

docker exec -it elasticsearch \
  bin/elasticsearch-service-tokens create elastic/kibana kibana-service-token

This will output something like:

SERVICE_TOKEN elastic/kibana/kibana-service-token = AAEAAWVsYXN0aWMva2liYW5h…

Then Copy the token into kibana.yml.

Up Kibana:

docker compose up -d kibana

4.2 Reset the elastic User Password

For admin login, reset the built-in elastic superuser password:

docker exec -it elasticsearch bin/elasticsearch-reset-password -u elastic

You’ll get an autogenerated password, for example:

Save this password — you’ll need it to log into Kibana.

✅ Step 5: Access the UI

• Elasticsearch: http://localhost:9200

It takes the user name password that I got from 4.2 section. After enter you can see this:

Kibana: http://localhost:5601

It takes the user name password that I got from 4.2 section. After enter you can see this:

⚙️ Step 6: Set Up Fleet Server

Now that Elasticsearch and Kibana are up, we’ll configure Fleet — the central agent management system in Kibana.

1. In Kibana, go to the Management section from the left panel.

2. Click on Fleet → Add Fleet Server.

   

3. Give your Fleet Server a name and set the URL

4. Generate a policy and continue.

   

   You’ll now be taken to the installation instructions.

   • Select your target OS (Linux, Windows, or Mac).

   • For me, I chose Linux, so I copied the provided installation command.

     

   • Run that command on the server where you want Fleet Server installed.

     

   • Once the installation completes, Kibana will move to the Confirm connection section.

     

     If everything went well, you should see the Fleet Server connected 🎉

🔑 Step 6.1: Fleet Settings & Service Token

Next, we need to configure Fleet to communicate with Elasticsearch.

1. In Kibana, go to Fleet → Settings.

   

2. In the Outputs section, you’ll see an existing output of type Elasticsearch.

3. On the right-hand side, click Edit.

   

4. Change the Type to Remote Elasticsearch.

5. You’ll see a field for Service Token — this is required for Fleet to connect securely.

   

   Generate a Service Token

   To create one, open a new tab in Kibana and go to Dev Tools (under Management). Run the following query:

    POST kbn:/api/fleet/service_tokens
    {
      "remote": true
    }
   

   This will generate a new service token.

   

   Apply the Token

   • Copy the generated token.

   • Paste it into the Service Token field in Fleet Settings.

   • Click Save.

✅ Now, Fleet is fully connected to Elasticsearch.

📊 Step 6.2: Confirm Fleet Logs

To verify everything is working:

• Go to the Fleet Server logs section in Kibana.

• You should see incoming logs from your host servers 🎉

  

  This means your Fleet setup is ready and collecting host-level logs.

🗄️ Step 7: Install Elastic MySQL Agent

Before setting up the MySQL Agent in Elastic, we need to make sure MySQL is configured to store logs properly.

7.1 Configure MySQL Logging

Edit the MySQL configuration file:

vi /etc/mysql/mysql.conf.d/mysqld.cnf

Add or update the following lines:

general_log_file        = /var/log/mysql/query.log
general_log             = 1
log_error               = /var/log/mysql/error.log
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/mysql-slow.log
long_query_time         = 1

Save the file and restart MySQL:

systemctl restart mysql.service

7.2 Add MySQL Integration in Kibana

1. In Kibana, go to Integrations (from the left panel).

2. Search for MySQL and select the first free one.

   

   This will guide you through creating an Agent Policy.

   • Integration name: Give a descriptive name.

   • Collect logs: Set the paths for:

     • General logs → /var/log/mysql/query.log

     • Error logs → /var/log/mysql/error.log

     • Slow logs → /var/log/mysql/mysql-slow.log

   • Collect metrics: Provide the MySQL host details.

     • Hostname/IP of your DB server

     • Username & password for a MySQL user with read access

👉 Best practice: Create a dedicated MySQL user for Elastic Agent.

7.3 Create a MySQL User for Elastic Agent

Log into MySQL and run:

CREATE USER 'elastic_agent'@'%' IDENTIFIED BY 'StrongPassword123!';

GRANT ALL PRIVILEGES ON . TO 'elastic_agent'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

This creates a user elastic_agent with a password and grants it permissions to access MySQL metrics.

7.4 Enroll and Install Elastic Agent

1. Go to Fleet → Agents in Kibana.

2. Click Add agent.

3. Select the policy created earlier (MySQL + System).

   • ✅ Confirm that the policy shows 2 integrations (MySQL & System).

4. Click Enroll in Fleet.

5. Under Install Elastic Agent on your host, you’ll get a command like this:

    curl -L -O https://artifacts.elastic.co/downloads/beats/elastic-agent/elastic-agent-8.15.5-linux-x86_64.tar.gz
    tar xzvf elastic-agent-8.15.5-linux-x86_64.tar.gz
    cd elastic-agent-8.15.5-linux-x86_64
    sudo ./elastic-agent install --url=https://172.31.70.14:8220 \
      --enrollment-token=MlhxSGk1a0JuMlFmb0k5TzV1ZzY6a05ndUl6eERSeWlzekRSVjdRWU5Qdw== \
      --insecure
   

   Add --insecure if you’re not using TLS certificates yet.

6. Once installed, the Elastic Agent will:

   • Collect MySQL metrics (queries, slow queries, performance, etc.)

   • Collect MySQL logs (general, error, slow query logs)

   • Collect System metrics (CPU, memory, disk, etc.)

✅ Now Elastic is collecting MySQL logs, slow queries, and performance metrics from your database machine!

🔎 Step 8: Visualize MySQL Metrics in Kibana

1. Login to Kibana: Open Kibana in your browser
   https://<your-kibana-host>:5601

2. Go to the "Integrations" Section

   • From the left sidebar, click on "Integrations".

   • Search for MySQL and open the MySQL integration you already added in Step 7.

3. Find the Prebuilt Dashboards

   • In the MySQL integration page, scroll down to "Assets".

   • You will see dashboards like:

     • [Logs MySQL] Overview

     • [Metrics MySQL] Overview

4. Open the Dashboard

   • Click View Dashboard on [Metrics MySQL] Overview.

   • This will show:

     • CPU, Memory usage of MySQL

     • Query performance

     • Connections

     • Error rates

     • Replication status (if applicable)

     • Slow query analysis

5. (Optional: Create Custom Visuals)

   • If you want custom graphs (e.g., “Top 5 slowest queries”):

     • Go to Analytics → Visualize Library.

     • Use the MySQL index pattern (logs-mysql.* or metrics-mysql.*).

     • Build custom charts (bar, line, pie).

     • Save them into a custom dashboard.