Skip to main content
Sumo Logic

Collect Logs from MySQL

This procedure explains how to collect logs from MySQL and ingest them into Sumo Logic.

Log Types

The Sumo Logic App for MySQL assumes the default MySQL error log file format for error logs, and the MySQL slow query file format for slow query logs.

  • MySQL Slow Query logs
  • MySQL Error logs

For more details on the MySQL log file format, see the MySQL documentation.

Prerequisites/Requirements

  • Create an app in MySQL that generates logs.
  • Enable MySQL Error and MySQL Slow Query logging.

Configure a Collector

In Sumo Logic, configure an Installed Collector.

Configure a Source

Create two local file sources for your installed collector, one for error logs and one for slow query logs.

MySQL error logs

MySQL error logs are single line logs. Configure the source to define a boundary regex for error logs.

  1. Configure a Local File Source.
  2. Configure the source fields:
    1. Name. (Required) E.g., "MySQL Errors" (Description is optional.)
    2. File Path. (Required) E.g., /var/log/mysql/mysql_error.log
    3. Source Category. (Required) E.g., prod/mysql_error. For more information, see Best Practices.
  3. Configure the Advanced section:
    1. Enable Timestamp Parsing. True
    2. Time Zone. UTC (or system default)
    3. Timestamp Format. Auto-detect
    4. Encoding. UTF-8
    5. Enable Multiline Processing.
      1. Detect Messages Spanning Multiple Lines. True
      2. Infer Boundaries - Detect message boundaries automatically. False
      3. Boundary Regex^\d{6}\s+\d{1,2}:\d{1,2}:\d{1,2}.*
  4. Click Save.

mysql_app_error_source_550x137.png

MySQL Slow Query logs

MySQL Slow Query logs are multi-line logs, so you must edit the source to enable Detect messages spanning multiple lines and define the Boundary Regex for slow queries.

To edit the Source

  1. Configure a Local File Source.
  2. Configure the source fields:
    1. Name. (Required) "MySQL Slow Queries" (Description is optional.)
    2. File Path. (Required) E.g., /var/log/mysql/mysql-slow.log
    3. Source Category. (Required) E.g., prod/mysql_slow. For more information, see Best Practices.
  3. Configure the Advanced section:
    1. Enable Timestamp Parsing. True
    2. Time Zone. UTC (or system default)
    3. Timestamp Format. Auto-detect
    4. Encoding. UTF-8
    5. Enable Multiline Processing.
      1. Detect Messages Spanning Multiple Lines. True
      2. Infer Boundaries - Detect message boundaries automatically. False
      3. Boundary Regex^#\sTime:\s+\d{6}\s+\d{1,2}:\d{1,2}:\d{1,2}.*
  4. Click Save.

mysql_app_slow_query_source_550x140.png

Sample Log Messages

Error Log

120326 16:56:45 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_21b2_0.MYI'; try to repair it
120326 16:56:45 [ERROR] Got an error from unknown thread, storage/myisam/mi_write.c:223

Slow Query Log

/usr/local/mysql/libexec/mysqld, Version: 3.23.54-log, started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 030207 15:03:33
# User@Host: wsuser[wsuser] @ localhost.localdomain [127.0.0.1]
# Query_time: 13  Lock_time: 0  Rows_sent: 117  Rows_examined: 234
use wsdb;
SELECT l FROM un WHERE ip='209.xx.xxx.xx';

Query Samples

Error Log - Errors by log type over time

_sourceCategory=mysql_error | parse " [*] *" as ErrorLogtype, errormsg 
| fields ErrorLogtype, errormsg
| timeslice 1h
| count by _timeslice, ErrorLogtype
| transpose row _timeslice column ErrorLogType

Slow Queries - Top users firing slow queries

_sourceCategory=mysql_slow "User@Host" "Query_time"
| parse regex "(?<query_block># User@Host:[\S\s]+?SET timestamp=\d+;[\S\s]+?;)" multi
| parse regex field=query_block "# User@Host: \S+?\[(?<user>\S*?)\] @ (?<host_name>\S+)\s\[(?<ip_addr>\S*?)\]" nodrop // Pttrn1-vrtn1
| parse regex field=query_block "# User@Host: \S+?\[(?<user>\S*?)\] @\s+\[(?<ip_addr>\S*?)\]\s+Id: (?<Id>\d{1,10})" nodrop // Pttrn1-vrtn2
| parse regex field=query_block "# User@Host: \S+?\[(?<user>\S*?)\] @ (?<host_name>\S+)\s\[(?<ip_addr>\S*?)\]\s+Id: (?<Id>\d{1,10})"
| fields -query_block
| count as frequency by user | sort by frequency

Sumo Logic App

Now that you have configured MySQL logs, install the Sumo Logic App for MySQL to take advantage of the preconfigured searches and dashboards that provide insight into website visitor behavior patterns, monitors server operations, and assists in troubleshooting issues that span entire web server farms.