Skip to content

voxpupuli/puppet-proxysql

Puppet module for ProxySQL

CI Code Coverage Puppet Forge Puppet Forge - downloads Puppet Forge - endorsement Puppet Forge - scores

Table of Contents

  1. Overview
  2. Setup - The basics of getting started with 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

Overview

The proxysql module installs, configures and manages the ProxySQL service.

This module will install the ProxySQL and manage it's configuration. It also extends Puppet to be able to manage mysql_users, mysql_servers, mysql_replication_hostgroups, mysql_galera_hostgroups, mysql_query_rules, proxysql_servers, scheduler and global_variables.

Setup

Setup Requirements

The module requires Puppet 5.5.8 and above. It also depends on:

For up to date details on external dependencies, please see the metadata.json or for released versions, the puppet forge page.

puppet/selinux is an optional soft dependency and not even listed in the metadata.json. No Operating Systems require this module to be present, but if it is, it will be used to install SELinux rules that allow logrotate to work. See manage_selinux

Beginning with proxysql

To install the ProxySQL software with all the default options:

include proxysql

By default, packages come from the official upstream package repositories which the module will configure. On new installations, (by default), the 2.7.x repository will be configured. If ProxySQL is already installed, then the repository matching the currently installed version will be used.

To use your Operating System's own packages set manage_repo => false.

class { 'proxysql':
  manage_repo => false,
}

or you can configure your own repository by eg. declaring your own yumrepo, pulp_rpmbind or rhn_channel resource and setting manage_repo => false.

For example, using pulp and katello/pulp

pulp_rpmbind { 'my_proxysql_repo':}

class { 'proxysql':
  manage_repo => false,
  require     => Pulp_rpmbind['my_proxysql_repo'],
}

Alternatively, you can specify a package_source and associated options. This mimics the old, (pre version 4), behaviour of this module.

class { 'proxysql':
  package_source         => 'https://github.com/sysown/proxysql/releases/download/v1.4.11/proxysql_1.4.11-debian9_amd64.deb',
  package_checksum_value => '65a3c2b98eefa42946ee59eef18ba18534c2a39d',
  package_checksum_type  => 'sha1',
}

You can customize options such as (but not limited to) listen_port, admin_password, monitor_password, ...

  class { 'proxysql':
    listen_port              => 3306,
    admin_password           => Sensitive('654321'),
    monitor_password         => Sensitive('123456'),
    override_config_settings => $override_settings,
  }

You can configure users\hostgroups\rules\schedulers using class parameters

  class { 'proxysql':
     mysql_servers    => [
       {
         'db1' => {
           'port' => 3306,
           'hostgroup_id' => 1,
         }
       },
       {
         'db2' => {
           'hostgroup_id' => 2,
         }
       },
     ],
     mysql_users      => [
       {
         'app' => {
           'password' => '*92C74DFBDA5D60ABD41EFD7EB0DAE389F4646ABB',
           'default_hostgroup' => 1,
         }
       },
       {
         'ro'  => {
           'password' => mysql_password('MyReadOnlyUserPassword'),
           'default_hostgroup' => 2,
         }
       },
     ],
     mysql_hostgroups => [
       {
         '1-2' => {
           'writer' => 1,
           'reader' => 2,
         }
       },
     ],
     mysql_group_replication_hostgroups => [
       {
         'hostgroup 2' => {
           'reader'  => 10,
           'writer'  => 5,
           'backup'  => 2,
           'offline' => 11,
         }
       },
     ],
     mysql_galera_hostgroups => [
       {
         'galera hostgroup 1' => {
           'writer'  => 1,
           'backup'  => 2,
           'reader'  => 3,
           'offline' => 4,
         }
       },
     ],
     mysql_rules      => [
       {
         'mysql_query_rule-1' => {
           'rule_id'         => 1,
           'match_pattern'   => 'testtable',
           'replace_pattern' => 'test.newtable',
           'apply'           => 1,
           'active'          => 1,
         }
       },
     ],
     schedulers       => [
       {
         'scheduler-1' => {
           'scheduler_id'  => 1,
           'active'        => 0,
           'filename'      => '/usr/bin/whoami',
         }
       },
     ],

Or by using individual resources:

  class { 'proxysql':
    listen_port    => 3306,
    admin_password => Sensitive('SuperSecretPassword'),
  }

  proxy_mysql_server { '192.168.33.31:3306-31':
    hostname     => '192.168.33.31',
    port         => 3306,
    hostgroup_id => 31,
  }

  proxy_mysql_server { '192.168.33.32:3306-31':
    hostname     => '192.168.33.32',
    port         => 3306,
    hostgroup_id => 31,
  }

  proxy_mysql_server { '192.168.33.33:3306-31':
    hostname     => '192.168.33.33',
    port         => 3306,
    hostgroup_id => 31,
  }

  proxy_mysql_server { '192.168.33.34:3306-31':
    hostname     => '192.168.33.34',
    port         => 3306,
    hostgroup_id => 31,
  }

  proxy_mysql_server { '192.168.33.35:3306-31':
    hostname     => '192.168.33.35',
    port         => 3306,
    hostgroup_id => 31,
  }

  proxy_mysql_replication_hostgroup { '30-31':
    writer_hostgroup => 30,
    reader_hostgroup => 31,
    comment          => 'Replication Group 1',
  }

  proxy_mysql_replication_hostgroup { '20-21':
    writer_hostgroup => 20,
    reader_hostgroup => 21,
    comment          => 'Replication Group 2',
  }

  proxy_mysql_group_replication_hostgroup { '5-2-10-11':
    reader_hostgroup        => 10,
    writer_hostgroup        => 5,
    backup_writer_hostgroup => 2,
    offline_hostgroup       => 11,
  }

  proxy_mysql_galera_hostgroup { '1-2-3-4':
    writer_hostgroup        => 1,
    backup_writer_hostgroup => 2,
    reader_hostgroup        => 3,
    offline_hostgroup       => 4,
  }

  proxy_mysql_user { 'tester':
    password          => 'testerpwd',
    default_hostgroup => 30,
  }

  proxy_mysql_query_rule { 'mysql_query_rule-1':
    rule_id               => 1,
    match_pattern         => '^SELECT',
    apply                 => 1,
    active                => 1,
    destination_hostgroup => 31,
  }

  proxy_scheduler { 'scheduler-1':
    scheduler_id => 1,
    active       => 0,
    filename     => '/usr/bin/whoami',
  }

  proxy_scheduler { 'scheduler-2':
    scheduler_id => 2,
    active       => 0,
    interval_ms  => 1000,
    filename     => '/usr/bin/id',
  }

Usage

Configuration is done by the proxysql class.

Customize config settings

You can override any configuration setting by using the override_config_settings hash. This hash resembles the structure of the proxysql.cnf file

{
    admin_variables => {
      refresh_interval => 2000,
      ...
    },
    mysql_variables => {
      monitor_writer_is_also_reader => false,
      ...
    },
    mysql_servers => {
      '127.0.0.1:33061-1' => {
         'address'      => '127.0.0.1',
         'port'         => 33061,
         'hostgroup_id' => 1,
       },
      '127.0.0.1:33062-1' => {
         'address'      => '127.0.0.1',
         'port'         => 33062,
         'hostgroup_id' => 1,
       },
      ...
    },
    mysql_users => { ... },
    mysql_query_rules => { ... },
    scheduler => { ... },
    mysql_replication_hostgroups => { ... },
    mysql_galera_hostgroups => { ... },

}

Reference

see REFERENCE.md

proxysql\_runtime fact

This module ships a fact that you may find useful in your profiles. It is not used by the module itself. The proxysql_runtime fact queries ProxySQL and returns a hash containing the contents of several of the runtime tables.

The fact will only return data if the mysql2 library is installed in your puppet agent's gem environment.

For systems using official puppet packages, (All In One packages), the following code can be used to install this gem and make the fact available.

$dev_packages = ['mariadb-devel','make','gcc']
ensure_packages($dev_packages)

package { 'mysql2 gem':
  ensure   => present,
  name     => 'mysql',
  provider => 'puppet_gem',
  require  => Package[$dev_packages],
}

Limitations

The module requires Puppet 5.5 or above. The proxysql_runtime fact only works when using the default value for mycnf_file_name.

Development

This module is originally developed by Matthias Crauwels for use at Ghent University, Belgium. This module is published under the Apache 2.0 license. It is now maintained by Vox Pupuli

We are open to feature requests, bug reports, contributions, etc...

Contributors

Original author: Matthias Crauwels