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.
• Router on each HA member, with topology-aware read/write 6446 and read-only 6447 endpoints.
• 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 XtraBackup backup and prepare, retention, concurrency locks, and atomic completion.
• mysqld_exporter metrics, 68 recording rules, 27 alerts, five Grafana dashboards, and VictoriaLogs error logs.
• Default sql_require_primary_key, blocking tables without primary keys that would break MGR replication and recovery.
• Convergent repair of dropped members and AdminAPI drift by rerunning mysql.yml. Safeguards restrict destructive paths.

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.

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, directories, and charset: No parameters change them. Memory sizing comes from node specifications. Override individual keys through mysql_parameters.
• Daily local full backups: No incremental chains, continuous binlog archives, or PITR. Physical restore is manual; follow the procedure.
• Manual complete-outage recovery: This avoids split-brain from automated guesses. Playbook failures print recovery instructions.
• No VIP, DNS, or HAProxy access layer: Connect through any member’s Router, preferably with a multi-host DSN.

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.