Ship MySQL logs

• Filebeat
• Docker sidecar

MySQL + Filebeat setup

Configuration tl;dr

Item	Description
Files	Sample configuration Logz.io public certificate
Listener	Port 5015. For help finding your region’s listener host, see Account region.
Default log locations	General query log: /var/log/mysql/mysql.log Slow query log: /var/log/mysql/mysql-slow.log Error log: /var/log/mysql/error.log
Log type (for preconfigured parsing)	General query log: mysql Slow query log: mysql_slow_query Error log: mysql_error

Guided configuration

Before you begin, you’ll need: Filebeat 7 or Filebeat 6, MySQL

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 Logz.io public certificate to your Filebeat server

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 MySQL as an input

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

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

    # Your Logz.io account token. You can find your token at
    #  https://app.logz.io/#/dashboard/settings/manage-accounts
    token: <<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

    # Your Logz.io account token. You can find your token at
    #  https://app.logz.io/#/dashboard/settings/manage-accounts
    token: <<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

    # Your Logz.io account token. You can find your token at
    #  https://app.logz.io/#/dashboard/settings/manage-accounts
    token: <<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

Set Logz.io as the output

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

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.

# ...
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 Logz.io 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

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="<<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
LOGZIO_TOKEN	Your Logz.io account token. Replace <<SHIPPING-TOKEN>> with the token of the account you want to ship to.
LOGZIO_LISTENER_HOST listener.logz.io	Logz.io listener host to ship the logs to. 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.
MYSQL_ERROR_LOG_FILE /var/log/mysql/error.log	Path to the MySQL error log.
MYSQL_SLOW_LOG_FILE /var/log/mysql/mysql-slow.log	Path to the MySQL slow query log.
MYSQL_LOG_FILE /var/log/mysql/mysql.log	Path to the MySQL general log.

Check Logz.io 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.