You can ship MySQL metrics using Metricbeat.

Metricbeat setup

Before you begin, you’ll need:

Download the Logz.io public certificate

For HTTPS shipping, download the Logz.io public certificate to your certificate authority folder.

sudo curl https://raw.githubusercontent.com/logzio/public-certificates/master/AAACertificateServices.crt --create-dirs -o /etc/pki/tls/certs/COMODORSADomainValidationSecureServerCA.crt
Add Logz.io to your Metricbeat configuration

Open the Metricbeat configuration file (<<PATH_TO_METRICBEAT>>/metricbeat.yml) with your preferred text editor.

Copy and paste the code block below, overwriting the previous contents, to replace the general configuration with the following Logz.io settings:

# ===== General =====
fields:
  logzio_codec: json
  token: <<SHIPPING-TOKEN>>
fields_under_root: true

Replace <<SHIPPING-TOKEN>> with a token for the Metrics account you want to ship to.
Here’s how to look up your Metrics token.

Set Logz.io as the output

Still in the same configuration file, check if Logz.io is already an output. If not, add it now.

# ===== Outputs =====
output.logstash:
  hosts: ["<<LISTENER-HOST>>:5015"]
    ssl.certificate_authorities: ['/etc/pki/tls/certs/COMODORSADomainValidationSecureServerCA.crt']

Replace <<LISTENER-HOST>> with your region’s listener host (for example, listener.logz.io). For more information on finding your account’s region, see Account region.

One last validation - make sure Logz.io is the only output and appears only once. If the file has other outputs, remove them.

Add MySQL module configuration

Still in the same configuration file, copy and paste the code block directly below:

metricbeat.modules:
- module: mysql
  metricsets:
    - "status"
    #- "galera_status"
  period: 10s

  # Host DSN should be defined as "user:pass@tcp(127.0.0.1:3306)/"
  # or "unix(/var/lib/mysql/mysql.sock)/",
  hosts: ["<<USERNAME>>:<<PASSWORD>>@tcp(127.0.0.1:3306)/"]

processors:
  - add_host_metadata: ~
  - include_fields: # Collected metrics
      fields: ["mysql.status.bytes.received", "mysql.status.bytes.sent", "mysql.status.command.delete", "mysql.status.command.insert", "mysql.status.command.select", "mysql.status.command.update", "mysql.status.connections", "mysql.status.open.files", "mysql.status.opened", "mysql.status.threads.created", "mysql.status.threads.running", "event.module", "metricset.name", "agent.hostname", "host.name","token","logzio_codec","type","service.type"]

fields:
  logzio_codec: json
  token: <<SHIPPING-TOKEN>>
fields_under_root: true
ignore_older: 3hr
type: metrics
output.logstash:
  hosts: ["<<LISTENER-HOST>>:5015"]
  ssl.certificate_authorities: ['/etc/pki/tls/certs/COMODORSADomainValidationSecureServerCA.crt']

For a full list of available Metricbeat configuration options for the MySQL module, please see Metricbeat’s documentation.

Replace the placeholders in the configuration

Still in the same configuration file, replace the placeholders to match your specifics.

  • Replace <<SHIPPING-TOKEN>> with a token for the Metrics account you want to ship to.
    Here’s how to look up your Metrics token.

  • Replace <<LISTENER-HOST>> with your region’s listener host (for example, listener.logz.io). For more information on finding your account’s region, see Account region.

  • Replace the placeholders <<USERNAME>> and <<PASSWORD>> with your MySQL credentials.

  • When configuring the hosts option, you’ll need to use a valid MySQL data source name of the following format: [username[:password]@][protocol[(address)]]/

  • @tcp(127.0.0.1:3306) is the default address for MySQL. Change it if you’re runnning MySQL on a different port.

Start Metricbeat

Start or restart Metricbeat for the changes to take effect.

Open your MySQL dashboard in Logz.io

Give your metrics a few minutes to get from your system to ours, and then open Logz.io.

Your metrics should appear in the preconfigured dashboard in your Metrics account. To open it, click > Manage in the left menu, then click Logz.io Dashboards > MySQL.