Send data via MySql Metrics to your Logstash instance provided by Logit.io

MySql Metrics

Collect and ship MySql metrics to Logstash and Elasticsearch.

Metricbeat is a lightweight shipper that helps you monitor your MySql database servers by collecting metrics running on the MySql server. Configure Metricbeat using the pre-defined examples below to collect and ship MySql database server metrics and statistics to Logstash or Elasticsearch.

Step 1 - Install Metricbeat

First we need to install Metricbeat.

deb (Debian/Ubuntu)

sudo apt-get install apt-transport-https
wget -qO - https://artifacts.elastic.co/GPG-KEY-elasticsearch | sudo apt-key add -
echo "deb https://artifacts.elastic.co/packages/oss-6.x/apt stable main" | sudo tee -a /etc/apt/sources.list.d/elastic-6.x.list

sudo apt-get update && sudo apt-get install metricbeat

rpm (Redhat/Centos)

sudo rpm --import https://packages.elastic.co/GPG-KEY-elasticsearch
echo "[elastic-6.x]
name=Elastic repository for 6.x packages
baseurl=https://artifacts.elastic.co/packages/6.x/yum
gpgcheck=1
gpgkey=https://artifacts.elastic.co/GPG-KEY-elasticsearch
enabled=1
autorefresh=1
type=rpm-md" | sudo tee /etc/yum.repos.d/elastic-beats.repo

sudo yum install metricbeat

sudo chkconfig --add metricbeat

macOS

curl -L -O https://artifacts.elastic.co/downloads/beats/metricbeat/metricbeat-6.8.4-darwin-x86_64.tar.gz
tar xzvf metricbeat-6.8.4-darwin-x86_64.tar.gz

Windows

  • Download the Metricbeat Windows zip file from the official downloads page.

  • Extract the contents of the zip file into C:\Program Files.

  • Rename the metricbeat-<version>-windows directory to Metricbeat.

  • Open a PowerShell prompt as an Administrator (right-click the PowerShell icon and select Run As Administrator). If you are running Windows XP, you may need to download and install PowerShell.

  • Run the following commands to install Metricbeat as a Windows service:

    PS > cd 'C:\Program Files\Metricbeat'
    PS C:\Program Files\Metricbeat> .\install-service-metricbeat.ps1
    
    Security warning
    Run only scripts that you trust. While scripts from the internet can be useful,
    this script can potentially harm your computer. If you trust this script, use
    the Unblock-File cmdlet to allow the script to run without this warning message.
    Do you want to run C:\Program Files\Winlogbeat\install-service-metricbeat.ps1?
    [D] Do not run  [R] Run once  [S] Suspend  [?] Help (default is "D"): R
    
    Status   Name               DisplayName
    ------   ----               -----------
    Stopped  metricbeat         metricbeat
    
If script execution is disabled on your system, you need to set the execution policy for the current session to allow the script to run. For example: PowerShell.exe -ExecutionPolicy UnRestricted -File .\install-service-metricbeat.ps1.
My OS isn't here! Don't see your system? Check out the official downloads page for more options (including 32-bit versions).

Step 2 - Locate configuration file

deb/rpm /etc/metricbeat/metricbeat.yml
mac/win <EXTRACTED_ARCHIVE>/metricbeat.yml

Step 3 - Configure output

We'll be shipping to Logstash so that we have the option to run filters before the data is indexed.
Comment out the elasticsearch output block.

## Comment out elasticsearch output
#output.elasticsearch:
#  hosts: ["localhost:9200"]

Uncomment and change the logstash output to match below.

output.logstash:
    hosts: ["your-logstash-host:your-port"]
    loadbalance: true
    ssl.enabled: true

Step 4 - Enable module

There are several built in metricbeat modules you can use. To enable the MySql module, run the following:

deb/rpm

metricbeat modules list
metricbeat modules enable mysql

macOS

./metricbeat modules list
./metricbeat modules enable mysql

Windows

PS > .\metricbeat.exe modules enable mysql

Step 5 - Configure module

Each module has its own configuration file where different metricsets can be enabled / disabled. Locate the configuration file for the MySql module.

deb/rpm /etc/metricbeat/modules.d/mysql.yml

mac/win ~/<EXTRACTED_ARCHIVE>/modules.d/mysql.yml

By default the status metricset is disabled. To enable or disable a metric simply comment or uncomment the line out.

- module: mysql
  metricsets:
    - status
    - galera_status
  period: 10s

You will also need to enter the host location of the database and the username and password to access it.

# Host DSN should be defined as "user:pass@tcp(127.0.0.1:3306)/"
# The username and password can either be set in the DSN or using the username
# and password config options. Those specified in the DSN take precedence.
hosts: ["(127.0.0.1:3306)/"]

# Username of hosts. Empty by default.
username: root

# Password of hosts. Empty by default.
password: secret

Step 6 - Start Metricbeat

Ok, time to start gathering metrics!

deb/rpm

sudo systemctl enable metricbeat
sudo systemctl start metricbeat

macOS

./metricbeat

Windows

PS C:\Program Files\Metricbeat> Start-Service metricbeat

Step 7 - Compatibility

The mysql MetricSets were tested with MySQL and Percona 5.7 and 8.0 and are expected to work with all versions >= 5.7.0. It is also tested with MariaDB 10.2, 10.3 and 10.4.

Step 8 - MySql Module Dashboard

The MySql module comes with a predefined Kibana dashboard.

expand view

Expand View

compact view

Compact View

Return to Search
Sign Up