How to properly setup HAProxy with Percona XtraDB Cluster?

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

Can someone pls explain to the step by step of how to properly setup HAProxy with a XtraDB Cluster. I keep getting the same error:
ERROR 2013 (HY000): Lost connection to MySQL server at ‘reading initial communication packet’, system error: 0

2

Hello @Narlandis

Here is the documentation for PXC and HAProxy configuration: Load balancing with HAProxy - Percona XtraDB Cluster

3

Has to use the old password type… will not work with the new mysql auth…

4

Hello @meyerder

Has to use the old password type… will not work with the new mysql auth…

Right,

The following is mentioned in the PXC doc for this issue: Load balancing with HAProxy - Percona XtraDB Cluster

In Percona XtraDB Cluster 8.0, the default authentication plugin is caching_sha2_password. HAProxy does not support this authentication plugin. Create a mysql user using the mysql_native_password authentication plugin.

5

@lalit.choudhary Question… Instead of it doing a checkcluster remote web call How can I set it up to do something like this instead… as having another service dependency is In my opinion another breakage point…

I would “think” we could do something to say if this procedure returns/does not return a result mark it as down/up…

DELIMITER $$

USE aa$$

DROP PROCEDURE IF EXISTS sp_aa$$

CREATE DEFINER=ae@% PROCEDURE sp_a(eid VARCHAR(15))
BEGIN
DECLARE my_wsrep_local_state_comment VARCHAR(25);
DECLARE my_wsrep_cluster_status VARCHAR(25);
DECLARE my_read_only VARCHAR(25);
DECLARE my_wsrep_ready VARCHAR(25);

SELECT VARIABLE_VALUE INTO my_wsrep_local_state_comment FROM performance_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_state_comment';
SELECT VARIABLE_VALUE INTO my_wsrep_cluster_status FROM performance_schema.global_status WHERE VARIABLE_NAME = 'wsrep_cluster_status';
SELECT VARIABLE_VALUE INTO my_read_only FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'READ_ONLY';
SELECT VARIABLE_VALUE INTO my_wsrep_ready FROM performance_schema.global_status WHERE VARIABLE_NAME = 'wsrep_ready';


IF
-- my_wsrep_local_state_comment IN ('Synced')
    my_wsrep_local_state_comment IN ('Synced','Donor/Desynced','Joining','Joined') 
    AND my_wsrep_cluster_status='Primary' 
    AND my_read_only='OFF' 
    AND my_wsrep_ready='ON'
THEN 
    SELECT my_wsrep_local_state_comment, my_wsrep_cluster_status, my_read_only, my_wsrep_ready;
END IF;

END$$

DELIMITER ;