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.crt through node_ca. MYSQL issues leaf certificates only.
• Include mysql in repositories with node_repo_modules: node,infra,mysql, or cache packages locally with repo_extra_packages: [mysql].
• Use supported platforms: x86_64 EL 8/9/10, Debian 12/13, Ubuntu 22/24; or aarch64 EL 9/10.
• Set production mysql_root_password, mysql_monitor_password, and mysql_cluster_password. Preflight rejects CHANGE_ME placeholders.

Identity

Each cluster is an inventory group with two required identity parameters:

One member selects standalone; three select InnoDB Cluster. Preflight rejects other counts. mysql_seq=1 coordinates bootstrap only; it does not set runtime PRIMARY.

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:

my-meta:
  hosts:
    10.10.10.10: { mysql_seq: 1 }
  vars:
    mysql_cluster: my-meta

Standalone has no Router or 6446/6447 endpoints. Connect to 3306. TLS, monitoring, and backups otherwise behave as in HA.

Three-Node InnoDB Cluster

my-test:
  hosts:
    10.10.10.11: { mysql_seq: 1 }
    10.10.10.12: { mysql_seq: 2 }
    10.10.10.13: { mysql_seq: 3 }
  vars:
    mysql_cluster: my-test
    mysql_databases:
      - { name: app }
    mysql_users:
      - name: app
        host: '%'
        password: DBUser.App
        connlimit: 20
        priv: { 'app.*': 'ALL PRIVILEGES' }

This creates one writable PRIMARY and two read-only SECONDARY members. It tolerates one node failure. Every member runs Router; any 6446 reaches the current primary.

Databases

mysql_databases declares databases additively:

mysql_databases:
  - { name: app }                                              # utf8mb4 / utf8mb4_0900_ai_ci by default
  - { name: app2, encoding: utf8mb4, collate: utf8mb4_general_ci }

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.

Users

mysql_users declares users and grants additively:

mysql_users:
  - name: app                        # username
    host: '%'                        # grant source, defaults to '%'
    password: DBUser.App             # required; special characters are handled
    connlimit: 20                    # MAX_USER_CONNECTIONS, 0 = unlimited
    priv:                            # grant map: 'db.table' -> privilege list
      'app.*': 'ALL PRIVILEGES'
      'app2.*': 'SELECT, INSERT, UPDATE, DELETE'

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 priv grants. Removing mappings does not REVOKE privileges.
• Do not declare platform identities: root, dbuser_monitor, dbuser_cluster, or dbuser_backup.
• The server requires TLS. PREFERRED negotiates encryption; DISABLED plaintext connections fail. Prefer explicit VERIFY_CA.

Parameter Overrides

mysql_parameters appends [mysqld] overrides at the end of managed configuration. The last value takes precedence:

my-test:
  vars:
    mysql_cluster: my-test
    mysql_parameters:
      max_connections: 500
      long_query_time: 2
      innodb_print_all_deadlocks: true   # booleans render as ON/OFF

Rules and safety:

• Start option names with a letter; ._- characters are permitted. Values must be single-line scalars.
• mysqld --validate-config checks 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.

Default buffer pool is 25% of node memory, at least 256MB. Redo capacity is half that size, limited to 128MB–4GB. Replica parallelism follows CPU count. Override innodb_buffer_pool_size and other keys through mysql_parameters when needed.

Backup Settings

mysql_backup_enabled: true            # daily backup timer, on by default
mysql_backup_repo:
  local:
    path: /data/backups/mysql         # local backup root
    retention: 7                      # keep the last 7 fulls

The backup contract (details in Administration):

• Run a daily full physical XtraBackup backup and prepare it immediately. The result is directly restorable.
• Standalone backs up locally. Every HA timer runs, but only the current PRIMARY performs backup. Others skip.
• Use <path>/<cluster>/<UTC timestamp>/, atomic latest symlinks, and retention-based pruning.
• No incremental chains, binlog archiving, or PITR. Standalone recovery reaches the most recent backup.

Platform Credentials

mysql_root_password: MySQL.Root          # local root (root@localhost, socket only)
mysql_monitor_password: MySQL.Monitor    # exporter identity
mysql_cluster_password: MySQL.Cluster    # AdminAPI / Router / backup identity

Credential lifecycle rules:

• Passwords must be single-line and cannot retain a CHANGE_ME prefix. Preflight enforces this.
• Ordinary HA reruns cannot rotate mysql_cluster_password. Metadata and Router keyrings bind it, so implicit rotation fails. Standalone rotation works normally.
• mysql_root_password cannot reset silently. If the live value differs, the task fails instead of replacing it.

Root owns /etc/mysql/pigsty/, with directory mode 0700 and file mode 0600. It contains secrets and ready-to-use local client configuration:

mysql --defaults-extra-file=/etc/mysql/pigsty/root.cnf        # local root session
mysql --defaults-extra-file=/etc/mysql/pigsty/cluster.cnf     # cluster session via the local Router (HA members only)

Full Example

Standalone plus three-node HA, matching the four-node sandbox:

all:
  children:
    infra:
      hosts:
        10.10.10.10: { infra_seq: 1 }

    my-meta:
      hosts:
        10.10.10.10: { mysql_seq: 1 }
      vars: { mysql_cluster: my-meta, node_cluster: my-meta }

    my-test:
      hosts:
        10.10.10.11: { mysql_seq: 1 }
        10.10.10.12: { mysql_seq: 2 }
        10.10.10.13: { mysql_seq: 3 }
      vars:
        mysql_cluster: my-test
        node_cluster: my-test
        mysql_databases:
          - { name: app }
        mysql_users:
          - { name: app, password: DBUser.App, priv: { 'app.*': 'ALL PRIVILEGES' } }
        mysql_parameters:
          max_connections: 500

  vars:
    version: v4.5.0
    admin_ip: 10.10.10.10
    node_repo_modules: node,infra,mysql
    node_tune: oltp

    mysql_root_password: MySQL.Root
    mysql_monitor_password: MySQL.Monitor
    mysql_cluster_password: MySQL.Cluster

Use conf/demo/mysql.yml for the full template. conf/mysql.yml belongs to OpenHalo, a MySQL-compatible PostgreSQL kernel, and is unrelated to this module.