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.