Ship MySQL logs

• Filebeat
• Docker sidecar

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.