Get a DemoStart Free TrialSign In

MySQL Logs

Ship logs from MySQL to logstash

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

Follow this step by step guide to get 'logs' from your system to Logit.io:

Step 1 - Install Filebeat

To get started first follow the steps below:

  • Install
  • Root access
  • Verify the required port is open

Older versions can be found here 7, 6, 5

Step 2 - Enable the Mysql Module

There are several built in filebeat modules you can use. To enable the mysql module run.

deb/rpm

filebeat modules list
filebeat modules enable mysql

macOS

cd <EXTRACTED_ARCHIVE>
./filebeat modules list
./filebeat modules enable mysql

Windows

.\Filebeat modules enable mysql

The default configured paths for MySQL logs are as follows.

/var/log/mysql/mysql.log

/var/log/mysql/mysql-slow.log

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

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

Additional module configuration can be done using the per module config files located in the modules.d folder, most commonly this would be to read logs from a non-default location

deb/rpm /etc/filebeat/modules.d/
mac/win <EXTRACTED_ARCHIVE>/modules.d/

- 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:

Step 3 - 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: ["your-logstash-host:your-ssl-port"]
    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 is a great choice.

Step 4 - Validate configuration

If you have issues starting in the next step, you can use these commands below to troubleshoot.

Let's check the configuration file is syntactically correct by running directly inside the terminal. If the file is invalid, will print an error loading config file error message with details on how to correct the problem.

deb/rpm

sudo  -e -c /etc//.yml

macOS

cd <EXTRACTED_ARCHIVE>
sudo ./ -e -c .yml

Windows

cd <EXTRACTED_ARCHIVE>
.\.exe -e -c .yml

Step 5 - Start filebeat

Start or restart to apply the configuration changes.

Step 6 - Check Logit.io for your logs

Now you should view your data:

View my data

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

Step 7 - how to diagnose no data in Stack

If you don't see data appearing in your Stack after following the steps, visit the Help Centre guide for steps to diagnose no data appearing in your Stack or Chat to support now.

Step 8 - MySQL dashboard

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

Predefined kibana dashboard screenshot

Step 9 - 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 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.

Return to Search
Sign Up

© 2023 Logit.io Ltd, All rights reserved.