Database Log Aggregation

In this lesson, you will learn how to unify disparate MySQL and MariaDB logs into a single, searchable environment to achieve cross-system observability.

The Problem with Local Logs

When you manage a fleet of database servers, logs like the error log, slow query log, and general query log live locally on each filesystem. If you are troubleshooting a performance degradation that spans multiple application nodes and database clusters, manually ssh-ing into five different servers to tail files is unsustainable. You lose the ability to perform time-series analysis or correlate a specific slow query with a spike in system load across the infrastructure.

Centralized Aggregation Architecture

Log aggregation solves this by decoupling the log generation from the log storage. The goal is to move logs from your database nodes to a centralized "Data Lake" or observability platform (such as the ELK stack—Elasticsearch, Logstash, Kibana—or Grafana Loki).

The standard workflow involves three steps:

1. Shipping: A lightweight agent (like Filebeat or Fluentd) runs on your database server. It continuously watches your slow_query_log file.
2. Parsing: The agent treats the log file as a stream, breaking unstructured text into structured fields (e.g., query_time, lock_time, rows_examined, and the sql_text).
3. Indexing: The parsed data is sent to a central server where it is indexed, allowing you to run complex queries across logs from multiple servers simultaneously.

Example: Shipping the Slow Query Log

If you configure your MariaDB slow_query_log to output to a file, your aggregator configuration would look roughly like this:

# Simplified agent configuration (Filebeat)
filebeat.inputs:
- type: log
  enabled: true
  paths:
    - /var/log/mysql/mysql-slow.log
  fields:
    environment: production
    cluster_id: shard_01

Once this data is centralized, you can write queries to answer questions like: "Which specific query pattern is appearing most frequently across all production shards in the last hour?" instead of searching each instance one by one. This visibility is essential when diagnosing issues identified during your Capacity Planning Metrics or Monitoring Long-Running Transactions.

Key Takeaway: Centralizing your database logs transforms isolated text files into a searchable, actionable intelligence stream, enabling visibility across complex, distributed database architectures.