leucos / ansible-proxysql Goto Github PK

View Code? Open in Web Editor NEW

6.0 2.0 9.0 50 KB

Ansible proxysql role

Shell 56.98% Python 43.02%

ansible-proxysql's Introduction

ProxySQL Ansible role

Project has moved to https://github.com/devops-works/ansible-proxysql

This role will install proxysql on Ubuntu Xenial (16.04) & Bionic (18.04) from sysown release.

Requirements

Tested on 2.5.0. Should work on 2.3+

Role Handlers

Name	Type	Description
Restart proxysql normal	service	Restarts proxysql using service (SysV based) when proxysql_initial is False
Restart proxysql initial	command	Restarts proxysql in initial mode when proxysql_initial is True
Restart proxysql	Virtual	Calls all above handlers

Name	Description
proxysql	Applies to whole role

Role Variables

Name	Default	Type	Description
proxysql_admin_user	admin	String	Username for ProxySQL admin
proxysql_admin_password	admin	String	Password for the above user
proxysql_admin_interface	127.0.0.1	String	Bind address for admin interface
proxysql_admin_port	6032	Number	Bind port for administrative interface
proxysql_initial	False	Bool	Wipes existing config database when True (see --initial CLI flag)
proxysql_mysql_interface	127.0.0.1	String	MySQL bind interface
proxysql_mysql_port	6033	Number	MySQL bind port
proxysql_mysql_query_rules	[]	Array	Configuration-file query rules
proxysql_mysql_servers	[]	Array	Configuration-file servers
proxysql_mysql_users	[]	Array	Configuration-file users

If you don't want to list proxy_mysql_servers in variables, you can set proxy_mysql_servers_group instead. The rolle will populate mysql_servers from the group name.

However, in this case, be sure to set the following variables for each server in the group:

Name	Default	Type	Description
proxysql_upstream_mysql_interface	false	String	Interface name upstream MySQL server listens on
proxysql_upstream_mysql_port	3306	String	Port upstream MySQL listens on
proxysql_hostgroup	false	Number	Hostgroup this MySQL server belongs to

Note that you can not use both proxy_mysql_servers_group and proxy_mysql_servers at the same time !

When using cluster mode, the following variables must be set:

Name	Default	Type	Description
proxysql_cluster_name	false	String	ProxySQL name
proxysql_cluster_password	false	String	ProxySQL password for cluster communications
proxysql_cluster_servers_group	false	String	Ansible group containing ProxySQL cluster members

Example

Requirements

- src: git+ssh://[email protected]/leucos/ansible-proxysql.git
  version: origin/master

Use a fixed tag instead of origin/master if possible

Playbook

- hosts: proxylb
  roles:
    - role-proxysql

Inventory

proxysql_mysql_query_rules:
  - match_pattern: "^SELECT .* FOR UPDATE$"
    destination_hostgroup: 0
  - match_pattern: "^SELECT"
    destination_hostgroup: 1

proxysql_mysql_servers:
   - address: 1.2.3.4
     port: 3306
     hostgroup: 0
   - address: 5.6.7.8
     port: 3306
     hostgroup: 1

Tests

To run tests locally in a Vagrant machine, just hit:

vagrant up
vagrant ssh -c specs

If you want to run the test playbook fast (i.e., without re-installing Ansible), just run:

vagrant ssh -c 'specs -p'

Notes

This role follows Semantic Versionning conventions.

Besides proxysql, the mysql-common package will be installed by this role.

License

MIT

Author Information

@leucos

ansible-proxysql's People

Contributors

Stargazers

Watchers

Forkers

jsecchiero crimsonalchemist ameeuw23 roadrunner sergiogiuffrida cleming larvatatw kjenney trangnth

ansible-proxysql's Issues

Please add the followings to the tasks/proxysql.yml

Hi,

First of all thanks for your work. I was wondering if you can make some modifications.

installing python-mysqldb next to mysql-common and mysql-client. It will enable users to use all the Ansible ProxySQL modules (http://docs.ansible.com/ansible/latest/list_of_database_modules.html)

Plus you are creating the /etc/proxysql.cnf with proper values, but those are not loading into the ProxySQL runtime layer actually (at lest for me). For that I had to add the following tasks:

    - name: Load the ProxySQL MYSQL USERS configuration from the config file to memory
      proxysql_manage_config:
        action: LOAD
        config_layer: CONFIG
        config_settings: MYSQL USERS
        direction: FROM
        login_user: "{{ proxysql_admin_user }}"
        login_password: "{{ proxysql_admin_password }}"
      tags:
        - configure_proxysql

    - name: Load the ProxySQL MYSQL USERS configuration from the config file to memory
      proxysql_manage_config:
        action: LOAD
        config_layer: MEMORY
        config_settings: MYSQL USERS
        direction: FROM
        login_user: "{{ proxysql_admin_user }}"
        login_password: "{{ proxysql_admin_password }}"
      tags:
        - configure_proxysql

    - name: Load the ProxySQL MYSQL SERVERS configuration from the config file to memory
      proxysql_manage_config:
        action: LOAD
        config_layer: CONFIG
        config_settings: MYSQL SERVERS
        direction: FROM
        login_user: "{{ proxysql_admin_user }}"
        login_password: "{{ proxysql_admin_password }}"
      tags:
        - configure_proxysql

    - name: Load the ProxySQL MYSQL SERVERS configuration from the config file to memory
      proxysql_manage_config:
        action: LOAD
        config_layer: MEMORY
        config_settings: MYSQL SERVERS
        direction: FROM
        login_user: "{{ proxysql_admin_user }}"
        login_password: "{{ proxysql_admin_password }}"
      tags:
        - configure_proxysql

If you want I can create a pull request for these modifications as well.

list variable error

proxysql.cnf.j2 : -

mysql_query_rules:
(
{% for usr in proxysql_mysql_users %}
{
rule_id={{ loop.index }}
match_pattern="{{ rule.match_pattern }}",
destination_hostgroup={{ rule.destination_hostgroup | default(0) }}
}
{% if not loop.last %},

{% endif %}
{% endfor %}
)

This should be :-

mysql_query_rules:
(
{% for rule in proxysql_mysql_query_rules %}
{
rule_id={{ loop.index }}
match_pattern="{{ rule.match_pattern }}",
destination_hostgroup={{ rule.destination_hostgroup | default(0) }}
}
{% if not loop.last %},

{% endif %}
{% endfor %}
)

Match mysql-server_version with deployed version

update global_variables set variable_value="5.7.21" where variable_name='mysql-server_version';
load mysql variables to run;save mysql variables to disk;

Set mysql-server_version in config based on MySQL version we're proxying.

See https://www.percona.com/blog/2018/05/09/does-the-version-number-matter

leucos / ansible-proxysql Goto Github PK

ansible-proxysql's Introduction

ProxySQL Ansible role

Project has moved to https://github.com/devops-works/ansible-proxysql

Requirements

Role Handlers

Tags

Role Variables

Example

Requirements

Playbook

Inventory

Tests

Notes

License

Author Information

ansible-proxysql's People

Contributors

Stargazers

Watchers

Forkers

ansible-proxysql's Issues

Recommend Projects

Recommend Topics

Recommend Org