Amazon RDS and Aurora

For Amazon RDS for MySQL (5.7, 8.0, 8.4), Amazon Aurora MySQL (2.x and 3.x) and Amazon RDS for MariaDB (10.6, 10.11). Your agents on EC2 collect it, from inside your VPC or over the database’s public endpoint. Read Database monitoring first for pools, statuses, the privacy mode and the alarms.

The endpoint

Use an instance endpoint, or an Aurora cluster’s writer endpoint (<cluster>.cluster-<id>.<region>.rds.amazonaws.com). Not:

• an RDS Proxy endpoint (.proxy-): through a proxy’s reader endpoint, counters mix several servers;
• an Aurora reader endpoint (.cluster-ro-) or custom endpoint (.cluster-custom-): each balances connections across several instances.

The setup warns about these host names, and about a host that does not end in .rds.amazonaws.com — fine for a CNAME you own, but the agent verifies the certificate against the name you enter. See Pooled endpoints. For an RDS Proxy, cloud metrics list the instances behind it, each with Add as a database.

Connection	When
Private (VPC)	The collectors are in the database’s VPC (or a peered one). The usual choice.
Public	The database is publicly accessible. Its security group must then allow only your collectors.

Network checklist

• On the database’s security group, allow inbound TCP 3306 from the collectors’ security group (or their subnet).
• No inbound rule is needed anywhere for WatchTower15: the agent connects out to the database and out to WatchTower15.
• Keep the database private: a public endpoint must only allow the collectors.
• For cloud metrics, the collectors also reach rds.<region>.amazonaws.com and monitoring.<region>.amazonaws.com on 443: through a NAT gateway, HTTPS_PROXY, or VPC interface endpoints.

TLS and the certificate authority

TLS is required, and by default the agent verifies the certificate and the host name against the Amazon RDS CA bundle, built into the agent: there is nothing to download. You may instead verify the certificate only, or use the server’s system store or a CA file.

The grant script

Step 4 generates it for your engine, version and authentication. Run it as the master user. For password authentication on MySQL 8.4:

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, SHOW_ROUTINE

REQUIRE SSL stays: require_secure_transport is off on RDS’s default parameter group. On MariaDB the script grants SLAVE MONITOR, BINLOG MONITOR in place of REPLICATION CLIENT, as MariaDB 10.5 and later need. 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 is off on the default parameter groups of RDS and Aurora: turning it on takes a custom DB parameter group with performance_schema set to 1 and a reboot (on Aurora, of every instance of the cluster), and is your choice to make. WatchTower15 never changes it. Everything else on this page works without it, with no reboot. 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 and Aurora MySQL 3.x — 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.

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

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 RDS for MySQL 5.7, Aurora MySQL 2.x, 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 as the master user, to the same user as the other grants, only if you want that check.

RDS for MySQL and Aurora MySQL:

-- 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

RDS for MariaDB:

-- 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. RDS creates the master user with host %, so the check reports it as an Info finding: dismiss it with a reason. See The optional account grant.

Where the collectors find the credentials

WatchTower15 stores where the credentials are, never a password or a key. Every source here survives a collector’s replacement, except a file written by hand.

Source	Authentication
RDS IAM authentication	No password at all: a token minted from the instance role for each connection
AWS Secrets Manager	Password, read at connect time with the instance role
AWS SSM Parameter Store	Password, from SecureString parameters
File on the host	Password, in a file on each collector

RDS IAM authentication

Enable IAM database authentication on the instance or cluster. The grant script then creates the user with IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS' (MariaDB: IDENTIFIED VIA AWSAuthenticationPlugin USING 'RDS'). Policy for the collectors’ instance role:

{
  "Version": "2012-10-17",
  "Statement": [
    { "Effect": "Allow", "Action": "rds-db:connect",
      "Resource": "arn:aws:rds-db:<region>:<account>:dbuser:<the instance resource id>/wt15_monitor" }
  ]
}

IAM has no secret to carry the redaction key: give it its own SSM parameter (or a secret, or a file), and the role read access to it.

AWS Secrets Manager

Give 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.

AWS SSM Parameter Store

Give the user and the names of two SecureString parameters: the password and the redaction key.

{
  "Version": "2012-10-17",
  "Statement": [
    { "Effect": "Allow", "Action": "ssm:GetParameter",
      "Resource": "arn:aws:ssm:<region>:<account>:parameter<the parameters>" },
    { "Effect": "Allow", "Action": "kms:Decrypt",
      "Resource": "arn:aws:kms:<region>:<account>:key/<the parameters' key>" }
  ]
}

File on the host

The file /etc/wt15-server-agent/databases.d/<database-uuid>.cnf on each collector, mode 0600, holding user, password and redaction-key. Write it from cloud-init on servers that get replaced, or with the CLI on a long-lived one — see the credentials file, which works the same for RDS.

The CLI

On a collector, as root:

sudo /var/lib/wt15-server-agent/wt15-server-agent db add <database-uuid>    # write the credentials file
sudo /var/lib/wt15-server-agent/wt15-server-agent db test <database-uuid>   # connect as the collector would
sudo /var/lib/wt15-server-agent/wt15-server-agent db list                   # this server's databases and roles
sudo /var/lib/wt15-server-agent/wt15-server-agent db reveal <database-uuid> <key>   # the name or statement behind a hashed key

db test reports reachability, the TLS suite, the grant tiers, over-privilege, what is available and the redaction key’s fingerprint. db list never prints a secret. db reveal prints 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), and sends nothing to WatchTower15. Column, index, view and routine keys come from the schema state the agent keeps, so run it on the database’s active collector. 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.

The redaction key

One key per database (openssl rand -hex 32), given to every collector: in the secret’s redaction_key field, its own SSM parameter, or the credentials file. A collector with another key shows Key mismatch; see Privacy mode and the redaction key for Accept new key.

Pools and replacement

Collectors in an Auto Scaling group: use a tag rule for the pool, so a replacement joins it on its own, with IAM, Secrets Manager or SSM — none of them needs anything on the server. See Collector pools.

Cloud metrics

The engine cannot see its host. Attach the read policy to your collectors’ instance role, and the active collector reads the instance’s CPU, freeable memory and swap, free storage, IOPS, latency, throughput, burst and credit balances, replica lag and database load from CloudWatch every 5 minutes, and its facts from the RDS API every hour, with that role. They show in the Capacity view of the database’s Overview, and in three RDS alarms, and RDS’s own storage figures become the storage ceiling below. WatchTower15 is never granted anything, and AWS bills about $1.23 to $1.84 a month per database to your account. The policy, the cost, the network and role a collector needs, which endpoints are matched and every message with its fix: Cloud metrics for RDS and Aurora. The policy’s eight read actions are in two statements: six for cloud metrics, and two for the slow and error logs.

Slow and error logs

The database’s Logs tab shows its slow query log and its error log. On RDS and Aurora the active collector reads the instance’s slowquery/ and error/ log files through the RDS API, with its own instance role: no CloudWatch Logs export, and no change to the instance. See Slow and error logs.

• The two log actions. The collector’s role needs the read policy’s second statement, WT15DatabaseLogsRead: rds:DescribeDBLogFiles and rds:DownloadDBLogFilePortion on arn:aws:rds:*:*:db:*. A role that carries only the first statement reads Permission denied on the Info tab’s Logs card: attach the full policy to the role again (its name, wt15-cloud-metrics-read, is the same). The logs are read from the instance cloud metrics match, so the first statement is needed too.
• The slow log needs a custom parameter group. The default groups cannot be edited, and their log_output is TABLE, so no file is written. Set slow_query_log to 1, log_output to FILE and long_query_time (for example 1 or 2) in a DB parameter group of your own, attach it to the instance (on Aurora, to every instance of the cluster) and reboot: a newly attached group takes effect at the reboot. See On Amazon RDS and Aurora.
• The error log is written on every instance: nothing to turn on.
• Error lines arrive up to about 6 minutes late: RDS writes the error log to its file every 5 minutes, and the collector reads every minute. The Database error-log errors alarm is as late as the lines.
• The calls are free: two DescribeDBLogFiles a minute per database when nothing is written, and DownloadDBLogFilePortion only while a log changes. Each is a read event in your CloudTrail. See The calls and your CloudTrail.

Aurora and replication

Aurora replicas share the cluster volume and use no binlog replication. An Aurora database’s Instance card, on its Info tab, reads Not applicable for replication, and the replication stopped and replication lag alarms never fire there. With cloud metrics, an Aurora reader’s Overview charts AWS’s own AuroraReplicaLag, in its Capacity view. On RDS for MySQL and MariaDB, a read replica’s state and lag are collected and alarmed as on any replica.

Storage and growth

Once your collectors’ role carries the cloud metrics read policy, WatchTower15 reads the instance’s storage from AWS: the ceiling is its storage autoscaling limit (Maximum storage threshold), else its allocated size, and the free space is AWS’s own FreeStorageSpace, plus the autoscaling headroom. Days to full is that free space divided by the growth per day, and it follows the instance’s storage on its own: nothing to enter, nothing to update.

Without the policy, enter the ceiling on the Info tab’s Storage ceiling card, in GiB: the Maximum storage threshold when storage autoscaling is on, otherwise the Allocated storage (RDS → Databases → the instance → Configuration → Storage), and update it after you change the instance’s storage. The free space is then an estimate, the ceiling less the instance total: RDS also stores logs and temporary files that the total does not count. With neither, there is no days to full, and the storage alarm is never evaluated. A ceiling you entered is kept, unused, while AWS’s figures are read, and applies again if they stop. See The Storage ceiling card.

Aurora has no ceiling — its cluster volume grows by itself — so an Aurora database shows its sizes, growth and projections without days to full.

RDS for MySQL 5.7 and Aurora MySQL 2.x. 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

The advisor’s 21 checks run on RDS and Aurora too, with these differences (see Where each check applies):

• Managed by Amazon RDS: binary log retention, which RDS keeps as mysql.rds_set_configuration says, and secure_file_priv.
• Not applicable on Aurora MySQL: the redo log and commit durability, which Aurora’s storage layer handles, and a writable replica, since Aurora readers are read-only.
• performance_schema, off on the default parameter groups, is needed for unused indexes and, through query analytics, for the checks on statements.
• RDS for MySQL 5.7, Aurora MySQL 2.x and RDS for 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 RDS for MySQL 8.0 and 8.4 and Aurora MySQL 3.x are not seen by the checks for a missing primary key and for redundant and unused indexes. See Schema checks.
• Two Info findings most RDS databases show: Unencrypted connections allowed, since require_secure_transport is off on the default parameter groups, and the master user’s host %, with the optional account grant. Dismiss either with a reason if you accept it.

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. The threads running threshold is absolute — WatchTower15 cannot see an RDS instance’s CPU count — so set it to suit the instance class. Database storage filling up needs the storage ceiling above, reported by AWS or entered, and is never evaluated on Aurora.

The three RDS alarms — RDS free storage low, RDS CPU high and RDS storage burst balance low — need cloud metrics: until your collectors’ role carries the read policy they are listed but never evaluated. RDS free storage low never fires on Aurora, which has no allocated storage, and RDS storage burst balance low only on gp2 storage.

Database error-log errors needs the error log to be read, with the two log actions, and is up to about 6 minutes late. 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.