MariaDB - OpenTelemetry Collector

The MariaDB app is a logs based app that helps you monitor MariaDB database. Pre-configured dashboards provide insight into the health of your database clusters, replication, error logs, slow queries and failed logins.

This app is tested with the following MariaDB versions:

Non-Kubernetes: MariaDB - Version 10.7.1

MariaDB logs are sent to Sumo Logic through OpenTelemetry filelog receiver.

Fields creation in Sumo Logic for MariaDB

Following are the Fields which will be created as part of MariaDB App install if not already present.

db.cluster.name. User configured. Enter a name to identify this MariaDb cluster. This cluster name will be shown in the Sumo Logic dashboards
db.system. Has a fixed value of mariadb.
deployment.environment. User configured. This is the deployment environment where the MariaDB cluster resides. For example: dev, prod, or qa.
sumo.datasource. Has a fixed value of mariadb.

Prerequisites

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 logs written to a log file can be collected via the Filelog receiver of OpenTelemetry.

To configure the MariaDB log files, locate your local server.cnf configuration file in the database directory.
Open server.cnf in a text editor.
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

Error Logs: MariaDB always writes its error log, but the destination is configurable.
Slow Query Logs: The slow query log is disabled by default.
General Query Logs. We do not recommend enabling general_log for performance reasons. These logs are not used by the Sumo Logic MariaDB App.

Save the server.cnf file.
Restart the MariaDB server: systemctl restart mariadb

For Linux systems with ACL Support, the otelcol install process should have created the ACL grants necessary for the otelcol system user to access default log locations. You can verify the active ACL grants using the getfacl command. Install the ACL in your Linux environment, if not installed.

The required ACL may not be supported for some rare cases, for example, Linux OS Distro, which is officially not supported by Sumo Logic. In this case, you can run the following command to explicitly grant the permissions.

sudo setfacl -R -m d:u:otelcol-sumo:r-x,d:g:otelcol-sumo:r-x,u:otelcol-sumo:r-x,g:otelcol-sumo:r-x <PATH_TO_LOG_FILE>

Run the above command for all the log files in the directory that need to be ingested, which are not residing in the default location.

If Linux ACL Support is not available, traditional Unix-styled user and group permission must be modified. It should be sufficient to add the otelcol system user to the specific group that has access to the log files.

For Windows systems, log files which are collected should be accessible by the SYSTEM group. Use the following set of PowerShell commands if the SYSTEM group does not have access.

$NewAcl = Get-Acl -Path "<PATH_TO_LOG_FILE>"
# Set properties
$identity = "NT AUTHORITY\SYSTEM"
$fileSystemRights = "ReadAndExecute"
$type = "Allow"
# Create new rule
$fileSystemAccessRuleArgumentList = $identity, $fileSystemRights, $type
$fileSystemAccessRule = New-Object -TypeName System.Security.AccessControl.FileSystemAccessRule -ArgumentList $fileSystemAccessRuleArgumentList
# Apply new rule
$NewAcl.SetAccessRule($fileSystemAccessRule)
Set-Acl -Path "<PATH_TO_LOG_FILE>" -AclObject $NewAcl

Collection configuration and app installation

As part of data collection setup and app installation, you can select the App from App Catalog and click on Install App. Follow the steps below.

Step 1: Set up Collector

note

If you want to use an existing OpenTelemetry Collector, you can skip this step by selecting the Use an existing Collector option.

To create a new Collector:

Select the Add a new Collector option.
Select the platform where you want to install the Sumo Logic OpenTelemetry Collector.

This will generate a command that you can execute in the machine environment you need to monitor. Once executed, it will install the Sumo Logic OpenTelemetry Collector.

Step 2: Configure integration

In this step, you will configure the yaml required for MariaDB Collection. Path of the log file configured to capture MariaDB logs is needed to be given here.

The files are typically located in /var/log/mariadb/. If you're using a customized path, check the respective conf file (default location: /etc/my.cnf) for this information. You can add any custom fields which you want to tag along with the data ingested in Sumo. Click on the Download YAML File button to get the yaml file.

Step 3: Send logs to Sumo Logic

Once you have the yaml file downloaded in step 2, you can copy the same to the machine which needs to be monitored. Follow the below steps based on the platform of the machine:

Copy the yaml file to /etc/otelcol-sumo/conf.d/ folder in the MariaDb instance which needs to be monitored.
restart the collector using:

sudo systemctl restart otelcol-sumo

Copy the yaml file to C:\ProgramData\Sumo Logic\OpenTelemetry Collector\config\conf.d folder in the machine which needs to be monitored.
Restart the collector using:

Restart-Service -Name OtelcolSumo

Copy the yaml file to /etc/otelcol-sumo/conf.d/ folder in the MariaDb instance which needs to be monitored.
Restart the otelcol-sumo process using:

  otelcol-sumo --config /etc/otelcol-sumo/sumologic.yaml --config "glob:/etc/otelcol-sumo/conf.d/*.yaml"

Copy the yaml file into your Chef cookbook files directory files/<downloaded_yaml_file>.
Use a Chef file resource in a recipe to manage it. cookbook_file '/etc/otelcol-sumo/conf.d/<downloaded_yaml_file>' do mode 0644 notifies :restart, 'service[otelcol-sumo]', :delayed end
Add the recipe to your collector setup to start collecting the data. Every team typically has their established way of applying the Chef recipe. The resulting Chef recipe should look something like:
```
cookbook_file '/etc/otelcol-sumo/conf.d/<downloaded_yaml_file>' do
    mode 0644
    notifies :restart, 'service[otelcol-sumo]', :delayed
end
```

Place the file into your Ansible playbook files directory.

Run the Ansible playbook.

ansible-playbook -i inventory install_sumologic_otel_collector.yaml
-e '{"installation_token": "<YOUR_TOKEN>", "collector_tags": {<YOUR_TAGS>}, "src_config_path": "files/conf.d"}'

Place the file into your Puppet module files directory modules/install_otel_collector/files/<downloaded_yaml>.

Use a Puppet file resource to manage it.

file { '/etc/otelcol-sumo/conf.d/<downloaded_yaml_file>':
    ensure => present,
    source => 'puppet:///modules/install_otel_collector/<downloaded_yaml_file>',
    mode => '0644',
    notify => Service[otelcol-sumo],
  }

Apply the Puppet manifest. Every team typically has their established way of applying the Puppet manifest. The resulting Puppet manifest should look something like:

node 'default' {
    class { 'install_otel_collector'
      installation_token => '<YOUR_TOKEN>',
      collector_tags => { <YOUR_TAGS> },
    }

    service { 'otelcol-sumo':
      provider => 'systemd',
      ensure => running,
      enable => true,
      require => Class['install_otel_collector'],
    }

    file { '/etc/otelcol-sumo/conf.d/<downloaded_yaml_file>':
      ensure => present,
      source => 'puppet:///modules/install_otel_collector/<downloaded_yaml_file>',
      mode => '0644',
      notify => Service[otelcol-sumo],
    }
  }

After successfully executing the above command, Sumo Logic will start receiving data from your host machine.

Click Next. This will install the app (dashboards and monitors) to your Sumo Logic Org.

Dashboard panels will start to fill automatically. It's important to note that each panel fills with data matching the time range query and received since the panel was created. Results won't immediately be available, but within 20 minutes, you'll see full graphs and maps.

Sample logs

2023-01-09*04:51:04 0 [ERROR] mysqld: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.

Sample queries

Following query is from the "Errors" panel of the overview dashboard in Mariadb app:

 %"sumo.datasource"=mariadb %"deployment.environment"=* %"db.cluster.name"=* "[ERROR]"
| json "log" nodrop
| if (isEmpty(log), _raw, log) as mariadb_log_message
| parse regex field=mariadb_log_message "\[(?<ErrorLogtype>[^\]]*)][\:]*\s(?<ErrorMsg>.*)"
| where ErrorLogtype="ERROR" | count as error_count

Viewing MariaDB dashboards

Overview

The MariaDB - Overview dashboard gives you an at-a-glance view of the state of your database clusters by monitoring key cluster information such as errors, failed logins, errors, queries executed, slow queries, lock waits, uptime, and more.

Use this dashboard to:

Quickly identify the number of errors, excessive slow queries, replication errors and failed login attempts.
Glance through the Top 10 errors and warning along with the geo location of the failed attempts of login.

Error Logs

The MariaDB - Error Logs dashboard provides insight into database error logs by specifically monitoring database shutdown/start events, errors over time, errors, warnings, and crash recovery attempts.

Use this dashboard to:

Quickly identify errors and patterns in logs for troubleshooting.
Monitor trends in the error log and identify outliers.
Ensure that server start, server stop, and crash recovery events are in line with expectations.
Dashboard filters allow you to narrow a search for the database clusters.

Failed Logins

The MariaDB - Failed Logins dashboard provides insights into all failed login attempts by location, users and hosts.

Use this dashboard to:

Monitor all failed login attempts and identify any unusual or suspicious activity.

Replication

The MariaDB - Replication dashboard provides insights into the state of database replication.

Use this dashboard to:

Quickly determine reasons for replication failures.
Monitor replication status trends.

Slow Queries

The MariaDB - Slow Queries dashboard provides insights into all slow queries executed on the database.

Slow queries are queries that take 10 seconds or more to execute (default value is 10 seconds as per MariaDB configuration which can be altered) and excessive slow queries are those that take 15 seconds or more to execute.

Use this dashboard to:

Identify all slow queries.
Quickly determine which queries have been identified as slow or excessive slow queries.
Monitor users and hosts running slow queries.
Determine which SQL commands are slower than others.
Examine slow query trends to determine if there are periodic performance bottlenecks in your database clusters.

Create monitors for MariaDB app

From your App Catalog:

From the Sumo Logic navigation, select App Catalog.
In the Search Apps field, search for and then select your app.
Make sure the app is installed.
Navigate to What's Included tab and scroll down to the Monitors section.
Click Create next to the pre-configured monitors. In the create monitors window, adjust the trigger conditions and notifications settings based on your requirements.
Scroll down to Monitor Details.
Under Location click on New Folder.
note
By default, monitor will be saved in the root folder. So to make the maintenance easier, create a new folder in the location of your choice.
Enter Folder Name. Folder Description is optional.
tip
Using app version in the folder name will be helpful to determine the versioning for future updates.
Click Create. Once the folder is created, click on Save.

MariaDB alerts

Name	Description	Alert Condition	Recover Condition
`MariaDB - Critical Errors`	This alert is triggered when there are critical database errors.	Count `>` 10	Count `<=` 10
`MariaDB - Excessive Slow Query Detected`	This alert is triggered when the average time to execute a query is more than 15 seconds for a 5 minute time interval.	Count `>=` 1	Count `<` 1
`MariaDB - Failed Login Attempts`	This alert is triggered when there are excessive failed login attempts in a short period.	Count `>=` 1	Count `<` 1
`MariaDB - Instance down`	This alert is triggered when the MariaDB instance is down.	Count `>=` 1	Count `<` 1
`MariaDB - Replication Failure`	This alert is triggered when there are replication failures.	Count `>=` 1	Count `<` 1

Fields creation in Sumo Logic for MariaDB​

Prerequisites​

Collection configuration and app installation​

Step 1: Set up Collector​

Step 2: Configure integration​

Step 3: Send logs to Sumo Logic​

Sample logs​

Sample queries​

Viewing MariaDB dashboards​

Overview​

Error Logs​

Failed Logins​

Replication​

Slow Queries​

Create monitors for MariaDB app​

MariaDB alerts​

Fields creation in Sumo Logic for MariaDB

Prerequisites

Collection configuration and app installation

Step 1: Set up Collector

Step 2: Configure integration

Step 3: Send logs to Sumo Logic

Sample logs

Sample queries

Viewing MariaDB dashboards

Overview

Error Logs

Failed Logins

Replication

Slow Queries

Create monitors for MariaDB app

MariaDB alerts