This is the multi-page printable view of this section. .
Module: MYSQL
- 1: Configuration
- 2: Parameters
- 3: Administration
- 4: Playbook
- 5: Monitoring
- 6: Metrics
- 7: FAQ
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.
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, RO6447), with optional reuse of theNODELayer 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_databasesandmysql_usersdeclarations that never delete data implicitly. mysql_parametersoverrides, includingmax_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_exporterand 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.ymlto 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:#fffIn three-node clusters, mysql_seq=1 coordinates bootstrap only. Runtime PRIMARY is elected. Reruns never force it back to node 1.
Components and Ports
| Component | Purpose | Fixed endpoint |
|---|---|---|
mysqld | Standalone server or MGR member | Classic 3306, X Protocol 33060 |
| Group Replication | Three-member replication and consensus (XCOM) | 33061 |
| MySQL Router | Topology-aware entry point on every HA member | RW 6446, RO 6447 |
| MySQL Shell | AdminAPI cluster lifecycle | Local control plane |
| XtraBackup | Daily full physical backup | Local backup repository |
mysqld_exporter | Server and MGR metrics | 9104 |
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:
| Arch | Supported systems |
|---|---|
x86_64 | EL 8/9/10, Debian 12/13, Ubuntu 22/24 |
aarch64 | EL 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 withmysql_data. Memory settings are derived from node resources and PostgreSQL colocation; override key settings withmysql_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
NODELayer 2 VIP (<vip>:6446/6447). No DNS / HAProxy access layer is included.
Documentation
| Page | Content |
|---|---|
| Configuration | Topology, identity, data directory, application databases/users, parameter overrides, and backups |
| Parameters | 16 public parameters (13 deployment + 3 removal) and fixed platform conventions |
| Administration | Status, shortcuts, client access, configuration changes, automatic recovery, and physical restore runbooks |
| Playbook | mysql.yml and mysql-rm.yml usage, tags, and guardrails |
| Monitoring | Dashboards, recording rules, alert rules, log queries |
| Metrics | Label model and the derived-metric dictionary |
| FAQ | Platform limits, primary-key policy, recovery, troubleshooting |
Quick Start
Declare a cluster in the inventory (full template: conf/demo/mysql.yml):
After NODE provisioning, deploy:
Then open the Grafana MySQL Overview dashboard to inspect the cluster.
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.crtthroughnode_ca. MYSQL issues leaf certificates only. - Include
mysqlin repositories withnode_repo_modules: node,infra,mysql, or cache packages locally withrepo_extra_packages: [mysql]. - Use supported platforms:
x86_64EL 8/9/10, Debian 12/13, Ubuntu 22/24; oraarch64EL 9/10. - Replace all three platform passwords (
mysql_root_password,mysql_monitor_password,mysql_cluster_password) with production values. Preflight rejectsCHANGE_MEprefixes and INI interpolation expressions of the form%(...)sinmysql_monitor_password.
Identity
Each cluster is an inventory group with two required identity parameters:
| Parameter | Level | Description |
|---|---|---|
mysql_cluster | Cluster | Must match the inventory group containing its members; also used for the backup subdirectory and monitoring cls label |
mysql_seq | Instance | 1 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:
Standalone has no Router or 6446/6447 endpoints. Connect to 3306. TLS, monitoring, and backups otherwise behave as in HA.
Three-Node InnoDB Cluster
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).
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:
| Field | Default | Description |
|---|---|---|
name | required | Database name, [A-Za-z0-9_$-]; system schema names are rejected |
encoding | utf8mb4 | Character set |
collate | utf8mb4_0900_ai_ci | Collation |
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.
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:
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
privgrants. Removing mappings does not REVOKE privileges. - Do not declare platform identities:
root,dbuser_monitor,dbuser_cluster, ordbuser_backup. - The server requires TLS.
PREFERREDnegotiates encryption;DISABLEDplaintext connections fail. Prefer explicitVERIFY_CA.
Parameter Overrides
mysql_parameters appends [mysqld] overrides at the end of managed configuration. The last value takes precedence:
Rules and safety:
- Start option names with a letter;
._-characters are permitted. Values must be single-line scalars. mysqld --validate-configchecks 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
Data directory conventions:
mysql_datadefaults to/data/mysql, withbinlog/andtmp/subdirectories. It cannot reside under reserved paths such as/etc,/usr,/var, or/tmp, except for managing a legacy/var/lib/mysqlin place;- Debian/Ubuntu nodes automatically render
/etc/apparmor.d/local/usr.sbin.mysqldto allow custommysql_dataand/etc/pki/ca.crt; - If
/var/lib/mysqlalready contains an initialized database andmysql_datapoints elsewhere, preflight rejects the configuration to prevent accidentally creating an empty database alongside old data.
mysql_backup_repo Fails PreflightUse 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 firstmysql_backupphase 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>/. Thelatestsymlink atomically points to the newest backup containingbackup.ok; prune according tomysql_backup_retention(default3); - Each successful backup writes its timestamp and size to
/var/lib/node_exporter/textfile/mysql.promforMySQLBackupStalemonitoring; - No incremental chains, binlog archiving, or PITR. Standalone recovery reaches the most recent backup.
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
Credential lifecycle rules:
- Passwords must be single-line and cannot retain a
CHANGE_MEprefix. Preflight enforces this. mysql_monitor_passwordis written to Go INI/etc/mysqld_exporter.cnfusing 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_passwordcannot 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:
--no-defaults with Manual mysqlsh CommandsPackaged 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:
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 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
| Parameter | Level | Default | Description |
|---|---|---|---|
mysql_cluster | Cluster | required | Cluster name and identity |
mysql_seq | Instance | required | 1 for standalone; sequential 1..3 for HA |
mysql_root_password | Cluster | DBUser.Root | Local root password |
mysql_monitor_password | Cluster | DBUser.Monitor | Exporter identity password |
mysql_cluster_password | Cluster | DBUser.Cluster | AdminAPI/Router/backup identity password |
mysql_databases | Cluster | [] | Additive database declarations |
mysql_users | Cluster | [] | Additive user and grant declarations |
mysql_parameters | Cluster/Instance | {} | [mysqld] option overrides |
mysql_data | Cluster/Instance | /data/mysql | MySQL data directory, including binlog/ and tmp/ |
mysql_backup_enabled | Cluster | true | Daily full-backup timer |
mysql_backup_dir | Cluster | /data/backups/mysql | Local physical backup root |
mysql_backup_retention | Cluster | 3 | Number of local full backups to retain |
mysql_exporter_enabled | Cluster | true | Exporter and monitoring target |
The removal parameters are used by mysql-rm.yml:
| Parameter | Level | Default | Description |
|---|---|---|---|
mysql_safeguard | Global/Cluster/CLI | true | Deletion protection; refuses removal by default |
mysql_rm_data | Global/Cluster/CLI | true | Remove data and managed configuration; always retain backups |
mysql_rm_pkg | Global/Cluster/CLI | false | Also 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:
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:
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:
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:
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:
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:
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:
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:
Constraints and behavior:
- Keys must match
[A-Za-z][A-Za-z0-9_.-]{0,63}; values must be single-line scalars.mysqld --validate-configrejects errors before changing the instance. - Reserved settings are rejected, treating
-and_alike and strippingloose_,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 includesgroup_replication_*,plugin_group_replication*,plugin_mysqlx_bind_address, andssl_*. - Rerun
mysql.ymlto apply changes. It rolls secondaries first, then the primary, with one brief write interruption. - Common overrides include
sql_require_primary_key(defaultON),long_query_time(default1),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:
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/mysqlin place; - If
/var/lib/mysqlcontains initialized system databases whilemysql_datapoints 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 runrestorecon -RF.
mysql_backup_enabled
Control the daily mysql-backup.timer, which adds up to 30 minutes of random delay:
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:
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:
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:
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_data
Whether removal destroys data and managed configuration, default true:
true(default): after stopping services and deregistering monitoring, deletemysql_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 retainmysql_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 requiresmysql_rm_data=true.
mysql_rm_pkg
Whether to also uninstall MySQL Server/Client/Shell/Router, Percona XtraBackup, and mysqld_exporter, default false:
Fixed Platform Conventions
These settings are fixed or derived. They are not inventory parameters:
| Item | Value |
|---|---|
| Versions | MySQL Server/Client/Shell/Router 8.4 LTS, Percona XtraBackup 8.4 |
| Ports and VIP | 3306 (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 identity | server_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 |
| Configuration | EL: /etc/my.cnf.d/pigsty.cnf; Debian/Ubuntu: /etc/mysql/mysql.conf.d/pigsty.cnf, with automatic local AppArmor rules |
| Service units | MySQL: 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 state | Errors: /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 |
| TLS | Required (require_secure_transport=ON); CA /etc/pki/ca.crt, leaf certificates /etc/mysql/pki/, automatically including node address and optional NODE VIP SANs |
| Charset | utf8mb4 / utf8mb4_0900_ai_ci |
| Memory and connections | Buffer 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 |
| Replication | GTID 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
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.
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:
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(includingmysql_dbandmysql_user, with the runtime PRIMARY included),-t mysql_backup, and-t mysql_monitorcan target surviving subsets; - Built-in recovery: a full three-node
mysql.ymlrun with all hosts reachable and all membersOFFLINEselects the highest GTID and runsrebootClusterFromCompleteOutagewithforce: false. A fresh replacement at a failed host’s IP triggersrescanto clear stale registration and Clone to rejoin; - Idempotent: Converged clusters return
changed=0within seconds. After AdminAPI rejoin/clone, one expectedchangedcan 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
Common tagged runs, all supporting -l on surviving subsets of a degraded cluster:
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:
- Health: All three HA members must be
ONLINE. Degraded clusters reject restarts; repair first. - Secondaries: Use live roles, not
mysql_seq. Restart one secondary at a time and wait forONLINE. - 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 scenario | Rationale |
|---|---|
| Partial member selection | Bootstrap, configuration, and cluster phases require every member; exceptions: mysql_provision, mysql_db, mysql_user, mysql_backup, mysql_monitor |
| Invalid topology | Member count must be 1 or 3, with consecutive mysql_seq |
| Unsupported platform | Arch/OS outside the support matrix (e.g. Ubuntu ARM64) |
| Placeholder or invalid passwords | Unreplaced CHANGE_ME prefix, or %(...)s INI interpolation in mysql_monitor_password |
| Unowned data or legacy database coexistence | Missing/mismatched .pigsty-mysql-initialized, or an existing /var/lib/mysql database while mysql_data points elsewhere |
| Deprecated backup parameter | Inventory still defines mysql_backup_repo; use mysql_backup_dir and mysql_backup_retention |
| Implicit password changes | Declared HA mysql_cluster_password or root password differs from live credentials |
| Illegal parameter overrides | Reserved keys, malformed names, or multi-line values in mysql_parameters |
| Rolling restart while degraded | Configuration-driven rolling restarts require all 3 members ONLINE |
| Non-fresh clone target | Replacement members must be brand-new machines with empty datadirs |
| Incomplete outage with unreachable hosts | Refuse 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:
mysql-rm.yml task tags:
| Tag | Description |
|---|---|
mysql_remove | Root role tag for the complete removal workflow |
mysql_safeguard | Validate -l scope, mysql_safeguard=false, parameters, and data ownership marker |
mysql_leave | Single-member removal: delegate cluster-leave.js to a survivor, switch primary if needed, then remove with force: false |
mysql_deregister | Remove /infra/targets/mysql/.yml on Infra nodes; aliases: deregister, rm_metrics |
mysql | Stop and disable mysql-backup.timer/service, mysqlrouter, mysqld_exporter, and mysqld |
mysql_data | With 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_pkg | With 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: trueremovesmysql_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 touchesmysql_backup_dir(/data/backups/mysql); - Graceful member removal with automatic switchover: AdminAPI runs through a survivor and requires an
ONLINEtarget andmysql_rm_data=true. For aPRIMARYtarget, it first callssetPrimaryInstance()to transfer leadership, thenremoveInstance(..., {force: false, timeout: 60}), and cleans the target’s local data/services; - Stop the whole cluster with data retained:
-e mysql_rm_data=falserequires 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 fullmysql.ymlrun to clean registration and Clone automatically; - Physical backup copy-back through the XtraBackup
--copy-backrunbook; - Cleanup of historical backups under
mysql_backup_dir; - Topology changes (1→3, 3→5) and member re-addressing.
5 - Monitoring
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:
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:
| Label | Meaning | Example |
|---|---|---|
job | Scrape job | mysql |
cls | Cluster name | my-test |
ins | Instance name | my-test-1 |
ip | Member address | 10.10.10.11 |
topology | Topology type | innodb_cluster / standalone |
Derived names use mysql:ins:* for instances and mysql:cls:* for clusters. See Metrics.
Grafana Dashboards
| Dashboard | Purpose |
|---|---|
| MySQL Overview | Fleet view: cluster inventory, health, QPS/TPS, active alerts, instance list |
| MySQL Cluster | One cluster: member states, workload, node resources, cluster logs |
| MySQL Instance | One instance: connections, statements, InnoDB, temp tables, locks, logs |
| MySQL Group Replication | MGR deep dive: roles, certification/applier queues, flow control, read-only safety, GR logs |
| MySQL Alert | Alert 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:
| Alert | Severity | Duration | Expression and Response |
|---|---|---|---|
MySQLDown | CRIT | 1m | mysql:ins:exporter_up == 1 and on (...) mysql:ins:up < 1: exporter is up but cannot connect to mysqld |
MySQLExporterDown | WARN | 2m | mysql:ins:exporter_up < 1: cannot scrape mysqld_exporter |
MySQLClusterDown | CRIT | 1m | mysql:cls:health{topology="innodb_cluster"} < 1: no majority or no unique online primary; rerun mysql.yml after all hosts are reachable |
MySQLClusterDegraded | WARN | 5m | mysql:cls:health{topology="innodb_cluster"} == 1: writable but a member is offline; rerun mysql.yml or replace at the same address |
MySQLReplicationLag | WARN | 10m | mysql:ins:gr_certifier_queue > 1000 or mysql:ins:gr_applier_queue > 1000: MGR certification/apply backlog exceeds 1000 transactions |
MySQLConnUsageHigh | WARN | 5m | mysql:ins:connection_usage > 0.80: connections exceed 80% of max_connections |
MySQLHistoryListLarge | WARN | 15m | mysql:ins:history_list_length > 1000000: long transactions block undo purge, with over one million history entries |
MySQLBackupStale | WARN | 5m | time() - 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:
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 asmysql:ins:statement_latency. - Router runtime logs:
/var/log/mysqlrouter/also remains local.
Verify the Pipeline
Verify every pipeline stage after deployment:
6 - Metrics
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
| Metric | Meaning |
|---|---|
mysql:ins:exporter_up | Scrape success (transport health) |
mysql:ins:up | MySQL connection probe success (database health) |
mysql:ins:uptime | Instance uptime in seconds |
mysql:cls:instances | Declared instance count |
mysql:cls:up | Online instance count |
mysql:cls:health | Cluster 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
| Metric | Meaning |
|---|---|
mysql:ins:qps | Questions per second |
mysql:ins:tps | Transactions per second (commit + rollback) |
mysql:ins:read_qps / mysql:ins:write_qps | Read-class / write-class command rate |
mysql:ins:row_ops | InnoDB row operations (read/insert/update/delete dimensions) |
mysql:ins:statement_rate | Performance-schema statement rate |
mysql:ins:statement_latency | Mean statement latency (seconds) |
mysql:ins:rows_examined_per_query | Average rows examined per query |
mysql:ins:statement_errors | Statement error rate |
mysql:ins:slow_queries / mysql:ins:slow_query_ratio | Slow query rate and ratio |
mysql:ins:no_index_queries | Rate of queries using no index |
Connections and Sessions
| Metric | Meaning |
|---|---|
mysql:ins:connections | Current connections (Threads_connected) |
mysql:ins:connection_usage | Connections / max_connections |
mysql:ins:connection_rate | New connection rate |
mysql:ins:threads_running / mysql:ins:threads_cached | Active / cached threads |
mysql:ins:aborted_connects / mysql:ins:aborted_clients | Failed handshakes / abnormal disconnects |
mysql:ins:connection_errors | Total connection error rate |
mysql:ins:rx_bytes / mysql:ins:tx_bytes | Network receive / transmit rate |
Temp Tables, Scans, and Caches
| Metric | Meaning |
|---|---|
mysql:ins:tmp_tables / mysql:ins:tmp_disk_tables | In-memory / on-disk temp table creation rate |
mysql:ins:tmp_disk_ratio | Share of temp tables spilling to disk |
mysql:ins:full_joins / mysql:ins:full_scans | Index-less join / full scan rate |
mysql:ins:sort_merge_passes | Sort merge passes (undersized sort buffer signal) |
mysql:ins:table_open_cache_hit_ratio | Table open cache hit ratio |
mysql:ins:open_files_usage | Open file usage ratio |
InnoDB
| Metric | Meaning |
|---|---|
mysql:ins:buffer_pool_hit_ratio | Buffer pool hit ratio |
mysql:ins:buffer_pool_usage / mysql:ins:buffer_pool_dirty_ratio | Buffer pool usage / dirty page ratio |
mysql:ins:buffer_pool_waits | Free-page wait rate (memory pressure signal) |
mysql:ins:data_read_bytes / mysql:ins:data_write_bytes | Data file read / write throughput |
mysql:ins:data_reads / mysql:ins:data_writes / mysql:ins:data_fsyncs | Data file I/O and fsync rates |
mysql:ins:redo_bytes | Redo write throughput |
mysql:ins:redo_utilization | Redo capacity utilization (checkpoint lag) |
mysql:ins:log_waits | Redo buffer wait rate |
mysql:ins:row_lock_waits / mysql:ins:row_lock_time | Row lock wait rate / time |
mysql:ins:deadlocks | Deadlock rate |
mysql:ins:history_list_length | Purge lag (history list length) |
mysql:ins:binlog_bytes | Total 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:
| Metric | Meaning |
|---|---|
mysql:ins:gr_member | Instance is in any MGR member state |
mysql:ins:gr_online | Instance is ONLINE |
mysql:ins:gr_primary / mysql:ins:gr_secondary | Instance is the ONLINE primary / a secondary |
Cluster-level quorum and topology:
| Metric | Meaning |
|---|---|
mysql:cls:gr_online_members | ONLINE member count |
mysql:cls:gr_primary_members | ONLINE primary count |
mysql:cls:gr_quorum | Majority held (0/1) |
mysql:cls:gr_single_primary | Exactly one primary (0/1) |
Replication pipeline (certification and apply):
| Metric | Meaning |
|---|---|
mysql:ins:gr_certifier_queue / mysql:ins:gr_applier_queue | Transactions backed up in certification / applier queues |
mysql:ins:gr_certifier_queue_ratio / mysql:ins:gr_applier_queue_ratio | Queue depth relative to flow-control thresholds |
mysql:ins:gr_checked_rate / mysql:ins:gr_applied_rate | Certification / apply throughput |
mysql:ins:gr_conflict_rate | Certification 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:
| Metric | Meaning |
|---|---|
mysql:ins:backup_time | Unix timestamp in seconds of this node’s latest successful local full physical backup |
mysql:cls:backup_time | Unix 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:
| Prefix | Content |
|---|---|
mysql_up / up | Database 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_info | MGR membership (member_state / member_role dimensions) |
mysql_perf_schema_transactions_* / mysql_perf_schema_conflicts_detected_total | MGR 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_bytes | Latest 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
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 (
mysqldumpormysqlsh 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:
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. RunALTER 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.