Skip to content
Last9
Book demo

MariaDB

Monitor MariaDB database performance and metrics using mysqld_exporter and OpenTelemetry with Last9

Use OpenTelemetry to instrument your MariaDB and send telemetry data to Last9.

Prerequisites

Before setting up MariaDB monitoring, ensure you have:

  • MySQL >= 5.6 or MariaDB >= 10.3
  • Administrative access to your MariaDB server
  • OpenTelemetry Collector installation access
  • Last9 account with integration credentials

  1. Install mysqld_exporter

    Create a system user and install the mysqld_exporter:

    # Create system user
    sudo useradd --no-create-home --shell /bin/false mysqld_exporter
    

    # Download and install
    cd /tmp
    curl -s https://api.github.com/repos/prometheus/mysqld_exporter/releases/latest \
      | grep browser_download_url \
      | grep linux-amd64 \
      | cut -d '"' -f 4 \
      | wget -qi -
    

    tar xvf mysqld_exporter*.tar.gz
    sudo mv mysqld_exporter-*.linux-amd64/mysqld_exporter /usr/local/bin/
    sudo chown mysqld_exporter:mysqld_exporter /usr/local/bin/mysqld_exporter

  2. Configure mysqld_exporter

    Create configuration file with database credentials:

    sudo tee /etc/.mysqld_exporter.cnf << EOF
    [client]
    user=exporter
    password=strong_password
    EOF
    

    sudo chown mysqld_exporter:mysqld_exporter /etc/.mysqld_exporter.cnf
    sudo chmod 600 /etc/.mysqld_exporter.cnf

  3. Create systemd Service

    Create a systemd service for the mysqld_exporter:

    sudo tee /etc/systemd/system/mysqld_exporter.service << EOF
    [Unit]
    Description=MySQLd Exporter
    Wants=network-online.target
    After=network-online.target
    

    [Service]
    User=mysqld_exporter
    Group=mysqld_exporter
    Type=simple
    ExecStart=/usr/local/bin/mysqld_exporter \
      --config.my-cnf=/etc/.mysqld_exporter.cnf \
      --collect.global_status \
      --collect.global_variables \
      --collect.info_schema.innodb_metrics \
      --collect.info_schema.processlist \
      --collect.info_schema.tables \
      --collect.info_schema.tables.databases='*' \
      --collect.perf_schema.eventsstatements \
      --collect.perf_schema.file_events \
      --collect.perf_schema.indexiowaits \
      --collect.perf_schema.tableiowaits \
      --collect.perf_schema.tablelocks \
      --collect.slave_status \
      --web.listen-address=:9104
    

    [Install]
    WantedBy=multi-user.target
    EOF
    

    sudo systemctl daemon-reload
    sudo systemctl start mysqld_exporter
    sudo systemctl enable mysqld_exporter

    This step ensures the mysqld_exporter is running and emitting metrics from MariaDB on port 9104.

  4. Install OpenTelemetry Collector

    Download and install the OpenTelemetry Collector:

    sudo apt-get update
    sudo apt-get -y install wget systemctl
    wget https://github.com/open-telemetry/opentelemetry-collector-releases/releases/download/v0.110.0/otelcol-contrib_0.110.0_linux_amd64.deb
    sudo dpkg -i otelcol-contrib_0.110.0_linux_amd64.deb

  5. Configure OpenTelemetry Collector

    Use the following configuration for Otel Collector:

    receivers:
      # Detailed configuration options can be found at https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/receiver/filelogreceiver
      filelog:
        # File path pattern to read logs from. Update this to the destination from where you want to read logs.
        include: [/var/log/mysql/*.log]
        include_file_path: true
        retry_on_failure:
          enabled: true
      hostmetrics:
        collection_interval: 60s
        scrapers:
          cpu:
            metrics:
              system.cpu.logical.count:
                enabled: true
          memory:
            metrics:
              system.memory.utilization:
                enabled: true
              system.memory.limit:
                enabled: true
          load:
          disk:
          filesystem:
            metrics:
              system.filesystem.utilization:
                enabled: true
          network:
          paging:
      prometheus:
        config:
          scrape_configs:
            - job_name: "mariadb"
              scrape_interval: 60s
              static_configs:
                - targets: ["localhost:9104"]
    

    processors:
      batch:
        timeout: 5s
        send_batch_size: 10000
        send_batch_max_size: 10000
      resourcedetection/system:
        detectors: ["system"]
        system:
          hostname_sources: ["os"]
      transform/add_timestamp:
        flatten_data: true
        log_statements:
          - context: log
            statements:
              - set(observed_time, Now())
              - set(time_unix_nano, observed_time_unix_nano) where time_unix_nano == 0
      transform/add_service:
        flatten_data: true
        log_statements:
          - context: log
            statements:
              - set(resource.attributes["service.name"], "mariadb")
      transform/hostmetrics:
        metric_statements:
          - context: datapoint
            statements:
              - set(attributes["host.name"], resource.attributes["host.name"])
              - set(attributes["cloud.account.id"], resource.attributes["cloud.account.id"])
              - set(attributes["cloud.availability_zone"], resource.attributes["cloud.availability_zone"])
              - set(attributes["cloud.platform"], resource.attributes["cloud.platform"])
              - set(attributes["cloud.provider"], resource.attributes["cloud.provider"])
              - set(attributes["cloud.region"], resource.attributes["cloud.region"])
              - set(attributes["host.type"], resource.attributes["host.type"])
              - set(attributes["host.image.id"], resource.attributes["host.image.id"])
    

    exporters:
      otlp/last9:
        endpoint: "{{ .Logs.WriteURL }}"
        headers:
          "Authorization": "{{ .Logs.AuthValue }}"
      debug:
        verbosity: detailed
    

    service:
      pipelines:
        logs:
          receivers: [filelog]
          processors:
            [
              batch,
              resourcedetection/system,
              transform/add_timestamp,
              transform/add_service,
            ]
          exporters: [otlp/last9]
        metrics:
          receivers: [prometheus, hostmetrics]
          processors: [batch, resourcedetection/system, transform/hostmetrics]
          exporters: [otlp/last9]

  6. Start OpenTelemetry Collector

    Start the collector with the configuration:

    otelcol-contrib --config /etc/otelcol-contrib/config.yaml --feature-gates transform.flatten.logs

Verification

To verify the setup is working:

  1. Check that mysqld_exporter is running:

    sudo systemctl status mysqld_exporter

  2. Test the exporter endpoint:

    curl http://localhost:9104/metrics

  3. Verify the collector is processing data and log into your Last9 account to confirm MariaDB metrics are being received

Need Help?

If you encounter any issues or have questions: