Comment installer un serveur MySQL avec équilibrage de charge avec ProxySQL sur Debian 11

ProxySQL est un proxy MySQL haute performance avec une empreinte réduite qui peut être installé dans plusieurs environnements tels que VM, K8s, bare-metal et conteneurs Docker. ProxySQL est une solution open-source qui offre une haute disponibilité pour votre cluster MySQL avec un support de multiplexage de connexions, fournit des règles de requêtes pour acheminer les écritures vers les primaires et distribue les lectures entre les répliques.

ProxySQL prend en charge à la fois AMD64 (x86_64) et ARM64. Il fournit un proxy de couche d’application pour votre application et propose également des requêtes avancées avec détection de basculement.

Dans ce guide, vous installerez et configurerez l’équilibrage de charge d’un cluster MySQL via ProxySQL sur un serveur Debian 11. Vous installerez ProxySQL v2 avec le client Percona XtraDB, puis ajouterez le cluster Percona XtraDB à ProxySQL, configurerez l’utilisateur de surveillance de ProxySQL, puis ajouterez la base de données et l’utilisateur qui seront utilisés pour vos applications.

Prérequis

Pour commencer avec ce guide, vous devez avoir les exigences suivantes :

• Un serveur Linux exécutant le système Debian 11 - Cet exemple utilise un serveur Debian avec le nom d’hôte ‘proxysql-deb‘.
• Un utilisateur non-root avec des privilèges d’administrateur sudo/root.
• Un cluster MySQL ou un cluster Percona XtraDB entièrement configuré.

C’est tout. Lorsque toutes les exigences sont prêtes, vous pouvez maintenant commencer l’installation de ProxySQL.

Configuration des dépôts

La première étape que vous devez effectuer pour ce guide est d’ajouter à la fois les dépôts ProxySQL et Percona XtraDB à votre serveur. Le dépôt Percona XtraDB sera utilisé pour installer le package ‘percona-xtradb-cluster-client‘ dont ProxySQL a besoin.

Pour commencer, installez quelques dépendances de base en entrant la commande apt suivante. Entrez y lorsque vous y êtes invité et appuyez sur ENTRER pour continuer.

sudo apt install wget gnupg2 lsb-release curl apt-transport-https ca-certificates

Ensuite, téléchargez le fichier de dépôt pour Percona XtraDB et installez-le via la commande ‘dpkg‘ ci-dessous.

wget -q https://repo.percona.com/apt/percona-release_latest.generic_all.deb  
sudo dpkg -i percona-release_latest.generic_all.deb

Maintenant, mettez à jour et rafraîchissez votre index de paquets Debian en utilisant la commande apt suivante ci-dessous. Vous devriez voir que le nouveau dépôt Percona XtraDB a été ajouté.

sudo apt update

Ensuite, entrez la commande ‘percona-release‘ pour activer le dépôt du cluster Percona XtraDB 8.0.

sudo percona-release setup pxc80

Avec le dépôt Percona XtraDB ajouté, exécutez maintenant la commande suivante pour ajouter la clé GPG et le dépôt pour ProxySQL.

wget -q -nv -O /etc/apt/trusted.gpg.d/proxysql-2.4.x-keyring.gpg 'https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/repo_pub_key.gpg'  
echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/$(lsb_release -sc)/ ./ | tee /etc/apt/sources.list.d/proxysql.list

Mettez à jour et rafraîchissez à nouveau votre index de paquets Debian pour appliquer le nouveau dépôt.

sudo apt update

Avec les dépôts Percona XtraDB et ProxySQL ajoutés, vous pouvez installer les packages ProxySQL et Percona XtraDB client.

Installation de ProxySQL et du client Percona XtraDB Cluster

Dans cette section, vous installerez le package ProxySQL v2 avec le package client Percona XtraDB. Entrez la commande apt suivante pour installer à la fois les packages ProxySQL et Percona XtraDB client.

sudo apt install proxysql2 percona-xtradb-cluster-client

Lorsque vous y êtes invité, entrez y pour confirmer et appuyez sur ENTRER pour continuer.

Une fois ProxySQL installé, il s’exécutera automatiquement et démarrera automatiquement au démarrage du système. Entrez la commande systemctl suivante pour vérifier l’état du service ProxySQL.

sudo systemctl is-enabled proxysql  
sudo systemctl start proxysql

Une sortie ‘enabled‘ confirme que ProxySQL démarrera automatiquement au démarrage du système. Et la sortie ‘active (running)‘ confirme que ProxySQL est en cours d’exécution.

Ensuite, entrez la commande mysql suivante pour vous connecter au serveur ProxySQL avec l’utilisateur et le mot de passe par défaut ‘admin‘.

mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='proxysql-deb> '

Une fois connecté avec succès, vous devriez obtenir le shell ProxySQL comme suit. Vous vous êtes connecté au module d’administration de ProxySQL via le client Percona XtraDB.

Maintenant, entrez la requête suivante pour obtenir la liste des bases de données sur le serveur ProxySQL. Vous devriez voir plusieurs bases de données disponibles sur le serveur ProxySQL.

SHOW DATABASES;

Avec le serveur ProxySQL et le client Percona XtraDB Cluster installés, vous êtes prêt à ajouter MySQL ou Percona XtraDB Cluster au serveur ProxySQL et à configurer l’équilibrage de charge.

Ajout de MySQL Cluster/Percona XtraDB Cluster à ProxySQL

La première étape que vous devez effectuer après avoir installé le serveur ProxySQL est d’ajouter votre MySQL ou Percona XtraDB Cluster au serveur ProxySQL. Et cela peut être réalisé via le shell de ProxySQL. Cet exemple utilise le cluster Percona XtraDB avec les détails suivants :

Nom d'hôte    Adresse IP      Port  
---------------------------------  
pxc01       192.168.5.15    3306  
pxc02       192.168.5.16    3306  
pxc03       192.168.5.17    3306

Avant de commencer, assurez-vous que vous êtes connecté au shell de ProxySQL. Ensuite, entrez les requêtes suivantes pour ajouter vos serveurs MySQL au ProxySQL. Assurez-vous de modifier les détails des adresses IP et des ports avec les détails de votre cluster MySQL.

INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,'192.168.5.15',3306);  
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,'192.168.5.16',3306);  
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,'192.168.5.17',3306);

Une sortie ‘Query OK, 1 row affected‘ confirme que le serveur MySQL a été ajouté au serveur ProxySQL.

Entrez la requête suivante pour vérifier la liste des serveurs MySQL disponibles sur ProxySQL. Vous devriez voir trois serveurs MySQL ajoutés au serveur ProxySQL.

SELECT * FROM mysql_servers;

Avec le MySQL ou Percona XtraDB Cluster ajouté à ProxySQL, vous devrez maintenant configurer la surveillance du cluster Percona XtraDB sur ProxySQL.

Configuration de l’utilisateur de surveillance ProxySQL

Pour garantir que MySQL ou Percona XtraDB Cluster est sain, ProxySQL surveillera en continu tous les serveurs MySQL disponibles en arrière-plan. Pour ce faire, vous devez créer un nouvel utilisateur sur votre cluster Percona XtraDB, puis définir l’utilisateur de surveillance sur le serveur ProxySQL.

Connectez-vous à votre serveur Percona XtraDB Cluster, accédez au shell MySQL via la commande ‘mysql’, puis exécutez les requêtes MySQL suivantes pour créer un nouvel utilisateur ‘monitor’ avec le mot de passe ‘monitor’.

CREATE USER 'monitor'@'%' IDENTIFIED WITH mysql_native_password by 'monitor';  
GRANT USAGE ON *.* TO 'monitor'@'%';  
FLUSH PRIVILEGES;

SELECT USER,host FROM mysql.user;  
SHOW GRANTS FOR 'monitor'@'%';

Ensuite, passez au serveur ProxySQL et exécutez la requête suivante pour définir l’utilisateur qui sera utilisé pour surveiller le cluster Percona XtraDB. Cela changera la variable par défaut ‘mysql-monitor_username‘ avec l’utilisateur ‘monitor’ et la variable ‘mysql-monitor_password‘ pour le mot de passe ‘monitor‘.

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';  
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';

Entrez la requête suivante pour mettre à jour certains paramètres supplémentaires pour surveiller le cluster Percona XtraDB sur le serveur ProxySQL.

UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');

Enregistrez les modifications que vous avez apportées aux ‘global_variables‘ sur le serveur ProxySQL en exécutant les requêtes suivantes.

LOAD MYSQL VARIABLES TO RUNTIME;  
SAVE MYSQL VARIABLES TO DISK;

Vous devriez recevoir une sortie comme celle-ci.

Maintenant, exécutez la requête suivante pour vérifier la liste des ‘global_variables‘ pour ‘mysql-monitor_‘ sur le serveur ProxySQL.

SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';

Vous devriez obtenir une sortie comme la capture d’écran suivante - La variable ‘mysql-monitor_username’ doit avoir la valeur de l’utilisateur ‘monitor’ et la variable ‘mysql-monitor_password’ pour le mot de passe est ‘monitor’. De plus, vous pouvez voir la valeur des variables ‘mysql-monitor_connect_interval’,’mysql-monitor_ping_interval’,’mysql-monitor_read_only_interval’ changée en ‘2000‘.

Enfin, entrez les requêtes suivantes pour vérifier l’état de surveillance du cluster Percona XtraDB sur le serveur ProxySQL. Cela vous montrera les journaux du dernier état de connexion et du dernier ping du serveur ProxySQL vers tous les serveurs Percona XtraDB Cluster disponibles.

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 6;  
SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 6;

Lorsque cela réussit, vous devriez recevoir une sortie comme celle-ci.

Les derniers journaux de connexion du serveur ProxySQL au cluster Percona XtraDB.

Les derniers journaux de ping du serveur ProxySQL au cluster Percona XtraDB.

Avec le MySQL ou Percona XtraDB Cluster surveillé par le serveur ProxySQL, vous êtes maintenant prêt à configurer un nouvel utilisateur que vos applications utiliseront.

Configuration de l’utilisateur de base de données pour les applications

Avec le serveur ProxySQL installé et configuré, et le MySQL ou Percona XtraDB Cluster ajouté comme backend de base de données, vous allez maintenant configurer un nouvel utilisateur que vos applications utiliseront. Ainsi, au lieu de se connecter directement au serveur MySQL, vos applications devraient se connecter au serveur ProxySQL qui fonctionne par défaut sur le port ‘6033‘.

Pour configurer un nouvel utilisateur pour vos applications, vous devez suivre les étapes suivantes :

• Créer un nouvel utilisateur sur le cluster MySQL ou le cluster Percona XtraDB et configurer les privilèges.
• Ajouter votre utilisateur Percona XtraDB Cluster au serveur ProxySQL - insérer dans la table ‘mysql_users‘.

Pour commencer, retournez à votre serveur Percona XtraDB Cluster et connectez-vous au shell MySQL via l’utilisateur root. Ensuite, créez un nouvel utilisateur MySQL et accordez à l’utilisateur des privilèges pour accéder à toutes les bases de données en entrant les requêtes suivantes. Dans cet exemple, vous allez créer un nouvel utilisateur ‘sbuser‘ avec le mot de passe ‘sbpass‘. Assurez-vous également de changer l’adresse IP ‘192.168.5.85‘ avec l’adresse IP du serveur ProxySQL.

CREATE USER 'sbuser'@'192.168.5.85' IDENTIFIED WITH mysql_native_password by 'sbpass';  
GRANT ALL PRIVILEGES ON *.* TO 'sbuser'@'192.168.5.85';  
FLUSH PRIVILEGES;

Ensuite, retournez au serveur ProxySQL et exécutez la requête suivante pour ajouter votre nouvel utilisateur MySQL au serveur ProxySQL. Vos utilisateurs MySQL doivent être ajoutés à la table ‘mysql_users‘ sur le serveur ProxySQL. De plus, au moment de la rédaction de cet article, le serveur ProxySQL ne prend pas encore en charge les mots de passe cryptés.

INSERT INTO mysql_users (username,password) VALUES ('sbuser','sbpass');

Maintenant, entrez les requêtes suivantes pour confirmer et enregistrer les modifications sur votre serveur ProxySQL. Avec cela, votre nouvel utilisateur est prêt et vous pouvez utiliser cet utilisateur MySQL pour vos applications.

LOAD MYSQL USERS TO RUNTIME;  
SAVE MYSQL USERS TO DISK;

Avec cela, votre application devrait maintenant se connecter au serveur ProxySQL avec l’utilisateur ‘sbuser‘ et le mot de passe ‘sbpass‘ avec le port 6033.

Vérifier la base de données et l’utilisateur

Dans cette section, vous allez vérifier l’installation de ProxySQL en vous connectant au serveur ProxySQL avec l’utilisateur et le mot de passe que vous avez créés.

Entrez la commande ‘mysql’ suivante pour vous connecter au serveur ProxySQL avec le nouvel utilisateur ‘sbuser’ et le mot de passe ‘sbpass’. Lorsque cela réussit, vous devriez voir le shell du serveur ProxySQL.

mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033 --prompt='proxysql-deb> '

Entrez la requête suivante pour vérifier la liste des bases de données sur le cluster Percona XtraDB. Dans cet exemple, il y a une base de données ‘percona‘ sur le cluster Percona XtraDB.

show databases;

Passez à la base de données ‘percona‘ via la requête ‘USE‘ ci-dessous.

use percona

Ensuite, entrez les requêtes suivantes pour vérifier la liste des tables dans la base de données ‘percona‘. Ensuite, récupérez les données disponibles dans la base de données actuelle sur laquelle vous travaillez.

show tables;  
select * from example;

Vous devriez recevoir une sortie comme celle-ci - Les bases de données stockées sur le cluster Percona XtraDB sont accessibles depuis le serveur ProxySQL. Cela signifie que votre installation de ProxySQL en tant qu’équilibreur de charge pour le cluster Percona XtraDB a réussi.

Ensuite, entrez les requêtes suivantes pour insérer de nouvelles données dans la base de données ‘percona‘ et la table ‘example‘.

INSERT INTO percona.example VALUES (4, 'pxc04');  
INSERT INTO percona.example VALUES (5, 'pxc05');

Ensuite, vérifiez à nouveau les nouvelles données mises à jour en utilisant la requête suivante. Si cela réussit, vous devriez obtenir les nouvelles données disponibles dans la table ‘example‘. Cela confirme que votre utilisateur peut lire et écrire dans les bases de données sur le cluster Percona XtraDB.

select * from example;

Enfin, pour vous assurer que vos nouvelles données sont mises à jour sur le cluster Percona XtraDB, vous devez vérifier directement depuis le serveur Percona XtraDB.

Connectez-vous à votre serveur Percona XtraDB Cluster, accédez au shell MySQL via l’utilisateur root, puis exécutez la requête suivante pour récupérer les données de la base de données ‘percona‘ table ‘example‘.

select * from percona.example;

Lorsque cela réussit, vous devriez obtenir les nouvelles données que vous avez ajoutées via le serveur ProxySQL.

Avec cela en tête, votre installation du serveur ProxySQL en tant qu’équilibreur de charge pour le cluster MySQL ou le cluster Percona XtraDB est réussie.

Conclusion

Dans ce guide, vous avez configuré un équilibrage de charge du cluster MySQL ou du cluster Percona XtraDB via ProxySQL sur un serveur Debian 11. Vous avez installé ProxySQLv2, ajouté le cluster Percona XtraDB à ProxySQL et configuré la surveillance du cluster Percona XtraDB sur ProxySQL.

En plus de cela, vous avez également créé et configuré des utilisateurs MySQL ou Percona XtraDB Cluster qui peuvent être utilisés pour vos applications. Vous avez également vérifié l’équilibrage de charge du cluster MySQL ou du cluster Percona XtraDB via ProxySQL qui permet une haute disponibilité et une tolérance aux pannes de votre serveur de base de données et de vos applications.

Avec cela, vous pouvez maintenant ajouter plus de nœuds Percona XtraDB Cluster à votre serveur de base de données équilibré. De plus, vous pouvez configurer la répartition des lectures/écritures via ProxySQL, configurer le sharding de ProxySQL et configurer la connexion SSL de ProxySQL au cluster Percona XtraDB. Pour en savoir plus sur ProxySQL, visitez la documentation officielle de ProxySQL.