Skip to main content
Sumo Logic

Collect MariaDB Logs and Metrics for Non-Kubernetes environments

Collection of MariaDB Logs and Metrics for Non-Kubernetes environments.

Sumo Logic uses the Telegraf operator for MariaDB metric collection and the Installed Collector for collecting MariaDB logs. The diagram below illustrates the components of the MariaDB collection in a non-Kubernetes environment. Telegraf uses the MySQL Input Plugin to obtain MariaDB metrics and the Sumo Logic output plugin to send the metrics to Sumo Logic. Logs from MariaDB are collected by a Local File Source.

 

The process to set up collection for MariaDB data is done through the following steps:

  1. Configure Logs Collection
    1. Configure MariaDB to log to a local file
    2. Configure a Collector
    3. Configure a Source
  2. Configure Metrics Collection
    1. Configure a Hosted Collector
    2. Configure an HTTP Logs and Metrics Source
    3. Install Telegraf
    4. Configure and start Telegraf

Configure Logs Collection

This section provides instructions for configuring log collection for MariaDB running on a non-Kubernetes environment for the Sumo Logic App for MariaDB.

By default, MariaDB logs are stored in a log file. MariaDB also supports forwarding logs via Syslog Audit Logs.

Sumo Logic supports collecting logs both via Syslog and a local log file. Utilizing Sumo Logic Cloud Syslog will require TCP TLS Port 6514 to be open in your network. Local log files can be collected via Installed collectors or FluentD. Installed collector will require you to allow outbound traffic to Sumo Logic endpoints for collection to work. For detailed requirements for Installed collectors, see this page.

Based on your infrastructure and networking setup choose one of these methods to collect MariaDB logs and follow the instructions below to set up log collection:

  1. Configure MariaDB to log to a local file
  2. Configure a Collector
  3. Configure a Source

MariaDB logs written to a log file can be collected via the Local File Source of a Sumo Logic Installed Collector.

To configure the MariaDB log file(s), locate your local server.cnf configuration file in the database directory.

  1. Open server.cnf in a text editor.

  2. Set the following  parameters in the [mariadb] section:

[mariadb]
log_error=/var/log/mariadb/mariadb-error.log
log_output=FILE
slow_query_log=1
slow_query_log_file = /var/log/mariadb/slow_query.log
long_query_time=2

 

  1. Save the server.cnf file.

  2. Restart the MariaDB server:
    systemctl restart mariadb

Use one of the following Sumo Logic Collector options:

This section demonstrates how to configure sources for the following log types:

  • Error Logs
  • Slow Query Logs

This section demonstrates how to configure a Local File Source for MariaDB Error Logs, for use with an Installed Collector. You may configure a Remote File Source, but the configuration is more complex.

To configure a local file source for MariaDB Error Logs, do the following:

  1. On the Collection Management screen, click Add, next to the collector, then select Add Source.

  2. Select Local File as the source type.

  3. Configure the Local File Source fields as follows:

  1. Name (Required). Enter a name for the source.

  2. Description (Optional).

  3. File Path (Required). Enter the path to your mariadb-error.log. The files are typically located in /var/log/mariadb/mariadb-error.log. If you are using a customized path, check the server.cnf file for this information

  4. The collection should begin. Set this for how far back historically you want to start collecting.

  5. Source Host (Optional). Sumo Logic uses the hostname assigned by the OS unless you enter a different hostname

  6. Source Category (Recommended). DB/MariaDB/ErrorLogs.

  7. Fields. Set the following fields:

  • component = database
  • db_system = mariadb
  • db_cluster = <Your_MariaDB_Cluster_Name>. Enter Default if you do not have one.
  • environment = <Your_Environment_Name> (for example, Dev, QA, or Prod)

  1. In the Advanced section, select the following options:

  1. Timestamp Parsing Settings: Make sure the setting matches the timezone on the log files.

  2. Enable Timetamp Parsing: Select Extract timestamp information from log file entries.

  3. Time Zone: Select the option to Use time zone from log file. If none is present use: and set the timezone to UTC.

  4. Timestamp Format: Select the option to Automatically detect the format.

  5. Encoding. UTF-8 is the default, but you can choose another encoding format from the menu if your MariaDB logs are encoded differently.

  6. Enable Multiline Processing. Uncheck the box to Detect messages spanning multiple lines. Since MariaDB Error logs are single line log files, disabling this option will ensure that your messages are collected correctly.

  1. Click Save.

After a few minutes, your new Source should be propagated down to the Collector and will begin submitting your MariaDB log files to the Sumo Logic service.

This section demonstrates how to configure a Local File Source for MariaDB Slow Query Logs, for use with an Installed Collector.

To configure a local file source for MariaDB Slow Query Logs, do the following:

  1. On the Collection Management screen, click Add, next to the collector, then select Add Source.

  2. Select Local File as the source type.

  3. Configure the Local File Source fields as follows:

  1. Name (Required). Enter a name for the source.

  2. Description (Optional).

  3. File Path (Required). Enter the path to your slow_query.log. The files are typically located in /var/log/mariadb/slow_query.log. If you are using a customized path, check the server.cnf file for this information

  4. The collection should begin. Set this for how far back historically you want to start collecting.

  5. Source Host (Optional). Sumo Logic uses the hostname assigned by the OS unless you enter a different hostname

  6. Source Category (Recommended). DB/MariaDB/SlowQuery.

  7. Fields. Set the following fields:

  • component = database
  • db_system = mariadb
  • db_cluster = <Your_mariadb_Cluster_Name>. Enter Default if you do not have one.
  • environment = <Your_Environment_Name> (for example, Dev, QA, or Prod)

  1. In the Advanced section, select the following options:

  1. Timestamp Parsing Settings: Make sure the setting matches the timezone on the log files.

  2. Enable Timetamp Parsing: Select Extract timestamp information from log file entries.

  3. Time Zone: Select the option to Use time zone from log file. If none is present use: and set the timezone to UTC.

  4. Timestamp Format: Select the option to Automatically detect the format.

  5. Encoding. UTF-8 is the default, but you can choose another encoding format from the menu if your MariaDB logs are encoded differently.

  6. Enable Multiline Processing

  • Detect Messages Spanning Multiple Lines. True
  • Infer Boundaries - Detect message boundaries automatically. False
  • Boundary Regex.  ^#\sTime:\s.*
  1. Click Save.

After a few minutes, your new Source should be propagated down to the Collector and will begin submitting your MariaDB log files to the Sumo Logic service.

Configure Metrics Collection

Setup a Sumo Logic HTTP Source

To create a new Sumo Logic hosted collector, perform the steps in the Configure a Hosted Collector documentation.

On the created Hosted Collector on the Collection Management screen, select Add Source.

  1. Select HTTP Logs & Metrics.

    1. Name. (Required). Enter a name for the source.

    2. Description. (Optional).

    3. Source Category. (Recommended). Be sure to follow the Best Practices for Source Categories. A recommended Source Category may be Prod/DB/MariaDB/Metrics.

  2. Select Save.

  3. Note the URL provided once you click Save. You can retrieve it again by selecting the Show URL next to the source on the Collection Management screen.

Setup Telegraf

Use the following steps to install Telegraf.

As part of collecting metrics data from Telegraf, we will use the MySQL Input Plugin to get data from Telegraf and the Sumo Logic output plugin to send data to Sumo Logic.

Create or modify telegraf.conf and copy and paste the text below:  

[[inputs.mysql]]
  servers = ["user_TO_BE_CHANGED:password_TO_BE_CHANGED@tcp(IP_ADDRESS_MARIADB_TO_BE_CHANGED:PORT_MARIADB_TO_BE_CHANGED)/?tls=false"]
  metric_version = 2
  table_schema_databases = []
  perf_summary_events = []
  gather_table_schema                       = true
  gather_process_list                       = true
  gather_info_schema_auto_inc               = true
  gather_user_statistics = true
  gather_slave_status = true
  gather_table_io_waits = true
  gather_table_lock_waits = true
  gather_index_io_waits = true
  gather_event_waits = true
  gather_file_events_stats = true
  gather_perf_events_statements = true 
  interval_slow = "30m"
[inputs.mysql.tags]
    environment="dev_TO_BE_CHANGEME"
    component="database"
    db_system="mariadb"
    db_cluster="mariadb_on_premise_TO_BE_CHANGEME"

[[outputs.sumologic]]
  url = "<URL_from_HTTP_Logs_and_Metrics_Source>"
  data_format = "prometheus"

Enter values for fields annotated with <VALUE_TO_BE_CHANGED> to the appropriate values. Do not include the brackets (<>) in your final configuration

  • Input plugins section, which is [[inputs.mysql]]:
    • servers. The the URL of your MariaDB server. For information about additional input plugin configuration options, see the Readme for the MySQL input plugin.
  • In the tags section, which is [inputs.mysql.tags]:
    • environment. This is the deployment environment where the MariaDB cluster identified by the value of servers resides. For example; dev, prod, or QA. While this value is optional we highly recommend setting it. 
    • db_cluster. Enter a name to identify this MariaDB cluster. This cluster name will be shown in our dashboards. 
  • In the output plugins section, which is [[outputs.sumologic]]
    • URL. This is the HTTP source URL created previously. See this doc for more information on additional parameters for configuring the Sumo Logic Telegraf output plugin.

Here’s an explanation for additional values set by this Telegraf configuration.

  • data_format. “prometheus” - In the output [[outputs.sumologic]] plugins section. Metrics are sent in the Prometheus format to Sumo Logic.
  • component. “database” - In the input [[inputs.mysql]] plugins section. This value is used by Sumo Logic apps to identify application components.
  • db_system. “mariadb” - In the input plugins sections. This value identifies the database system.

See this doc for all other parameters that can be configured in the Telegraf agent globally.

At this point, Telegraf should start collecting the MariaDB metrics and forward them to the Sumo Logic HTTP Source.