Using Ansible to Manage MySQL at DigitalOcean

1 .

2 .Introduction digitalocean.com 

3 .What does DO do? Simple, Developer-focused Cloud Hosting digitalocean.com 

4 .What are we using Ansible for? digitalocean.com 

5 .Example Deployment digitalocean.com 

6 .Example Project Layout digitalocean.com 

7 .Project Layout ● Inventories ● Local Module Library ● Group Variables / Host Variables ● Roles ○ Component roles ○ Project Specific Roles ● Playbooks ○ Server Templates ○ Cluster Configuration ○ Actions ● Makefiles digitalocean.com 

8 .Inventories ● List hosts (by environment) ● Define groups ● Guardrails ansible-playbook -i inventories/development ... digitalocean.com 

9 .Inventories ● List hosts (by environment) all: children: ● Define groups mysql: children: ● Guardrails mysql_managed: hosts: test-mysql-0[1:3].atlantic.com: test-mysql-0[1:6].pacific.com: mysql_unmanaged: digitalocean.com 

10 .Inventories ● List hosts (by environment) ● Define groups ● Guardrails ansible-playbook ... --extra-vars="target_env=development” ... Playbook: --- - hosts: mysql:!mysql_unmanaged:&{{ target_env }} ... digitalocean.com 

11 .Inventories: Constructed Groups plugin: constructed strict: false groups: dev: inventory_hostname.startswith('dev-') digitalocean.com 

12 .Inventories: Constructed Groups plugin: constructed strict: false groups: dev_mysql: (group_names|intersect(['mysql', 'dev']))|length >= 2 digitalocean.com 

13 .Inventories: Ordering inventories - development - 10_mysql.yml - 90_environment.yml - 99_dev_mysql.yml - production - staging digitalocean.com 

14 .Variable Order of Precedence 1. command values (eg “-u user”) 12. play vars 2. role defaults 13. play vars_prompt 3. inventory file or script group vars 14. play vars_files 4. inventory group_vars/all 15. role vars (defined in role/vars/main.yml) 5. playbook group_vars/all 16. block vars (only for tasks in block) 6. inventory group_vars/* 17. task vars (only for the task) 7. playbook group_vars/* 18. include_vars 8. inventory file or script host vars 19. set_facts / registered vars 9. inventory host_vars/* 20. role (and include_role) params 10. playbook host_vars/* 21. include params 11. host facts / cached set_facts 22. extra vars (always win precedence) digitalocean.com 

15 .Variable Management ● Role defaults interface with the role ● Define project level generic variables applicable to all environments ○ playbook group_vars/all ○ playbook group_vars/* ● Host specific overrides ○ inventory host_vars/* ● Variables we construct ○ role vars / include_vars / set_facts ● Functional role variables ○ role (and include_role) params ● Guardrails ○ extra vars (always win precedence) digitalocean.com 

16 .Variable Management Example - Defaults --- ### proxysql install proxysql_create_image: "{{ global_create_image | default(false) }}" proxysql_download_src: https://github.com/sysown/proxysql/releases/download proxysql_version: 1.4.10 proxysql_mysql_client_version: 5.7 proxysql_user: proxysql proxysql_group: proxysql proxysql_datadir: /var/lib/proxysql proxysql_restart_missing_heartbeats: 10 ... # autocommit proxysql_mysql_autocommit_false_is_transaction: false proxysql_mysql_autocommit_false_not_reusable: false proxysql_mysql_enforce_autocommit_on_reads: false proxysql_mysql_forward_autocommit: false ... digitalocean.com 

17 .Variable Management Example - Vars --- ... ### percona required packages proxysql_release: "{{ proxysql_download_src }}/v{{ proxysql_version }}/proxysql_{{ proxysql_version }}-ubuntu18_amd64.deb" ... proxysql_mysql_variables: autocommit_false_is_transaction: variable: "autocommit_false_is_transaction" variable_value: "{{ proxysql_mysql_autocommit_false_is_transaction | to_json }}" autocommit_false_not_reusable: variable: "autocommit_false_not_reusable" variable_value: "{{ proxysql_mysql_autocommit_false_not_reusable | to_json }}" client_found_rows: variable: "client_found_rows" variable_value: "{{ proxysql_mysql_client_found_rows | to_json }}" ... digitalocean.com 

18 .Variable Management Example - Config #jinja2: lstrip_blocks: "true" datadir="{{ proxysql_datadir }}" restart_on_missing_heartbeats={{ proxysql_restart_missing_heartbeats }} admin_variables= { {% for config_item in proxysql_admin_variables|dictsort %} {% if config_item.1.variable_value is not none %} {{ config_item.1.variable }}={{ config_item.1.variable_value | to_json }} {% endif %} {% endfor %} } mysql_variables= { {% for config_item in proxysql_mysql_variables|dictsort %} {% if config_item.1.variable_value is not none %} {{ config_item.1.variable }}={{ config_item.1.variable_value | to_json }} {% endif %} {% endfor %} } digitalocean.com 

19 .Anatomy of a Role digitalocean.com 

20 .Anatomy of a Role ● A role should be map to a single unit of functionality that utilise a common set of variables. ● Roles should be intuitive, and wherever possible mimic a common structure. ● Role Variable Management ○ Where possible, a [component] role should be generic, and any variables should map to sensible defaults. ○ The interface into role customisation should be via scalar role defaults. ○ Role variables should be used for variables that shouldn't be overridden in normal circumstance, or as syntactic sugar to construct variables internal to the role. ● A role should have repeatable logic and should avoid logical branching that might be non-repeatable. digitalocean.com 

21 .Component Roles digitalocean.com 

22 .Role Versioning - name: role_mysql_proxysql src: git+ssh://git@github.pacific.com/ansible/role_mysql_proxysql.git version: 1.1.1 digitalocean.com 

23 .Example ProxySQL Deployment digitalocean.com 

24 .Testing Roles digitalocean.com 

25 .Molecule ● pip install --user molecule ○ pip install --user molecule[ec2] ○ pip install --user molecule[docker] digitalocean.com 

26 .Molecule Commands ● create / destroy / list / cleanup ● prepare ● dependency ● login digitalocean.com 

27 .Anatomy of a Role digitalocean.com 

28 .Molecule Commands ● lint ● syntax ● idempotence ● verify ● check digitalocean.com 

29 .Molecule Commands ● converge ● test ● side-effects digitalocean.com