Managed Database: RDS and Aurora

terraform {
  required_version = ">= 1.8"

  required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = "~> 6.0"
    }
  }
}

provider "aws" {
  region = "eu-west-1"
}

variable "private_subnet_ids" {
  description = "Private data subnets in at least two Availability Zones."
  type        = list(string)
}

variable "vpc_id" {
  type = string
}

variable "node_security_group_id" {
  description = "Security group of the EKS nodes (or of the pods when security groups for pods are used)."
  type        = string
}

variable "db_instance_class" {
  type    = string
  default = "db.m7g.large"
}

resource "aws_db_subnet_group" "lumenpress" {
  name       = "lumenpress-prod"
  subnet_ids = var.private_subnet_ids
}

resource "aws_security_group" "db" {
  name        = "lumenpress-prod-db"
  description = "MariaDB access from the WordPress pods only"
  vpc_id      = var.vpc_id
}

resource "aws_vpc_security_group_ingress_rule" "db_from_nodes" {
  security_group_id            = aws_security_group.db.id
  referenced_security_group_id = var.node_security_group_id
  ip_protocol                  = "tcp"
  from_port                    = 3306
  to_port                      = 3306
  description                  = "MariaDB from EKS nodes"
}

resource "aws_db_parameter_group" "lumenpress" {
  name        = "lumenpress-mariadb11-8"
  family      = "mariadb11.8"
  description = "Lumen Press WordPress: utf8mb4, TLS required, slow query log"

  parameter {
    name  = "character_set_server"
    value = "utf8mb4"
  }

  parameter {
    name  = "character_set_client"
    value = "utf8mb4"
  }

  parameter {
    name  = "character_set_connection"
    value = "utf8mb4"
  }

  parameter {
    name  = "character_set_results"
    value = "utf8mb4"
  }

  parameter {
    name  = "max_allowed_packet"
    value = "67108864"
  }

  parameter {
    name  = "slow_query_log"
    value = "1"
  }

  parameter {
    name  = "long_query_time"
    value = "1"
  }

  parameter {
    name  = "log_output"
    value = "FILE"
  }

  parameter {
    name  = "require_secure_transport"
    value = "1"
  }

  lifecycle {
    create_before_destroy = true
  }
}

resource "aws_db_instance" "lumenpress" {
  identifier = "lumenpress-prod"

  engine         = "mariadb"
  engine_version = "11.8"
  instance_class = var.db_instance_class

  allocated_storage     = 100
  max_allocated_storage = 400
  storage_type          = "gp3"
  storage_encrypted     = true

  db_name  = "lumenpress"
  username = "admin_lumenpress"

  # RDS creates the master password in Secrets Manager and rotates it.
  manage_master_user_password = true

  multi_az               = true
  db_subnet_group_name   = aws_db_subnet_group.lumenpress.name
  vpc_security_group_ids = [aws_security_group.db.id]
  publicly_accessible    = false
  parameter_group_name   = aws_db_parameter_group.lumenpress.name
  ca_cert_identifier     = "rds-ca-rsa2048-g1"

  backup_retention_period         = 14
  backup_window                   = "02:00-03:00"
  maintenance_window              = "sun:03:30-sun:04:30"
  auto_minor_version_upgrade      = true
  copy_tags_to_snapshot           = true
  deletion_protection             = true
  skip_final_snapshot             = false
  final_snapshot_identifier       = "lumenpress-prod-final"
  enabled_cloudwatch_logs_exports = ["error", "slowquery"]

  database_insights_mode = "standard"
}

resource "aws_db_instance" "lumenpress_replica" {
  identifier                 = "lumenpress-prod-replica-1"
  replicate_source_db        = aws_db_instance.lumenpress.identifier
  instance_class             = var.db_instance_class
  parameter_group_name       = aws_db_parameter_group.lumenpress.name
  vpc_security_group_ids     = [aws_security_group.db.id]
  publicly_accessible        = false
  auto_minor_version_upgrade = true
  ca_cert_identifier         = "rds-ca-rsa2048-g1"
}

output "endpoint" {
  value = aws_db_instance.lumenpress.address
}

#!/usr/bin/env bash
# Forces a Multi-AZ failover of the instance named below while measuring what a visitor sees.
# Run against staging first. Needs the AWS CLI configured for the right account and region.
set -euo pipefail

instance=lumenpress-staging
url=https://staging.lumenpress.example/wp-json/
log=$(mktemp)

# Probe once a second for the whole test; /wp-json/ needs the database, so it fails when the database is away.
( while true; do
    curl -s -o /dev/null --max-time 5 -w '%{time_starttransfer} %{http_code}\n' "$url" | sed "s/^/$(date +%T) /" >> "$log" || true
    sleep 1
  done ) &
probe=$!
trap 'kill "$probe" 2>/dev/null || true' EXIT

sleep 20
echo "Forcing failover of $instance at $(date +%T)"
aws rds reboot-db-instance --db-instance-identifier "$instance" --force-failover --output text --query 'DBInstance.DBInstanceStatus'

aws rds wait db-instance-available --db-instance-identifier "$instance"
echo "Instance available again at $(date +%T)"
sleep 20

echo "Events during the test:"
aws rds describe-events --source-type db-instance --source-identifier "$instance" --duration 30 \
  --query 'Events[].[Date,Message]' --output text

echo "Probe results other than HTTP 200:"
grep -v ' 200$' "$log" || echo "none"

<?php
/**
 * wp-config.php fragment: the database section for the tiered Lumen Press site on RDS.
 * Values come from environment variables that Kubernetes fills from Secrets Manager.
 */
define( 'DB_NAME', getenv( 'WORDPRESS_DB_NAME' ) ?: 'lumenpress' );
define( 'DB_USER', getenv( 'WORDPRESS_DB_USER' ) ?: 'wp_app' );
define( 'DB_PASSWORD', getenv( 'WORDPRESS_DB_PASSWORD' ) );
define( 'DB_HOST', getenv( 'WORDPRESS_DB_HOST' ) );
define( 'DB_CHARSET', 'utf8mb4' );
define( 'DB_COLLATE', '' );

// Encrypt the connection. Core passes these flags to mysqli_real_connect() and nothing else.
define( 'MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL );

// Verify the server certificate. Read by the db.php drop-in shown on this page, not by core.
define( 'DB_SSL_CA', '/etc/ssl/certs/rds-global-bundle.pem' );

<?php
/**
 * Drop-in wp-content/db.php: a wpdb that verifies the database server certificate.
 *
 * Core calls mysqli_real_connect() with MYSQL_CLIENT_FLAGS and never mysqli_ssl_set(), so a CA bundle
 * cannot be passed through wp-config.php. This subclass supplies the CA from the DB_SSL_CA constant.
 * Keep it in step with wpdb::db_connect() when WordPress changes it.
 */
class Lumen_Verified_TLS_Wpdb extends wpdb {

	public function db_connect( $allow_bail = true ) {
		$this->is_mysql = true;
		mysqli_report( MYSQLI_REPORT_OFF );

		$dbh       = mysqli_init();
		$host      = $this->dbhost;
		$port      = null;
		$socket    = null;
		$host_data = $this->parse_db_host( $this->dbhost );
		if ( $host_data ) {
			list( $host, $port, $socket ) = $host_data;
		}

		if ( defined( 'DB_SSL_CA' ) ) {
			mysqli_ssl_set( $dbh, null, null, DB_SSL_CA, null, null );
		}
		$flags = defined( 'MYSQL_CLIENT_FLAGS' ) ? MYSQL_CLIENT_FLAGS : 0;

		// phpcs:ignore WordPress.PHP.NoSilencedErrors.Discouraged
		@mysqli_real_connect( $dbh, $host, $this->dbuser, $this->dbpassword, null, $port, $socket, $flags );

		if ( $dbh->connect_errno ) {
			$this->dbh = null;
			if ( $allow_bail ) {
				$this->bail( '<h1>Error establishing a database connection</h1>', 'db_connect_fail' );
			}
			return false;
		}

		$this->dbh = $dbh;
		if ( ! $this->has_connected ) {
			$this->init_charset();
		}
		$this->has_connected = true;
		$this->set_charset( $this->dbh );
		$this->ready = true;
		$this->set_sql_mode();
		$this->select( $this->dbname, $this->dbh );

		return true;
	}
}

$wpdb = new Lumen_Verified_TLS_Wpdb( DB_USER, DB_PASSWORD, DB_NAME, DB_HOST );

# Fragment of the Lumen Press image: trust the RDS certificate authorities and ship the verifying db.php.
FROM wordpress:php8.4-fpm

ADD --chmod=0644 https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem /etc/ssl/certs/rds-global-bundle.pem

COPY --chown=root:root --chmod=0644 db.php /usr/src/wordpress/wp-content/db.php

-- Run once as the RDS master user, over TLS.
CREATE USER 'wp_app'@'%' IDENTIFIED BY '<generate-and-store-in-secrets-manager>' REQUIRE SSL;
GRANT SELECT, INSERT, UPDATE, DELETE ON `lumenpress`.* TO 'wp_app'@'%';

CREATE USER 'wp_migrate'@'%' IDENTIFIED BY '<generate-and-store-in-secrets-manager>' REQUIRE SSL;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX, REFERENCES, CREATE TEMPORARY TABLES, LOCK TABLES
  ON `lumenpress`.* TO 'wp_migrate'@'%';

SHOW GRANTS FOR 'wp_app'@'%';
SHOW GRANTS FOR 'wp_migrate'@'%';

[www]
user = www-data
group = www-data
listen = 127.0.0.1:9000

; Static pool: the number of PHP workers, and so of database connections, per pod never changes.
pm = static
pm.max_children = 16
pm.max_requests = 500

; Fail a stuck request instead of holding a worker and a database connection for ever.
request_terminate_timeout = 60s

SHOW GLOBAL VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Aborted_connects';
SHOW GLOBAL STATUS LIKE 'Connection_errors_max_connections';

<?php
/**
 * LudicrousDB configuration for Lumen Press on RDS or Aurora: the writer takes every write, the reader
 * endpoint (an RDS read replica or the Aurora reader endpoint) takes the reads.
 * Lives next to wp-config.php. Host names come from the environment, never from the file.
 */

// Writer: every write, and reads when the reader is unreachable.
$wpdb->add_database(
	array(
		'host'          => DB_HOST,
		'user'          => DB_USER,
		'password'      => DB_PASSWORD,
		'name'          => DB_NAME,
		'write'         => 1,
		'read'          => 2, // Fallback for reads.
		'dataset'       => 'global',
		'timeout'       => 0.5,
		'lag_threshold' => null,
	)
);

// Reader: reads only. A lag threshold makes LudicrousDB skip a replica that has fallen behind.
$wpdb->add_database(
	array(
		'host'          => getenv( 'LUMEN_DB_READER_HOST' ),
		'user'          => DB_USER,
		'password'      => DB_PASSWORD,
		'name'          => DB_NAME,
		'write'         => 0,
		'read'          => 1,
		'dataset'       => 'global',
		'timeout'       => 0.5,
		'lag_threshold' => 5,
	)
);

terraform {
  required_version = ">= 1.8"

  required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = "~> 6.0"
    }
  }
}

provider "aws" {
  region = "eu-west-1"
}

variable "aurora_engine_version" {
  description = "List the choices with: aws rds describe-db-engine-versions --engine aurora-mysql"
  type        = string
}

variable "aurora_parameter_group_family" {
  description = "Parameter group family that matches the engine version."
  type        = string
  default     = "aurora-mysql8.0"
}

variable "db_subnet_group_name" {
  type = string
}

variable "db_security_group_id" {
  type = string
}

resource "aws_rds_cluster_parameter_group" "lumenpress" {
  name   = "lumenpress-aurora-cluster"
  family = var.aurora_parameter_group_family

  parameter {
    name  = "character_set_server"
    value = "utf8mb4"
  }

  parameter {
    name  = "require_secure_transport"
    value = "ON"
  }
}

resource "aws_rds_cluster" "lumenpress" {
  cluster_identifier = "lumenpress-prod"
  engine             = "aurora-mysql"
  engine_version     = var.aurora_engine_version
  database_name      = "lumenpress"
  master_username    = "admin_lumenpress"

  manage_master_user_password = true

  db_subnet_group_name            = var.db_subnet_group_name
  vpc_security_group_ids          = [var.db_security_group_id]
  db_cluster_parameter_group_name = aws_rds_cluster_parameter_group.lumenpress.name

  storage_encrypted               = true
  backup_retention_period         = 14
  deletion_protection             = true
  copy_tags_to_snapshot           = true
  skip_final_snapshot             = false
  final_snapshot_identifier       = "lumenpress-prod-final"
  enabled_cloudwatch_logs_exports = ["error", "slowquery"]
}

resource "aws_rds_cluster_instance" "lumenpress" {
  count = 2

  identifier         = "lumenpress-prod-${count.index + 1}"
  cluster_identifier = aws_rds_cluster.lumenpress.id
  engine             = aws_rds_cluster.lumenpress.engine
  engine_version     = aws_rds_cluster.lumenpress.engine_version
  instance_class     = "db.r7g.large"
}

output "writer_endpoint" {
  value = aws_rds_cluster.lumenpress.endpoint
}

output "reader_endpoint" {
  value = aws_rds_cluster.lumenpress.reader_endpoint
}