Self-managed MySQL and MariaDB on Amazon EC2

For a database you run yourself on an EC2 instance: MySQL 5.7, 8.0 or 8.4, or MariaDB 10.11. Read Database monitoring first for pools, statuses, the privacy mode and the alarms.

Two ways in

Provider entry	Connection	Collected by
On a monitored server itself	Unix socket or loopback TCP (127.0.0.1)	That server only: it is the one collector
On another server in the same VPC	Private network (TCP)	Any servers you choose, with failover

At step 2 you also say which server runs the database. On a socket or loopback that server is required — it is the only one that can reach the database, so it is also the whole pool, and a pool of one never fails over. Over the private network it is optional: the database is then shown on that server’s page, and step 3 still chooses which servers collect it. Its slow and error logs are read only on that server.

Point the collectors at one database server, never at a pooler or a load balancer in front of several: their counters would mix (see Pooled endpoints).

Network checklist

• Socket or loopback: nothing to open.
• Private network: allow the database’s port (usually 3306) from the collectors — in the database server’s security group, or its host firewall.
• Nothing is opened to WatchTower15: the agent connects out.

TLS and the certificate authority

Connection	TLS
Socket	Not used: the connection never leaves the server
Loopback	Off by default; you may verify a certificate
Private network	Required. Verify the certificate (the default), the certificate and its name, or encrypt without verifying

Over TCP, a connection without TLS is refused before the agent dials. The certificate authority is the file named ssl-ca in the credentials file, or the server’s system store.

Some MySQL 5.7 builds offer only RSA key exchange. The agent accepts it for the 5.7 family only, and Verify names the suite with no forward secrecy. Upgrade, or rebuild against a current OpenSSL, when you can.

The grant script

Step 4 generates it for your version and connection. Over TCP it looks like this (MySQL 8.4, private network):

CREATE USER 'wt15_monitor'@'<collectors-subnet>/<netmask>' IDENTIFIED WITH caching_sha2_password BY '<random 32+ chars>'
  REQUIRE SSL WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'wt15_monitor'@'<collectors-subnet>/<netmask>';   -- tier A
-- tiers B–D: performance_schema digests, InnoDB statistics, REFERENCES, SHOW VIEW (and SHOW_ROUTINE on MySQL 8)

Replace the subnet with your collectors’ subnet (for example 10.0.1.0/255.255.255.0), never one server’s address. On loopback the host part is 127.0.0.1. View definitions stay out of scope. See The grant script.

Tier B — SELECT on performance_schema.events_statements_summary_by_digest — gives query analytics its statements. It also needs performance_schema, which MySQL turns on by default and MariaDB does not: there it takes performance_schema = ON under [mysqld] and a restart, your choice to make. WatchTower15 never changes it. Everything else on this page works without it. See Turning on performance_schema. Its SELECT on performance_schema.table_io_waits_summary_by_index_usage gives the advisor its unused indexes, with performance_schema on too. The script’s other performance_schema grants are not read yet: grant them now, so you grant once.

Tier C — SELECT on mysql.innodb_table_stats and mysql.innodb_index_stats — gives the Storage & growth tab its row estimates and the sizes of tables kept in a shared tablespace, and the advisor the size of each unused index. Without it, sizes still come from the tablespaces, which PROCESS (tier A) reads. PROCESS also gives the advisor’s checks on tables and indexes InnoDB’s catalogue.

Tier D — REFERENCES, SHOW VIEW, and SHOW_ROUTINE on MySQL 8 — gives schema-change history its full layer: exact column types, character sets and collations, index prefixes, views and non-InnoDB tables. With PROCESS alone it covers InnoDB tables, their columns with a coarse type, and their indexes. MySQL 8.0 before 8.0.20 has no SHOW_ROUTINE and refuses it: leave it out of the tier D line there.

Routines are tracked on MySQL 8.0.20 and later with SHOW_ROUTINE, which tier D includes, and on MariaDB 11.3 and later with SHOW CREATE ROUTINE, which the script does not grant. On MariaDB 11.3 or later, grant it to the same user as the other grants, for example:

GRANT SHOW CREATE ROUTINE ON *.* TO 'wt15_monitor'@'<collectors-subnet>/<netmask>';

Without the privilege, routines are not tracked. Granting it later records a new baseline silently: the routines that exist then are not listed as created. Routines are not tracked on MySQL 5.7, MySQL 8.0 before 8.0.20 or MariaDB before 11.3. See Routines.

The optional account grant

The script ends with one optional grant: SELECT on six columns of mysql.user, for the advisor’s account check — anonymous accounts, and accounts open to any host. Run it to the same user as the other grants — over a socket with socket authentication, 'wt15-server-agent'@'localhost' — only if you want that check.

MySQL 5.7, 8.0 and 8.4:

-- Optional: account checks (the advisor). Six columns of mysql.user, never a password hash.
-- Locked accounts are left out (MySQL 8 roles are locked accounts).
GRANT SELECT (User, Host, plugin, account_locked, password_expired, ssl_type) ON mysql.user TO 'wt15_monitor'@'<collectors-subnet>/<netmask>';   -- optional: account checks

MariaDB 10.11:

-- Optional: account checks (the advisor). Six columns of mysql.user, never a password hash.
-- MariaDB's mysql.user has no account_locked, so locking is not visible; roles (is_role) are left out.
GRANT SELECT (User, Host, plugin, password_expired, ssl_type, is_role) ON mysql.user TO 'wt15_monitor'@'<collectors-subnet>/<netmask>';   -- optional: account checks

Never a password hash: neither grant names authentication_string or Password, and the server refuses to read a column the grant does not name. Without it everything else works, and the account check reads not evaluated. MariaDB keeps an account’s lock beside its password hash, which is never read, so a locked any-host account is still reported there: dismiss it with a reason. See The optional account grant.

Socket authentication

Over a socket, the collector can log in as the agent’s own OS user, wt15-server-agent, with no password anywhere:

-- MySQL: auth_socket is not loaded by default in MySQL Community. Load it first, on every replica too:
--   INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';
CREATE USER 'wt15-server-agent'@'localhost' IDENTIFIED WITH auth_socket WITH MAX_USER_CONNECTIONS 3;

-- MariaDB 10.4 and later: unix_socket is on by default.
CREATE USER 'wt15-server-agent'@'localhost' IDENTIFIED VIA unix_socket WITH MAX_USER_CONNECTIONS 3;

…followed by the same tier grants. Two things the socket needs:

• Write permission on the socket. Connecting to a socket needs write access to it. Add the agent’s user to the socket’s group (usually mysql) and restart the agent.
• A socket outside /tmp. The agent’s service runs with PrivateTmp=yes, so a socket under /tmp or /var/tmp is invisible to it. Use the server’s real path (SELECT @@socket), which defaults to /var/run/mysqld/mysqld.sock.

Where the collectors find the credentials

WatchTower15 stores where the credentials are, never a password or a key.

Source	Survives a server’s replacement	Offered on
File on the host	When your provisioning writes it	Every connection
Socket authentication	Not applicable (it goes with its host)	Socket
AWS Secrets Manager	Yes	Private network
AWS SSM Parameter Store	Yes	Private network

The credentials file

One file per database, on each collector: /etc/wt15-server-agent/databases.d/<database-uuid>.cnf, mode 0600, owned by the agent’s user or root. It holds user, password, ssl-ca and redaction-key:

[client]
user = wt15_monitor
password = <the monitoring user's password>
ssl-ca = /etc/ssl/certs/your-database-ca.pem
redaction-key = <the database's redaction key>

On one long-lived server, write it with the agent’s CLI, as root:

sudo /var/lib/wt15-server-agent/wt15-server-agent db add <database-uuid>

It asks for the user, the password (not shown), the CA file and the redaction key (it generates one if you leave it empty), writes the file 0600 for the agent’s user, then runs db test. The setup’s Credentials step shows this command with the database’s uuid filled in; the uuid is also in the address of the database’s page.

On servers that get replaced, write it from your provisioning, after the agent’s install — for example in cloud-init:

#cloud-config
runcmd:
  - <the WatchTower15 agent install command for your organisation>
  - install -d -m 0755 /etc/wt15-server-agent/databases.d
  - printf '[client]\nuser = wt15_monitor\npassword = %s\nredaction-key = %s\n' "${WT15_DB_PASSWORD}" "${WT15_DB_REDACTION_KEY}" | install -o wt15-server-agent -m 0600 /dev/stdin /etc/wt15-server-agent/databases.d/<database-uuid>.cnf

A file on one server

Picking File for a pool of one server shows a warning: if that server is in an Auto Scaling group, its replacement starts without the file and the database goes Awaiting collector. Write the file from your provisioning, or use Secrets Manager or SSM.

AWS Secrets Manager and SSM Parameter Store

Over the private network, the collectors can read the credentials at connect time with their own instance role:

• Secrets Manager: the secret’s ARN, and the names of its username, password and redaction_key fields. Policy for the collectors’ instance role (never for WatchTower15):

  {
    "Version": "2012-10-17",
    "Statement": [{
      "Effect": "Allow",
      "Action": "secretsmanager:GetSecretValue",
      "Resource": "<the secret ARN>"
    }]
  }

  With a customer-managed KMS key, add kms:Decrypt on that key.

• SSM Parameter Store: the user, and the names of two SecureString parameters — the password and the redaction key. The role needs ssm:GetParameter on both, and kms:Decrypt on their key.

The CLI

The agent’s binary is /var/lib/wt15-server-agent/wt15-server-agent. Use the full path: it works everywhere, including under sudo.

Command	Does
db add <database-uuid>	Writes the credentials file (as root)
db test <database-uuid>	Connects as the collector would: reachability, TLS and its suite, the grant tiers, over-privilege, what is available, the redaction key’s fingerprint. Over a socket it explains a permission or PrivateTmp problem.
db list	The databases this server was given, its role for each, and whether a credentials file is present. It never prints a secret.
db reveal <database-uuid> <key>	The schema, table, column, index, view or routine a key on the Storage & growth or Schema changes tab names, the normalised statement a key on the Queries tab names (with tier B), or the table, index or account a key on the Advisor tab names (an account with the optional account grant), found with the redaction key on this server. Column, index, view and routine keys come from the schema state the agent keeps, so run it on the database’s active collector. It sends nothing to WatchTower15. See Revealing a key on your host and, for the Schema changes, Queries and Advisor tabs, Revealing a key, Revealing a statement and the Advisor’s.

Run as root, db test tests a socket login as root, not as the agent’s user.

The redaction key

Generate one key per database (openssl rand -hex 32) and give it to every collector: in the credentials file as redaction-key, in the secret’s redaction_key field, or in its own SSM parameter. With socket authentication, put it in a credentials file holding only redaction-key. A collector with another key shows Key mismatch; see Privacy mode and the redaction key for Accept new key.

Pools and replacement

Over the private network, choose specific servers in order of preference, or a tag rule so a replacement server joins the pool on its own. Pair a tag rule with Secrets Manager, SSM, or a file written by your provisioning. See Collector pools.

Storage and growth

The ceiling for days to full is the volume holding the database’s datadir on the server it runs on, the one you name at step 2. Without that server there is no ceiling. The Storage & growth tab names the volume it matched, so check it:

• MySQL in a container: datadir is the path inside the container, so the match may pick the wrong host volume, often /.
• A datadir reached through a symlink: the match picks the link’s volume, not the one that holds the files.
• InnoDB files outside datadir (innodb_data_home_dir, innodb_undo_directory, a table’s DATA DIRECTORY) are counted against the datadir volume.

Only the database’s own growth is projected; other files on the volume are not, and the volume’s own alarms watch them.

MySQL 5.7. Reading the tablespaces can wait for as long as tables are being created, altered or dropped, so that read and the files read are skipped while DDL runs (temporary tables included) and run on a short connection of their own. Under a DDL stream that never stops, a read is forced at most once a day, and never more than one read waits on the server at a time: the monitoring user’s three connections leave room for it beside the collector’s own connection and the standby’s check. See MySQL 5.7 and Aurora MySQL 2.x.

The advisor

Every one of the advisor’s 21 checks can run on a database you manage yourself (see Where each check applies), with these exceptions:

• The TLS certificate is not checked over a Unix socket, or on loopback without TLS.
• Row versions are checked on MySQL 8.0.29 and later and 8.4 only.
• MariaDB turns performance_schema off by default, which unused indexes and, through query analytics, the checks on statements need; and its account locking is not visible.
• MySQL 5.7 and MariaDB. While tables are being created, altered or dropped, a facts pass leaves InnoDB’s catalogue out and is retried every 30 minutes; the catalogue is read anyway at the latest 24 hours after the DDL began. See Skipped passes.
• Partitioned tables on MySQL 8.0 and 8.4 are not seen by the checks for a missing primary key and for redundant and unused indexes. See Schema checks.

Slow and error logs

The database’s Logs tab shows its slow query log and its error log, read by the agent on the server the database runs on, the one you name at step 2, from the files the database names (slow_query_log_file, log_error), or from its systemd unit’s journal when log_error is stderr. See Slow and error logs.

• That server must be one of the database’s collectors, and its probe must have passed: it reads with the same credential source and redaction key, whatever its role. It need not be the active collector, and a failover does not move the reading. Without a server named at step 2, the Info tab’s Logs card reads No host server. See The host server.
• Turn the slow log on yourself: SET PERSIST slow_query_log = ON on MySQL 8.0 and 8.4, or SET GLOBAL and the server’s configuration on MySQL 5.7 and MariaDB, with log_output including FILE. WatchTower15 never changes it. See Turning the slow log on.
• In a container, mount the log directory at the same path on the host, set log_error to a file in it, and run the container in UTC or in the host’s time zone. Otherwise the card reads File not on this host. See Databases in containers.
• The agent reads only the files the database reports, and only regular files inside the directory it named. Its service already has the access it needs to read them.

Alarms and gaps

The sixteen default alarms apply. A database with no collector shows Awaiting collector: it raises no alarm until it has reported once. On MySQL 5.7 the deadlock alarm and chart are unavailable: 5.7 does not count deadlocks where the agent can read them. The three RDS alarms (RDS free storage low, RDS CPU high, RDS storage burst balance low) are listed too, and never evaluated: a self-managed database has no RDS series. Its host’s CPU, memory and volumes are the server’s own telemetry and alarms, from the agent on the server it runs on. Database error-log errors is evaluated only while the server the database runs on reads its error log: see The alarm.

The Overview’s 30-day coverage gives each gap’s cause when you hover over it; Paused and No collector do not lower the share.