Administration
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
| Operation | Command |
|---|---|
| Deploy / converge / recover a cluster | ./mysql.yml -l <cluster> |
| Preflight without changes | ./mysql.yml -l <cluster> --check |
| Local root client shortcut | my (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 topology | my -e "SELECT MEMBER_HOST,MEMBER_STATE,MEMBER_ROLE FROM performance_schema.replication_group_members;" |
| Trigger a backup | sudo 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:
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:
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:
Guidance:
- The server requires TLS and rejects plaintext. Client
PREFERREDnegotiates encryption automatically. Prefer explicitVERIFY_CAwith the Pigsty CA; JDBC usessslMode=VERIFY_CA. - Layer 2 VIP or multi-address DSN: if the group’s
NODEmodule 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 JDBCjdbc: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:
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:
Rolling-restart semantics (verified by testing):
- Validate rendered settings with
mysqld --validate-config. Invalid options stop execution without changing the service. - Check health. Fewer than three ONLINE members blocks rolling restarts. Repair before changing configuration.
- Restart secondaries one at a time, waiting for
ONLINEafter each. Restart the primary last. - 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:
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:
| Symptom | Action |
|---|---|
A member remains OFFLINE while its process runs but GR is stopped | Rerun ./mysql.yml -l <cluster> for an incremental rejoin |
A member repeatedly fails to join, logging peers not configured | Same: the converge pins group_replication_group_seeds back to the declared list |
| A member does not return after a network partition | Wait about 1 minute for reconnection, then rerun ./mysql.yml -l <cluster> if needed |
All three members are OFFLINE but hosts are reachable | Rerun ./mysql.yml -l <cluster>; the highest-GTID member runs rebootClusterFromCompleteOutage automatically |
All members are OFFLINE and some hosts are lost | Follow Complete Outage Recovery to restore quorum manually |
| A damaged host is replaced | Prepare 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:
Notes:
- No manual
removeInstanceis needed for replacement: if the new host reuses the same IP and has an empty data directory, a full-clustermysql.ymlrun detects the fresh instance, callscluster.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_dataand 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):
Notes:
- This version no longer writes
.pigsty-mysql-retiredor requiresmysql_rm_confirm; -e mysql_rm_data=falseis supported only when stopping the entire cluster, including a standalone cluster; single-member removal requiresmysql_rm_data=true;mysql-rm.ymlnever deletesmysql_backup_dir(/data/backups/mysql). Administrators must remove old physical backups manually after confirming they are no longer needed.
Manage Backups
Backup directory layout on the current primary’s local disk, retaining the latest mysql_backup_retention: 3 copies by default:
Check backup freshness on all HA members because backups follow the primary:
Backup monitoring and troubleshooting:
- After a successful backup, the script atomically updates
/var/lib/node_exporter/textfile/mysql.promwithmysql_backup_last_success_timestamp_secondsandmysql_backup_size_bytes. VictoriaMetrics derivesmysql:ins:backup_timeandmysql:cls:backup_time; no successful cluster backup for 30 hours triggersMySQLBackupStale(WARN); - On failure, previously committed backups and the
latestsymlink 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.
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:
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:
Notes:
- The cluster remains
super_read_onlyand cannot accept writes until rebuilding completes; - Default
sql_require_primary_key=ONprevents 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 runALTER 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.