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

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.
• Set production mysql_root_password, mysql_monitor_password, and mysql_cluster_password. Preflight rejects CHANGE_ME placeholders.

Identity

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

One member selects standalone; three select InnoDB Cluster. Preflight rejects other counts. mysql_seq=1 coordinates bootstrap only; it does not set runtime PRIMARY.

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

This creates one writable PRIMARY and two read-only SECONDARY members. It tolerates one node failure. Every member runs Router; any 6446 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. Removing an entry never drops a database; deletion is manual.

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.

Default buffer pool is 25% of node memory, at least 256MB. Redo capacity is half that size, limited to 128MB–4GB. Replica parallelism follows CPU count. Override innodb_buffer_pool_size and other keys through mysql_parameters when needed.

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

• Run a daily full physical XtraBackup backup and prepare it immediately. The result is directly restorable.
• Standalone backs up locally. Every HA timer runs, but only the current PRIMARY performs backup. Others skip.
• Use <path>/<cluster>/<UTC timestamp>/, atomic latest symlinks, and retention-based pruning.
• No incremental chains, binlog archiving, or PITR. Standalone recovery reaches 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 cannot retain a CHANGE_ME prefix. Preflight enforces this.
• 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.

Root owns /etc/mysql/pigsty/, with directory mode 0700 and file mode 0600. It contains secrets and ready-to-use local client configuration:

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

Use conf/demo/mysql.yml for the full template. conf/mysql.yml belongs to OpenHalo, a MySQL-compatible PostgreSQL kernel, and is unrelated to this module.

2 - Parameters

Deployment exposes 11 parameters; removal adds two protected-operation parameters. Versions, ports, directories, charset, TLS paths, and schedules are fixed. Memory sizing follows node specifications. Use mysql_parameters to change server behavior.

Quick Reference

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

Earlier parameters are no longer supported: mysql_role, mysql_services, mysql_packages, mysql_data, mysql_port, mysql_replication_*, and mysql_*_username. 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

This determines instance names (my-test-1), MGR group UUID, backup directory (<repo>/my-test/), and monitoring cls.

mysql_seq

Required sequence: 1 for standalone, consecutive 1, 2, 3 for HA. It also sets server_id:

10.10.10.11: { mysql_seq: 1 }

mysql_seq=1 marks the bootstrap coordinator only. Runtime PRIMARY is elected; reruns preserve its placement.

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

Exporter password for dbuser_monitor@'127.0.0.1'. This read-only, loopback account has a three-connection limit:

The default is DBUser.Monitor:

mysql_monitor_password: DBUser.Monitor

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

Backup

mysql_backup_enabled

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

mysql_backup_enabled: true

false stops the timer but retains script and configuration. If backup was never enabled, the repository directory is absent and manual execution 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

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

Removal Parameters

mysql_safeguard

Removal safety switch, default true. Explicitly set false for mysql-rm.yml; otherwise, removal stops:

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

mysql_rm_confirm

Confirmation string, empty by default. For one member, exactly match its instance name, such as my-test-3. For full-cluster or standalone removal, exactly match mysql_cluster. Both confirmation and mysql_safeguard=false are required.

Fixed Platform Conventions

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

3 - Administration

Declare state in the inventory and apply it with the playbook. Most dropped members or AdminAPI drift recover after ./mysql.yml -l <cluster>. Member replacement, physical restore, and complete-outage recovery require the manual procedures below.

Quick Reference

Status Checks

Run these commands as root on a cluster member. Only root can read /etc/mysql/pigsty/ credentials. Examples use EL service names. Debian/Ubuntu uses 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 has three ONLINE rows and exactly one PRIMARY. For AdminAPI status:

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());'

Use MySQL Overview or mysql:cls:health for fleet status: 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:

• 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.
• There is no VIP/DNS layer. Use a multi-host DSN, such as jdbc:mysql://10.10.10.11:6446,10.10.10.12:6446,10.10.10.13:6446/app, to avoid one Router failure point. Alternatively, list all members in the application load balancer.
• 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 converge
./mysql.yml -l my-test -t mysql_provision   # business objects only (faster)

In HA, changes run on the current primary and replicate to other members. Declarations are additive. Nothing is dropped or revoked implicitly. Perform removals manually, then update inventory.

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

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 the switchover automatically. Rerun ./mysql.yml -l <cluster> to verify convergence. Primary placement is runtime state, so the playbook does not move it back.

Member Failures and Self-Healing

No action is needed for ordinary failover. After a primary crash, MGR elects a replacement in approximately 20 seconds. Routers follow it. Systemd restarts the failed member, which rejoins automatically. Intervene for these cases:

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

Replace a Failed Member

Replacement hardware must reuse the failed member’s service address. Keep inventory unchanged. For failed my-test-3 (10.10.10.13), use three steps:

# 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 removes the address from cluster metadata. Only an absent address qualifies for fresh clone. Retirement requires a reachable ONLINE SECONDARY or detached member. For dead hardware, use force removal in 1b.
• Replacement hardware must be fully clean, with an empty datadir and no Router keyring. OS reinstallation guarantees this. Preflight or Router bootstrap rejects partial cleanup.
• Clone copies the full dataset; time depends on size. One primary and one secondary keep the cluster available.
• Address changes during replacement and long-term two-node operation are unsupported.

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. This blocks ordinary mysql.yml reruns and accidental revival. To restore service deliberately:

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

Two commands suffice for standalone instances. HA needs another step. Reruns start services, but all three Group Replication members remain OFFLINE. No member self-bootstraps, to prevent split-brain. The playbook reports complete outage. Follow steps 3–4 of complete-outage recovery to restore quorum.

Playbooks never destroy datadirs, backups, or packages. Decide on destruction manually 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 HA members because backups follow the primary:

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

Restore from Physical Backup

This procedure restores standalone data to the latest backup. It is destructive: later writes are lost. Check the latest timestamp first. For HA, restore one primary node, then clone it to the other members.

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

If all three members are OFFLINE, MGR does not rebuild quorum automatically, preventing split-brain. This can follow power loss or cascading failure. mysql.yml stops with instructions. Use this 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 restores all reachable members. Step 4 rejoins remaining members without manual per-node work.
• If only a minority survives, complete reboot to restore writes, then replace failed members.
• super_read_only prevents writes until step 3 finishes. Members usually allow reads. Previously expelled members can remain in offline_mode and reject ordinary connections.
• 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

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:

• Complete HA clusters: Partial -l selection fails preflight to prevent topology divergence. Multiple complete clusters or no -l are permitted.
• 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.
• Initial three-node deployment takes approximately two minutes: certificates, configuration/init, AdminAPI creation, two clones, Router bootstrap, 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

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:

These checks reduce risk but cannot guarantee against data loss. Before bypassing checks, verify backups and exact scope. Marker deletion and datadir erasure must be deliberate decisions.

mysql-rm.yml

Retirement accepts three scopes. Each requires mysql_safeguard=false and mysql_rm_confirm exactly matching its 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

Retirement behavior:

• Single-member retirement uses AdminAPI force: false for an ONLINE SECONDARY, or verifies an already detached member, then stops local services. The script runs on the target, which must be reachable. For dead hardware, use manual force removal. Primary retirement is rejected; first switch with setPrimaryInstance. Removing two of three members together is also rejected.
• Full-cluster retirement stops Router and backup timers, then secondaries before the primary. It deregisters exporters and monitoring targets.
• Each datadir receives .pigsty-mysql-retired, blocking ordinary mysql.yml reruns.
• All data remains: datadirs, backups, configuration, certificates, packages, metadata, and Router identities. Destruction requires a separate manual decision after backup verification.

--check previews the full plan without changes.

Playbook Boundaries

These operations remain outside playbook scope. Follow manual Administration procedures:

• 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

MYSQL sends mysqld_exporter metrics to VictoriaMetrics and Journald/Vector error logs to VictoriaLogs. Grafana supplies five dashboards. vmalert loads 68 recording rules and 27 alerts.

Collection Architecture

Each node runs mysqld_exporter on 9104 with least-privilege dbuser_monitor@'127.0.0.1'. Deployment creates a discovery file on the INFRA node:

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

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

Grafana Dashboards

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

The 27 rules use severity CRIT, WARN, or INFO. Use these alerts for paging:

Availability and Cluster State

Capacity and Performance

Other alerts cover:

• Connections: MySQLConnectionsHigh WARN at 80%, MySQLConnectionsCritical CRIT at 95%.
• Replication queues: MySQLGRQueueHigh WARN and MySQLGRQueueCritical CRIT.
• Flow control: MySQLGRFlowControlHigh.
• InnoDB: MySQLBufferPoolWaits, MySQLInnoDBLogWaits, MySQLRedoCapacityHigh, MySQLDeadlocksHigh, and MySQLHistoryListLarge.
• INFO hints: slow queries, disk temporary tables, full joins, buffer-pool hit ratio, and recent restarts.

Tests showed approximately 20-second primary crash failovers produced pending alerts without false pages. Complete outages fired ClusterNoPrimary and QuorumLost within two minutes.

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

mysqld_exporter supplies raw mysql_ metrics. vmalert derives mysql:ins:* and mysql:cls:* metrics for dashboards and alerts. This page defines those metrics.

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

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

Workload

Connections and Sessions

Temp Tables, Scans, and Caches

InnoDB

Group Replication

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

Cluster-level quorum and topology:

Replication pipeline (certification and apply):

Raw Metric Families

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

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

7 - FAQ

How mature is the MYSQL module?

This pilot targets simple, low-cost MySQL clusters. Deployment/convergence, HA failover, daily backups, and monitoring/alerts have systematic tests, including fault injection and complete-outage exercises. Destructive recovery remains manual, with procedures. It has no PITR, VIP/DNS access layer, or automatic scaling; its scope is smaller than PGSQL. Before production, validate your requirements and rehearse recovery.

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.

Each node has its own Router, without a shared VIP. List every member’s 6446 in a multi-host DSN to tolerate 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?

Complete outage requires manual recovery to prevent split-brain. Run dba.rebootClusterFromCompleteOutage() on the most advanced member, then rerun the playbook for the others. See complete-outage recovery. The mysql.yml failure message prints the same instructions.

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

Daily full physical backups reside at /data/backups/mysql/<cluster>/ on the current primary. After failover, new backups use the new primary; compare all members for the latest. There are no incremental chains, binlog archives, or PITR. Standalone can recover only to its latest backup, losing up to one day of writes. HA mainly depends on three synchronized replicas, with backups for final recovery and full rebuilds. See Physical Restore, and synchronize backup directories yourself for off-site protection.

Will I be alerted if backups fail?

Not yet. Backup-freshness metrics and alerts are a known gap. VictoriaLogs receives unit:mysql-backup logs, also visible in the Instance dashboard Router / Backup Logs panel. Add external log checks for important environments and rehearse restoration regularly.

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.

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

mysql-rm.yml retains all data and writes a retirement marker. Remove /var/lib/mysql/.pigsty-mysql-retired on each member and rerun mysql.yml to revive it (details). This suffices for standalone. HA also needs quorum rebuilding. The retirement marker prevents accidental revival. Separate .pigsty-mysql-initialized ownership checks still prevent assigning data to another cluster.

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

That template is OpenHalo, a PostgreSQL kernel with MySQL wire compatibility (pg_mode: mysql). It is unrelated to native MYSQL, whose template is conf/demo/mysql.yml. Use this module for native MySQL ecosystem compatibility. Consider OpenHalo for MySQL-protocol applications on PostgreSQL infrastructure.

The playbook fails with no ONLINE member holds the cluster?

This means no reachable ONLINE member carries the cluster. Follow the printed complete-outage instructions. If members are ONLINE, check coordinator seq-1 connectivity to every member’s port 3306. Reconciliation runs there. Also verify TLS trust and /etc/pki/ca.crt.

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.