community.proxysql.proxysql_galera_hostgroups module – Manages galera hostgroups using the proxysql admin interface

Note

This module is part of the community.proxysql collection (version 1.6.0).

You might already have this collection installed if you are using the ansible package. It is not included in ansible-core. To check whether it is installed, run ansible-galaxy collection list.

To install it, use: ansible-galaxy collection install community.proxysql. You need further requirements to be able to use this module, see Requirements for details.

To use it in a playbook, specify: community.proxysql.proxysql_galera_hostgroups.

Synopsis

  • Unlike regular async replication, Galera hostgroup are NOT defined in mysql_replication_hostgroups table. Instead there is a separate table that is specifically designed for Galera hostgroups. The reason for this is that more advanced topology support is needed in order to accommodate the deployment options available for Galera (e.g. controlled number of writers, cluster level replication thresholds etc.)

Requirements

The below requirements are needed on the host that executes this module.

  • PyMySQL

  • mysqlclient

Parameters

Parameter

Comments

active

integer

Enabled (1) or disabled (0) defined hostgroup configuration.

Choices:

  • 0

  • 1 ← (default)

backup_writer_hostgroup

integer / required

Id of the hostgroup that will contain all the Galera nodes that are standby writers.

comment

string

Text field that can be used for any purposes defined by the user.

Default: ""

config_file

path

Specify a config file from which login_user and login_password are to be read.

Default: ""

load_to_runtime

boolean

Dynamically load config to runtime memory.

Choices:

  • false

  • true ← (default)

login_host

string

The host used to connect to ProxySQL admin interface.

Default: "127.0.0.1"

login_password

string

The password used to authenticate to ProxySQL admin interface.

login_port

integer

The port used to connect to ProxySQL admin interface.

Default: 6032

login_unix_socket

string

The socket used to connect to ProxySQL admin interface.

login_user

string

The username used to authenticate to ProxySQL admin interface.

max_transactions_behind

integer

maximum number of writesets behind the cluster that ProxySQL should allow before shunning the node to prevent stale reads

Default: 0

max_writers

integer

number of Read-Write instances populated in the writer hostgroup

Default: 1

offline_hostgroup

integer / required

Id of the hostgroup all failed nodes will be moved to.

reader_hostgroup

integer / required

Id of the hostgroup that will contain all the Galera nodes that are readers.

save_to_disk

boolean

Save config to sqlite db on disk to persist the configuration.

Choices:

  • false

  • true ← (default)

state

string

When present - adds the galera hostgroup, when absent - removes the galera hostgroup.

Choices:

  • "present" ← (default)

  • "absent"

writer_hostgroup

integer / required

Id of the hostgroup that will contain all the Galera nodes that are active writers.

writer_is_also_reader

integer

writer_is_also_reader - 0 nodes with `read_only=0` will be placed either in the writer_hostgroup and in the backup_writer_hostgroup after a topology change, these will be excluded from the reader_hostgroup

writer_is_also_reader - 1 nodes with `read_only=0` will be placed in the writer_hostgroup or backup_writer_hostgroup and are all also placed in reader_hostgroup after a topology change

writer_is_also_reader - 2 Only the nodes with `read_only=0` which are placed in the in the backup_writer_hostgroup are also placed in the reader_hostgroup after a topology change i.e. the nodes with `read_only=0` exceeding the defined `max_writers`.

Choices:

  • 0 ← (default)

  • 1

  • 2

Notes

Note

  • Supports check_mode.

Examples

---
# This example adds a new galera hostgroup, it saves the mysql server config
# to disk and loads it to a runtime and also enables the config which has
# maximum of 2 writers from the writers_hostgroup.

- name: Add a galera hostgroup
  community.proxysql_galera_hostgroups:
    login_user: "admin"
    login_password: "admin"
    writer_hostgroup: 1
    backup_writer_hostgroup: 2
    reader_hostgroup: 3
    offline_hostgroup: 4
    active: 1
    max_writers: 2
    writer_is_also_reader: 0

# This example disables galera hostgroup by setting active to 0

- name: Disable a galera hostgroup
  community.proxysql_galera_hostgroups:
    login_user: "admin"
    login_password: "admin"
    writer_hostgroup: 1
    backup_writer_hostgroup: 2
    reader_hostgroup: 3
    offline_hostgroup: 4
    active: 0

# This example removes a galera hostgroup from configuration,
# saves the mysql server config to disk, and loads the mysql
# server config to runtime. It uses credentials in a supplied
# config file to connect to the proxysql admin interface.

- name: Remove a galera hostgroup
  community.proxysql_galera_hostgroups:
      config_file: '/tmp/proxysql.cnf'
      writer_hostgroup: 0
      backup_writer_hostgroup: 1
      reader_hostgroup: 2
      offline_hostgroup: 3
      active: 0
      state: absent

Return Values

Common return values are documented here, the following are the fields unique to this module:

Key

Description

stdout

dictionary

The galera hostgroup modified or removed from proxysql.

Returned: On create/update will return the newly modified group, on delete it will return the deleted record.

Sample: {"changed": true, "galera_group": {"active": "1", "backup_writer_hostgroup": "1", "comment": "test", "max_transactions_behind": "0", "max_writers": "3", "offline_hostgroup": "3", "reader_hostgroup": "2", "writer_hostgroup": "0", "writer_is_also_reader": "1"}, "msg": "Updated galera hostgroups in check_mode", "state": "present"}

Authors

  • Tomas Palevičius (@tompal3)