Administration

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

Quick Reference

Status Checks

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

On any member, confirm services and topology:

systemctl status mysqld mysqlrouter mysqld_exporter mysql-backup.timer

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

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

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

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

Client Access

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

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

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

Guidance:

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

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

Manage Databases and Users

Edit mysql_databases / mysql_users in the inventory, then converge:

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

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

Change Cluster Parameters

All tuning goes through mysql_parameters:

mysql_parameters:
  max_connections: 500
  long_query_time: 2

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

Rolling-restart semantics (verified by testing):

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

Standalone instances restart in place.

Switchover

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

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

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

Member Failures and Self-Healing

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

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

Replace a Failed Member

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

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

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

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

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

Notes:

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

Retire and Resurrect a Cluster

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

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

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

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

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

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

Manage Backups

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

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

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

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

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

Restore from Physical Backup

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

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

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

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

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

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

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

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

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

Recover from a Complete Outage

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

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

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

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

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

Notes:

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

Platform Password Boundaries

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

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