MySQL Logs

Ship logs from MySQL to Logstash

Configure Filebeat to ship logs from MySQL to Logstash and Elasticsearch.

Install Integration

Please click on the Install Integration button to configure your stack for this source.

Install Filebeat

To get started you will need to install filebeat. To do this you have two main options:

Choose the filebeat (opens in a new tab) ZIP file (Windows ZIP x86_64) or
Choose the Microsoft Software Installer MSI (opens in a new tab) file (Windows MSI x86_64 (beta))

To successfully install filebeat and set up the required Windows service you will need to have administrator access.

If you have chosen to download the zip file:

Extract the contents of the zip file into C:\Program Files.
Rename the extracted folder to filebeat
Open a PowerShell prompt as an Administrator (right-click the PowerShell icon and select Run As Administrator).
From the PowerShell prompt, run the following commands to install filebeat as a Windows service:

cd 'C:\Program Files\filebeat'

.\install-service-filebeat.ps1

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-filebeat.ps1

For more information about Powershell execution policies see here (opens in a new tab).

If you have chosen to download the filebeat.msi file:

double-click on it and the relevant files will be downloaded.

At the end of the installation process you'll be given the option to open the folder where filebeat has been installed.

Open a PowerShell prompt as an Administrator (right-click the PowerShell icon and select Run As Administrator).
From the PowerShell prompt, change directory to the location where filebeat was installed and run the following command to install filebeat as a Windows service:

.\install-service-filebeat.ps1

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-filebeat.ps1

For more information about Powershell execution policies see here (opens in a new tab).

Enable the MySQL module

There are several built in filebeat modules you can use. You will need to enable the mysql module:

.\filebeat.exe modules list
.\filebeat.exe modules enable mysql

The default configured paths for MySQL logs are as follows:

c:\programdata\MySQL\MySQL Server*\error.log*

c:\programdata\MySQL\MySQL Server*\mysql-slow.log*

In the module config under modules.d, change the module settings to match your environment. You must enable at least one fileset in the module.

Filesets are disabled by default.

Copy the snippet below and replace the contents of the mysql.yml module file:

# Module: mysql
# Docs: https://www.elastic.co/guide/en/beats/filebeat/8.12/filebeat-module-mysql.html
 
- module: mysql
  # Error logs
  error:
    enabled: true
 
  # Set custom paths for the log files. If left empty,
  # Filebeat will choose the paths depending on your OS.
  #var.paths:
 
  # Slow logs
  slowlog:
    enabled: true
 
  # Set custom paths for the log files. If left empty,
  # Filebeat will choose the paths depending on your OS.
  #var.paths:

Update Your Configuration File

The configuration file below is pre-configured to send data to your Logit.io Stack via Logstash.

Copy the configuration file below and overwrite the contents of filebeat.yml.

# ============================== Filebeat modules ==============================
filebeat.config.modules:
    path: ${path.config}/modules.d/*.yml
    reload.enabled: false
    #reload.period: 10s
 
# ================================== Outputs ===================================
# ------------------------------ Logstash Output -------------------------------
output.logstash:
    hosts: ["@logstash.host:@logstash.sslPort"]
    loadbalance: true
    ssl.enabled: true
 
# ================================= Processors =================================
processors:
    - add_host_metadata:
        when.not.contains.tags: forwarded
    - add_cloud_metadata: ~
    - add_docker_metadata: ~
    - add_kubernetes_metadata: ~

If you're running Filebeat 7 add this code block to the end. Otherwise, you can leave it out.

# ... For Filebeat 7 only ...
filebeat.registry.path: /var/lib/filebeat

If you're running Filebeat 6 add this code block to the end. Otherwise, you can leave it out.

# ... For Filebeat 6 only ...
registry_file: /var/lib/filebeat/registry

Validate your YAML

It's a good idea to run the configuration file through a YAML validator to rule out indentation errors, clean up extra characters, and check if your YAML file is valid. Yamllint.com (opens in a new tab) is a great choice.

Validate configuration

In the directory where Filebeat is installed, run the following command to validate the installation:

.\@beatname.exe test config -c @beatname.yml

If the yml file is invalid, @beatname will print a description of the error. For example, if the output.logstash section was missing, @beatname would print no outputs are defined, please define one under the output section

Start filebeat

To start Filebeat, run in Powershell:

Start-Service filebeat

Check Logit.io for your logs

Data should now have been sent to your Stack.

View My Data

If you don't see take a look at How to diagnose no data in Stack below for how to diagnose common issues.

How to diagnose no data in Stack

If you don't see data appearing in your stack after following this integration, take a look at the troubleshooting guide for steps to diagnose and resolve the problem or contact our support team and we'll be happy to assist.

MySQL Dashboard

The MySQL module comes with predefined Kibana dashboards. To view your dashboards for any of your Logit.io stacks, launch Logs and choose Dashboards.

Predefined kibana dashboard screenshot

MySQL Logging Overview

MySQL is an open source relational database management system created by Michael Widenius in 1995, this relational database runs across the majority of operating systems & is closely associated with its usage for web applications.

MySQL powers some of the world's highest traffic sites, including Facebook, YouTube & Pinterest.

MySQL is able to work within an operating system to organise data into multiple data tables and show which data types may be related to each other. This helps the user to easily structure their data.

When used in this way, relational databases can be used to test database integrity, manage users and create backups of vital data.

MySQL Servers create numerous logs that you can use for troubleshooting and analysis, the most important ones include: Slow query logs, General query logs & error logs.

These logs default to a text file format, which can quickly become tedious to parse and process quickly to spot functional problems, opportunities to improve performance and identify security issues.

Our built in HA (high availability) MySQL log file analyser (opens in a new tab) can be used to centralise your data & set up alerts to monitor your log data in real-time as well as deliver metrics for Kibana visualisations & reports with easily.

Mulesoft Network Device