timorunge.proxysql

ProxySQL

This role does not work with ProxySQL versions 2.0.1 or higher.

It is compatible only with Ansible version 2.7.9 or higher due to a bug in the transition from MySQLdb to PyMySQL (#40123) in the MySQL module utilities.

Release 0.3.0 works with Ansible version 2.7.0 or lower.

This role installs and sets up ProxySQL, which is a proxy for MySQL that focuses on high performance and high availability.

Since version 2.3.0, Ansible has included a module to set up ProxySQL. This Ansible role leverages this module while adding additional features such as:

Automatic installation on various operating systems
(Pre-) generation of proxysql.cnf
Management of a ProxySQL Cluster using a custom module to set up ProxySQL servers
Ability to distinguish between dynamic and static global_variables and restart ProxySQL if needed

Please check the "Known issues or Important Information" section for additional context.

Requirements

This role requires Ansible version 2.5.0 or higher.

You can install a stable version using pip:

pip install ansible==2.7.9

All platform requirements can be found in the metadata file.

Install

To install the role, run:

ansible-galaxy install timorunge.proxysql

Role Variables

The following variables can be used with this role. For all variables, refer to defaults/main.yml.

# Enable or disable ProxySQL as a service.
# Type: Bool
proxysql_service_enabled: True

# Restart ProxySQL if static variables change. See `proxysql_non_dynamic_variables` in `vars/main.yml` for a list of static variables.
# Type: Bool
proxysql_restart_on_static_variables_change: True

# Repository

# If both `proxysql_use_official_repo` and `proxysql_use_percona_repo` are set to `False`, the module will automatically download the specified 
# `proxysql_version` as a package from GitHub and install it.

# Use the official ProxySQL repository.
# Type: Bool
proxysql_use_official_repo: True

# Use the Percona repository.
# Type: Bool
proxysql_use_percona_repo: False

# Define the repository version for the Percona repository.
# Type: Str
proxysql_percona_release: latest

# The version of ProxySQL to install if not using the official repository.
# Type: Int
proxysql_version: 1.4.15

# Configuration

# Directory for ProxySQL to store its database and logs.
# Type: Str
proxysql_datadir: /var/lib/proxysql

# Define the proxysql.cnf template
# Type: Str
proxysql_proxysql_cnf_template: proxysql.cnf.j2

# Define the proxysql-admin.cnf template
# Type: Str
proxysql_proxysql_admin_cnf_template: proxysql-admin.cnf.j2

# Login variables for configuring ProxySQL. Used in `main.yml` file for easier setup.
# Type: Str
proxysql_login_admin_host: 127.0.0.1
proxysql_login_admin_password: admin
proxysql_login_admin_port: 6032
proxysql_login_admin_user: admin

# Global variables
# `admin_variables`: Global variables for the admin interface and prefixed with `admin-`.
# `mysql_variables`: Global variables for handling incoming MySQL traffic, prefixed with `mysql-`.

# Each variable should be a string or an integer. Mark boolean values as strings, e.g. "True" or "False".

# For complete reference, check:
# https://github.com/sysown/proxysql/wiki/Global-variables

# Format:
# Type: Dict
proxysql_global_variables:
  login_host: "{{ proxysql_login_admin_host }}"
  login_password: "{{ proxysql_login_admin_password }}"
  login_port: "{{ proxysql_login_admin_port }}"
  login_user: "{{ proxysql_login_admin_user }}"

# Format:
# Type: Dict
proxysql_global_variables_kv: {}

# Backend servers
# `proxysql_backend_servers`: Specifies backend servers used for incoming MySQL traffic.

# Format:
# Type: Dict
proxysql_backend_servers: {}

# ProxySQL servers
# `proxysql_proxysql_servers`: Specifies ProxySQL servers used for clustering.

# Format:
# Type: Dict
proxysql_proxysql_servers: {}

# Replication hostgroups
# `proxysql_replication_hostgroups`: Define writer and reader host groups.

# Format:
# Type: Dict
proxysql_replication_hostgroups: {}

# Users
# `proxysql_mysql_users`: Defines users who can connect to the proxy, and for backend servers.

# Format:
# Type: Dict
proxysql_mysql_users: {}

# Query rules
# `proxysql_query_rules`: Defines rules for classifying and routing incoming MySQL traffic.

# Format:
# Type: Dict
proxysql_query_rules: {}

# Percona ProxySQL Admin
# Tool for configuring Percona XtraDB cluster nodes into ProxySQL.

# Format:
# Type: Dict
proxysql_percona_admin_tool:
  PROXYSQL_DATADIR: "{{ proxysql_datadir }}"
  PROXYSQL_USERNAME: "{{ proxysql_login_admin_user }}"
  PROXYSQL_PASSWORD: "{{ proxysql_login_admin_password }}"
  PROXYSQL_HOSTNAME: "{{ proxysql_login_admin_host }}"
  PROXYSQL_PORT: "{{ proxysql_login_admin_port }}"

Examples

1) Full Configuration Example

Here is a full example configuration of ProxySQL. The role will directly download the 1.4.15 package and not use any repository (with proxysql_use_official_repo set to False).

- hosts: proxysql
  gather_facts: True
  vars:
    proxysql_version: 1.4.15
    proxysql_service_enabled: True
    proxysql_use_official_repo: True
    proxysql_login_admin_host: 127.0.0.1
    proxysql_login_admin_password: admin
    proxysql_login_admin_port: 6032
    proxysql_login_admin_user: admin
    proxysql_global_variables:
      login_host: "{{ proxysql_login_admin_host }}"
      login_password: "{{ proxysql_login_admin_password }}"
      login_port: "{{ proxysql_login_admin_port }}"
      login_user: "{{ proxysql_login_admin_user }}"
    proxysql_global_variables_kv:
      admin-admin_credentials: "{{ proxysql_login_admin_user }}:{{ proxysql_login_admin_password }}"
      admin-mysql_ifaces: "{{ proxysql_login_admin_host }}:{{ proxysql_login_admin_port }}"
      mysql-commands_stats: "True"
      mysql-interfaces: 127.0.0.1:6033
      mysql-max_connections: 8192
      mysql-threads: 4
    proxysql_backend_servers:
      mysql-srv1-hg1:
        comment: mysql-srv1-hg1
        hostgroup: 1
        hostname: 172.16.77.101
        login_host: "{{ proxysql_login_admin_host }}"
        login_password: "{{ proxysql_login_admin_password }}"
        login_port: "{{ proxysql_login_admin_port }}"
        login_user: "{{ proxysql_login_admin_user }}"
        max_connections: 1000
        status: ONLINE
        weight: 1
    proxysql_proxysql_servers:
      proxysql-srv-1:
        comment: proxysql-srv-1
        hostname: 172.16.77.201
        login_host: "{{ proxysql_login_admin_host }}"
        login_password: "{{ proxysql_login_admin_password }}"
        login_port: "{{ proxysql_login_admin_port }}"
        login_user: "{{ proxysql_login_admin_user }}"
        weight: 0
    proxysql_replication_hostgroups:
      Cluster:
        comment: Cluster
        login_host: "{{ proxysql_login_admin_host }}"
        login_password: "{{ proxysql_login_admin_password }}"
        login_port: "{{ proxysql_login_admin_port }}"
        login_user: "{{ proxysql_login_admin_user }}"
        reader_hostgroup: 2
        writer_hostgroup: 1
    proxysql_mysql_users:
      user1:
        active: 1
        default_hostgroup: 1
        login_host: "{{ proxysql_login_admin_host }}"
        login_password: "{{ proxysql_login_admin_password }}"
        login_port: "{{ proxysql_login_admin_port }}"
        login_user: "{{ proxysql_login_admin_user }}"
        username: user1
      user2:
        active: 1
        default_hostgroup: 1
        login_host: "{{ proxysql_login_admin_host }}"
        login_password: "{{ proxysql_login_admin_password }}"
        login_port: "{{ proxysql_login_admin_port }}"
        login_user: "{{ proxysql_login_admin_user }}"
        username: user2
    proxysql_query_rules:
      catchall:
        active: 1
        apply: 1
        destination_hostgroup: 1
        login_host: "{{ proxysql_login_admin_host }}"
        login_password: "{{ proxysql_login_admin_password }}"
        login_port: "{{ proxysql_login_admin_port }}"
        login_user: "{{ proxysql_login_admin_user }}"
        match_pattern: .*@.*
        rule_id: 1
  roles:
    - timorunge.proxysql

2) Installation from the Official Repository

This example shows using the official ProxySQL repository (proxysql_use_official_repo set to True). The repository is not providing packages for Ubuntu > 16.04, so consider setting proxysql_use_official_repo to False for newer Ubuntu versions or use the Percona repository.

- hosts: proxysql
  vars:
    proxysql_use_official_repo: True
    proxysql_use_percona_repo: False
    proxysql_login_admin_host: 127.0.0.1
    proxysql_login_admin_password: admin
    proxysql_login_admin_port: 6032
    proxysql_login_admin_user: admin

3) Installation from the Percona Repository

In this example, we use the Percona repository (proxysql_use_percona_repo set to True).

- hosts: proxysql
  vars:
    proxysql_use_official_repo: False
    proxysql_use_percona_repo: True
    proxysql_login_admin_host: 127.0.0.1
    proxysql_login_admin_password: admin
    proxysql_login_admin_port: 6032
    proxysql_login_admin_user: admin

4) Installation from .deb or .rpm Package

You can directly install ProxySQL from a GitHub release. Just set the proxysql_version and ensure that both proxysql_use_official_repo and proxysql_use_percona_repo are set to False.

- hosts: proxysql
  vars:
    proxysql_version: 1.4.15
    proxysql_use_official_repo: False
    proxysql_use_percona_repo: False
    proxysql_login_admin_host: 127.0.0.1
    proxysql_login_admin_password: admin
    proxysql_login_admin_port: 6032
    proxysql_login_admin_user: admin

5) Manual Restart of ProxySQL After Changes

If you prefer to restart ProxySQL manually after changing static variables, set proxysql_restart_on_static_variables_change to False. This may affect some tests related to idempotence.

- hosts: proxysql
  vars:
    proxysql_use_official_repo: False
    proxysql_restart_on_static_variables_change: False
    proxysql_login_admin_host: 127.0.0.1
    proxysql_login_admin_password: admin
    proxysql_login_admin_port: 6032
    proxysql_login_admin_user: admin

Known Issues

1) ProxySQL > 1.4.7 on Ubuntu 16.04

On Ubuntu 16.04, there may be communication issues with Ansible/ProxySQL > 1.4.7 via mysql-python/python-mysqldb.

2) Packages for Ubuntu > 16.04

ProxySQL doesn’t provide the latest packages for Ubuntu versions > 16.04. This role handles this by downloading the relevant release for the install.

3) Non-Dynamic Global Variables

ProxySQL has global variables that cannot be changed while running (see proxysql_non_dynamic_variables). This role allows automatic restarts to accommodate changes.