galera_proxysql

pdk
Galera and ProxySQL Cluster

Massimiliano Adamo

maxadamo

12,144 downloads

851 latest version

5.0 quality score

Version information

  • 1.0.1 (latest)
  • 1.0.0
  • 0.9.55
  • 0.9.50
  • 0.9.10
  • 0.9.9
  • 0.9.8
  • 0.9.7
  • 0.9.6
  • 0.9.5
  • 0.9.0
  • 0.5.1
  • 0.5.0
  • 0.3.4
  • 0.3.3
  • 0.3.1
  • 0.3.0
  • 0.2.0
  • 0.1.3
  • 0.1.2
  • 0.1.1
  • 0.1.0
released Aug 25th 2020
This version is compatible with:
  • Puppet Enterprise 2019.8.x, 2019.7.x, 2019.5.x, 2019.4.x, 2019.3.x, 2019.2.x, 2019.1.x, 2019.0.x, 2018.1.x
  • Puppet >= 5.5.0 < 7.0.0
  • RedHat
    ,
    CentOS

Start using this module

Documentation

maxadamo/galera_proxysql — version 1.0.1 Aug 25th 2020

galera_proxysql

Table of Contents

  1. Description
  2. Setup - The basics of getting started with galera_proxysql
  3. Usage - Configuration options and additional functionality
  4. Reference - An under-the-hood peek at what the module is doing and how
  5. Limitations - OS compatibility, etc.
  6. Development - Guide for contributing to the module

Description

This module sets up and bootstrap Galera cluster and ProxySQL. The status of the cluster is checked at run time through the fact galera_status and puppet will attempt to re-join the node in case of disconnection.

If puppet fails to recover a node you can use the script galera_wizard.py provided with this module.

ProxySQL will be set up on 2 nodes (no more, no less) with Keepalived and 1 floating IP.

  • if you want only the Galera cluster you need at least 3 servers and 3 ipv4 (and optionally 3 ipv6)
  • if you want the full stack you need at least 5 servers and 6 IPv4 (and optionally 6 IPv6)

Initial State Snapshot Transfer is supported only through Percona XtraBackup (on average DBs I see no reason to use mysqldump or rsync since the donor would be unavailable during the transfer: see Galera Documentation).

Xtrabackup is now supported by puppetlabs/mysql mysql::backup::xtrabackup, hence I decided to remove XtraBackup from this module.

When bootstrapping, avoid running puppet on all the nodes at same time. You need to bootstrap one node first and then you can join the other nodes.

Read at (actual) limitations in the section below.

Setup

Beginning with galera_proxysql

Sensitive type for passwords is not mandatory, but it's recommended. If it's not being used the module will emit a notifycation.

To setup Galera:

class { '::galera_proxysql':
  root_password    => Sensitive($root_password),
  sst_password     => Sensitive($sst_password),
  monitor_password => Sensitive($monitor_password),
  proxysql_hosts   => $proxysql_hosts,
  proxysql_vip     => $proxysql_vip,
  galera_hosts     => $galera_hosts,
  trusted_networks => $trusted_networks,
  manage_lvm       => true,
  vg_name          => 'rootvg',
  lv_size          => $lv_size;
}

To setup ProxySQL:

class { '::galera_proxysql::proxysql::proxysql':
  monitor_password => Sensitive($monitor_password),
  trusted_networks => $trusted_networks,
  proxysql_hosts   => $proxysql_hosts,
  proxysql_vip     => $proxysql_vip,
  galera_hosts     => $galera_hosts;
}

Once you have run puppet on every node, you can manage or check the cluster using the script:

[root@test-galera01 ~]# galera_wizard.py -h
usage: galera_wizard.py [-h] [-cg] [-dr] [-je] [-be] [-jn] [-bn]

Use this script to bootstrap, join nodes within a Galera Cluster
----------------------------------------------------------------
  Avoid joining more than one node at once!

optional arguments:
  -h, --help                 show this help message and exit
  -cg, --check-galera        check if all nodes are healthy
  -dr, --dry-run             show SQL statements to run on this cluster
  -je, --join-existing       join existing Cluster
  -be, --bootstrap-existing  bootstrap existing Cluster
  -jn, --join-new            join existing Cluster
  -bn, --bootstrap-new       bootstrap new Cluster
  -f, --force                force bootstrap-new or join-new Cluster

Author: Massimiliano Adamo <maxadamo@gmail.com>

Usage

The module will fail on Galera with an even number of nodes and with a number of nodes lower than 3.

To setup a Galera Cluster (and optionally a ProxySQL cluster with Keepalived) you need a hash declaration. If you use hiera it will be like this:

galera_hosts:
  test-galera01.example.net:
    ipv4: '192.168.0.83'
    ipv6: '2001:123:4::6b'
  test-galera02.example.net:
    ipv4: '192.168.0.84'
    ipv6: '2001:123:4::6c'
  test-galera03.example.net:
    ipv4: '192.168.0.85'
    ipv6: '2001:123:4::6d'
proxysql_hosts:
  test-proxysql01.example.net:
    priority: 250              # optional: defaults to 100
    state: 'MASTER'            # optional: defaults to 'BACKUP'
    ipv4: '192.168.0.86'
    ipv6: '2001:123:4::6e'
  test-proxysql02.example.net:
    ipv4: '192.168.0.87'
    ipv6: '2001:123:4::6f'
proxysql_vip:
  test-proxysql.example.net:
    ipv4: '192.168.0.88'
    ipv4_subnet: '22'
    ipv6: '2001:123:4::70'

If you do not intend to use ipv6, just skip the ipv6 keys as following:

galera_hosts:
  test-galera01.example.net:
    ipv4: '192.168.0.83'
  test-galera02.example.net:
    ipv4: '192.168.0.84'
  test-galera03.example.net:
... and so on ..

you need an array of trusted networks/hosts (a list of ipv4/ipv6 networks/hosts allowed to connect to MySQL socket):

trusted_networks:
  - 192.168.0.1/24
  - 2001:123:4::70/64
  - 192.168.1.44
... and so on ...

Create a new DB user: you could also use the puppetlabs/mysql define and class, but with this define you can create DB and user either on the nodes and on proxysql.

On Galera, to create user Zabbix and DB Zabbix:

galera_proxysql::create::user { 'zabbix':
  ensure         => present, # defaults to present
  dbpass         => Sensitive(lookup('zabbix_db_pass', String, 'first', 'default_pass')),
  galera_hosts   => $galera_hosts,
  proxysql_hosts => $proxysql_hosts,
  proxysql_vip   => $proxysql_hosts,
  privileges     => ['ALL'],
  table          => ['zabbix.*', 'zobbix.mytable', 'zubbix.*']; # array or string
}

On ProxySQL:

galera_proxysql::create::user { 'whatever_user':
  dbpass => Sensitive(lookup('my_db_pass', String, 'first', 'nothing_can_be_worse_than_trump'));
}

Reference

Limitations

  • In order to add SSL on the frontend, I need to add support for ProxySQL 2 (right ProxySQL 1.4.xx is being used)
  • not yet tested on ipv4 only (but it should work)
  • there are too many moving parts and I decided to temporarily strip support to Ubuntu.
  • Spec test needs improvements (move to Litmus?)
  • No changelog is available

Development

Feel free to make pull requests and/or open issues on my GitHub Repository

Release Notes/Contributors/Etc. Optional