Module: MYSQL

MySQL is a widely used open-source relational database. Pigsty’s MYSQL module installs a fixed native MySQL 8.4 LTS platform on managed nodes. Choose a standalone instance or three-node single-primary InnoDB Cluster with Group Replication. The module manages TLS, backups, monitoring, and lifecycle.

Module Capabilities

The MYSQL module currently provides:

• Fixed native MySQL 8.4 LTS server, client, Shell, Router, and matching XtraBackup packages.
• Standalone or three-node single-primary InnoDB Cluster, created and reconciled through MySQL Shell AdminAPI.
• Flexible HA access: MySQL Router on every HA member (RW 6446, RO 6447), with optional reuse of the NODE Layer 2 VIP (<vip>:6446/6447, automatically added to certificate SANs)
• TLS with leaf certificates from the shared Pigsty CA. Non-TLS connections are rejected.
• Additive mysql_databases and mysql_users declarations that never delete data implicitly.
• mysql_parameters overrides, including max_connections, with rolling restarts after configuration changes.
• Daily full physical backups: XtraBackup with preparation, an initial baseline backup on first deployment, retention, a concurrency lock, atomic commit, and freshness metrics
• Complete observability: mysqld_exporter and backup metrics, 70 recording rules, 8 actionable alerts, 5 Grafana dashboards, and error logs in VictoriaLogs
• Default sql_require_primary_key, blocking tables without primary keys that would break MGR replication and recovery.
• Convergent operations: rerun mysql.yml to recover offline members, a complete three-node outage (all members reachable, selecting the highest GTID), or a replacement machine at the same IP (stale registration cleanup and Clone rebuild), with safeguards for dangerous operations

Module Architecture

NODE supplies node management, repositories, and the shared CA. INFRA supplies VictoriaMetrics, VictoriaLogs, Grafana, and Alertmanager. MYSQL depends on both, but not ETCD or PGSQL.

flowchart LR
    admin["Pigsty admin node"] -->|"mysql.yml"| mysqld["mysqld ×3 / single-primary MGR<br>3306 · TLS"]
    client["Application clients"] -->|"RW 6446 / RO 6447"| router["MySQL Router<br>(on every HA member)"]
    router --> mysqld
    mysqld --> backup["XtraBackup daily full<br>(current primary only)"]
    mysqld --> exporter["mysqld_exporter :9104"]
    mysqld --> journal["Error log → Journald"]
    exporter --> vm["VictoriaMetrics"]
    journal --> vector["Vector"] --> vl["VictoriaLogs"]
    vm --> grafana["Grafana"]
    vl --> grafana
    vm --> alertmanager["Alertmanager"]

    style mysqld fill:#4479A1,stroke:#33618a,color:#fff
    style router fill:#70C1B3,stroke:#4f968b,color:#fff
    style vm fill:#E66B7A,stroke:#b84e5c,color:#fff
    style vl fill:#C98367,stroke:#9e634e,color:#fff

In three-node clusters, mysql_seq=1 coordinates bootstrap only. Runtime PRIMARY is elected. Reruns never force it back to node 1.

Components and Ports

The role creates and manages three platform identities:

• dbuser_cluster@'%': TLS-only AdminAPI and Router bootstrap identity (created on HA clusters only);
• dbuser_monitor@'127.0.0.1': least-privilege exporter identity;
• dbuser_backup@'localhost': local XtraBackup identity.

Supported Platforms

The native-package platform gate admits:

Preflight rejects Debian/Ubuntu ARM64 because Oracle APT has no MySQL 8.4 arm64 packages. On ARM, use EL 9/10, such as Rocky Linux.

Scope and Boundaries

MYSQL provides a fixed platform. Before adoption, accept these scope limits:

• One or three nodes only: No in-place 1→3 or 3→5 expansion and no persistent two-node operation. Use logical migration for capacity changes and same-address replacement for hardware refresh.
• Fixed versions, ports, and character set: these are not exposed as parameters. Data defaults to /data/mysql, configurable with mysql_data. Memory settings are derived from node resources and PostgreSQL colocation; override key settings with mysql_parameters.
• Daily local full backups: no incremental chains, continuous binlog archiving, or PITR. Physical copy-back recovery is manual; see the runbook.
• Automatic complete-outage recovery requires all three members to be reachable: it always uses force: false. If fewer than three hosts are reachable, repair them or follow Complete Outage Recovery. The role never forces majority recovery automatically.
• Local Router with an optional L2 VIP: clients can use any member’s Router, a multi-address DSN, or the NODE Layer 2 VIP (<vip>:6446/6447). No DNS / HAProxy access layer is included.

Documentation

Quick Start

Declare a cluster in the inventory (full template: conf/demo/mysql.yml):

all:
  children:
    my-test:
      hosts:
        10.10.10.11: { mysql_seq: 1 }
        10.10.10.12: { mysql_seq: 2 }
        10.10.10.13: { mysql_seq: 3 }
      vars:
        mysql_cluster: my-test
        mysql_databases: [ { name: app } ]
        mysql_users: [ { name: app, password: DBUser.App, priv: { 'app.*': 'ALL PRIVILEGES' } } ]

  vars:
    node_repo_modules: node,infra,mysql   # repos must include the mysql module
    mysql_root_password: MySQL.Root       # change all sample passwords in production
    mysql_monitor_password: MySQL.Monitor
    mysql_cluster_password: MySQL.Cluster

After NODE provisioning, deploy:

./node.yml  -l my-test             # node provisioning: repo, shared CA, monitoring agents
./mysql.yml -l my-test --check     # preflight the complete three-node cluster
./mysql.yml -l my-test             # real run; a three-node cluster takes ~2 minutes

mysql -h 10.10.10.11 -P 6446 -u app -pDBUser.App \
  --ssl-mode=VERIFY_CA --ssl-ca=/etc/pki/ca.crt app   # connect through the Router RW endpoint

Then open the Grafana MySQL Overview dashboard to inspect the cluster.