MySQL + Filebeat setup

Configuration tl;dr

Files
Sample configuration
Encryption 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

You’ll need: Filebeat 7 or Filebeat 6, MySQL

  1. 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
    
  2. Download the Logz.io certificate

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

    sudo wget https://raw.githubusercontent.com/logzio/public-certificates/master/COMODORSADomainValidationSecureServerCA.crt -P /etc/pki/tls/certs/
    
  3. 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
    
  4. Add Logz.io as an output

    If Logz.io is not an output, add it now.

    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']
    
  5. Start Filebeat

    Start or restart Filebeat for the changes to take effect.

  6. 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.

MySQL + Docker sidecar setup

Configuration
  1. Pull the Docker image

    Download the logzio/mysql-logs image:

    docker pull logzio/mysql-logs
    
  2. 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

    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.
  3. 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.