> ## Documentation Index
> Fetch the complete documentation index at: https://opensre.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MariaDB

> Connect MariaDB so OpenSRE can diagnose database issues during investigations

OpenSRE uses MariaDB diagnostics to investigate database-related alerts — checking server health, finding slow queries, monitoring replication, and analyzing active threads and InnoDB engine state.

## Prerequisites

* MariaDB 10.5+ (10.11 LTS or 11.x recommended)
* Network access from the OpenSRE environment to your MariaDB instance
* A database user with at least `SELECT` + `PROCESS` privileges (and `SELECT` on `performance_schema` for slow-query insights)

## Setup

### Option 1: Interactive CLI

```bash theme={null}
opensre integrations setup mariadb
```

You will be prompted for host, port, database, username, password, and whether to enable SSL.

### Option 2: Environment variables

Add to your `.env`:

```bash theme={null}
MARIADB_HOST=db.example.com
MARIADB_PORT=3306
MARIADB_DATABASE=production
MARIADB_USERNAME=opensre_ro
MARIADB_PASSWORD=...
MARIADB_SSL=true
```

| Variable           | Default   | Description                                                                               |
| ------------------ | --------- | ----------------------------------------------------------------------------------------- |
| `MARIADB_HOST`     | —         | **Required.** MariaDB server hostname or IP                                               |
| `MARIADB_PORT`     | `3306`    | MariaDB server port                                                                       |
| `MARIADB_DATABASE` | —         | **Required.** Target database for slow-query analysis                                     |
| `MARIADB_USERNAME` | —         | **Required.** Database user                                                               |
| `MARIADB_PASSWORD` | *(empty)* | Database password; required unless the user is configured for passwordless authentication |
| `MARIADB_SSL`      | `true`    | Use TLS with certificate verification                                                     |

### Option 3: Persistent store

Credentials are automatically persisted to `~/.opensre/integrations.json` with `0o600` permissions:

```json theme={null}
{
  "version": 1,
  "integrations": [
    {
      "id": "mariadb-prod",
      "service": "mariadb",
      "status": "active",
      "credentials": {
        "host": "db.example.com",
        "port": 3306,
        "database": "production",
        "username": "opensre_ro",
        "password": "...",
        "ssl": true
      }
    }
  ]
}
```

## Recommended user setup

Create a dedicated read-only user for OpenSRE so it cannot modify data:

```sql theme={null}
CREATE USER 'opensre_ro'@'%' IDENTIFIED BY 'strong-password';
GRANT SELECT, PROCESS, REPLICATION CLIENT ON *.* TO 'opensre_ro'@'%';
GRANT SELECT ON performance_schema.* TO 'opensre_ro'@'%';
FLUSH PRIVILEGES;
```

The `PROCESS` privilege lets OpenSRE read `information_schema.PROCESSLIST`. `REPLICATION CLIENT` enables `SHOW ALL SLAVES STATUS` / `SHOW SLAVE STATUS`. `SELECT` on `performance_schema` is only needed if you want slow-query insights.

## TLS configuration

SSL is enabled by default and uses the system CA bundle to verify the server certificate. Set `MARIADB_SSL=false` only in trusted local networks (development).

## Investigation tools

When OpenSRE investigates a MariaDB-related alert, five diagnostic tools are available:

### Process list

Retrieves active threads from `information_schema.PROCESSLIST`, excluding sleeping connections. Results are sorted by duration so long-running queries appear first.

### Global status

Returns a curated set of key metrics from `SHOW GLOBAL STATUS` — thread counts, connection totals, slow query count, InnoDB buffer pool statistics, row lock waits, and uptime.

### InnoDB status

Runs `SHOW ENGINE INNODB STATUS` and returns the engine status text, truncated to 4000 characters with a truncation marker appended when shortening occurs. Useful for investigating deadlocks, buffer pool pressure, and I/O patterns.

### Slow queries

Reads `performance_schema.events_statements_summary_by_digest` to list statements by average execution time. Requires `performance_schema` to be enabled.

<Info>
  If `performance_schema` is disabled, the tool returns an informative note instead of failing. Enable it in `my.cnf` with `performance_schema=ON`.
</Info>

### Replication status

Runs `SHOW ALL REPLICAS STATUS` (MariaDB multi-source replication; alias: `SHOW ALL SLAVES STATUS` on older builds) with a fallback to `SHOW REPLICA STATUS`. Returns all configured replication channels, each with I/O thread state, SQL thread state, seconds behind primary, last error, and log positions.

## Verify

```bash theme={null}
opensre integrations verify mariadb
```

Expected output:

```
SERVICE   SOURCE       STATUS    DETAIL
mariadb   local env    passed    Connected to MariaDB 11.8.6-MariaDB; target database: production.
```

## Troubleshooting

| Symptom                                          | Fix                                                                                                                                   |
| ------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------- |
| **Connection refused**                           | Verify host/port, check firewall rules, and confirm MariaDB is listening on the network interface (`bind-address` in `my.cnf`).       |
| **Access denied for user**                       | Confirm the username/password and that the user is granted access from the OpenSRE host (`'opensre_ro'@'%'` or a specific IP).        |
| **SSL: CERTIFICATE\_VERIFY\_FAILED**             | The server certificate is not trusted by the system CA bundle. Install the correct CA or set `MARIADB_SSL=false` in trusted networks. |
| **performance\_schema is disabled**              | Slow-query tool returns an empty list with a note. Enable in `my.cnf`: `performance_schema=ON`.                                       |
| **SELECT command denied on performance\_schema** | Grant `SELECT` on `performance_schema.*` to the user.                                                                                 |
| **This server is not configured as a replica**   | Expected on standalone instances — replication tool returns an empty channel list, other tools still work.                            |

## Security best practices

* Use a **dedicated read-only** user — never `root` or an admin account.
* Always enable **TLS** in production (`MARIADB_SSL=true`, which is the default).
* Keep passwords out of source control — use `.env` or the persistent store.
* Rotate credentials periodically and scope them to specific hosts where possible.