setup-maxscale.yml

## RUN THIS PLAYBOOK WITH
## > ansible-playbook setup-mariadb-galera.yml
---

# JUST GATHERING FACTS
- hosts: cluster_control,frontend_db1,backend_db1,backend_db2
  become: true


- hosts: backend_db1
  become: true
  become_user: root
  tasks: 
    - name: Add MaxScale MySQL User on Cluster
      vars:
        vaultsettings: "{{ lookup('hashi_vault', 'secret=secret/lasair/settings', errors='warn') | default('undefined', true) }}"
        localsettings: "{{ lookup('template', 'localsettings.yml', errors='warn')|from_yaml | default('undefined', true) }}"
        settings: "{% if vaultsettings == 'undefined' %}{{ localsettings }}{% else %}{{ vaultsettings }}{% endif %}"
        maxscale_password: "{{ settings.maxscale_password }}"
        password: "{{ settings.master_db_root_password }}"
        ip: "{{ hostvars[groups['cluster_control'][0]]['ansible_all_ipv4_addresses'][0] }}"

      community.mysql.mysql_query:
        query:
          - CREATE USER IF NOT EXISTS 'maxscale'@'{{ ip }}' IDENTIFIED BY "{{ maxscale_password }}"
          - GRANT SELECT ON mysql.user TO 'maxscale'@'{{ ip }}';
          - GRANT SELECT ON mysql.db TO 'maxscale'@'{{ ip }}';
          - GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'{{ ip }}';
          - GRANT SELECT ON mysql.columns_priv TO 'maxscale'@'{{ ip }}';
          - GRANT SELECT ON mysql.proxies_priv TO 'maxscale'@'{{ ip }}';
          - GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'{{ ip }}';
          - GRANT SHOW DATABASES ON *.* TO 'maxscale'@'{{ ip }}';
        login_user: root
        login_password: "{{ password }}"
        single_transaction: yes

# MAKE LOWERCASE TABLES FOR EACH SERVER IN THE CLUSTER


- hosts: backend_db1,backend_db2,frontend_db1
  become: true
  tasks:
    - name: Request lowercase tables names on all mysql servers
      ini_file:
        path: /etc/mysql/my.cnf
        section: MYSQLD
        option: lower_case_table_names
        value: "1"
        backup: no
    - name: Request lowercase tables names on all mysql servers
      ini_file:
        path: /etc/mysql/my.cnf
        section: MYSQLD
        option: log_bin_trust_function_creators
        value: "1"
        backup: no
    - name: Increase open file limit
      ini_file:
        path: /etc/systemd/system/mariadb.service.d/override.conf
        section: Service
        option: LimitNOFILE
        value: "infinity"
        backup: no
    # 2023-11-21 KWS Added temp table size limit and batch limit.
    - name: Limit temporary table size
      ini_file:
        path: /etc/mysql/my.cnf
        section: MYSQLD
        option: tmp_disk_table_size
        value: "10G"
        backup: no
    - name: Increase batch limit to infinity
      ini_file:
        path: /etc/mysql/my.cnf
        section: MYSQLD
        option: wsrep_max_ws_rows
        value: "0"
        backup: no


- hosts: cluster_control
  become: true
  vars:
    # 2023-11-21 KWS Versions of MaxScale are specific to the Ubuntu 20.04 and 22.04 versions.
    #maxscale_deb_url: https://dlm.mariadb.com/1961572/MaxScale/2.5.18/packages/ubuntu/focal/x86_64/maxscale-2.5.18-1.ubuntu.focal.x86_64.deb
    #maxscale_deb_url: https://dlm.mariadb.com/3477300/MaxScale/23.08.3/packages/ubuntu/jammy/x86_64/maxscale-23.08.3-1.ubuntu.jammy.x86_64.deb
    maxscale_deb_url: https://dlm.mariadb.com/3995340/MaxScale/24.02.4/packages/ubuntu/noble/x86_64/maxscale_24.02.4~noble-1_amd64.deb
  roles: 
    - gkansible.gkservercollection.maxscale

- hosts: cluster_control
  become: true
  # 2022-02-14 KWS Added the following role so we can refer to the template implicitly instead of
  #                using an absolute path.
  vars:
    runtag: "credential-templates"
  roles: 
    - gkansible.gkservercollection.maxscale


- hosts: cluster_control
  become: true
  # 2022-02-14 KWS Added the following role so we can refer to the template implicitly instead of
  #                using an absolute path.
  tasks: 
    - name: maxscale restart
      service:
        name: maxscale
        state: restarted