Drop support for DB Loadbalancing with HAProxy + clustercheck

MariaDB Clustercheck has not been updated for a long time and ProxySQL
has been working well as the default.

Dropped DB Loadbalancing with HAProxy and MariaDB clustercheck support and
ProxySQL now gets enabled automatically when MariaDB is enabled.

Replaced MariaDB container healthcheck method with healthcheck.sh from
official MariaDB docker images.

Performing service upgrade with this patch will remove remaining
mariadb_clustercheck container.

Depends-on: https://review.opendev.org/c/openstack/kolla/+/962657
Change-Id: Ib655eae79ba73f4582b29fef82c2d882a474329b
Signed-off-by: Seunghun Lee <seunghun@stackhpc.com>

14 files changed, 63 insertions, 90 deletions

diff --git a/ansible/group_vars/all/mariadb.yml b/ansible/group_vars/all/mariadb.yml
index ea6c9fda9..5a5439fea 100644
--- a/ansible/group_vars/all/mariadb.yml
+++ b/ansible/group_vars/all/mariadb.yml
@@ -18,10 +18,9 @@ mariadb_port: "{{ database_port }}"
 mariadb_wsrep_port: "4567"
 mariadb_ist_port: "4568"
 mariadb_sst_port: "4444"
-mariadb_clustercheck_port: "4569"
 mariadb_enable_tls_backend: "{{ database_enable_tls_backend }}"
 
-mariadb_monitor_user: "{{ 'monitor' if enable_proxysql | bool else 'haproxy' }}"
+mariadb_monitor_user: "monitor"
 
 mariadb_datadir_volume: "mariadb"
 
@@ -30,8 +29,8 @@ mariadb_default_database_shard_hosts: "{% set default_shard = [] %}{% for host i
 mariadb_shard_id: "{{ mariadb_default_database_shard_id }}"
 mariadb_shard_name: "shard_{{ mariadb_shard_id }}"
 mariadb_shard_group: "mariadb_{{ mariadb_shard_name }}"
-mariadb_loadbalancer: "{{ 'proxysql' if enable_proxysql | bool else 'haproxy' }}"
-mariadb_backup_target: "{{ 'active' if mariadb_loadbalancer == 'haproxy' else 'replica' }}"
+mariadb_loadbalancer: proxysql
+mariadb_backup_target: replica
 mariadb_shard_root_user_prefix: "root_shard_"
 mariadb_shard_backup_user_prefix: "backup_shard_"
 mariadb_shards_info: "{{ groups['mariadb'] | database_shards_info() }}"
diff --git a/ansible/group_vars/all/proxysql.yml b/ansible/group_vars/all/proxysql.yml
index 822483d09..386a9be56 100644
--- a/ansible/group_vars/all/proxysql.yml
+++ b/ansible/group_vars/all/proxysql.yml
@@ -1,4 +1,4 @@
 ---
-enable_proxysql: "yes"
+enable_proxysql: "{{ enable_mariadb }}"
 
 proxysql_admin_port: "6032"
diff --git a/ansible/roles/mariadb/defaults/main.yml b/ansible/roles/mariadb/defaults/main.yml
index 837baaab8..16165e901 100644
--- a/ansible/roles/mariadb/defaults/main.yml
+++ b/ansible/roles/mariadb/defaults/main.yml
@@ -8,25 +8,7 @@ mariadb_services:
     volumes: "{{ mariadb_default_volumes + mariadb_extra_volumes }}"
     dimensions: "{{ mariadb_dimensions }}"
     healthcheck: "{{ mariadb_healthcheck }}"
-    environment:
-      MYSQL_USERNAME: "{{ mariadb_monitor_user }}"
-      MYSQL_PASSWORD: "{% if enable_proxysql | bool %}{{ mariadb_monitor_password }}{% endif %}"
-      MYSQL_HOST: "{{ api_interface_address }}"
-      AVAILABLE_WHEN_DONOR: "1"
     haproxy:
-      mariadb:
-        enabled: "{{ enable_mariadb | bool and not enable_external_mariadb_load_balancer | bool }}"
-        mode: "tcp"
-        port: "{{ database_port }}"
-        listen_port: "{{ mariadb_port }}"
-        frontend_tcp_extra:
-          - "option clitcpka"
-          - "timeout client 3600s"
-        backend_tcp_extra:
-          - "option srvtcpka"
-          - "timeout server 3600s"
-          - "{% if enable_mariadb_clustercheck | bool %}option httpchk{% endif %}"
-        custom_member_list: "{{ internal_haproxy_members.split(';') }}"
       mariadb_external_lb:
         enabled: "{{ enable_external_mariadb_load_balancer | bool }}"
         mode: "tcp"
@@ -39,18 +21,6 @@ mariadb_services:
           - "option srvtcpka"
           - "timeout server 3600s"
         custom_member_list: "{{ external_haproxy_members.split(';') }}"
-  mariadb-clustercheck:
-    container_name: mariadb_clustercheck
-    group: "{{ mariadb_shard_group }}"
-    enabled: "{{ enable_mariadb_clustercheck | bool }}"
-    image: "{{ mariadb_clustercheck_image_full }}"
-    volumes: "{{ mariadb_clustercheck_default_volumes + mariadb_clustercheck_extra_volumes }}"
-    dimensions: "{{ mariadb_clustercheck_dimensions }}"
-    environment:
-      MYSQL_USERNAME: "{{ mariadb_monitor_user }}"
-      MYSQL_PASSWORD: "{% if enable_proxysql | bool %}{{ mariadb_monitor_password }}{% endif %}"
-      MYSQL_HOST: "{{ api_interface_address }}"
-      AVAILABLE_WHEN_DONOR: "1"
 
 ####################
 # Database
@@ -61,8 +31,7 @@ database_max_timeout: 120
 ####################
 # HAProxy
 ####################
-internal_haproxy_members: "{% for host in mariadb_default_database_shard_hosts %} server {{ hostvars[host].ansible_facts.hostname }} {{ 'api' | kolla_address(host) }}:{{ mariadb_port }} check port {% if enable_mariadb_clustercheck | bool %}{{ mariadb_clustercheck_port }}{% else %}{{ mariadb_port }}{% endif %} inter 2000 rise 2 fall 5{% if not loop.first %} backup{% endif %};{% endfor %}"
-external_haproxy_members: "{% for host in mariadb_default_database_shard_hosts %} server {{ host }} {{ host }}:{{ mariadb_port }} check port {% if enable_mariadb_clustercheck | bool %}{{ mariadb_clustercheck_port }}{% else %}{{ mariadb_port }}{% endif %} inter 2000 rise 2 fall 5{% if not loop.first %} backup{% endif %};{% endfor %}"
+external_haproxy_members: "{% for host in mariadb_default_database_shard_hosts %} server {{ host }} {{ host }}:{{ mariadb_port }} check port {{ mariadb_port }} inter 2000 rise 2 fall 5{% if not loop.first %} backup{% endif %};{% endfor %}"
 
 ####################
 # Docker
@@ -71,12 +40,7 @@ mariadb_image: "{{ docker_image_url }}mariadb-server"
 mariadb_tag: "{{ openstack_tag }}"
 mariadb_image_full: "{{ mariadb_image }}:{{ mariadb_tag }}"
 
-mariadb_clustercheck_image: "{{ docker_image_url }}mariadb-clustercheck"
-mariadb_clustercheck_tag: "{{ mariadb_tag }}"
-mariadb_clustercheck_image_full: "{{ mariadb_clustercheck_image }}:{{ mariadb_clustercheck_tag }}"
-
 mariadb_dimensions: "{{ default_container_dimensions }}"
-mariadb_clustercheck_dimensions: "{{ default_container_dimensions }}"
 
 mariadb_default_volumes:
   - "{{ node_config_directory }}/mariadb/:{{ container_config_directory }}/:ro"
@@ -84,20 +48,15 @@ mariadb_default_volumes:
   - "{{ '/etc/timezone:/etc/timezone:ro' if ansible_facts.os_family == 'Debian' else '' }}"
   - "{{ mariadb_datadir_volume }}:/var/lib/mysql"
   - "kolla_logs:/var/log/kolla/"
-mariadb_clustercheck_default_volumes:
-  - "{{ node_config_directory }}/mariadb-clustercheck/:{{ container_config_directory }}/:ro"
-  - "/etc/localtime:/etc/localtime:ro"
-  - "{{ '/etc/timezone:/etc/timezone:ro' if ansible_facts.os_family == 'Debian' else '' }}"
-  - "kolla_logs:/var/log/kolla/"
 
 mariadb_extra_volumes: "{{ default_extra_volumes }}"
-mariadb_clustercheck_extra_volumes: "{{ default_extra_volumes }}"
 
 mariadb_enable_healthchecks: "{{ enable_container_healthchecks }}"
 mariadb_healthcheck_interval: "{{ default_container_healthcheck_interval }}"
 mariadb_healthcheck_retries: "{{ default_container_healthcheck_retries }}"
 mariadb_healthcheck_start_period: "{{ default_container_healthcheck_start_period }}"
-mariadb_healthcheck_test: ["CMD-SHELL", "/usr/bin/clustercheck"]
+mariadb_healthcheck_test: ["CMD-SHELL", "/usr/bin/healthcheck.sh --defaults-file /etc/{{ 'mysql/' if kolla_base_distro in ['ubuntu', 'debian'] else '' }}healthcheck.cnf --connect --galera_online"]
+
 mariadb_healthcheck_timeout: "{{ default_container_healthcheck_timeout }}"
 mariadb_healthcheck:
   interval: "{{ mariadb_healthcheck_interval }}"
@@ -122,19 +81,14 @@ mariadb_wsrep_extra_provider_options: []
 ####################
 mariadb_backup_host: "{{ groups[mariadb_shard_group][0] }}"
 mariadb_backup_database_schema: "mysql"
-mariadb_backup_database_user: "{% if mariadb_loadbalancer == 'haproxy' %}backup{% else %}{{ mariadb_shard_backup_user_prefix }}{{ mariadb_shard_id | string }}{% endif %}"
+mariadb_backup_database_user: "{{ mariadb_shard_backup_user_prefix }}{{ mariadb_shard_id | string }}"
 mariadb_backup_type: "full"
-mariadb_backup_possible: "{{ mariadb_loadbalancer != 'haproxy' or inventory_hostname in mariadb_default_database_shard_hosts }}"
-
-####################
-# Clustercheck
-####################
-enable_mariadb_clustercheck: "{{ 'True' if mariadb_loadbalancer == 'haproxy' else 'False' }}"
+mariadb_backup_possible: "{{ inventory_hostname in mariadb_default_database_shard_hosts }}"
 
 ####################
 # Sharding
 ####################
-mariadb_shard_database_user: "{% if mariadb_loadbalancer == 'haproxy' %}{{ database_user }}{% else %}{{ mariadb_shard_root_user_prefix }}{{ mariadb_shard_id | string }}{% endif %}"
+mariadb_shard_database_user: "{{ mariadb_shard_root_user_prefix }}{{ mariadb_shard_id | string }}"
 mariadb_database_shard: "{{ mariadb_shards_info }}"
 
 # Database
diff --git a/ansible/roles/mariadb/handlers/main.yml b/ansible/roles/mariadb/handlers/main.yml
index c5d120615..5e1b68ae9 100644
--- a/ansible/roles/mariadb/handlers/main.yml
+++ b/ansible/roles/mariadb/handlers/main.yml
@@ -72,19 +72,3 @@
     - groups[mariadb_shard_group + '_port_alive_False'] is defined
     - inventory_hostname in groups[mariadb_shard_group + '_port_alive_False']
   listen: Restart mariadb container
-
-- name: Restart mariadb-clustercheck container
-  vars:
-    service_name: "mariadb-clustercheck"
-    service: "{{ mariadb_services[service_name] }}"
-  become: true
-  kolla_container:
-    action: "recreate_or_restart_container"
-    common_options: "{{ docker_common_options }}"
-    image: "{{ service.image }}"
-    name: "{{ service.container_name }}"
-    volumes: "{{ service.volumes }}"
-    dimensions: "{{ service.dimensions }}"
-    environment: "{{ service.environment }}"
-  when:
-    - service | service_enabled_and_mapped_to_host
diff --git a/ansible/roles/mariadb/tasks/config.yml b/ansible/roles/mariadb/tasks/config.yml
index bf1779be5..405e34ffb 100644
--- a/ansible/roles/mariadb/tasks/config.yml
+++ b/ansible/roles/mariadb/tasks/config.yml
@@ -70,6 +70,22 @@
   become: true
   when: service | service_enabled_and_mapped_to_host
 
+- name: Copying over healthcheck.cnf
+  vars:
+    service_name: "mariadb"
+    service: "{{ mariadb_services[service_name] }}"
+  merge_configs:
+    sources:
+      - "{{ role_path }}/templates/healthcheck.cnf.j2"
+      - "{{ node_custom_config }}/healthcheck.cnf"
+      - "{{ node_custom_config }}/mariadb/{{ inventory_hostname }}/healthcheck.cnf"
+    dest: "{{ node_config_directory }}/{{ service_name }}/healthcheck.cnf"
+    mode: "0660"
+  become: true
+  when:
+    - mariadb_enable_healthchecks | bool
+    - service | service_enabled_and_mapped_to_host
+
 - include_tasks: copy-certs.yml
   when:
     - mariadb_copy_certs | bool
diff --git a/ansible/roles/mariadb/tasks/loadbalancer.yml b/ansible/roles/mariadb/tasks/loadbalancer.yml
index 78cac3fb5..bc7439dfd 100644
--- a/ansible/roles/mariadb/tasks/loadbalancer.yml
+++ b/ansible/roles/mariadb/tasks/loadbalancer.yml
@@ -30,7 +30,7 @@
       login_user: "{{ database_user }}"
       login_password: "{{ database_password }}"
       name: "{{ mariadb_monitor_user }}"
-      password: "{% if enable_proxysql | bool %}{{ mariadb_monitor_password }}{% endif %}"
+      password: "{{ mariadb_monitor_password }}"
       host: "%"
       priv: "*.*:USAGE,REPLICATION CLIENT"
   tags: always
diff --git a/ansible/roles/mariadb/tasks/register.yml b/ansible/roles/mariadb/tasks/register.yml
index 8c679d938..1b733afa7 100644
--- a/ansible/roles/mariadb/tasks/register.yml
+++ b/ansible/roles/mariadb/tasks/register.yml
@@ -29,7 +29,7 @@
       login_user: "{{ database_user }}"
       login_password: "{{ database_password }}"
       name: "{{ mariadb_monitor_user }}"
-      password: "{% if enable_proxysql | bool %}{{ mariadb_monitor_password }}{% endif %}"
+      password: "{{ mariadb_monitor_password }}"
       host: "%"
       priv: "*.*:USAGE,REPLICATION CLIENT"
   when:
diff --git a/ansible/roles/mariadb/tasks/upgrade.yml b/ansible/roles/mariadb/tasks/upgrade.yml
index 5b10a7e11..d72cc5b3d 100644
--- a/ansible/roles/mariadb/tasks/upgrade.yml
+++ b/ansible/roles/mariadb/tasks/upgrade.yml
@@ -1,2 +1,13 @@
 ---
 - import_tasks: deploy.yml
+
+# TODO(seunghun1ee): Remove this task after 2026.1
+- name: "Stop and remove mariadb_clustercheck containers"
+  become: true
+  kolla_container:
+    action: "stop_and_remove_container"
+    common_options: "{{ docker_common_options }}"
+    name: "mariadb_clustercheck"
+    ignore_missing: true
+  when:
+    - "'mariadb_clustercheck' not in skip_stop_containers"
diff --git a/ansible/roles/mariadb/templates/healthcheck.cnf.j2 b/ansible/roles/mariadb/templates/healthcheck.cnf.j2
new file mode 100644
index 000000000..670280dd5
--- /dev/null
+++ b/ansible/roles/mariadb/templates/healthcheck.cnf.j2
@@ -0,0 +1,3 @@
+[mariadb-client]
+user={{ mariadb_monitor_user }}
+password={{ mariadb_monitor_password }}
diff --git a/ansible/roles/mariadb/templates/mariadb-clustercheck.json.j2 b/ansible/roles/mariadb/templates/mariadb-clustercheck.json.j2
deleted file mode 100644
index aad07bff6..000000000
--- a/ansible/roles/mariadb/templates/mariadb-clustercheck.json.j2
+++ /dev/null
@@ -1,11 +0,0 @@
-{
-    "command": "socat_wrapper {% if network_address_family == 'ipv6' %}-6{% endif %} -d -lf/var/log/kolla/mariadb/mariadb-clustercheck.log tcp-l:{{ mariadb_clustercheck_port }},fork,reuseaddr,bind={{ api_interface_address }} EXEC:clustercheck",
-    "config_files": [],
-    "permissions": [
-        {
-            "path": "/var/log/kolla/mariadb",
-            "owner": "mysql:mysql",
-            "recurse": true
-        }
-    ]
-}
diff --git a/ansible/roles/mariadb/templates/mariadb.json.j2 b/ansible/roles/mariadb/templates/mariadb.json.j2
index 7910d6929..2ecc9fa5e 100644
--- a/ansible/roles/mariadb/templates/mariadb.json.j2
+++ b/ansible/roles/mariadb/templates/mariadb.json.j2
@@ -8,6 +8,13 @@
             "owner": "mysql",
             "perm": "0600"
         }
+        {% if mariadb_enable_healthchecks | bool %},
+        {
+            "source": "{{ container_config_directory }}/healthcheck.cnf",
+            "dest": "/etc/{{ mysql_dir }}/healthcheck.cnf",
+            "owner": "mysql",
+            "perm": "0600"
+        }{% endif %}
         {% if database_enable_tls_backend | bool %},
         {
             "source": "{{ container_config_directory }}/ca-certificates/root.crt",
diff --git a/ansible/roles/prometheus/tasks/bootstrap.yml b/ansible/roles/prometheus/tasks/bootstrap.yml
index 3eda6b1a3..9cc8e2634 100644
--- a/ansible/roles/prometheus/tasks/bootstrap.yml
+++ b/ansible/roles/prometheus/tasks/bootstrap.yml
@@ -3,7 +3,7 @@
   become: true
   vars:
     shard_id: "{{ item.key }}"
-    shard_root_user: "{% if mariadb_loadbalancer == 'haproxy' %}{{ database_user }}{% else %}{{ mariadb_shard_root_user_prefix }}{{ shard_id | string }}{% endif %}"
+    shard_root_user: "{{ mariadb_shard_root_user_prefix }}{{ shard_id | string }}"
     shard_host: "{{ mariadb_shards_info.shards[shard_id].hosts[0] }}"
   kolla_toolbox:
     container_engine: "{{ kolla_container_engine }}"
diff --git a/releasenotes/notes/drop-clustercheck-and-haproxy-support-for-mariadb-4cbd7c8590a34981.yaml b/releasenotes/notes/drop-clustercheck-and-haproxy-support-for-mariadb-4cbd7c8590a34981.yaml
new file mode 100644
index 000000000..ab486ca79
--- /dev/null
+++ b/releasenotes/notes/drop-clustercheck-and-haproxy-support-for-mariadb-4cbd7c8590a34981.yaml
@@ -0,0 +1,13 @@
+---
+features:
+  - |
+    ProxySQL is now automatically enabled when MariaDB is enabled.
+    MariaDB container healthcheck method was updated as healthcheck script was
+    replaced from Clustercheck to official MariaDB docker image's
+    `healthcheck.sh <https://mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/installing-mariadb/binary-packages/automated-mariadb-deployment-and-administration/docker-and-mariadb/using-healthcheck-sh>`__
+upgrade:
+  - |
+    Database loadbalancing with HAProxy and MariaDB Clustercheck is no longer
+    supported. For the system that uses HAProxy and Clustercheck, upgrading
+    MariaDB with ``kolla-ansible upgrade`` will deploy ProxySQL containers and
+    remove MariaDB Clustercheck containers.
diff --git a/tests/check-logs.sh b/tests/check-logs.sh
index cb8b76396..a9f7d464e 100755
--- a/tests/check-logs.sh
+++ b/tests/check-logs.sh
@@ -63,9 +63,6 @@ function check_fluentd_missing_logs {
         /var/log/kolla/mariadb/mariadb-bootstrap.log)
             continue
             ;;
-        /var/log/kolla/mariadb/mariadb-clustercheck.log)
-            continue
-            ;;
         /var/log/kolla/mariadb/mariadb-upgrade.log)
             continue
             ;;