vdzhorov.proxysql

ProxySQL

This role works with ProxySQL version 2.0.1 or higher if you're using RHEL 8. If you want to install version 1.4.x, you need RHEL 7 or earlier.

This role requires Ansible version 2.7.9 or newer because of a bug related to a MySQL module issue.

Version 0.3.0 of this role is compatible with Ansible 2.7.0 or earlier.

This role installs and sets up ProxySQL - a fast and reliable proxy for MySQL.

Starting from version 2.3.0, Ansible includes a module to configure ProxySQL. This role uses this feature and adds some extra functionalities, including:

Automatic installation for various operating systems
(Pre-) generation of proxysql.cnf
Management of a ProxySQL Cluster using a custom module for configuring ProxySQL servers
Ability to handle changes in dynamic and static global_variables, with a restart of ProxySQL when necessary

Look at the "Known issues or: Good to know" section for more information.

Requirements

This role needs at least Ansible 2.5.0.

You can easily install a stable version using pip:

pip install ansible==2.7.9

All platform requirements are in the metadata file.

Install

ansible-galaxy install vdzhorov.proxysql

Role Variables

You can set the following variables for this role. Check defaults/main.yml for more details.

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

# Restart ProxySQL if static variables change
# Type: Bool
proxysql_restart_on_static_variables_change: True

# Repository settings

# If both `proxysql_use_official_repo` and `proxysql_use_percona_repo` are set to `False`, the specified `proxysql_version` will be downloaded from GitHub.

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

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

# Repository version for Percona
# Type: Str
proxysql_percona_release: latest 

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

# Configuration settings

# Directory to save database and logs for ProxySQL
# 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 ProxySQL configuration
# 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
# 

# Global variable definitions with dict format
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 }}"

# Backend servers configuration
proxysql_backend_servers: {}

# ProxySQL servers for clustering
proxysql_proxysql_servers: {}

# Replication hostgroups configuration
proxysql_replication_hostgroups: {}

# User accounts for ProxySQL
proxysql_mysql_users: {}

# Query rules for MySQL traffic
proxysql_query_rules: {}

# Percona ProxySQL Admin settings
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

This example configures ProxySQL to download the version 1.4.15 and not use the repository (proxysql_use_official_repo is 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
  roles:
    - timorunge.proxysql

2) Installation from the official repository

To use the ProxySQL repository, set proxysql_use_official_repo to True.

- hosts: proxysql
  vars:
    proxysql_use_official_repo: True
    proxysql_login_admin_host: 127.0.0.1
    ...

3) Installation from the Percona repository

To use the Percona repository, set proxysql_use_percona_repo to True.

- hosts: proxysql
  vars:
    proxysql_use_official_repo: False
    proxysql_use_percona_repo: True
    proxysql_login_admin_host: 127.0.0.1
    ...

4) Installation from deb or rpm package

To install ProxySQL from a GitHub release, set both proxysql_use_official_repo and proxysql_use_percona_repo 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
    ...

5) No automatic restart of ProxySQL after static variable change

To control when ProxySQL restarts after changing static variables, set proxysql_restart_on_static_variables_change to False.

- hosts: proxysql
  vars:
    proxysql_use_official_repo: False
    proxysql_restart_on_static_variables_change: False
    proxysql_login_admin_host: 127.0.0.1
    ...

Known issues or: Good to know

1) ProxySQL > 1.4.7 on Ubuntu 16.04 (fixed)

Using Ansible (any version) with ProxySQL > 1.4.7 on Ubuntu 16.04 may cause communication issues with mysql-python. A workaround is to use mysqlclient.

2) Packages for Ubuntu > 16.04 (fixed)

ProxySQL doesn’t provide updated packages for Ubuntu versions above 16.04. This role will download the 16.04 release and install it.

3) Non-dynamic global variables

Changes to certain global_variables in ProxySQL require a restart. This role can handle this by generating proxysql.cnf.

4) ProxySQL clustering

Clustering in ProxySQL is still experimental, meaning it may not work perfectly. Ensure you configure hostname and port correctly during initialization.