Module: MYSQL

• 1: Configuration
• 2: Parameters
• 3: Administration
• 4: Playbook
• 5: Monitoring
• 6: Metrics
• 7: FAQ

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

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

./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

1 - Configuration

The MYSQL module is driven by the inventory: you declare the desired cluster, and mysql.yml converges the live state to match. This page covers topology planning and every configuration block; see Parameters for the full reference.

Before You Deploy

• Target nodes are NODE-managed, with the shared CA installed at /etc/pki/ca.crt (managed by the node_ca role; the MySQL role only issues leaf certificates);
• Package repositories include the mysql module: node_repo_modules: node,infra,mysql, or a local repo cached with repo_extra_packages: [mysql];
• The platform is in the support matrix: x86_64 on EL 8/9/10, Debian 12/13, Ubuntu 22/24; or aarch64 on EL 9/10;
• The three platform passwords (mysql_root_password, mysql_monitor_password, mysql_cluster_password) are set to production values — preflight rejects CHANGE_ME placeholders.

Identity

Each cluster is an inventory group with two required identity parameters:

Topology is inferred from member count: 1 member is a standalone, 3 members form an InnoDB Cluster; any other count is rejected at preflight. mysql_seq=1 is only the bootstrap coordinator, not the runtime primary.

Instance names follow {{ mysql_cluster }}-{{ mysql_seq }} (e.g. my-test-1). The inventory host address (IP or resolvable hostname) is the advertised MySQL and MGR address and cannot be changed by an ordinary rerun.

Standalone Instance

The minimal standalone declaration:

my-meta:
  hosts:
    10.10.10.10: { mysql_seq: 1 }
  vars:
    mysql_cluster: my-meta

Standalone instances have no Router (6446/6447 do not exist); clients connect to 3306 directly. Backups, monitoring, and TLS behave exactly as in HA mode.

Three-Node InnoDB Cluster

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
        host: '%'
        password: DBUser.App
        connlimit: 20
        priv: { 'app.*': 'ALL PRIVILEGES' }

This yields a single-primary MGR cluster: one writable PRIMARY, two read-only SECONDARY members, tolerating one node failure. Every member runs a Router, so port 6446 on any member reaches the current primary.

Databases

mysql_databases declares databases additively:

mysql_databases:
  - { name: app }                                              # utf8mb4 / utf8mb4_0900_ai_ci by default
  - { name: app2, encoding: utf8mb4, collate: utf8mb4_general_ci }

Each entry accepts only these three fields; preflight validation rejects additional keys.

Convergence is additive: reruns create missing databases, but removing an entry never drops one. Deleting data is a manual operation by design.

Users

mysql_users declares users and grants additively:

mysql_users:
  - name: app                        # username
    host: '%'                        # grant source, defaults to '%'
    password: DBUser.App             # required; special characters are handled
    connlimit: 20                    # MAX_USER_CONNECTIONS, 0 = unlimited
    priv:                            # grant map: 'db.table' -> privilege list
      'app.*': 'ALL PRIVILEGES'
      'app2.*': 'SELECT, INSERT, UPDATE, DELETE'

Grant scopes are written as 'db.table', with * wildcards on either side ('*.*', 'app.*'); values are comma-separated privilege names. Preflight validates usernames, hosts, scopes, and privilege words, rejecting malformed declarations.

Semantics:

• Missing users are created; existing users get their password and connection limit updated;
• Grants in priv are applied, but removing a mapping does not REVOKE;
• The platform identities (root, dbuser_monitor, dbuser_cluster, dbuser_backup) cannot be declared;
• The server enforces TLS: the client default PREFERRED mode negotiates encryption automatically, and plaintext (DISABLED) connections are rejected; prefer an explicit VERIFY_CA.

Parameter Overrides

mysql_parameters overrides [mysqld] options, rendered at the end of the managed config so the last value wins:

my-test:
  vars:
    mysql_cluster: my-test
    mysql_parameters:
      max_connections: 500
      long_query_time: 2
      innodb_print_all_deadlocks: true   # booleans render as ON/OFF

Rules and safety:

• Keys must be plain option names (letter first; ._- allowed); values must be single-line scalars;
• The rendered config still passes mysqld --validate-config, so a bad option fails at deploy time without touching the running service;
• Platform-reserved options cannot be overridden: identity and protocol (user, pid_file, server_id, datadir, socket, port, bind_address, mysqlx_bind_address, report_host, mysqlx, …), replication and plugins (gtid_mode, enforce_gtid_consistency, log_bin, relay_log, plugin_load*, clone, plugin_clone, plugin_mysqlx, group_replication_*, …), and TLS (require_secure_transport, ssl_*) are role-managed and rejected if declared;
• Parameter changes trigger an orchestrated rolling restart: secondaries first, primary last.

Memory needs no configuration: the buffer pool is 25% of node memory (256MB floor), redo capacity is half the buffer pool (128MB–4GB), and replica parallelism follows CPU count. For precise control, override innodb_buffer_pool_size and friends via mysql_parameters.

Backup Settings

mysql_backup_enabled: true            # daily backup timer, on by default
mysql_backup_repo:
  local:
    path: /data/backups/mysql         # local backup root
    retention: 7                      # keep the last 7 fulls

The backup contract (details in Administration):

• One XtraBackup full physical backup per day, prepared immediately after — the output directory is directly restorable;
• Standalone backs up locally; in HA every member’s timer fires, but only the current PRIMARY actually runs — other members skip cleanly;
• Layout is <path>/<cluster>/<UTC timestamp>/, with an atomic latest symlink and retention-based pruning;
• No incremental chain, no binlog archiving, no PITR: for a standalone the recovery point is the most recent backup.

Platform Credentials

mysql_root_password: MySQL.Root          # local root (root@localhost, socket only)
mysql_monitor_password: MySQL.Monitor    # exporter identity
mysql_cluster_password: MySQL.Cluster    # AdminAPI / Router / backup identity

Credential lifecycle rules:

• Passwords must be single-line and must not keep the CHANGE_ME prefix — enforced at preflight;
• On HA clusters, mysql_cluster_password cannot be rotated by an ordinary rerun: it is embedded in cluster metadata and Router keyrings, so implicit rotation is rejected (standalone instances have no such binding and rotate normally);
• mysql_root_password cannot be silently reset either: if the live root password differs from the declaration, the task fails explicitly instead of overwriting it.

Credential material lives in /etc/mysql/pigsty/ (root-owned: directory 0700, files 0600), including ready-to-use client configs for local operations:

mysql --defaults-extra-file=/etc/mysql/pigsty/root.cnf        # local root session
mysql --defaults-extra-file=/etc/mysql/pigsty/cluster.cnf     # cluster session via the local Router (HA members only)

Full Example

Standalone plus three-node HA, matching the four-node sandbox:

all:
  children:
    infra:
      hosts:
        10.10.10.10: { infra_seq: 1 }

    my-meta:
      hosts:
        10.10.10.10: { mysql_seq: 1 }
      vars: { mysql_cluster: my-meta, node_cluster: my-meta }

    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
        node_cluster: my-test
        mysql_databases:
          - { name: app }
        mysql_users:
          - { name: app, password: DBUser.App, priv: { 'app.*': 'ALL PRIVILEGES' } }
        mysql_parameters:
          max_connections: 500

  vars:
    version: v4.5.0
    admin_ip: 10.10.10.10
    node_repo_modules: node,infra,mysql
    node_tune: oltp

    mysql_root_password: MySQL.Root
    mysql_monitor_password: MySQL.Monitor
    mysql_cluster_password: MySQL.Cluster

See conf/demo/mysql.yml for the full template. Note that conf/mysql.yml is the OpenHalo template (a MySQL-compatible PostgreSQL kernel) and is unrelated to this module.

2 - Parameters

The MYSQL deployment role deliberately exposes only 11 parameters; the removal role adds 2 protected operations parameters. Software versions, ports, directories, charset, TLS paths, and timer schedules are fixed by the role; memory sizing is derived from node specs. To adjust server behavior, use mysql_parameters.

Quick Reference

The removal parameters are used by mysql-rm.yml:

Variables that appeared on earlier versions of this page — mysql_role, mysql_services, mysql_packages, mysql_data, mysql_port, mysql_replication_*, mysql_*_username — are no longer part of the interface. Do not use them.

Identity

mysql_cluster

Required cluster identity; must match an inventory group containing the member hosts (enforced at preflight). Starts with a letter, digit, or underscore; ._- allowed; up to 63 characters:

mysql_cluster: my-test

Used to derive instance names (my-test-1), the deterministic MGR group UUID, the backup directory (<repo>/my-test/), and the cls monitoring label.

mysql_seq

Required instance sequence. 1 for standalone; a consecutive 1, 2, 3 for HA. Doubles as server_id:

10.10.10.11: { mysql_seq: 1 }

mysql_seq=1 only marks the bootstrap coordinator. The runtime primary is elected, and reruns never move it back.

Credentials

mysql_root_password

Password for root@'localhost', usable only locally (socket or loopback). Single-line, and must not keep the CHANGE_ME prefix:

The default is DBUser.Root:

mysql_root_password: DBUser.Root

Set at first launch. Afterwards, if the live password differs from the declaration, the run fails explicitly rather than resetting it — rotate manually with ALTER USER, then update the inventory.

mysql_monitor_password

Password for dbuser_monitor@'127.0.0.1', used by mysqld_exporter: loopback-only, capped at 3 connections, read-only privileges:

The default is DBUser.Monitor:

mysql_monitor_password: DBUser.Monitor

mysql_cluster_password

Shared password for dbuser_cluster@'%' (TLS-required) and dbuser_backup@'localhost', covering AdminAPI cluster management, Router bootstrap, and XtraBackup:

The default is DBUser.Cluster:

mysql_cluster_password: DBUser.Cluster

On HA clusters this password is embedded in cluster metadata and Router keyrings, so it cannot be rotated by an ordinary rerun: a mismatch between the live value and the declaration is rejected at preflight. Standalone instances have no such binding — update the inventory and rerun.

Business Objects

mysql_databases

Additive database list accepting only name / encoding / collate:

mysql_databases:
  - { name: app }
  - { name: app2, encoding: utf8mb4, collate: utf8mb4_general_ci }

Creates and updates only; removing an entry never drops a database. Syntax and validation rules: Configuration.

mysql_users

Additive user list with fields name / host / password / connlimit / priv:

mysql_users:
  - name: app
    host: '%'
    password: DBUser.App
    connlimit: 20
    priv: { 'app.*': 'ALL PRIVILEGES' }

Grants are applied but never revoked implicitly; platform identities cannot be declared. Syntax and validation rules: Configuration.

mysql_parameters

A dictionary of [mysqld] overrides, rendered at the end of the managed config (last value wins):

mysql_parameters:
  max_connections: 500
  long_query_time: 2
  innodb_buffer_pool_size: 2G
  innodb_print_all_deadlocks: true    # true/false render as ON/OFF

Constraints and behavior:

• Keys match [A-Za-z][A-Za-z0-9_.-]{0,63}; values are single-line scalars. The rendered config still passes mysqld --validate-config, so typos fail at deploy time without touching the running instance;
• Reserved options are rejected (- and _ spellings are treated alike): user, pid_file, server_id, datadir, socket, port, bind_address, mysqlx_bind_address, report_host, gtid_mode, enforce_gtid_consistency, log_bin, relay_log, require_secure_transport, ssl_ca, ssl_cert, ssl_key, plugin_load, plugin_load_add, clone, plugin_clone, mysqlx, and plugin_mysqlx, plus all group_replication_*, plugin_group_replication*, plugin_mysqlx_bind_address, and ssl_* options;
• Applying a change reruns mysql.yml, which orchestrates a rolling restart (secondaries first, primary last) — expect one brief write interruption when the primary restarts;
• Commonly overridden defaults: sql_require_primary_key (default ON), long_query_time (default 1), binlog_expire_logs_seconds (default 7 days), and the memory settings.

A note on dynamic variables: the few replication settings AdminAPI manages via SET PERSIST are authoritative at runtime; on every converge the role pins group_replication_group_seeds back to the declared member list to prevent persisted drift.

Backup

mysql_backup_enabled

Whether the daily backup timer runs (mysql-backup.timer, daily with up to 30 minutes of randomized delay):

mysql_backup_enabled: true

Setting false stops the timer but keeps the backup script and config. Note that if backups were never enabled, the repository directory does not exist and a manual trigger exits immediately.

mysql_backup_repo

The local backup repository — local is the only supported method:

mysql_backup_repo:
  local:
    path: /data/backups/mysql     # absolute path; must not overlap the datadir
    retention: 7                  # keep the last N committed fulls (1-9999)

Layout and restore procedure: Administration.

Monitoring

mysql_exporter_enabled

Whether mysqld_exporter runs and the VictoriaMetrics target is registered:

mysql_exporter_enabled: true

Setting false stops the exporter and converges /infra/targets/mysql/<instance>.yml to an empty list (the file itself is only removed by mysql-rm.yml).

Removal Parameters

mysql_safeguard

Protected-removal safety switch, defaulting to true. You must explicitly set it to false when running mysql-rm.yml, or the role refuses to continue:

./mysql-rm.yml -l my-test -e mysql_safeguard=false -e mysql_rm_confirm=my-test

mysql_rm_confirm

Target-name confirmation string, empty by default. When removing a single member, it must exactly equal the instance name, such as my-test-3; when removing a complete cluster or standalone instance, it must exactly equal mysql_cluster. Both this value and mysql_safeguard=false are required.

Fixed Platform Conventions

The following are fixed or derived by the role — not inventory parameters — listed here for operators’ reference:

3 - Administration

This page covers day-to-day operations for the MYSQL module. The governing principle: declare state in the inventory, converge with the playbook. Most anomalies — a dropped member, drifted AdminAPI state — heal with a single ./mysql.yml -l <cluster> rerun. Only three destructive scenarios (member replacement, physical restore, complete-outage recovery) require the manual runbooks below.

Quick Reference

Status Checks

Run the commands in this page on a cluster member as root: the client configs and secrets under /etc/mysql/pigsty/ are readable by root only. Examples use EL unit names — on Debian/Ubuntu the MySQL service unit is mysql, not mysqld.

On any member, confirm services and topology:

systemctl status mysqld mysqlrouter mysqld_exporter mysql-backup.timer

mysql --defaults-extra-file=/etc/mysql/pigsty/root.cnf -e "
  SELECT MEMBER_HOST, MEMBER_STATE, MEMBER_ROLE, MEMBER_VERSION
  FROM performance_schema.replication_group_members ORDER BY MEMBER_HOST;"

A healthy three-node cluster shows three ONLINE rows with exactly one PRIMARY. For the AdminAPI view:

mysqlsh --js -e '
shell.options.useWizards=false;
var pw = os.loadTextFile("/etc/mysql/pigsty/cluster-password").replace(/[\r\n]+$/, "");
shell.connect({user:"dbuser_cluster", password:pw, host:"127.0.0.1", port:3306,
  "ssl-mode":"VERIFY_CA", "ssl-ca":"/etc/pki/ca.crt"});
print(dba.getCluster().status());'

For a fleet-level view, use the Grafana MySQL Overview dashboard or the derived metric mysql:cls:health (2 healthy / 1 degraded / 0 critical).

Client Access

HA clients connect through any member’s Router, which follows failovers automatically:

# Read-write endpoint (current primary)
mysql -h <any-member> -P 6446 -u app -pDBUser.App --ssl-mode=VERIFY_CA --ssl-ca=/etc/pki/ca.crt app

# Read-only endpoint (round-robin over secondaries)
mysql -h <any-member> -P 6447 -u app -pDBUser.App --ssl-mode=VERIFY_CA --ssl-ca=/etc/pki/ca.crt app

Guidance:

• TLS is enforced server-side and plaintext connections are rejected; the client default PREFERRED mode negotiates encryption automatically, but prefer an explicit VERIFY_CA (JDBC: sslMode=VERIFY_CA) trusting the Pigsty CA;
• There is no VIP/DNS layer. To avoid a single Router node becoming a point of failure, configure a multi-host DSN, e.g. jdbc:mysql://10.10.10.11:6446,10.10.10.12:6446,10.10.10.13:6446/app, or list all members in your application-side load balancer;
• Standalone clusters have no Router — connect to 3306 directly;
• A member that is partitioned or has lost quorum makes its local Router refuse both RW and RO connections (fail-safe): no stale reads through the Router.

Measured expectations: a graceful primary stop interrupts writes for ~3–4 seconds; a primary crash (kill -9) for ~20 seconds with default eviction settings; rolling restarts of secondaries are invisible to clients.

Manage Databases and Users

Edit mysql_databases / mysql_users in the inventory, then converge:

./mysql.yml -l my-test                      # full converge
./mysql.yml -l my-test -t mysql_provision   # business objects only (faster)

In HA, object changes execute on the current primary and replicate out. Declarations are additive: nothing is dropped or revoked implicitly — do those by hand, then update the inventory to match.

Change Cluster Parameters

All tuning goes through mysql_parameters:

mysql_parameters:
  max_connections: 500
  long_query_time: 2

./mysql.yml -l my-test --check    # preview the pending change
./mysql.yml -l my-test            # apply with an orchestrated rolling restart

Rolling-restart semantics (verified by testing):

1. The rendered config passes mysqld --validate-config first — a bad option fails the run without touching the service;
2. Cluster health is checked up front: a degraded cluster (fewer than 3 ONLINE) refuses a rolling restart — repair first, then change;
3. Secondaries restart one at a time, each waiting to return ONLINE; the primary restarts last;
4. The primary restart triggers one automatic failover with a write pause of a few seconds — schedule a change window if that matters.

Standalone instances restart in place.

Switchover

The module does not orchestrate planned switchovers; use AdminAPI when you need one:

mysqlsh --js -e '
shell.options.useWizards=false;
var pw = os.loadTextFile("/etc/mysql/pigsty/cluster-password").replace(/[\r\n]+$/, "");
shell.connect({user:"dbuser_cluster", password:pw, host:"127.0.0.1", port:3306,
  "ssl-mode":"VERIFY_CA", "ssl-ca":"/etc/pki/ca.crt"});
dba.getCluster().setPrimaryInstance("10.10.10.12:3306");   // the new primary
'

Routers follow automatically. Rerun ./mysql.yml -l <cluster> afterwards to confirm convergence — primary placement is runtime state, not declared state, so the playbook will not move it back.

Member Failures and Self-Healing

No action is needed during a failure: after a primary crash, MGR elects a new primary within ~20 seconds and Routers re-route; the crashed member is restarted by systemd and rejoins on its own. Intervene only in these cases:

Matching alerts: MySQLClusterMemberOffline (WARN), MySQLClusterNoPrimary / MySQLClusterQuorumLost (CRIT).

Replace a Failed Member

The replacement contract: the new machine reuses the failed member’s service address (the inventory does not change). Three steps, assuming my-test-3 (10.10.10.13) died:

# 1. Remove the failed member. If the machine is still reachable, use the retirement playbook:
./mysql-rm.yml -l 10.10.10.13 -e mysql_safeguard=false -e mysql_rm_confirm=my-test-3

# 1b. If the machine is truly dead (unreachable over SSH), the playbook cannot run on it;
#     force-remove it from any healthy member via AdminAPI instead:
mysqlsh --js -e '
shell.options.useWizards=false;
var pw = os.loadTextFile("/etc/mysql/pigsty/cluster-password").replace(/[\r\n]+$/, "");
shell.connect({user:"dbuser_cluster", password:pw, host:"127.0.0.1", port:3306,
  "ssl-mode":"VERIFY_CA", "ssl-ca":"/etc/pki/ca.crt"});
dba.getCluster("my-test").removeInstance("10.10.10.13:3306", {force: true});'

# 2. Provision a fresh machine at the same address (reinstall the OS), then manage it
./node.yml -l 10.10.10.13

# 3. Reconverge the complete cluster: the new member is cloned and joined automatically
./mysql.yml -l my-test --check
./mysql.yml -l my-test

Notes:

• Step 1’s real job is evicting the address from cluster metadata — only an address absent from metadata takes the fresh-clone path. The retirement playbook requires a reachable target (an ONLINE SECONDARY or an already-detached member); for a dead machine, use the force removal in 1b instead;
• The replacement must be a truly fresh machine (empty datadir, no leftover Router keyring) — an OS reinstall guarantees that. Half-clean machines are rejected by preflight or the Router bootstrap;
• Clone copies the full dataset; duration scales with data size. The cluster stays available throughout (one primary, one secondary online);
• Changing a member’s address during replacement is not supported, nor is running two nodes long-term.

Retire and Resurrect a Cluster

Retire a whole cluster (stop services, deregister monitoring, keep all data):

./mysql-rm.yml -l my-test -e mysql_safeguard=false -e mysql_rm_confirm=my-test

Retirement writes /var/lib/mysql/.pigsty-mysql-retired on every member, which blocks ordinary mysql.yml reruns so a retired instance cannot be revived by accident. To deliberately resurrect:

ansible my-test -b -a 'rm -f /var/lib/mysql/.pigsty-mysql-retired'
./mysql.yml -l my-test

Two commands suffice for a standalone. HA clusters need one more step: the rerun brings services up, but all three members return with Group Replication OFFLINE (split-brain protection — nobody self-bootstraps) and the playbook exits with the complete-outage error. Continue with steps 3–4 of Recover from a Complete Outage to rebuild quorum.

Actual destruction (removing datadirs, backups, packages) is never done by playbooks — that is a manual decision made after verifying backups.

Manage Backups

systemctl list-timers mysql-backup.timer          # next scheduled run
systemctl start mysql-backup                      # run now (in HA the primary executes, secondaries skip)
journalctl -u mysql-backup --since today          # backup logs

Backup layout, on the current primary’s local disk:

/data/backups/mysql/<cluster>/
├── 20260729T053900Z/          # one prepared full backup (directly restorable)
│   ├── backup.ok              # commit marker: present only for fully successful backups
│   ├── backup.log             # XtraBackup output
│   └── ...                    # InnoDB data files
├── ...                        # last N kept per retention
└── latest -> 20260729T053900Z # atomic pointer to the newest backup

Check backup freshness — on all members for HA, since backups follow the primary:

ansible my-test -b -a 'ls -l /data/backups/mysql/my-test/latest'

Restore from Physical Backup

This runbook restores a standalone instance to its most recent backup. It is destructive: writes after the backup are lost — check the latest timestamp first. Rebuilding an HA cluster works the same way: restore one node as the primary, then let the others rejoin via clone.

# 0. Verify the backup is committed: backup.ok must exist
BK=/data/backups/mysql/my-meta/latest
sudo test -f $BK/backup.ok && sudo cat $BK/backup.ok

# 1. Stop the server; keep the wreckage for forensics until verified
sudo systemctl stop mysqld
sudo mv /var/lib/mysql /var/lib/mysql.destroyed

# 2. Copy the backup back (already prepared — no --prepare step needed)
sudo mkdir -p /var/lib/mysql && sudo chown mysql:mysql /var/lib/mysql && sudo chmod 750 /var/lib/mysql
sudo xtrabackup --copy-back --target-dir=$BK
sudo rm -f /var/lib/mysql/backup.ok /var/lib/mysql/backup.log   # bookkeeping files carried over by copy-back

# 3. Recreate runtime directories the backup does not contain
sudo mkdir -p /var/lib/mysql/binlog /var/lib/mysql/tmp
sudo chown -R mysql:mysql /var/lib/mysql
sudo chmod 750 /var/lib/mysql/binlog /var/lib/mysql/tmp

# 4. Recreate the Pigsty ownership marker (set cluster/instance/topology for your instance)
echo '{"version": 1, "cluster": "my-meta", "instance": "my-meta-1", "topology": "standalone"}' | \
  sudo tee /var/lib/mysql/.pigsty-mysql-initialized > /dev/null
sudo chown mysql:mysql /var/lib/mysql/.pigsty-mysql-initialized
sudo chmod 600 /var/lib/mysql/.pigsty-mysql-initialized

# 5. Restore SELinux context on EL, then start
sudo restorecon -RF /var/lib/mysql 2>/dev/null || true
sudo systemctl start mysqld

# 6. Verify data and GTID position; confirm the playbook still converges
sudo mysql --defaults-extra-file=/etc/mysql/pigsty/root.cnf -e 'SELECT @@gtid_executed; SHOW DATABASES;'
./mysql.yml -l my-meta        # expect a green run (changed=0 or routine items only)

The step-4 marker is Pigsty’s proof of datadir ownership: without it (or with mismatched content), mysql.yml refuses to manage the restored datadir. For HA members, use "topology": "innodb_cluster" and the member’s own instance name.

Recover from a Complete Outage

When all three members are OFFLINE (power loss, cascading failure), MGR deliberately does not rebuild quorum on its own — that is split-brain protection — and mysql.yml refuses with instructions. The procedure:

# 1. Confirm mysqld is running everywhere (systemd usually restarted it) and GR is OFFLINE
ansible my-test -b -a "mysql --defaults-extra-file=/etc/mysql/pigsty/root.cnf -NBe \
  \"SELECT COALESCE((SELECT MEMBER_STATE FROM performance_schema.replication_group_members \
  WHERE MEMBER_ID=@@server_uuid),'OFFLINE')\""

# 2. Find the most advanced member: compare GTID sets, pick the superset (ties: any)
ansible my-test -b -a "mysql --defaults-extra-file=/etc/mysql/pigsty/root.cnf -NBe 'SELECT @@gtid_executed'"

# 3. Reboot the cluster from that member via AdminAPI (replace 10.10.10.12 accordingly)
mysqlsh --js -e '
shell.options.useWizards=false;
var pw = os.loadTextFile("/etc/mysql/pigsty/cluster-password").replace(/[\r\n]+$/, "");
shell.connect({user:"dbuser_cluster", password:pw, host:"10.10.10.12", port:3306,
  "ssl-mode":"VERIFY_CA", "ssl-ca":"/etc/pki/ca.crt"});
var c = dba.rebootClusterFromCompleteOutage("my-test");
print(c.status().defaultReplicaSet.status);'

# 4. Rerun the playbook: members still OFFLINE are rejoined automatically
./mysql.yml -l my-test

Notes:

• Step 3 usually brings every reachable member back at once; stragglers are rejoined by step 4 — no per-node manual work;
• If only a minority of machines survived, complete the reboot first to restore writes, then follow Replace a Failed Member for the rest;
• No writes are possible until step 3 completes (super_read_only); members usually remain readable, though a member that was expelled earlier may sit in offline_mode and refuse ordinary connections;
• The default sql_require_primary_key=ON prevents the PK-less tables that would otherwise block this procedure.

Platform Password Boundaries

Operational boundaries for the three platform passwords (details: Parameters):

• mysql_monitor_password: update the inventory and rerun — rotates cleanly;
• mysql_root_password: implicit resets are refused. Rotate manually — ALTER USER 'root'@'localhost' IDENTIFIED BY '...'; on the primary — then update the inventory and rerun to refresh credential files;
• mysql_cluster_password: on HA clusters, bound to cluster metadata and Router keyrings — ordinary reruns reject rotation, and no automated HA procedure ships yet (standalone instances rotate normally via inventory + rerun). If HA rotation is unavoidable, do it manually via AdminAPI, sync every member’s credential files, then update the inventory.

4 - Playbook

The MYSQL module ships two playbooks: mysql.yml deploys and converges, while mysql-rm.yml performs protected member retirement and cluster teardown. Re-running the former converges toward declared state; the latter is a separate lifecycle operation whose target, backups, and exact confirmation value must be checked again before every real run.

mysql.yml

Runs the full check → install → bootstrap → access → provision → backup → monitor convergence on the selected clusters:

./mysql.yml -l my-test --check     # preflight: validate declarations against the live state
./mysql.yml -l my-test             # converge one cluster (must select all members)
./mysql.yml                        # converge every MySQL cluster in the inventory

Usage rules:

• HA clusters must be selected whole: a -l that covers only some members is rejected at preflight (guarding against topology divergence). Multiple complete clusters, or no -l at all, are fine;
• Idempotent: a converged cluster reruns as changed=0 in seconds. The run immediately after an AdminAPI membership operation (rejoin/clone) may report one convergence changed — the replication seed list being pinned back to the declaration — which is expected;
• Check mode: on fresh nodes it can only preview up to package installation (later steps need the installed platform); on deployed clusters it previews fully;
• Initial three-node deployment takes ~2 minutes: certificates → config and initialization → AdminAPI cluster creation → cloning two members → Router bootstrap on each → business objects → backup and monitoring.

Stages and Tags

mysql
├── mysql_check       # identity, platform, credentials, parameters, datadir ownership (always)
├── mysql_install     # install the fixed MySQL 8.4 package set
├── mysql_bootstrap
│   ├── mysql_cert    # issue and install node TLS leaf certificates
│   ├── mysql_config  # render config (incl. mysql_parameters); initialize empty datadirs only
│   ├── mysql_launch  # start / rolling-restart mysqld; converge root and AdminAPI identities
│   └── mysql_cluster # create or reconcile the InnoDB Cluster (rejoin / clone)
├── mysql_access
│   └── mysql_router  # bootstrap and verify Router on HA members
├── mysql_provision   # converge platform identities and declared databases/users
├── mysql_backup      # install the backup script and daily timer
├── mysql_monitor     # configure the exporter and register monitoring targets
└── mysql_done        # print the instance summary

Common tag-scoped runs:

./mysql.yml -l my-test -t mysql_provision    # business databases and users only
./mysql.yml -l my-test -t mysql_backup       # backup config and timer only
./mysql.yml -l my-test -t mysql_monitor      # exporter and target registration only

For parameter and configuration changes, run the full playbook — they involve the rolling-restart orchestration described below.

Config Changes and Rolling Restarts

The mysql_launch stage orchestrates restarts whenever the config file, certificates, or systemd units change:

1. Health precondition: an HA cluster must have all 3 members ONLINE before a rolling restart; degraded clusters are refused (repair first, then change);
2. Secondaries first: ordered by live runtime role (not mysql_seq), each secondary restarts and must return ONLINE before the next;
3. Primary last: the final restart triggers one automatic failover with a seconds-long write pause.

Standalone instances restart in place. mysqld --validate-config at render time guarantees invalid options fail before any service is touched.

Guardrails

mysql.yml refuses to act in the following situations, with errors that state the reason and the way forward:

These guardrails significantly reduce operational risk, but they are not a guarantee that data can never be lost. Every bypass—such as deleting markers or wiping data directories—must be a deliberate decision made after verifying backups and the exact scope.

mysql-rm.yml

The retirement playbook accepts three scopes, each requiring double confirmation (mysql_safeguard=false plus mysql_rm_confirm exactly matching the target):

# Retire one member of an HA cluster (target must be reachable: an ONLINE SECONDARY or an already-detached member)
./mysql-rm.yml -l 10.10.10.13 --check -e mysql_safeguard=false -e mysql_rm_confirm=my-test-3
./mysql-rm.yml -l 10.10.10.13         -e mysql_safeguard=false -e mysql_rm_confirm=my-test-3

# Retire a whole HA cluster
./mysql-rm.yml -l my-test -e mysql_safeguard=false -e mysql_rm_confirm=my-test

# Retire a standalone instance
./mysql-rm.yml -l my-meta -e mysql_safeguard=false -e mysql_rm_confirm=my-meta

What it does — and does not do:

• Single-member retirement removes an ONLINE SECONDARY via AdminAPI (force: false), or verifies an already-detached member, then stops local services. The removal script runs on the target itself, so the target must be reachable — for a dead machine, use manual force removal instead (Replace a Failed Member). Retiring the primary directly is refused (switch it away first with setPrimaryInstance); so is retiring 2 of 3 members at once;
• Whole-cluster retirement stops the Router and backup timer, stops secondaries before the primary, and deregisters exporters and monitoring targets;
• Every datadir gets the retirement marker .pigsty-mysql-retired, blocking ordinary mysql.yml reruns;
• All data is preserved: datadirs, backups, config, certificates, packages, metadata, and Router identities stay untouched. Actual destruction is a separate, manual, backup-verified decision.

--check previews the complete plan without touching anything.

Playbook Boundaries

The following are out of playbook scope by design; manual procedures live in Administration:

• Planned switchover (setPrimaryInstance);
• Force removal of dead, unreachable members (removeInstance with force: true);
• Quorum rebuild after a complete outage (rebootClusterFromCompleteOutage);
• Physical restore (XtraBackup copy-back runbook);
• Destruction: removing datadirs, backups, or retirement markers;
• Topology changes (1→3, 3→5) and member re-addressing.

5 - Monitoring

The MYSQL module plugs into Pigsty’s observability stack: metrics flow through mysqld_exporter into VictoriaMetrics, error logs flow through Journald/Vector into VictoriaLogs, Grafana ships 5 dashboards, and vmalert loads 68 recording rules plus 27 alert rules.

Collection Architecture

Each MySQL node runs one mysqld_exporter (port 9104) using the least-privilege monitor account (dbuser_monitor@'127.0.0.1'). Deployment writes a file-based service-discovery target on the Infra node:

/infra/targets/mysql/<instance>.yml     # e.g. my-test-1.yml

The VictoriaMetrics mysql scrape job consumes this directory. mysql_exporter_enabled: false converges the target to an empty list; only mysql-rm.yml deletes target files.

Enabled collectors include global status/variables, binlog size, InnoDB metrics, the process list, performance-schema statement digests (top 50), table/index I/O waits, and MGR membership plus replication statistics.

Label Model

All MySQL metrics carry a consistent label set:

Derived rules are named mysql:ins:* (instance level) and mysql:cls:* (cluster level); the full dictionary is in Metrics.

Grafana Dashboards

Cluster health at a glance: mysql:cls:health is 2 (healthy) / 1 (degraded but writable) / 0 (critical or unwritable) — the Overview’s Healthy Clusters stat and Cluster Health timeline are built on it.

The Group Replication dashboard is only meaningful for innodb_cluster topologies; selecting a standalone cluster legitimately shows No data on MGR panels.

Alert Rules

The 27 alert rules are tiered by severity (CRIT / WARN / INFO). The ones to page on:

Availability and Cluster State

Capacity and Performance

Connection pressure (MySQLConnectionsHigh WARN at 80% / MySQLConnectionsCritical CRIT at 95%), replication queues (MySQLGRQueueHigh WARN / MySQLGRQueueCritical CRIT), flow control (MySQLGRFlowControlHigh), InnoDB signals (MySQLBufferPoolWaits, MySQLInnoDBLogWaits, MySQLRedoCapacityHigh, MySQLDeadlocksHigh, MySQLHistoryListLarge), and INFO-level hints for slow queries, disk temp tables, full joins, buffer-pool hit ratio, and recent restarts.

Observed behavior from testing: a primary crash-failover (~20s) only produces pending alerts, no false pages; a genuine complete outage drives ClusterNoPrimary and QuorumLost to firing within 2 minutes.

Log Queries

MySQL error logs are written twice: to /var/log/mysql/error.log and via syslog → Journald → Vector → VictoriaLogs. Entries carry app=mysqld-<instance>, so the dashboard log panels work out of the box, and LogsQL queries are straightforward:

# Recent errors from one instance
curl -s http://<infra>:9428/select/logsql/query \
  -d 'query=app:mysqld-my-test-1 level:err _time:1h'

# All MySQL-related logs for a cluster, including backup runs
curl -s http://<infra>:9428/select/logsql/query \
  -d 'query=job:syslog cls:my-test (app:~"mysqld-" OR unit:mysql-backup) _time:1h | limit 100'

Note the log cls label is the node cluster name (node_cluster) — keep node_cluster aligned with mysql_cluster, as the configuration examples do, so metric and log labels agree.

Known boundaries:

• The slow query log (slow.log, 1s threshold) stays on local disk and is not shipped to VictoriaLogs — inspect it on the instance, or use the statement-digest metrics (mysql:ins:statement_latency and friends);
• Router runtime logs live in /var/log/mysqlrouter/ and are also local-only.

Verify the Pipeline

Self-check every hop after deployment:

# Exporter itself
curl -s http://<member>:9104/metrics | grep -E '^mysql_up '

# VictoriaMetrics scrape and recording rules
curl -s 'http://<infra>:8428/api/v1/query?query=mysql_up'
curl -s 'http://<infra>:8428/api/v1/query?query=mysql:cls:health'

# vmalert rule groups (expect mysql-rules and mysql-alerts)
curl -s 'http://<infra>:8880/api/v1/rules' | grep -o '"name":"mysql-[a-z]*"'

# Log ingestion
curl -s 'http://<infra>:9428/select/logsql/query' -d 'query=app:~"mysqld-" _time:1h | stats by (app) count()'

6 - Metrics

MYSQL metrics come from mysqld_exporter (raw metrics, mysql_ prefix) and vmalert recording rules (mysql:ins:* / mysql:cls:*). Dashboards and alerts are built on the derived metrics; this page is their dictionary.

Common Labels

Every metric carries job=mysql and the identity labels cls / ins / ip / topology (standalone or innodb_cluster). Instance-level derived metrics keep all identity labels; cluster-level metrics aggregate to cls + topology.

Availability

For HA clusters, mysql:cls:health combines quorum, single-primary, and full-membership status; for standalones it is 2 × mysql:cls:up.

Workload

Connections and Sessions

Temp Tables, Scans, and Caches

InnoDB

Group Replication

Instance-level membership flags (value 1 or absent — the series does not exist when the condition is false, which is why alerts use unless):

Cluster-level quorum and topology:

Replication pipeline (certification and apply):

Raw Metric Families

For anything not covered by derived metrics, query the exporter’s raw families:

Browse the complete list with the mysql_ prefix in VictoriaMetrics vmui (/select/vmui).

7 - FAQ

How mature is the MYSQL module?

It is a pilot module aiming for a simple, inexpensive, good-enough MySQL cluster. The four core capabilities — deployment and convergence, HA failover, daily backups, monitoring and alerting — have been tested systematically, including fault injection and complete-outage drills. Destructive recovery flows are deliberately manual, with runbooks provided. It does not aim for PGSQL-module completeness: no PITR, no VIP/DNS access layer, no automatic scaling. Validate and rehearse recovery against your own requirements before serious production use.

Why is MySQL fixed at 8.4? Can I pick a version?

MYSQL is a “fixed platform”, not a general installer: server, client, Shell, Router, and XtraBackup are all pinned to the 8.4 LTS line, which keeps component compatibility and behavior predictable and eliminates a version-matrix testing burden. That is the core trade-off keeping this pilot simple. If you need other versions or deep customization, this module is not the right tool.

Why only 1 or 3 nodes? How do I scale?

Topology is fixed: standalone or three-node single-primary InnoDB Cluster. Preflight rejects other member counts, and the datadir identity marker blocks in-place 1→3 conversion. Dynamic membership would drag in quorum management, Router re-bootstrap, and convergence-path complexity beyond what a pilot should carry.

Scaling paths:

• Vertical: move to bigger machines one at a time via same-address replacement (rolling hardware refresh);
• Standalone → HA: build a new three-node cluster and migrate logically (mysqldump or mysqlsh util.dumpInstance);
• Read scaling: send read-only traffic to 6447, shared by the two secondaries.

Why does CREATE TABLE fail with ERROR 3750 (primary key required)?

The platform defaults to sql_require_primary_key=ON. PK-less tables are read-only under Group Replication and, worse, block AdminAPI cluster rebuilds during disaster recovery — better to fail at CREATE than to explode mid-recovery. Give every table a primary key; if you are onboarding a legacy system that truly cannot change, override:

mysql_parameters: { sql_require_primary_key: false }

Standalone instances keep the same default so they stay HA-portable.

Why is Ubuntu/Debian ARM64 rejected?

Oracle’s APT repository ships no arm64 packages for MySQL 8.4 — nothing Pigsty can work around. On ARM (including VMs on Apple Silicon), use EL 9/10 (Rocky/Alma): Oracle’s YUM repository has full aarch64 support.

Which port should clients use? Is TLS mandatory?

For HA, connect to 6446 (read-write) or 6447 (read-only) on any member; the Router follows failovers. Standalone connects to 3306 directly. TLS is mandatory: the server sets require_secure_transport=ON and rejects plaintext connections (ERROR 3159). The client default PREFERRED mode negotiates TLS automatically (only an explicit DISABLED is refused); prefer VERIFY_CA trusting /etc/pki/ca.crt.

Routers are per-node with no shared VIP — use a multi-host DSN listing all members’ 6446 to survive the loss of any single node.

The primary moved. Will it move back automatically?

No — and it does not need to. mysql_seq=1 is only the bootstrap order; the runtime primary is wherever MGR elected it, which is a fully legitimate state after failovers or rolling restarts. Reruns never relocate the primary. To place it deliberately, use setPrimaryInstance.

A member went down. What do I do?

Usually nothing: systemd restarts a crashed mysqld and the member rejoins on its own (a primary crash completes failover and self-healing in ~20 seconds). If a member stays OFFLINE — after a healed network partition, or a STOP GROUP_REPLICATION — rerun ./mysql.yml -l <cluster> and it will be rejoined. If that fails, read the error: it states the cause and the next step.

All three nodes are down. How do I recover?

This is the one availability scenario requiring manual action (deliberate split-brain protection): run dba.rebootClusterFromCompleteOutage() on the most advanced member, then rerun the playbook to converge the rest. Full steps: Recover from a Complete Outage. The mysql.yml failure message in this state prints exactly these instructions.

Where are backups stored? Can I restore to a point in time?

Backups are daily full physical backups stored under /data/backups/mysql/<cluster>/ on the current primary (after a failover, new backups follow the new primary — check every member when looking for the latest). There is no incremental chain and no binlog archiving, hence no PITR: a standalone’s recovery point is its most recent backup (worst case, one day of writes); an HA cluster’s data safety rests primarily on its three synchronized replicas, with backups as the last line and for full rebuilds. Restore procedure: Restore from Physical Backup. For off-site protection, sync the backup directory yourself.

Will I be alerted if backups fail?

Not yet — a known gap. There is no backup-freshness metric or alert; backup logs are shipped to VictoriaLogs (unit:mysql-backup) and visible on the Instance dashboard’s Router / Backup Logs panel. For important environments, add external log checks and rehearse restores periodically.

Why are some mysql_parameters keys rejected?

Identity (server_id, datadir, ports, …), replication (gtid_mode, log_bin, group_replication_*), and the TLS family are part of the platform’s guarantees. Overriding them would corrupt cluster identity or security floors, so preflight rejects them (- and _ spellings alike). Everything else is allowed and still validated by mysqld --validate-config. Full reserved list: Parameters.

Does changing parameters cause downtime?

One controlled blip: parameter changes trigger an orchestrated rolling restart — secondaries first (invisible to clients), primary last with one automatic failover (measured at ~3–4 seconds of write pause). Degraded clusters refuse rolling restarts so a change can never pile onto an outage. Schedule a window if your workload is failover-sensitive.

How do I change root or platform passwords?

• mysql_monitor_password: update the inventory and rerun — done (the exporter config is refreshed along the way);
• mysql_root_password: implicit resets are refused (protection against silent misconfiguration). Run ALTER USER 'root'@'localhost' ... manually, then update the inventory and rerun;
• mysql_cluster_password: on HA clusters, bound to cluster metadata and Router keyrings — ordinary reruns reject rotation and no automated HA flow ships yet (standalones rotate normally). If unavoidable, rotate manually via AdminAPI and sync each member’s credential files before updating the inventory.

How do I resurrect a retired cluster? What if the marker is deleted by mistake?

mysql-rm.yml keeps all data and writes a retirement marker. To resurrect: delete /var/lib/mysql/.pigsty-mysql-retired on each member and rerun mysql.yml (details) — that suffices for a standalone; an HA cluster additionally needs its quorum rebuilt per Recover from a Complete Outage. The marker only prevents accidental revival; datadir ownership is checked independently through .pigsty-mysql-initialized, so removing the retirement marker can never hand the data to a different cluster.

Why doesn’t conf/mysql.yml match this module?

That template is OpenHalo — a MySQL wire-compatible solution on a PostgreSQL kernel (pg_mode: mysql) — unrelated to this module. The native MySQL template is conf/demo/mysql.yml. Rule of thumb: need real MySQL ecosystem compatibility → this module; running MySQL-protocol apps on PostgreSQL infrastructure → consider OpenHalo.

The playbook fails with no ONLINE member holds the cluster?

That is the complete-outage verdict: no ONLINE member is carrying the cluster (or the members that are online cannot be reached). Follow the printed instructions — see Recover from a Complete Outage. If members are online and you still see this, check connectivity from the seq-1 (coordinator) member — where the reconciliation script runs — to every member’s port 3306, and the TLS trust chain (/etc/pki/ca.crt in place).

Monitoring shows no data / blank dashboards?

Walk the pipeline: curl http://<member>:9104/metrics | grep mysql_up (exporter) → confirm instance files under /infra/targets/mysql/ on the Infra node → query up{job="mysql"} in VictoriaMetrics. Note the Group Replication dashboard legitimately shows No data for standalone clusters. Full self-check commands: Monitoring.