Ship MySQL logs

• Filebeat
• Docker sidecar

MySQL + Filebeat setup

MySQL is an open-source relational database management system. Filebeat is often the easiest way to get logs from your system to Layerlog. Layerlog has a dedicated configuration wizard to make it simple to configure Filebeat. If you already have Filebeat and you want to add new sources, check out our other shipping instructions to copy&paste just the relevant changes from our code examples.

Configuration

Before you begin, you’ll need:

• Filebeat 7 or Filebeat 6
• Port 5015 open

Configure MySQL to write general query logs

In the MySQL configuration file (/etc/mysql/my.cnf), paste these lines:

general_log_file = /var/log/mysql/mysql.log
general_log= 1
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes = 1

Restart MySQL:

sudo /etc/init.d/mysql restart

Download the Layerlog public certificate to your credentials server

For HTTPS shipping, download the Layerlog 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 MySQL as an input in your Filebeat configuration

In the Filebeat configuration file (/etc/filebeat/filebeat.yml), add MySQL to the filebeat.inputs section.

Replace <<LOG-SHIPPING-TOKEN>> with the token of the account you want to ship to.

# ...
filebeat.inputs:
- type: log
  paths:
    - /var/log/mysql/mysql.log

  fields:
    logzio_codec: plain

    # You can manage your tokens at
    # http://panel.layerlog.com/#/dashboard/settings/manage-tokens/log-shipping
    token: <<LOG-SHIPPING-TOKEN>>
    type: mysql
  fields_under_root: true
  encoding: utf-8
  ignore_older: 3h

- type: log
  paths:
    - /var/log/mysql/mysql-slow.log

  fields:
    logzio_codec: plain

    # You can manage your tokens at
    # http://panel.layerlog.com/#/dashboard/settings/manage-tokens/log-shipping
    token: <<LOG-SHIPPING-TOKEN>>
    type: mysql_slow_query
  fields_under_root: true
  encoding: utf-8
  ignore_older: 3h
  multiline:
    pattern: '^# Time:'
    negate: true
    match: after

- type: log
  paths:
    - /var/log/mysql/error.log

  fields:
    logzio_codec: plain

    # You can manage your tokens at
    # http://panel.layerlog.com/#/dashboard/settings/manage-tokens/log-shipping
    token: <<LOG-SHIPPING-TOKEN>>
    type: mysql_error
  fields_under_root: true
  encoding: utf-8
  ignore_older: 3h

If you’re running Filebeat 7, paste this code block. Otherwise, you can leave it out.

# ... For Filebeat 7 only ...
filebeat.registry.path: /var/lib/filebeat
processors:
- rename:
    fields:
    - from: "agent"
      to: "filebeat_agent"
    ignore_missing: true
- rename:
    fields:
    - from: "log.file.path"
      to: "source"
    ignore_missing: true

If you’re running Filebeat 6, paste this code block.

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

Preconfigured log types

Parameter	Log Type	Default log location
General query log	mysql	/var/log/mysql/mysql.log
Slow query log	mysql_slow_query	/var/log/mysql/mysql-slow.log
Error log	mysql_error	/var/log/mysql/error.log

The log type is used to apply the appropriate Layerlog preconfigured parsing pipeline so that your logs will be automatically parsed.

Set Layerlog as the output

If Layerlog is not an output, add it now. Remove all other outputs.

Replace <<LISTENER-HOST>> with the host for your region. For example, listener.layerlog.com if your account is hosted on AWS US East, or listener-nl.layerlog.com if hosted on Azure West Europe.

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

Start Filebeat

Start or restart Filebeat for the changes to take effect.

Check Layerlog for your logs

Give your logs some time to get from your system to ours, and then open Kibana.

If you still don’t see your logs, see log shipping troubleshooting.

Set up a Docker sidecar for MySQL

MySQL is an open-source relational database management system. Docker sidecar is a container that runs on the same Pod as the application container. This integration allows you to send your MySQL logs to your Layerlog account using a Docker sidecar.

Pull the Docker image

Download the logzio/mysql-logs image:

docker pull logzio/mysql-logs

Run the Docker image

For a complete list of options, see the parameters below the code block.👇

docker run -d --name logzio-mysql-logs \
-e LOGZIO_TOKEN="<<LOG-SHIPPING-TOKEN>>" \
-e LOGZIO_LISTENER_HOST="<<LISTENER-HOST>>" \
-v /var/log/logzio:/var/log/logzio \
-v /var/log/mysql:/var/log/mysql \
logzio/mysql-logs

Parameters

Parameter	Description	Required/Default
LOGZIO_TOKEN	Your Layerlog log shipping token directs the data securely to your Layerlog Log Management account. The default token is auto-populated in the examples when you’re logged into the Layerlog app as an Admin. Manage your tokens. Replace <<LOG-SHIPPING-TOKEN>> with the token of the account you want to ship to.	Required
LOGZIO_LISTENER_HOST	Listener URL and port. Replace <<LISTENER-HOST>> with the host for your region. For example, listener.layerlog.com if your account is hosted on AWS US East, or listener-nl.layerlog.com if hosted on Azure West Europe.	https://listener.layerlog.com:8071
MYSQL_ERROR_LOG_FILE	Path to the MySQL error log.	/var/log/mysql/error.log
MYSQL_SLOW_LOG_FILE	Path to the MySQL slow query log.	/var/log/mysql/mysql-slow.log
MYSQL_LOG_FILE	Path to the MySQL general log.	/var/log/mysql/mysql.log

Check Layerlog for your logs

Give your logs some time to get from your system to ours, and then open Kibana.

If you still don’t see your logs, see log shipping troubleshooting.