Skip to content

This is the multi-page printable view of this section. .

Return to the regular view of this page.

Module: MYSQL

Deploy native MySQL 8.4 LTS as a standalone instance or a three-node InnoDB Cluster, with TLS, daily backups, and full observability.

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.

Current status: Pilot module

MYSQL is a supplementary pilot module for a simple, low-cost MySQL cluster. Its scope is smaller than PGSQL. Core capabilities have been systematically tested: deployment convergence, HA failover, recovery after a complete three-node outage, replacement at the same address, daily backups, and monitoring/alerts. Destructive procedures such as physical backup copy-back remain manual operations. See the runbooks under Administration.


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

ComponentPurposeFixed endpoint
mysqldStandalone server or MGR memberClassic 3306, X Protocol 33060
Group ReplicationThree-member replication and consensus (XCOM)33061
MySQL RouterTopology-aware entry point on every HA memberRW 6446, RO 6447
MySQL ShellAdminAPI cluster lifecycleLocal control plane
XtraBackupDaily full physical backupLocal backup repository
mysqld_exporterServer and MGR metrics9104

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:

ArchSupported systems
x86_64EL 8/9/10, Debian 12/13, Ubuntu 22/24
aarch64EL 9/10

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

PageContent
ConfigurationTopology, identity, data directory, application databases/users, parameter overrides, and backups
Parameters16 public parameters (13 deployment + 3 removal) and fixed platform conventions
AdministrationStatus, shortcuts, client access, configuration changes, automatic recovery, and physical restore runbooks
Playbookmysql.yml and mysql-rm.yml usage, tags, and guardrails
MonitoringDashboards, recording rules, alert rules, log queries
MetricsLabel model and the derived-metric dictionary
FAQPlatform limits, primary-key policy, recovery, troubleshooting

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.

1 - Configuration

Plan MySQL topology and identity; declare databases, users, parameter overrides, and backup policy.

Declare the cluster in inventory. mysql.yml adjusts live state to match. This page covers topology and configuration blocks. See Parameters for the full reference.


Before You Deploy

  • Manage nodes with NODE. Install the shared CA at /etc/pki/ca.crt through node_ca. MYSQL issues leaf certificates only.
  • Include mysql in repositories with node_repo_modules: node,infra,mysql, or cache packages locally with repo_extra_packages: [mysql].
  • Use supported platforms: x86_64 EL 8/9/10, Debian 12/13, Ubuntu 22/24; or aarch64 EL 9/10.
  • Replace all three platform passwords (mysql_root_password, mysql_monitor_password, mysql_cluster_password) with production values. Preflight rejects CHANGE_ME prefixes and INI interpolation expressions of the form %(...)s in mysql_monitor_password.

Identity

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

ParameterLevelDescription
mysql_clusterClusterMust match the inventory group containing its members; also used for the backup subdirectory and monitoring cls label
mysql_seqInstance1 for standalone; contiguous 1..3 for HA; used for instance names and the initial bootstrap coordinator

Topology follows member count: one member is standalone; three members form an InnoDB Cluster. Preflight rejects other counts. mysql_seq=1 identifies only the initial bootstrap coordinator, not the runtime primary. server_id is derived from the member’s IPv4 address as an unsigned 32-bit integer, or the first 32 bits of SHA-1 for a non-IPv4 hostname; no manual assignment is needed.

Instances use {{ mysql_cluster }}-{{ mysql_seq }}, such as my-test-1. Inventory IP or resolvable hostname becomes the advertised MySQL/MGR address. Ordinary reruns cannot change it.


Standalone Instance

The minimal standalone declaration:

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

Standalone has no Router or 6446/6447 endpoints. Connect to 3306. TLS, monitoring, and backups otherwise behave as in HA.


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' }

Deployment creates single-primary MGR: one writable PRIMARY and two read-only SECONDARY members, tolerating one failure. Each member runs Router, so any member’s 6446 reaches the current primary. If the group’s NODE module enables a Layer 2 VIP (vip_enabled: true, vip_address, vip_vrid), it is added to every member’s TLS leaf certificate SANs. Clients can use <vip>:6446 (RW) and <vip>:6447 (RO).

Cluster Convergence and Partial Limits

Full mysql.yml runs involving bootstrap, configuration, or topology coordination must select all members with -l, or omit -l to converge every MySQL cluster. Preflight rejects partial selection. Only independent phases -t mysql_provision (including mysql_db and mysql_user, with the current PRIMARY included), -t mysql_backup, and -t mysql_monitor permit convergence on a surviving subset of a degraded cluster.


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 }
FieldDefaultDescription
namerequiredDatabase name, [A-Za-z0-9_$-]; system schema names are rejected
encodingutf8mb4Character set
collateutf8mb4_0900_ai_ciCollation

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

Convergence is additive. Reruns create missing databases. Removing an entry never drops a database; deletion is manual.

Every table needs a primary key

Default sql_require_primary_key=ON rejects tables without primary keys with ERROR 3750. Such tables become read-only under MGR and block AdminAPI disaster recovery. Give every table a primary key, including an invisible-column key if needed. Override through mysql_parameters only if necessary.


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'

Use 'db.table' grant scopes. Either part can be *, as in '*.*' and 'app.*'. Values are comma-separated privileges. Preflight rejects malformed users, hosts, scopes, or privilege names.

Semantics:

  • Create missing users. Update existing passwords and connection limits.
  • Apply priv grants. Removing mappings does not REVOKE privileges.
  • Do not declare platform identities: root, dbuser_monitor, dbuser_cluster, or dbuser_backup.
  • The server requires TLS. PREFERRED negotiates encryption; DISABLED plaintext connections fail. Prefer explicit VERIFY_CA.

Parameter Overrides

mysql_parameters appends [mysqld] overrides at the end of managed configuration. The last value takes precedence:

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:

  • Start option names with a letter; ._- characters are permitted. Values must be single-line scalars.
  • mysqld --validate-config checks the rendered result. Invalid options stop deployment without changing the running service.
  • Reserved settings cannot be overridden. These include identity/protocol (user, pid_file, server_id, datadir, socket, port, bind_address, mysqlx_bind_address, report_host, mysqlx, …). They also include replication/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_*). The role owns and rejects overrides for these keys.
  • Changes trigger rolling restarts: secondaries first, primary last.

Memory defaults are automatic: innodb_buffer_pool_size uses 50% of physical memory on a dedicated MySQL node and 25% when colocated with PostgreSQL (pg_cluster defined), reserving at least 1 GiB for the system with a 256 MiB lower bound. innodb_redo_log_capacity defaults to half the buffer pool, clamped to 128 MiB–4 GiB. max_connections defaults to 1000, and replication parallelism follows CPU count. Override individual settings with mysql_parameters when needed.


Data Directory and Backup Configuration

mysql_data: /data/mysql               # MySQL data directory, including binlog and tmp subdirectories
mysql_backup_enabled: true            # daily full backups enabled by default
mysql_backup_dir: /data/backups/mysql # local backup root; must not overlap mysql_data
mysql_backup_retention: 3             # retain the latest 3 committed full backups

Data directory conventions:

  • mysql_data defaults to /data/mysql, with binlog/ and tmp/ subdirectories. It cannot reside under reserved paths such as /etc, /usr, /var, or /tmp, except for managing a legacy /var/lib/mysql in place;
  • Debian/Ubuntu nodes automatically render /etc/apparmor.d/local/usr.sbin.mysqld to allow custom mysql_data and /etc/pki/ca.crt;
  • If /var/lib/mysql already contains an initialized database and mysql_data points elsewhere, preflight rejects the configuration to prevent accidentally creating an empty database alongside old data.
Deprecated mysql_backup_repo Fails Preflight

Use the flat parameters mysql_backup_dir and mysql_backup_retention. mysql_check fails if the inventory still defines mysql_backup_repo.

The backup contract (details in Administration):

  • Run a daily XtraBackup full physical backup, immediately followed by --prepare, yielding a directory ready for copy-back. The first mysql_backup phase creates an initial baseline if no backup exists;
  • Standalone nodes back up locally. Every HA member has a timer, but only the current writable PRIMARY executes the backup; other members skip it;
  • Backups live under <mysql_backup_dir>/<mysql_cluster>/<UTC-timestamp>/. The latest symlink atomically points to the newest backup containing backup.ok; prune according to mysql_backup_retention (default 3);
  • Each successful backup writes its timestamp and size to /var/lib/node_exporter/textfile/mysql.prom for MySQLBackupStale monitoring;
  • No incremental chains, binlog archiving, or PITR. Standalone recovery reaches the most recent backup.
Backups follow the primary

After failover, backups use the new primary’s local disk. Compare latest timestamps on all members and select the newest. For off-site protection, synchronize backups yourself, for example with scheduled rclone or rsync.


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 cannot retain a CHANGE_ME prefix. Preflight enforces this.
  • mysql_monitor_password is written to Go INI /etc/mysqld_exporter.cnf using a """...""" literal with $ escaped as $$. INI interpolation substrings of the form %(...)s, such as %(host)s, are forbidden;
  • Ordinary HA reruns cannot rotate mysql_cluster_password. Metadata and Router keyrings bind it, so implicit rotation fails. Standalone rotation works normally.
  • mysql_root_password cannot reset silently. If the live value differs, the task fails instead of replacing it.

Credentials are stored under /etc/mysql/pigsty/, owned by root with directory mode 0700 and file mode 0600. Nodes also install interactive shortcuts through /etc/profile.d/mysql.sh:

my                                                            # equivalent to sudo mysql --defaults-extra-file=/etc/mysql/pigsty/root.cnf
mysh                                                          # HA AdminAPI shortcut; prints dba.getCluster().status() by default
mysql --defaults-extra-file=/etc/mysql/pigsty/cluster.cnf     # cluster session via the local Router (HA members only)
Always Use --no-defaults with Manual mysqlsh Commands

Packaged client configuration can contain options MySQL Shell does not recognize. Explicitly use mysqlsh --no-defaults ... for manual commands. The mysh function and built-in maintenance scripts already include it.


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.6.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 the complete conf/demo/mysql.yml template. conf/fork/halo.yml is for OpenHalo, a PostgreSQL kernel with MySQL protocol compatibility, and is unrelated to the native MYSQL module.

2 - Parameters

The MYSQL module’s 16 public parameters: 13 for deployment, 3 for protected removal, and fixed platform conventions.

The MYSQL deployment role exposes 13 parameters; the removal role adds 3 protected operational parameters, for 16 total. Versions, ports, character set, TLS paths, and timer schedules are fixed by the role. Memory defaults follow node resources and PostgreSQL colocation. Use mysql_parameters to adjust server behavior.


Quick Reference

ParameterLevelDefaultDescription
mysql_clusterClusterrequiredCluster name and identity
mysql_seqInstancerequired1 for standalone; sequential 1..3 for HA
mysql_root_passwordClusterDBUser.RootLocal root password
mysql_monitor_passwordClusterDBUser.MonitorExporter identity password
mysql_cluster_passwordClusterDBUser.ClusterAdminAPI/Router/backup identity password
mysql_databasesCluster[]Additive database declarations
mysql_usersCluster[]Additive user and grant declarations
mysql_parametersCluster/Instance{}[mysqld] option overrides
mysql_dataCluster/Instance/data/mysqlMySQL data directory, including binlog/ and tmp/
mysql_backup_enabledClustertrueDaily full-backup timer
mysql_backup_dirCluster/data/backups/mysqlLocal physical backup root
mysql_backup_retentionCluster3Number of local full backups to retain
mysql_exporter_enabledClustertrueExporter and monitoring target

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

ParameterLevelDefaultDescription
mysql_safeguardGlobal/Cluster/CLItrueDeletion protection; refuses removal by default
mysql_rm_dataGlobal/Cluster/CLItrueRemove data and managed configuration; always retain backups
mysql_rm_pkgGlobal/Cluster/CLIfalseAlso uninstall MySQL / Router / XtraBackup / Exporter packages

Old variables such as mysql_backup_repo (explicitly rejected by preflight), mysql_rm_confirm, mysql_role, mysql_services, mysql_packages, mysql_port, mysql_replication_*, and mysql_*_username are not part of the current public interface. Do not use them.


Identity

mysql_cluster

Required cluster identity, matching the member inventory group. Preflight enforces this. Start with a letter, digit, or underscore. ._- is permitted; maximum length is 63:

mysql_cluster: my-test

Used for instance names (my-test-1), the AdminAPI cluster name, backup subdirectories (<mysql_backup_dir>/my-test/), and the monitoring cls label. AdminAPI generates the MGR Group UUID when creating the cluster, which then manages it internally.

mysql_seq

Required instance sequence. Use 1 for standalone, or contiguous 1, 2, 3 for three nodes:

10.10.10.11: { mysql_seq: 1 }

mysql_seq=1 identifies only the initial bootstrap coordinator. MGR elects the runtime primary, and rerunning the playbook does not move it back. mysql_seq is not server_id, which is derived from the member’s IPv4 address as an unsigned 32-bit integer.


Credentials

mysql_root_password

Local root@'localhost' password for socket or loopback access. Use one line without a CHANGE_ME prefix:

The default is DBUser.Root:

mysql_root_password: DBUser.Root

Set it at initial launch. Later mismatches between live and declared values fail explicitly. Rotate manually with ALTER USER, then update inventory.

mysql_monitor_password

Password for dbuser_monitor@'127.0.0.1', used by mysqld_exporter with read-only privileges, loopback access only, and a maximum of 3 connections. Default: DBUser.Monitor:

mysql_monitor_password: DBUser.Monitor

This password is rendered into Go INI /etc/mysqld_exporter.cnf, with $ escaped as $$. INI interpolation syntax of the form %(...)s, such as %(host)s, is forbidden and rejected by preflight.

mysql_cluster_password

Shared password for TLS-only dbuser_cluster@'%' and local dbuser_backup@'localhost'. It supports AdminAPI, Router bootstrap, and XtraBackup:

The default is DBUser.Cluster:

mysql_cluster_password: DBUser.Cluster

HA metadata and Router keyrings bind this password. Ordinary reruns cannot rotate it; preflight rejects mismatches. Standalone has no such binding. Update inventory and rerun there.


Business Objects

mysql_databases

Additive database list accepting only name / encoding / collate:

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

Create and update only. Removing an entry never drops a database. See Configuration for syntax and validation.

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 apply without implicit revocation. Platform identities are forbidden. See Configuration for syntax and validation.


mysql_parameters

Dictionary of [mysqld] overrides appended to managed configuration. The last value takes precedence:

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 must match [A-Za-z][A-Za-z0-9_.-]{0,63}; values must be single-line scalars. mysqld --validate-config rejects errors before changing the instance.
  • Reserved settings are rejected, treating - and _ alike and stripping loose_, skip_, disable_, enable_, and combinations of these prefixes before validation: 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, plugin_mysqlx. This also includes group_replication_*, plugin_group_replication*, plugin_mysqlx_bind_address, and ssl_*.
  • Rerun mysql.yml to apply changes. It rolls secondaries first, then the primary, with one brief write interruption.
  • Common overrides include sql_require_primary_key (default ON), long_query_time (default 1), binlog_expire_logs_seconds (default seven days), and memory settings.

AdminAPI replication settings written through SET PERSIST control runtime behavior. Each run restores group_replication_group_seeds to declared membership to prevent persisted drift.


Storage and Backup Parameters

mysql_data

MySQL data directory, default /data/mysql, with binlog/ and tmp/ subdirectories:

mysql_data: /data/mysql

Constraints and platform behavior:

  • Must be a multilevel absolute path, neither equal to nor nested with mysql_backup_dir, and outside reserved paths such as /etc, /usr, /var, or /tmp. The sole exception is managing legacy /var/lib/mysql in place;
  • If /var/lib/mysql contains initialized system databases while mysql_data points elsewhere, preflight refuses to start an empty database alongside existing data;
  • Debian/Ubuntu nodes maintain local AppArmor rules in /etc/apparmor.d/local/usr.sbin.mysqld; EL nodes automatically run restorecon -RF.

mysql_backup_enabled

Control the daily mysql-backup.timer, which adds up to 30 minutes of random delay:

mysql_backup_enabled: true

When enabled, if <mysql_backup_dir>/<mysql_cluster>/latest/backup.ok is absent, the mysql_backup phase creates an initial baseline on the current writable primary. Setting this to false disables the timer and removes /var/lib/node_exporter/textfile/mysql.prom, while retaining existing backups and scripts.

mysql_backup_dir

Root directory for local full physical backups, default /data/backups/mysql:

mysql_backup_dir: /data/backups/mysql

Must be a multilevel absolute path outside reserved system locations and must not overlap mysql_data. Backups reside under <mysql_backup_dir>/<mysql_cluster>/; even destructive mysql-rm.yml runs never delete this directory. The old structured mysql_backup_repo parameter is deprecated and rejected by preflight.

mysql_backup_retention

Number of committed full physical backups to retain locally (1..9999), default 3:

mysql_backup_retention: 3

After a new backup completes --prepare and atomically updates latest, the script removes backups beyond the retention count. See Administration for layout and recovery.


Monitoring

mysql_exporter_enabled

Whether mysqld_exporter runs and the VictoriaMetrics target is registered:

mysql_exporter_enabled: true

false stops the exporter and empties /infra/targets/mysql/<instance>.yml. Only mysql-rm.yml removes the file.


Removal Parameters

mysql_safeguard

Deletion safeguard, default true, like VAULT’s vault_safeguard. Explicitly pass -e mysql_safeguard=false to mysql-rm.yml; otherwise preflight refuses removal:

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

mysql_rm_data

Whether removal destroys data and managed configuration, default true:

mysql_rm_data: true
  • true (default): after stopping services and deregistering monitoring, delete mysql_data, /etc/mysql/pigsty, /etc/mysql/pki, pigsty.cnf, Router configuration/keyring, exporter configuration, systemd overrides and backup units, /var/lib/pigsty/mysql-backup, /var/lib/node_exporter/textfile/mysql.prom, and /etc/profile.d/mysql.sh, but always retain mysql_backup_dir;
  • false: supported only for the entire cluster (-l <cluster> -e mysql_safeguard=false -e mysql_rm_data=false). Stop services and deregister monitoring while retaining all data and configuration. Rerun ./mysql.yml -l <cluster> to restart and recover in place. Single-member removal requires mysql_rm_data=true.

mysql_rm_pkg

Whether to also uninstall MySQL Server/Client/Shell/Router, Percona XtraBackup, and mysqld_exporter, default false:

mysql_rm_pkg: false

Fixed Platform Conventions

These settings are fixed or derived. They are not inventory parameters:

ItemValue
VersionsMySQL Server/Client/Shell/Router 8.4 LTS, Percona XtraBackup 8.4
Ports and VIP3306 (Classic), 33060 (X Protocol, loopback only for standalone), 33061 (MGR), 6446/6447 (Router RW/RO, also on <vip>:6446/6447 with a NODE Layer 2 VIP), 9104 (Exporter)
Derived identityserver_id is the member IPv4 as an unsigned 32-bit integer, or the first 32 bits of SHA-1 for non-IPv4 names; AdminAPI generates the MGR Group UUID
ConfigurationEL: /etc/my.cnf.d/pigsty.cnf; Debian/Ubuntu: /etc/mysql/mysql.conf.d/pigsty.cnf, with automatic local AppArmor rules
Service unitsMySQL: mysqld on EL, mysql on Debian/Ubuntu; Router: mysqlrouter; Exporter: mysqld_exporter; backups: mysql-backup.{service,timer}
Credentials and shortcuts/etc/mysql/pigsty/, root-owned with directory 0700 and files 0600; /etc/profile.d/mysql.sh provides my and mysh
Logs and backup stateErrors: /var/log/mysql/error.log, also mirrored to Journald; slow queries: /var/log/mysql/slow.log, threshold 1s; failed backup logs: /var/lib/pigsty/mysql-backup/; backup metrics: /var/lib/node_exporter/textfile/mysql.prom
TLSRequired (require_secure_transport=ON); CA /etc/pki/ca.crt, leaf certificates /etc/mysql/pki/, automatically including node address and optional NODE VIP SANs
Charsetutf8mb4 / utf8mb4_0900_ai_ci
Memory and connectionsBuffer pool: 50% of memory when dedicated, 25% with PG (pg_cluster defined), reserving at least 1 GiB for the system with a 256 MiB lower bound; redo: clamp(buffer pool × 50%, 128 MiB, 4 GiB); max_connections: 1000
ReplicationGTID enforced, sql_require_primary_key=ON, single-primary MGR, BEFORE_ON_PRIMARY_FAILOVER consistency
Data ownership marker<mysql_data>/.pigsty-mysql-initialized records version, cluster, instance, and topology for ownership validation

3 - Administration

MySQL status checks, shortcuts, client access, configuration changes, automatic recovery, member replacement, physical restore, and outage recovery.

This page covers daily MYSQL operations. Declare state in the inventory and converge it with playbooks. Rerunning ./mysql.yml -l <cluster> recovers an offline member, a complete outage with all three hosts reachable, or a dead member replaced at the same IP. Physical backup copy-back and incomplete outages with fewer than three reachable hosts require the manual procedures below.


Quick Reference

OperationCommand
Deploy / converge / recover a cluster./mysql.yml -l <cluster>
Preflight without changes./mysql.yml -l <cluster> --check
Local root client shortcutmy (equivalent to sudo mysql --defaults-extra-file=/etc/mysql/pigsty/root.cnf)
AdminAPI cluster status (HA)mysh (prints dba.getCluster().status() by default)
Inspect MGR topologymy -e "SELECT MEMBER_HOST,MEMBER_STATE,MEMBER_ROLE FROM performance_schema.replication_group_members;"
Trigger a backupsudo systemctl start mysql-backup (only the writable primary executes it in HA)
Remove and destroy one member./mysql-rm.yml -l <IP> -e mysql_safeguard=false (automatically switches primary first if needed)
Stop a cluster while retaining data./mysql-rm.yml -l <cluster> -e mysql_safeguard=false -e mysql_rm_data=false
Destroy the entire cluster./mysql-rm.yml -l <cluster> -e mysql_safeguard=false (always retains the backup directory)

Status Checks and Shortcuts

Installation deploys /etc/profile.d/mysql.sh, which provides my for all topologies and mysh on HA nodes. Credentials are stored under /etc/mysql/pigsty/ and readable only by root. Examples use EL; 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

my -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 and exactly one PRIMARY. Use mysh for the AdminAPI view, or call mysqlsh --no-defaults manually:

mysh

# Equivalent manual command: always pass --no-defaults when invoking mysqlsh directly
sudo mysqlsh --no-defaults --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());'

Use MySQL Overview or mysql:cls:health for fleet status: 2 healthy, 1 degraded, 0 critical.


Client Access

Connect to an HA cluster through any member’s Router or the node Layer 2 VIP. Router follows primary changes automatically:

# Read/write endpoint: any member IP or NODE VIP routes to the current primary
mysql -h <member-or-VIP> -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 <member-or-VIP> -P 6447 -u app -pDBUser.App --ssl-mode=VERIFY_CA --ssl-ca=/etc/pki/ca.crt app

Guidance:

  • The server requires TLS and rejects plaintext. Client PREFERRED negotiates encryption automatically. Prefer explicit VERIFY_CA with the Pigsty CA; JDBC uses sslMode=VERIFY_CA.
  • Layer 2 VIP or multi-address DSN: if the group’s NODE module enables a VIP (vip_enabled: true, vip_address, vip_vrid), it floats among surviving nodes and is automatically added to TLS leaf certificate SANs. Clients can use <vip>:6446 (RW) and <vip>:6447 (RO). Without a VIP, configure a multi-address DSN, such as JDBC jdbc:mysql://10.10.10.11:6446,10.10.10.12:6446,10.10.10.13:6446/app;
  • Standalone instances have no Router. Connect directly to 3306.
  • A partitioned or quorum-lost member makes its Router reject read/write and read-only connections. This prevents stale Router reads.

Measured write interruptions are approximately 3–4 seconds for graceful primary shutdown and 20 seconds for kill -9 with default eviction. Secondary rolling restarts do not interrupt clients.


Manage Databases and Users

Edit mysql_databases / mysql_users in the inventory, then converge:

./mysql.yml -l my-test                      # full convergence; all members must be reachable
./mysql.yml -l my-test -t mysql_provision   # converge databases/users only; mysql_db or mysql_user can also run separately

HA object changes run only on the current primary and propagate through replication. During a single-node failure, -t mysql_provision / -t mysql_db / -t mysql_user can target surviving members with -l, provided the current PRIMARY is included. Declarations are additive: they do not drop databases/users or revoke grants. Perform those operations manually and update the inventory afterward.


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. Validate rendered settings with mysqld --validate-config. Invalid options stop execution without changing the service.
  2. Check health. Fewer than three ONLINE members blocks rolling restarts. Repair before changing configuration.
  3. Restart secondaries one at a time, waiting for ONLINE after each. Restart the primary last.
  4. Primary restart causes one automatic failover and a write pause of a few seconds. Schedule a change window if needed.

Standalone instances restart in place.


Switchover

Perform a planned switchover from any HA member with mysh:

mysh 'dba.getCluster().setPrimaryInstance("10.10.10.12:3306")'

Router follows the switch automatically. Rerun ./mysql.yml -l <cluster> afterward to confirm convergence. Runtime primary placement is not declared state, so the playbook will not switch back to member 1.


Member Failures and Self-Healing

Routine failover needs no manual intervention: MGR elects a new primary within roughly 20 seconds after a primary crash, and Router reroutes traffic. systemd restarts the failed member, which rejoins automatically. For persistent failures:

SymptomAction
A member remains OFFLINE while its process runs but GR is stoppedRerun ./mysql.yml -l <cluster> for an incremental rejoin
A member repeatedly fails to join, logging peers not configuredSame: the converge pins group_replication_group_seeds back to the declared list
A member does not return after a network partitionWait about 1 minute for reconnection, then rerun ./mysql.yml -l <cluster> if needed
All three members are OFFLINE but hosts are reachableRerun ./mysql.yml -l <cluster>; the highest-GTID member runs rebootClusterFromCompleteOutage automatically
All members are OFFLINE and some hosts are lostFollow Complete Outage Recovery to restore quorum manually
A damaged host is replacedPrepare a fresh machine at the same IP, run ./node.yml, then ./mysql.yml -l <cluster>; see Replace a Failed Member

Matching alerts: MySQLClusterDegraded (WARN), MySQLClusterDown(CRIT)、MySQLDown (CRIT).


Replace a Failed Member

Replacement requires reusing the failed machine’s service address, with the inventory unchanged. Assume my-test-3 (10.10.10.13) needs replacement:

# Case A: the old host is permanently unreachable; prepare a fresh machine at the same IP
./node.yml  -l 10.10.10.13          # 1. manage the new node: repositories, shared CA, and monitoring agents
./mysql.yml -l my-test --check      # 2. preflight the entire cluster
./mysql.yml -l my-test              # 3. rescan stale registration, then Clone and rejoin automatically

# Case B: the old host is reachable and scheduled for rebuild; a PRIMARY target switches primary first
./mysql-rm.yml -l 10.10.10.13 -e mysql_safeguard=false   # 1. leave gracefully and clear local data/configuration
./mysql.yml    -l my-test                                # 2. initialize and Clone back into the cluster

Notes:

  • No manual removeInstance is needed for replacement: if the new host reuses the same IP and has an empty data directory, a full-cluster mysql.yml run detects the fresh instance, calls cluster.rescan({removeInstances}) to remove the old UUID registration, and rebuilds it through MySQL Clone;
  • If the old host is permanently lost and will not yet be replaced, remove only its metadata from a survivor with mysh 'dba.getCluster().removeInstance("10.10.10.13:3306", {force: true})';
  • The new host must be fresh, with empty mysql_data and no leftover Router state. Clone time scales with data size; the cluster remains available with one primary and one replica;
  • Changing the member IP during replacement and long-term two-node operation are unsupported.

Stop with Data Retained or Destroy a Cluster

mysql-rm.yml destroys data and managed configuration by default (mysql_rm_data: true). It also supports stopping an entire cluster while retaining data (-e mysql_rm_data=false):

# 1. Stop the entire cluster and deregister monitoring; retain /data/mysql and managed configuration
./mysql-rm.yml -l my-test -e mysql_safeguard=false -e mysql_rm_data=false

# Restart and automatically recover all three nodes; no manual lock-file removal or rebootCluster required
./mysql.yml -l my-test

# 2. Destroy the entire cluster: remove /data/mysql and managed configuration, retain /data/backups/mysql
./mysql-rm.yml -l my-test -e mysql_safeguard=false

# To uninstall packages as well:
./mysql-rm.yml -l my-test -e mysql_safeguard=false -e mysql_rm_pkg=true

Notes:

  • This version no longer writes .pigsty-mysql-retired or requires mysql_rm_confirm;
  • -e mysql_rm_data=false is supported only when stopping the entire cluster, including a standalone cluster; single-member removal requires mysql_rm_data=true;
  • mysql-rm.yml never deletes mysql_backup_dir (/data/backups/mysql). Administrators must remove old physical backups manually after confirming they are no longer needed.

Manage Backups

systemctl list-timers mysql-backup.timer          # next scheduled run
sudo systemctl start mysql-backup                 # trigger manually; only the writable HA primary runs it, replicas skip
journalctl -u mysql-backup --since today          # backup logs

Backup directory layout on the current primary’s local disk, retaining the latest mysql_backup_retention: 3 copies by default:

/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
├── ...                        # retain the latest N backups according to mysql_backup_retention (default 3)
└── latest -> 20260729T053900Z # atomic pointer to the newest backup

Check backup freshness on all HA members because backups follow the primary:

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

Backup monitoring and troubleshooting:

  • After a successful backup, the script atomically updates /var/lib/node_exporter/textfile/mysql.prom with mysql_backup_last_success_timestamp_seconds and mysql_backup_size_bytes. VictoriaMetrics derives mysql:ins:backup_time and mysql:cls:backup_time; no successful cluster backup for 30 hours triggers MySQLBackupStale (WARN);
  • On failure, previously committed backups and the latest symlink remain intact. Detailed XtraBackup logs for the failed run are retained in /var/lib/pigsty/mysql-backup/<UTC-timestamp>.log.

Restore from Physical Backup

The following runbook restores a standalone instance to its latest backup. This is destructive: writes after the backup are lost. Confirm that the latest timestamp is acceptable. If mysql_data is customized, replace /data/mysql below with the actual path. For a full HA rebuild, restore one member first and Clone the remaining empty nodes from it.

# 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 backup timer, any running backup, and MySQL; preserve existing data (use mysql on Debian/Ubuntu)
sudo systemctl stop mysql-backup.timer mysql-backup.service mysqld
sudo mv /data/mysql /data/mysql.destroyed

# 2. Copy back the prepared backup; no --prepare is needed, but always pass --no-defaults and --datadir
sudo install -d -m 0750 -o mysql -g mysql /data/mysql
sudo xtrabackup --no-defaults --copy-back --target-dir="$BK" --datadir=/data/mysql
sudo rm -f /data/mysql/backup.ok /data/mysql/backup.log   # bookkeeping files carried over by copy-back

# 3. Recreate runtime subdirectories not included in the backup
sudo install -d -m 0750 -o mysql -g mysql /data/mysql/binlog /data/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 /data/mysql/.pigsty-mysql-initialized > /dev/null
sudo chmod 0600 /data/mysql/.pigsty-mysql-initialized
sudo chown -R mysql:mysql /data/mysql

# 5. Restore SELinux contexts on EL, then start MySQL and the backup timer
sudo restorecon -RF /data/mysql 2>/dev/null || true
sudo systemctl start mysqld mysql-backup.timer

# 6. Verify data and GTID position; confirm the playbook still converges
my -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 proves datadir ownership. Missing or mismatched contents cause mysql.yml to reject the restored directory. For HA, use "topology": "innodb_cluster" and the member’s own instance name.


Recover from a Complete Outage

When all three members are OFFLINE, for example after a power outage or stopping with data retained, use the appropriate path:

All Three Hosts Reachable: Automatic Recovery

When SSH and mysqld are reachable on all three members, run the playbook against the complete cluster:

./mysql.yml -l my-test

The playbook compares @@gtid_executed sets, selects the most advanced member, runs dba.rebootClusterFromCompleteOutage("my-test") with force: false, and incrementally rejoins remaining OFFLINE members.

Some Hosts Lost: Manual Forced Recovery

If some hosts cannot start because of hardware failure, the playbook refuses automatic rebuilding to prevent split brain: an unreachable host may contain newer transactions. After accepting possible loss of unsynchronized transactions, select the most advanced surviving GTID set and rebuild manually:

# 1. Compare executed GTID sets on all survivors and select the most advanced, assumed here to be 10.10.10.12
ansible 10.10.10.11,10.10.10.12 -b -a "mysql --defaults-extra-file=/etc/mysql/pigsty/root.cnf -NBe 'SELECT @@gtid_executed'"

# 2. Rebuild quorum on that member with mysqlsh --no-defaults
sudo mysqlsh --no-defaults --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", {force: true});
print(c.status().defaultReplicaSet.status);'

# 3. Replace lost hosts at their original IPs, then run the full-cluster playbook to Clone back to three replicas
./node.yml  -l 10.10.10.13
./mysql.yml -l my-test

Notes:

  • The cluster remains super_read_only and cannot accept writes until rebuilding completes;
  • Default sql_require_primary_key=ON prevents tables without primary keys from blocking recovery.

Platform Password Boundaries

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

  • mysql_monitor_password: Change inventory and rerun to rotate.
  • mysql_root_password: Implicit resets are rejected. On the primary, manually run ALTER USER 'root'@'localhost' IDENTIFIED BY '...';. Then update inventory and rerun to refresh credential files.
  • mysql_cluster_password: HA metadata and Router keyrings bind this password. Ordinary reruns reject rotation, and no automated HA rotation exists. If necessary, rotate manually through AdminAPI, synchronize all member credential files, then update inventory. Standalone instances rotate through inventory and rerun.

4 - Playbook

Deploy, converge, tune, and retire MySQL clusters with mysql.yml and mysql-rm.yml.

mysql.yml deploys and adjusts clusters to declared state. Separate mysql-rm.yml retires members or clusters with safeguards. Before every removal, recheck the target, backups, and exact confirmation value.


mysql.yml

Selected clusters follow check → install → bootstrap → access → provision → backup → monitor:

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

  • Full-cluster selection by default, with partial limits for independent phases: full runs and bootstrap, configuration, or topology coordination require all cluster members in -l. Independent -t mysql_provision (including mysql_db and mysql_user, with the runtime PRIMARY included), -t mysql_backup, and -t mysql_monitor can target surviving subsets;
  • Built-in recovery: a full three-node mysql.yml run with all hosts reachable and all members OFFLINE selects the highest GTID and runs rebootClusterFromCompleteOutage with force: false. A fresh replacement at a failed host’s IP triggers rescan to clear stale registration and Clone to rejoin;
  • Idempotent: Converged clusters return changed=0 within seconds. After AdminAPI rejoin/clone, one expected changed can restore declared replication seeds.
  • Check mode: Fresh nodes preview through package installation only; later stages need installed components. Deployed clusters support full previews.
  • First deployment of three nodes takes about 2 minutes: certificates → configuration/init → AdminAPI cluster creation → Clone two replicas → Router bootstrap on each member → application objects → initial backup and monitoring registration.

Stages and Tags

id (node-id)          # derive node identity and hardware facts
mysql
├── mysql_check       # identity, platform, credentials, parameters, datadir ownership (always)
├── mysql_install     # install the fixed MySQL 8.4 packages and /etc/profile.d/mysql.sh
├── mysql_bootstrap
│   ├── mysql_cert    # issue and install TLS leaf certificates, including optional NODE VIP SANs
│   ├── mysql_config  # render mysql_config_file and initialize empty data directories with mysql_init
│   ├── mysql_launch  # start / rolling-restart mysqld; converge root and AdminAPI identities
│   └── mysql_cluster # create, recover, or converge InnoDB Cluster: mysql_cluster_manage / mysql_cluster_verify
├── mysql_access
│   └── mysql_router  # bootstrap and verify Router on HA members
├── mysql_provision   # converge mysql_platform_user, application databases (mysql_db), and users (mysql_user)
├── mysql_backup      # install scripts, render units, enable timer, and create initial backup: mysql_backup_install/config/timer/init
├── mysql_monitor     # configure mysql_exporter and register targets: mysql_register / register / add_metrics; alias monitor
└── mysql_done        # print instance and endpoint summary

Common tagged runs, all supporting -l on surviving subsets of a degraded cluster:

./mysql.yml -l my-test -t mysql_provision    # databases/users only; partial -l must include the current PRIMARY
./mysql.yml -l my-test -t mysql_db           # application databases only
./mysql.yml -l my-test -t mysql_user         # application users and grants only
./mysql.yml -l my-test -t mysql_backup       # backup scripts, timer, and initial backup only
./mysql.yml -l my-test -t mysql_monitor      # exporter and target registration only

Run the full playbook for parameter and configuration changes. They require the rolling-restart workflow below.


Config Changes and Rolling Restarts

mysql_launch orchestrates restarts after configuration, certificate, or Systemd unit changes:

  1. Health: All three HA members must be ONLINE. Degraded clusters reject restarts; repair first.
  2. Secondaries: Use live roles, not mysql_seq. Restart one secondary at a time and wait for ONLINE.
  3. Primary: Restart last. One automatic failover pauses writes for a few seconds.

Standalone restarts in place. Render-time mysqld --validate-config rejects invalid options before service changes.


Guardrails

mysql.yml rejects these conditions and reports the cause and next steps:

Refused scenarioRationale
Partial member selectionBootstrap, configuration, and cluster phases require every member; exceptions: mysql_provision, mysql_db, mysql_user, mysql_backup, mysql_monitor
Invalid topologyMember count must be 1 or 3, with consecutive mysql_seq
Unsupported platformArch/OS outside the support matrix (e.g. Ubuntu ARM64)
Placeholder or invalid passwordsUnreplaced CHANGE_ME prefix, or %(...)s INI interpolation in mysql_monitor_password
Unowned data or legacy database coexistenceMissing/mismatched .pigsty-mysql-initialized, or an existing /var/lib/mysql database while mysql_data points elsewhere
Deprecated backup parameterInventory still defines mysql_backup_repo; use mysql_backup_dir and mysql_backup_retention
Implicit password changesDeclared HA mysql_cluster_password or root password differs from live credentials
Illegal parameter overridesReserved keys, malformed names, or multi-line values in mysql_parameters
Rolling restart while degradedConfiguration-driven rolling restarts require all 3 members ONLINE
Non-fresh clone targetReplacement members must be brand-new machines with empty datadirs
Incomplete outage with unreachable hostsRefuse automatic rebuilding when quorum is down and some members are unreachable; all three reachable permits highest-GTID recovery

These safeguards reduce operational mistakes but do not guarantee zero data loss. Bypassing them, such as removing leftover data directories, requires an explicit operator decision backed by verified backups and an exact target scope.


mysql-rm.yml

Removal requires explicit -l targets (one HA member, a full cluster, or a standalone instance; selecting two of three is rejected) and -e mysql_safeguard=false:

# Remove and destroy one HA member; switch primary first if needed; mysql_rm_data=true is mandatory
./mysql-rm.yml -l 10.10.10.13 --check -e mysql_safeguard=false
./mysql-rm.yml -l 10.10.10.13         -e mysql_safeguard=false

# Destroy the whole cluster: default mysql_rm_data=true removes data/configuration but retains backups
./mysql-rm.yml -l my-test -e mysql_safeguard=false

# Stop the whole cluster with data/configuration retained; rerun mysql.yml to recover
./mysql-rm.yml -l my-test -e mysql_safeguard=false -e mysql_rm_data=false

# Destroy the cluster and uninstall packages
./mysql-rm.yml -l my-meta -e mysql_safeguard=false -e mysql_rm_pkg=true

mysql-rm.yml task tags:

TagDescription
mysql_removeRoot role tag for the complete removal workflow
mysql_safeguardValidate -l scope, mysql_safeguard=false, parameters, and data ownership marker
mysql_leaveSingle-member removal: delegate cluster-leave.js to a survivor, switch primary if needed, then remove with force: false
mysql_deregisterRemove /infra/targets/mysql/.yml on Infra nodes; aliases: deregister, rm_metrics
mysqlStop and disable mysql-backup.timer/service, mysqlrouter, mysqld_exporter, and mysqld
mysql_dataWith mysql_rm_data=true, remove data and managed configuration/certificates/state; -t mysql_data still checks ownership, leaves the cluster, and stops services first
mysql_pkgWith mysql_rm_pkg=true, uninstall MySQL / Router / XtraBackup / Exporter packages

Execution semantics and safeguards:

  • Destroy data/configuration by default, always retain backups: mysql_rm_data: true removes mysql_data, /etc/mysql/pigsty, /etc/mysql/pki, pigsty.cnf, Router state/keyring, exporter configuration, backup units/scripts/state, /var/lib/node_exporter/textfile/mysql.prom, and /etc/profile.d/mysql.sh, but never touches mysql_backup_dir (/data/backups/mysql);
  • Graceful member removal with automatic switchover: AdminAPI runs through a survivor and requires an ONLINE target and mysql_rm_data=true. For a PRIMARY target, it first calls setPrimaryInstance() to transfer leadership, then removeInstance(..., {force: false, timeout: 60}), and cleans the target’s local data/services;
  • Stop the whole cluster with data retained: -e mysql_rm_data=false requires selecting the entire cluster. It stops services and deregisters monitoring without writing retirement locks. Rerun ./mysql.yml -l <cluster> at any time to start services and recover quorum automatically.

Check mode (--check) validates all safeguards and previews actions without changing live state.


Playbook Boundaries

The following operations remain manual; see Administration:

  • Planned switchover to a chosen primary (mysh 'dba.getCluster().setPrimaryInstance(...)');
  • Quorum recovery with fewer than three reachable hosts, or forced removal of a permanently lost member without replacement (removeInstance(..., {force: true})). A fresh replacement at the same IP instead allows a full mysql.yml run to clean registration and Clone automatically;
  • Physical backup copy-back through the XtraBackup --copy-back runbook;
  • Cleanup of historical backups under mysql_backup_dir;
  • Topology changes (1→3, 3→5) and member re-addressing.

5 - Monitoring

MySQL metric collection, Grafana dashboards, alert rules, and log queries.

MYSQL uses Pigsty’s observability stack: mysqld_exporter and node_exporter textfiles feed VictoriaMetrics, Journald/Vector send error logs to VictoriaLogs, Grafana provides 5 dashboards, and vmalert loads 70 recording rules and 8 actionable alerts.


Collection Architecture

Each MySQL node runs mysqld_exporter on 9104, collecting server and MGR metrics with the least-privilege dbuser_monitor@'127.0.0.1' account. mysql-backup also writes the local physical backup timestamp and size to /var/lib/node_exporter/textfile/mysql.prom; node_exporter reports these, and rules join them to cluster metrics by ip. Deployment creates file-discovery targets on Infra nodes:

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

The VictoriaMetrics mysql job reads this directory. mysql_exporter_enabled: false empties the target list. Only mysql-rm.yml removes the file.

Collectors cover global status/variables, binlog size, InnoDB, process lists, top-50 statement digests, table/index I/O waits, MGR membership, and replication statistics.


Label Model

All MySQL metrics carry a consistent label set:

LabelMeaningExample
jobScrape jobmysql
clsCluster namemy-test
insInstance namemy-test-1
ipMember address10.10.10.11
topologyTopology typeinnodb_cluster / standalone

Derived names use mysql:ins:* for instances and mysql:cls:* for clusters. See Metrics.


Grafana Dashboards

DashboardPurpose
MySQL OverviewFleet view: cluster inventory, health, QPS/TPS, active alerts, instance list
MySQL ClusterOne cluster: member states, workload, node resources, cluster logs
MySQL InstanceOne instance: connections, statements, InnoDB, temp tables, locks, logs
MySQL Group ReplicationMGR deep dive: roles, certification/applier queues, flow control, read-only safety, GR logs
MySQL AlertAlert summary and key platform logs

mysql:cls:health reports 2 healthy, 1 degraded but writable, or 0 critical or unwritable. Overview’s Healthy Clusters stat and Cluster Health timeline use it.

Group Replication panels apply only to innodb_cluster. No data is expected for standalone clusters.


Alert Rules

files/victoria/rules/mysql.yml provides 8 focused, actionable alerts. Diagnostic signals such as slow queries, temporary tables, buffer pool hit rate, and redo utilization remain on Grafana dashboards rather than generating noisy alerts:

AlertSeverityDurationExpression and Response
MySQLDownCRIT1mmysql:ins:exporter_up == 1 and on (...) mysql:ins:up < 1: exporter is up but cannot connect to mysqld
MySQLExporterDownWARN2mmysql:ins:exporter_up < 1: cannot scrape mysqld_exporter
MySQLClusterDownCRIT1mmysql:cls:health{topology="innodb_cluster"} < 1: no majority or no unique online primary; rerun mysql.yml after all hosts are reachable
MySQLClusterDegradedWARN5mmysql:cls:health{topology="innodb_cluster"} == 1: writable but a member is offline; rerun mysql.yml or replace at the same address
MySQLReplicationLagWARN10mmysql:ins:gr_certifier_queue > 1000 or mysql:ins:gr_applier_queue > 1000: MGR certification/apply backlog exceeds 1000 transactions
MySQLConnUsageHighWARN5mmysql:ins:connection_usage > 0.80: connections exceed 80% of max_connections
MySQLHistoryListLargeWARN15mmysql:ins:history_list_length > 1000000: long transactions block undo purge, with over one million history entries
MySQLBackupStaleWARN5mtime() - mysql:cls:backup_time > 30 * 3600: no successful local full backup across the cluster for 30 hours, or the first backup failed

Observed behavior: automatic primary failover (about 20 seconds) briefly enters pending without firing a false alarm. A real complete outage or offline member triggers MySQLClusterDown or MySQLClusterDegraded after its duration window.


Log Queries

MySQL writes errors to /var/log/mysql/error.log and through syslog → Journald → Vector → VictoriaLogs. app=mysqld-<instance> labels support dashboard panels and LogsQL queries:

# 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'

Log cls uses the node cluster, node_cluster. Match it to mysql_cluster, as examples do, to align metric and log labels.

Known boundaries:

  • Slow queries: slow.log, with a 1s threshold, remains local. Inspect it on the instance or use statement digests such as mysql:ins:statement_latency.
  • Router runtime logs: /var/log/mysqlrouter/ also remains local.

Verify the Pipeline

Verify every pipeline stage 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

Label model, derived-metric dictionary, and raw metric families of the MYSQL module.

MYSQL metrics come from mysqld_exporter (mysql_ raw metrics), node_exporter backup textfiles (/var/lib/node_exporter/textfile/mysql.prom), and vmalert recording rules (mysql:ins:* / mysql:cls:*). Dashboards and alerts primarily use recording rules; this page is their complete dictionary.


files/victoria/rules/mysql.yml provides 70 module recording rules, all listed here. mysql_exporter_up = up{job="mysql"} additionally comes from Infra’s shared liveness recording rules. These recorded metrics do not appear at the raw mysqld_exporter endpoint. mysql_up is the exporter’s database connection probe result.

Common Labels

Metrics carry job=mysql, cls, ins, ip, and topology (standalone or innodb_cluster). Instance-derived metrics retain all identity labels. Cluster metrics aggregate to cls and topology.


Availability

MetricMeaning
mysql:ins:exporter_upScrape success (transport health)
mysql:ins:upMySQL connection probe success (database health)
mysql:ins:uptimeInstance uptime in seconds
mysql:cls:instancesDeclared instance count
mysql:cls:upOnline instance count
mysql:cls:healthCluster health: 2 healthy / 1 degraded-writable / 0 critical

For HA, mysql:cls:health combines quorum, single-primary, and full-membership state. For standalone, it equals 2 × mysql:cls:up.


Workload

MetricMeaning
mysql:ins:qpsQuestions per second
mysql:ins:tpsTransactions per second (commit + rollback)
mysql:ins:read_qps / mysql:ins:write_qpsRead-class / write-class command rate
mysql:ins:row_opsInnoDB row operations (read/insert/update/delete dimensions)
mysql:ins:statement_ratePerformance-schema statement rate
mysql:ins:statement_latencyMean statement latency (seconds)
mysql:ins:rows_examined_per_queryAverage rows examined per query
mysql:ins:statement_errorsStatement error rate
mysql:ins:slow_queries / mysql:ins:slow_query_ratioSlow query rate and ratio
mysql:ins:no_index_queriesRate of queries using no index

Connections and Sessions

MetricMeaning
mysql:ins:connectionsCurrent connections (Threads_connected)
mysql:ins:connection_usageConnections / max_connections
mysql:ins:connection_rateNew connection rate
mysql:ins:threads_running / mysql:ins:threads_cachedActive / cached threads
mysql:ins:aborted_connects / mysql:ins:aborted_clientsFailed handshakes / abnormal disconnects
mysql:ins:connection_errorsTotal connection error rate
mysql:ins:rx_bytes / mysql:ins:tx_bytesNetwork receive / transmit rate

Temp Tables, Scans, and Caches

MetricMeaning
mysql:ins:tmp_tables / mysql:ins:tmp_disk_tablesIn-memory / on-disk temp table creation rate
mysql:ins:tmp_disk_ratioShare of temp tables spilling to disk
mysql:ins:full_joins / mysql:ins:full_scansIndex-less join / full scan rate
mysql:ins:sort_merge_passesSort merge passes (undersized sort buffer signal)
mysql:ins:table_open_cache_hit_ratioTable open cache hit ratio
mysql:ins:open_files_usageOpen file usage ratio

InnoDB

MetricMeaning
mysql:ins:buffer_pool_hit_ratioBuffer pool hit ratio
mysql:ins:buffer_pool_usage / mysql:ins:buffer_pool_dirty_ratioBuffer pool usage / dirty page ratio
mysql:ins:buffer_pool_waitsFree-page wait rate (memory pressure signal)
mysql:ins:data_read_bytes / mysql:ins:data_write_bytesData file read / write throughput
mysql:ins:data_reads / mysql:ins:data_writes / mysql:ins:data_fsyncsData file I/O and fsync rates
mysql:ins:redo_bytesRedo write throughput
mysql:ins:redo_utilizationRedo capacity utilization (checkpoint lag)
mysql:ins:log_waitsRedo buffer wait rate
mysql:ins:row_lock_waits / mysql:ins:row_lock_timeRow lock wait rate / time
mysql:ins:deadlocksDeadlock rate
mysql:ins:history_list_lengthPurge lag (history list length)
mysql:ins:binlog_bytesTotal on-disk binlog size (bytes)

Group Replication

Instance membership flags have value 1 or are absent. False conditions produce no series, so alerts use unless:

MetricMeaning
mysql:ins:gr_memberInstance is in any MGR member state
mysql:ins:gr_onlineInstance is ONLINE
mysql:ins:gr_primary / mysql:ins:gr_secondaryInstance is the ONLINE primary / a secondary

Cluster-level quorum and topology:

MetricMeaning
mysql:cls:gr_online_membersONLINE member count
mysql:cls:gr_primary_membersONLINE primary count
mysql:cls:gr_quorumMajority held (0/1)
mysql:cls:gr_single_primaryExactly one primary (0/1)

Replication pipeline (certification and apply):

MetricMeaning
mysql:ins:gr_certifier_queue / mysql:ins:gr_applier_queueTransactions backed up in certification / applier queues
mysql:ins:gr_certifier_queue_ratio / mysql:ins:gr_applier_queue_ratioQueue depth relative to flow-control thresholds
mysql:ins:gr_checked_rate / mysql:ins:gr_applied_rateCertification / apply throughput
mysql:ins:gr_conflict_rateCertification conflict rate (should be 0 under single-primary)

Backups

mysql-backup writes /var/lib/node_exporter/textfile/mysql.prom, then metrics are joined to instances and clusters by ip. Since backups follow the runtime primary, freshness is evaluated at cluster level, for example time() - mysql:cls:backup_time:

MetricMeaning
mysql:ins:backup_timeUnix timestamp in seconds of this node’s latest successful local full physical backup
mysql:cls:backup_timeUnix timestamp in seconds of the latest successful local full physical backup across all cluster members

Raw Metric Families

For data not covered by derived metrics, query raw exporter families:

PrefixContent
mysql_up / upDatabase probe / scrape status
mysql_global_status_*Full SHOW GLOBAL STATUS counters
mysql_global_variables_*Key system variables (e.g. max_connections)
mysql_perf_schema_events_statements_*Statement digests (top 50 by digest)
mysql_perf_schema_table_io_waits_* / ..._index_io_waits_*Table / index I/O waits
mysql_perf_schema_replication_group_member_infoMGR membership (member_state / member_role dimensions)
mysql_perf_schema_transactions_* / mysql_perf_schema_conflicts_detected_totalMGR certification, applier queues, and conflicts
mysql_binlog_*Binlog file count and size
mysql_info_schema_processlist_*Session distribution by state
mysql_backup_last_success_timestamp_seconds / mysql_backup_size_bytesLatest successful physical backup timestamp and size in bytes (node_exporter textfile)

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

7 - FAQ

Frequently asked questions and troubleshooting for the Pigsty MySQL pilot module.

How mature is the MYSQL module?

This pilot module targets simple, affordable MySQL clusters. Deployment convergence, HA failover, complete three-node outage recovery, replacement at the same address, daily physical backups, and monitoring/alerts have been systematically tested. Physical backup copy-back remains manual, with a runbook. It does not aim for PGSQL-level completeness: there is no PITR, DNS/HAProxy access layer, or online topology scaling. Local Router and the NODE Layer 2 VIP are supported. Validate the module against your requirements and rehearse recovery before production use.

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

The fixed platform pins server, client, Shell, Router, and XtraBackup to 8.4 LTS. This keeps compatibility predictable and avoids a broad version test matrix. That tradeoff keeps the pilot simple. Use another tool for other versions or extensive customization.

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

Topology is standalone or three-node single-primary InnoDB Cluster. Preflight rejects other counts, and datadir identity blocks in-place 1→3 conversion. Dynamic membership would add quorum management, Router re-bootstrap, and convergence complexity beyond this pilot’s scope.

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)?

sql_require_primary_key=ON is the default. Tables without primary keys are read-only under Group Replication and block AdminAPI disaster recovery. Rejecting them at creation avoids recovery failures. Give every table a primary key. Override only for a legacy system that cannot change:

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 APT provides no MySQL 8.4 arm64 packages. On ARM, including Apple Silicon VMs, use EL 9/10 (Rocky/Alma). Oracle YUM supports aarch64 fully.

Which port should clients use? Is TLS mandatory?

For HA, use any member’s Router on 6446 for read/write or 6447 for read-only. Routers follow failover. Standalone clients use 3306. The server requires TLS through require_secure_transport=ON; plaintext fails with ERROR 3159. Default PREFERRED negotiates TLS, while explicit DISABLED fails. Prefer VERIFY_CA with /etc/pki/ca.crt.

Router runs locally on every node. For a single floating endpoint, enable the group’s NODE Layer 2 VIP (vip_enabled: true, vip_address, vip_vrid). The VIP is included in TLS leaf certificate SANs and exposes <vip>:6446/6447. Without a VIP, configure a multi-address DSN with all three members’ 6446 endpoints to tolerate a node failure.

The primary moved. Will it move back automatically?

No. mysql_seq=1 sets bootstrap order only. MGR can elect any runtime primary after failover or rolling restart. Reruns preserve this placement. Use setPrimaryInstance for deliberate placement.

A member went down. What do I do?

Usually no action is needed. Systemd restarts mysqld, which rejoins automatically. Primary failover and recovery take approximately 20 seconds. If a member remains OFFLINE after network recovery or STOP GROUP_REPLICATION, rerun ./mysql.yml -l <cluster>. If rejoin fails, follow the reported cause and next step.

All three nodes are down. How do I recover?

Once networking and mysqld are reachable on all three hosts, run ./mysql.yml -l <cluster>. The playbook compares GTID sets, runs dba.rebootClusterFromCompleteOutage() with force: false on the most advanced member, and rejoins the others. If a complete outage also involves lost hosts, leaving fewer than three reachable members, automatic recovery is refused to prevent split brain. Follow Complete Outage Recovery on the most advanced survivor.

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

Backups are daily full physical backups on the current primary, under mysql_backup_dir/<cluster>/ (default /data/backups/mysql/<cluster>/), retaining the latest mysql_backup_retention: 3 copies. After failover, new backups follow the new primary, so inspect all members. There are no incrementals or binlog archives, hence no PITR. A standalone instance restores to its latest backup, potentially losing a day’s writes. HA relies primarily on three-copy synchronous replication; backups provide fallback and full-cluster rebuilds. See Restore Physical Backup. Replicate backup directories offsite separately.

Will I be alerted if backups fail?

Yes. mysql-backup writes the latest successful backup timestamp and size to /var/lib/node_exporter/textfile/mysql.prom, using 0 if the first backup fails. VictoriaMetrics derives mysql:cls:backup_time; no successful backup across the cluster for 30 hours triggers MySQLBackupStale (WARN). Detailed failed-run XtraBackup logs are under /var/lib/pigsty/mysql-backup/, and service logs are available in VictoriaLogs as unit:mysql-backup.

Why are some mysql_parameters keys rejected?

The platform owns identity (server_id, datadir, ports, …), replication (gtid_mode, log_bin, group_replication_*), and TLS settings. Overrides could break identity or security guarantees. Preflight rejects both - and _ spellings. Other options are permitted and checked by mysqld --validate-config. See the reserved list.

Does changing parameters cause downtime?

Configuration changes roll secondaries first, without client interruption, then restart the primary. One automatic failover pauses writes for approximately 3–4 seconds in tests. Degraded clusters reject rolling restarts to avoid adding changes during an outage. Schedule a window for failover-sensitive workloads.

How do I change root or platform passwords?

  • mysql_monitor_password: Update inventory and rerun. Exporter configuration updates too.
  • mysql_root_password: Implicit resets fail to prevent silent misconfiguration. Run ALTER USER 'root'@'localhost' ... manually, then update inventory and rerun.
  • mysql_cluster_password: HA metadata and Router keyrings bind it. Ordinary reruns reject rotation, and no automated HA procedure exists. Standalone rotation works normally. If necessary in HA, use AdminAPI, synchronize member credential files, then update inventory.

Does mysql-rm.yml Delete Data? How Can I Stop and Restart with Data Retained?

mysql-rm.yml defaults to mysql_rm_data: true. With -e mysql_safeguard=false, it deletes mysql_data (default /data/mysql) and managed configuration, but always retains mysql_backup_dir (/data/backups/mysql). To stop temporarily with data intact, select the entire cluster and add -e mysql_rm_data=false; see Stop with Data Retained or Destroy a Cluster. This version no longer writes .pigsty-mysql-retired. Rerun ./mysql.yml -l <cluster> to restart in place and recover quorum automatically.

How Does the OpenHalo Template Relate to This Module?

conf/fork/halo.yml is the OpenHalo template: MySQL wire protocol compatibility on PostgreSQL (pg_mode: mysql), unrelated to native MYSQL. This module uses conf/demo/mysql.yml. Use native MYSQL for the MySQL 8.4 ecosystem and InnoDB Cluster; evaluate OpenHalo when selected MySQL protocol applications need to run on PGSQL infrastructure.

Why Does the Playbook Refuse Complete-Outage Recovery?

If there are no ONLINE members and some declared hosts are unreachable, the playbook cannot determine whether they contain newer GTID transactions, so it refuses automatic rebootClusterFromCompleteOutage. Restore connectivity and rerun ./mysql.yml -l <cluster> when possible; with all three reachable, it selects the highest GTID automatically. If a host is permanently lost, follow Complete Outage Recovery to force rebuilding manually on the most advanced survivor.

Monitoring shows no data / blank dashboards?

First run curl http://<member>:9104/metrics | grep mysql_up. Then check INFRA files in /infra/targets/mysql/ and query up{job="mysql"} in VictoriaMetrics. Standalone Group Replication panels correctly show No data. See Monitoring for full checks.