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.