Managed¶
Production MySQL runs on Amazon RDS for MySQL 8. AWS operates the instance — automated backups, minor-version patching, host replacement — while verilib-frontend owns the schema, the migrations, and almost all writes.
| Engine | MySQL 8.0 on Amazon RDS |
| Port | 3306 |
| Database name | verilib |
| Application user | verilib |
| Credentials | DB_USER / DB_PASSWORD in the host's .env (see Credentials) |
| Public access | Must stay disabled — reachable only from allowed security groups inside the VPC |
| Local development | Docker Compose MySQL, see Database → Local development |
The application connects with plain Eloquent/PDO over the MySQL protocol; there is no RDS-specific client or driver. Both write paths and the DB-free cert workers are described on Database.
Naming convention¶
RDS, security group, and parameter group names must be unique per account and Region, so every resource carries an environment suffix. <env> is dev, staging, or prod.
| Resource | Name pattern |
|---|---|
| DB instance identifier | verilib-mysql-<env> |
| Security group | verilib-rds-mysql-<env> |
| DB subnet group | verilib-rds-subnet-<env> |
| DB parameter group | verilib-mysql8-<env> |
Tag every resource Project=verilib and Environment=<env>.
The first instance was created before this convention and carries unsuffixed names (verilib-mysql, verilib-rds-mysql, verilib-rds-subnet, verilib-mysql8). Leave it as it is — renaming an instance changes its endpoint — and apply the suffix to every environment added after it.
The database name and user stay verilib in each environment — the instances are separate, so the logical names do not need to differ. Passwords must differ per environment.
Instance configuration¶
| Setting | Value | Why |
|---|---|---|
| Engine version | MySQL 8.0.x |
Matches the schema dump the app ships (db.sql is an 8.0 dump) |
| Storage | gp3, autoscaling enabled | Write bursts are I/O-shaped; gp3 IOPS/throughput are tunable independently of size |
| Multi-AZ | Standby for prod; single-AZ acceptable for dev |
Failover is a durability feature, not extra write capacity |
| Automated backups | Enabled | See Backup |
| Encryption at rest | Enabled | |
| Performance Insights | Enabled | The write-heavy paths need query-level attribution, not just host CPU |
| Deletion protection | Enabled for staging / prod |
Parameter group¶
| Parameter | Value | Why |
|---|---|---|
max_allowed_packet |
67108864 (64 MiB) |
The atomize persist path writes large payloads — notably the repos.graph JSON blob and bulk snippet inserts |
character_set_server |
utf8mb4 |
Matches the schema dump and the PDO DSN (charset=utf8mb4) |
collation_server |
utf8mb4_unicode_ci |
Use a separate parameter group per environment so tuning in dev cannot alter prod.
Network access¶
flowchart LR
fe[PHP frontend<br/>+ queue workers] -->|3306, SG-allowed| rds[(Amazon RDS<br/>MySQL 8)]
atomizer[atomize-processor] -->|3306, SG-allowed| rds
cert[cert workers] -.->|no DB access| rds
laptop[Operator laptop] -->|SSH tunnel via bastion| fe| Rule | Detail |
|---|---|
| Inbound | MySQL/Aurora 3306 from the security groups of the app and worker hosts — not from CIDR ranges of the open internet |
| Public access | Keep disabled. Toggle at RDS → Databases → instance → Modify → Connectivity |
| Operator access | SSH tunnel through an allowed host, then connect to 127.0.0.1 on the forwarded port |
Never expose 3306 to 0.0.0.0/0
Enabling public access with an open CIDR puts the platform's entire product database on the internet. If public access is temporarily needed for an administrative task, scope the inbound rule to a single /32 and revert both the rule and the public-access flag when finished.
Keep the database in the application's Region
The heavy paths (atomize persist, certify clone) issue many statements per job, so wall-clock time is dominated by round-trips. An RDS instance in a different Region from the PHP writers adds latency to every statement. RDS instances cannot be relocated between Regions — a Region change means snapshot-copy or dump/import into a new instance, which yields a new endpoint.
Connecting the application¶
Set on the application host (repository-root .env for a server checkout; the mounted env file for Docker stacks):
DB_HOST=<rds-endpoint>
DB_PORT=3306
DB_NAME=verilib
DB_USER=verilib
DB_PASSWORD=<password>
DB_TYPE=eloquent
Variable reference: Configuration and environment variables.
Credentials¶
The master password is set when the instance is created and pasted into DB_PASSWORD on each host that connects. The frontend resolves credentials only from the env file — public/bootstrap.php loads it and nothing else — so that file is where the password lives.
That makes file hygiene the whole control:
| Rule | Why |
|---|---|
.env is gitignored — keep it that way |
The password is in plain text; a commit publishes it |
Restrict permissions, e.g. chmod 640 owned by the PHP-FPM user |
The default 644 leaves the password readable by every local account |
| One password per environment | A shared password makes dev access equal to prod access |
To rotate: change the master password on the RDS instance, update DB_PASSWORD in the .env of every host that connects, then restart PHP-FPM and the DB-writing workers as described below.
Dotenv will not override an already-set process variable, so restart PHP
public/bootstrap.php loads the env file with Dotenv's immutable mode: values already present in the process environment win over the file. Editing .env is not enough — reload PHP-FPM and Apache (for example systemctl restart php-fpm then systemctl reload httpd), or recreate the containers for a Docker stack. The DB-writing workers (upload_response, atomize_response, validate_response, livelog, validate_livelog) must be restarted too.
Define DB_PORT exactly once
The two connection sites in the frontend disagree about the port, so a stale duplicate is a live failure mode:
| Site | Port handling |
|---|---|
public/core/Database.php (PDO, page/model queries) |
DSN is mysql:host=…;dbname=…;charset=utf8mb4 — no port, so the driver default 3306 is used |
public/api/user/User.php (mysqli, auth API) |
Passes DB_PORT explicitly, defaulting to 3306 |
A leftover second DB_PORT= line later in .env overrides the first. The symptom is asymmetric: pages that use PDO still reach RDS on 3306 while the auth API tries the wrong port and dies, which surfaces as a failed login or an unrendered page rather than an obvious connection error. Keep a single DB_PORT=3306.
Standing up another environment¶
To create a dev or staging instance alongside an existing one:
- Provision the instance with its own security group, subnet group, and parameter group, following the naming convention above, and set a password unique to that environment.
- Seed it from a dump of an existing environment — see Database backup and restore.
- Apply any
sql/*.sqlmigrations the dump predates, plus the atomizer's additive SQL — see Scheme → Migrations. - Verify row counts for
repos,codes,atoms,atomsdependencies,atomsnippets, andcertificates, and confirmcharacter_set_server/collation_server. - Point the target stack at the endpoint and exercise atomize and certify before treating it as usable.
Scaling levers¶
RDS makes capacity easy to change, but the topology is a single primary writer serving write-heavy atomize and certify paths. Resizing raises headroom; it does not parallelise writes.
| Symptom | Lever |
|---|---|
| High CPU during persist | Larger instance class |
| High write latency / IOPS saturation with spare CPU | Raise gp3 IOPS and throughput |
| Long duration with low CPU and low IOPS | Round-trip bound — reduce statements per job (batch multi-row inserts) rather than buying hardware |
| Too many concurrent large transactions | Fewer concurrent DB-writing workers; prefetch is already 1 per consumer |
| Read-heavy reporting added later | Read replicas — these do not relieve the write paths |
See Scalability → Data-plane scaling and Performance.
Read the live settings before you rely on them
The instance class, storage allocation, Multi-AZ state, retention window, and deletion-protection flag are properties of each instance and drift as it is resized, so they are not recorded here. Read them from the RDS console for the environment you are working on, and treat the table above as the target rather than a description of what is set. There is no published load test for the write-heavy paths.
Documentation source of truth¶
The provisioning steps for an instance — console fields, security group rules, parameter group values, dump and import commands — are maintained upstream in docs/mysql-to-amazon-rds-migration.md in verilib-frontend (private repo access required), alongside the schema itself (db.sql, sql/). This page describes how the platform uses RDS; follow the upstream runbook to build one.