locals {
  # The systemd service name and conf.d directory both follow the package name:
  # mariadb-server -> mariadb(.conf.d), mysql-server -> mysql(.conf.d).
  db_service_name = strcontains(var.db_engine_package, "maria") ? "mariadb" : "mysql"
}

data "openstack_images_image_v2" "os" {
  name        = var.image_name
  most_recent = true
}

data "openstack_networking_network_v2" "external" {
  name = var.external_network
}


resource "openstack_networking_network_v2" "private" {
  name           = "${var.instance_name}-private"
  admin_state_up = true
}

resource "openstack_networking_subnet_v2" "private" {
  name            = "${var.instance_name}-private-sn"
  network_id      = openstack_networking_network_v2.private.id
  cidr            = var.private_cidr
  ip_version      = 4
  enable_dhcp     = true
  dns_nameservers = ["8.8.8.8", "8.8.4.4"]
}

resource "openstack_networking_router_v2" "router" {
  name                = "${var.instance_name}-router"
  external_network_id = data.openstack_networking_network_v2.external.id
  admin_state_up      = true
}

resource "openstack_networking_router_interface_v2" "private" {
  router_id = openstack_networking_router_v2.router.id
  subnet_id = openstack_networking_subnet_v2.private.id
}

resource "openstack_networking_secgroup_v2" "mysql" {
  name        = "${var.instance_name}-sg"
  description = "SSH from any IPv4; MySQL/MariaDB from configured CIDRs only"
}

resource "openstack_networking_secgroup_rule_v2" "mysql" {
  for_each = toset(var.allowed_cidrs)

  direction         = "ingress"
  ethertype         = "IPv4"
  protocol          = "tcp"
  port_range_min    = 3306
  port_range_max    = 3306
  remote_ip_prefix  = each.value
  security_group_id = openstack_networking_secgroup_v2.mysql.id
}

resource "openstack_networking_secgroup_rule_v2" "ssh" {
  direction         = "ingress"
  ethertype         = "IPv4"
  protocol          = "tcp"
  port_range_min    = 22
  port_range_max    = 22
  remote_ip_prefix  = "0.0.0.0/0"
  security_group_id = openstack_networking_secgroup_v2.mysql.id
}

resource "openstack_blockstorage_volume_v3" "mysql_data" {
  name = "${var.instance_name}-data"
  size = var.volume_size
}

resource "openstack_networking_port_v2" "mysql" {
  name               = "${var.instance_name}-port"
  network_id         = openstack_networking_network_v2.private.id
  security_group_ids = [openstack_networking_secgroup_v2.mysql.id]

  fixed_ip {
    subnet_id = openstack_networking_subnet_v2.private.id
  }

  depends_on = [openstack_networking_router_interface_v2.private]
}

resource "openstack_compute_instance_v2" "mysql" {
  name        = var.instance_name
  flavor_name = var.db_flavor
  key_pair    = var.key_name

  user_data = templatefile("${path.module}/cloud-init/mysql.yaml", {
    db_engine_package = var.db_engine_package
    db_service_name   = local.db_service_name
    backup_schedule   = var.backup_schedule
    db_name           = var.db_name
    db_user           = var.db_user
    db_password       = var.db_password
  })

  block_device {
    uuid                  = data.openstack_images_image_v2.os.id
    source_type           = "image"
    destination_type      = "volume"
    volume_size           = 20
    boot_index            = 0
    delete_on_termination = true
  }

  network {
    port = openstack_networking_port_v2.mysql.id
  }
}

resource "openstack_compute_volume_attach_v2" "mysql_data" {
  instance_id = openstack_compute_instance_v2.mysql.id
  volume_id   = openstack_blockstorage_volume_v3.mysql_data.id
}

variable "allowed_cidrs" {
  description = "IPv4 CIDRs allowed to reach MySQL/MariaDB on port 3306"
  type        = list(string)
}

variable "key_name" {
  description = "Existing SSH keypair name in the project for compute instances"
  type        = string
}

variable "db_flavor" {
  description = "Flavor for the database instance"
  type        = string
  default     = "m2a.large"
}

variable "volume_size" {
  description = "Cinder volume size in GiB for MySQL/MariaDB data"
  type        = number
  default     = 50
}

variable "db_engine_package" {
  description = "APT package installed for the database engine. Defaults to MariaDB, a drop-in MySQL-compatible replacement and the same engine wordpress-mysql already installs. Set to mysql-server for upstream MySQL on distributions that carry it in the default repositories."
  type        = string
  default     = "mariadb-server"
}

variable "backup_schedule" {
  description = "Cron schedule for the mysqldump backup script"
  type        = string
  default     = "0 2 * * *"
}

variable "image_name" {
  description = "Boot image name"
  type        = string
  default     = "Ubuntu-24.04"
}

variable "external_network" {
  description = "Shared external network for router gateway and floating IPs; defaults to PublicStatic (persisted FIP / production pattern). Override with PublicEphemeral for ephemeral demos."
  type        = string
  default     = "PublicStatic"
}

variable "private_cidr" {
  description = "CIDR for the private subnet hosting the database"
  type        = string
  default     = "192.168.50.0/24"
}

variable "instance_name" {
  description = "Compute instance display name"
  type        = string
  default     = "mysql"
}

variable "db_name" {
  description = "Application database created on first boot"
  type        = string
  default     = "appdb"
}

variable "db_user" {
  description = "Application database user created on first boot"
  type        = string
  default     = "appuser"
}

variable "db_password" {
  description = "Password for the application database user. Required, no default, so no credential ships with the template."
  type        = string
  sensitive   = true
}

#cloud-config
package_update: true
write_files:
  - path: /usr/local/bin/mysql-backup.sh
    permissions: "0755"
    content: |
      #!/bin/bash
      set -e
      install -d -o mysql -g mysql /var/lib/mysql/backups
      ts=$(date +%Y%m%d-%H%M%S)
      mysqldump --all-databases | gzip > /var/lib/mysql/backups/mysqldump-$ts.sql.gz
  - path: /etc/cron.d/mysql-backup
    owner: root:root
    permissions: "0644"
    content: |
      ${backup_schedule} root /usr/local/bin/mysql-backup.sh
runcmd:
  - |
    set -e
    export DEBIAN_FRONTEND=noninteractive
    # The data volume attaches as /dev/sdb on this platform (not /dev/vdb).
    DEV=/dev/sdb
    for i in $(seq 1 30); do [ -b "$DEV" ] && break; sleep 5; done
    if ! blkid "$DEV" >/dev/null 2>&1; then mkfs.ext4 -F -L mysqldata "$DEV"; fi
    mkdir -p /var/lib/mysql
    mount "$DEV" /var/lib/mysql
    grep -q "$DEV" /etc/fstab || echo "$DEV /var/lib/mysql ext4 defaults,nofail 0 2" >> /etc/fstab
    for i in 1 2 3; do apt-get update && break; sleep 10; done
    apt-get install -y ${db_engine_package}
    install -d /etc/mysql/${db_service_name}.conf.d
    printf '[mysqld]\nbind-address = 0.0.0.0\ndatadir = /var/lib/mysql\n' > /etc/mysql/${db_service_name}.conf.d/99-template.cnf
    install -d -o mysql -g mysql /var/lib/mysql/backups
    systemctl enable ${db_service_name}
    systemctl restart ${db_service_name}
    mysql -e "CREATE DATABASE IF NOT EXISTS ${db_name};"
    mysql -e "CREATE USER IF NOT EXISTS '${db_user}'@'%' IDENTIFIED BY '${db_password}';"
    mysql -e "GRANT ALL PRIVILEGES ON ${db_name}.* TO '${db_user}'@'%'; FLUSH PRIVILEGES;"

# MySQL/MariaDB Database

Standalone self-managed MySQL/MariaDB on a dedicated instance with a data volume, security
rules scoped to client CIDRs, and a scheduled `mysqldump` backup. Unlike
`wordpress-mysql`, this template ships no application tier: use it when the workload needs a
relational MySQL-compatible database without a bundled CMS.


**Network class:** production — `external_network` defaults to `PublicStatic` for persisted floating IPs and multi-tier stacks; override with `PublicEphemeral` for ephemeral demos.

## Prerequisites

- OpenTofu >= 1.6.0 or Terraform >= 1.6.0
- Quake AI account with OpenStack credentials
- One or more trusted IPv4 CIDRs that should reach MySQL/MariaDB on port 3306

## Usage

1. Clone or copy this template directory
2. Copy `terraform.tfvars.example` to `terraform.tfvars` and fill in your values
3. Source your OpenStack credentials: `source openrc.sh`
4. Initialize: `tofu init`
5. Preview: `tofu plan`
6. Apply: `tofu apply`

## Variables

| Name | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `allowed_cidrs` | list(string) | yes | n/a | IPv4 CIDRs allowed to reach MySQL/MariaDB on port 3306 |
| `key_name` | string | yes | n/a | Existing SSH keypair name in the project for compute instances |
| `db_flavor` | string | no | `m2a.large` | Flavor for the database instance |
| `volume_size` | number | no | `50` | Cinder volume size in GiB for database data |
| `db_engine_package` | string | no | `mariadb-server` | APT package for the database engine |
| `backup_schedule` | string | no | `0 2 * * *` | Cron schedule for the `mysqldump` backup |
| `image_name` | string | no | `Ubuntu-24.04` | Boot image name |
| `external_network` | string | no | `PublicStatic` | Persisted FIP / production default; override with `PublicEphemeral` for demos |
| `private_cidr` | string | no | `192.168.50.0/24` | Private subnet CIDR for the database |
| `instance_name` | string | no | `mysql` | Compute instance display name |
| `db_name` | string | no | `appdb` | Application database created on first boot |
| `db_user` | string | no | `appuser` | Application database user created on first boot |
| `db_password` | string | yes | n/a | Password for the application database user |

## Documentation

Full documentation: [MySQL/MariaDB Database](/resources/iac-templates/mysql-database)