# Prepare MySQL

Set up a monitoring login and `performance_schema` on your MySQL server before installing the
collector.  These steps differ from PostgreSQL.  Then install the collector with
[Docker](/docs/connect/docker/), [Kubernetes](/docs/connect/kubernetes/), or alongside
[AWS RDS and Aurora](/docs/connect/aws-rds-aurora/).

The MySQL versions DBGorilla supports are listed under
[Supported versions](/docs/reference/supported-databases/#supported-versions).

## Database grants

Create a dedicated monitoring login with read-only access.  DBGorilla never needs write access.  The
login needs several grants.

### Required

```sql
CREATE USER 'dbg_monitor'@'%' IDENTIFIED BY 'replace-me';

GRANT SELECT ON performance_schema.* TO 'dbg_monitor'@'%';
GRANT PROCESS ON *.* TO 'dbg_monitor'@'%';             -- see other sessions in the processlist
GRANT REPLICATION CLIENT ON *.* TO 'dbg_monitor'@'%';  -- replica lag metrics

-- plus, for each database you want schema captured from:
GRANT SELECT    ON app.* TO 'dbg_monitor'@'%';
GRANT SHOW VIEW ON app.* TO 'dbg_monitor'@'%';

-- needed for statistics, which are on by default:
GRANT SELECT ON mysql.innodb_table_stats TO 'dbg_monitor'@'%';
GRANT SELECT ON mysql.innodb_index_stats TO 'dbg_monitor'@'%';
```

`SELECT` and `SHOW VIEW` on a database allow schema capture to read its tables and views.  Both
grants provide read access only.  Repeat those two lines for every database you list in `databases`.
Without `SHOW VIEW`, views the login cannot read are omitted, and DBGorilla shows a warning naming
the grant.

The two `mysql.*` grants are read-only.  They let the collector copy InnoDB's optimizer statistics,
including row counts and index sizes but never table rows, into an empty copy of your schema.  That is how a
recommendation is tested before you see it.  Without them, recommendations reach you without that
test.

`PROCESS` lets the collector see sessions other than its own.  Without it, the collector still
connects, but activity monitoring shows only the collector.

Without `REPLICATION CLIENT`, only replica lag metrics are unavailable.

No grant on `information_schema` is needed.  Every account can already read it, and MySQL refuses
the grant.

### Optional, for schema objects

Each grant adds one type of schema object to what DBGorilla sees: scheduled events, triggers, or
stored routines.  Without a grant, that type is omitted and the rest of the schema is captured
normally.  For events and routines, DBGorilla shows a warning under the database in the topology
that names the grant that adds them.  The warning clears after the grant is added and the collector
next refreshes the schema.  This requires collector 0.12.2 or later.

Repeat the `app.*` lines for every database in `databases`.

```sql
GRANT EVENT     ON app.* TO 'dbg_monitor'@'%';
GRANT TRIGGER   ON app.* TO 'dbg_monitor'@'%';
GRANT SHOW_ROUTINE ON *.* TO 'dbg_monitor'@'%';        -- MySQL 8.0.20 and later; server-wide only
```

| Grant | What DBGorilla gets | Without it | What else it allows in MySQL |
|---|---|---|---|
| `EVENT` | Scheduled event definitions | Events are left out, and the warning names `EVENT`. | Creating, changing and dropping events in that database.  MySQL has no read-only form of it. |
| `TRIGGER` | Trigger definitions | Triggers are left out.  MySQL reports nothing to warn about, so if you expect triggers and see none, check this grant first. | Creating, dropping and running triggers on that database's tables |
| `SHOW_ROUTINE` | Stored procedure and function definitions | Routines are left out.  If the login can run routines (`EXECUTE`) but not read them, the warning names `SHOW_ROUTINE`. | Nothing.  It is read-only. |

`SHOW_ROUTINE` can only be granted server-wide, on `*.*`.  On MySQL 8.0.19 and earlier it does not
exist, and the only alternative, `SELECT ON *.*`, reads every table on the server, so leave
routines out rather than grant it.

:::note[Why these grants are safe to give the collector]
With these grants the collector only reads.  DBGorilla's agents never receive the collector's
credentials and never connect to your database.  Any statement DBGorilla asks the collector to run
goes through MySQL's prepared-statement protocol, and MySQL does not accept `CREATE`, `ALTER` or
`DROP EVENT`, or `CREATE` or `DROP TRIGGER`, as a prepared statement.
:::

## performance_schema

`performance_schema` is the MySQL counterpart to PostgreSQL's `pg_stat_statements`.

`performance_schema = ON` is required.  It is on by default on RDS, Aurora and most installs.
Without it there are no statement digests and no wait attribution.

`performance_schema = ON` does not enable all of its consumers.  Three consumers provide features,
and nothing tells you when one of them is off:

| Consumer | What you lose without it |
|---|---|
| `statements_digest` | The workload catalog: query text and per-digest statistics |
| `events_statements_current` | The link from an active session to its digest, and picosecond query durations |
| `events_waits_current` | Real wait events.  Attribution falls back to a thread-state heuristic. |

Check which consumers are enabled:

```sql
SELECT NAME, ENABLED FROM performance_schema.setup_consumers;
```

:::caution[Set `events_waits_current` where it persists]

It is off by default on RDS.  Enabling it with a runtime `UPDATE` does not survive a restart or a
failover.  Set it in the parameter group on RDS and Aurora, or in `my.cnf` for self-managed.  A
runtime-only change works until the next failover, then stops with no error.
:::

### Replicas on self-managed MySQL

For self-managed replica discovery, set `report_host` in each replica's own `my.cnf`.
`SHOW REPLICAS` reports a replica's host only when the replica sets it.  Without it the row has an
empty host and that replica cannot be monitored.

Aurora has no binlog replication, so this does not apply there.  Its members come from the RDS API.

A complete MySQL collector config is in the
[MySQL example](/docs/reference/collector-configuration/#configuration-1).  For IAM login on RDS, see
[AWS RDS with IAM authentication](/docs/connect/aws-rds-aurora/#aws-rds-with-iam-authentication).