Come installare un server MySQL con bilanciamento del carico con ProxySQL su Debian 11

ProxySQL è un proxy MySQL ad alte prestazioni con un ingombro ridotto che può essere installato in più ambienti come VM, K8s, bare-metal e contenitori Docker. ProxySQL è una soluzione open-source che fornisce alta disponibilità per il tuo cluster MySQL con supporto per il multiplexing delle connessioni, fornisce regole di query per instradare le scritture ai primari e distribuisce le letture tra le repliche.

ProxySQL supporta sia AMD64 (x86_64) che ARM64. Fornisce un proxy a livello di applicazione per la tua applicazione e offre anche query avanzate con rilevamento dei guasti.

In questa guida, installerai e configurerai il bilanciamento del carico del cluster MySQL tramite ProxySQL su un server Debian 11. Installerai ProxySQL v2 con il client Percona XtraDB, quindi aggiungerai il cluster Percona XtraDB a ProxySQL, configurerai l’utente di monitoraggio di ProxySQL, quindi aggiungerai il database e l’utente che verranno utilizzati per le tue applicazioni.

Requisiti

Per iniziare con questa guida, devi avere i seguenti requisiti:

• Un server Linux che esegue il sistema Debian 11 - Questo esempio utilizza un server Debian con hostname ‘ proxysql-deb ‘.
• Un utente non root con privilegi di amministratore sudo/root.
• Cluster MySQL o Cluster Percona XtraDB completamente configurato.

Ecco fatto. Quando tutti i requisiti sono pronti, puoi ora iniziare l’installazione di ProxySQL.

Configurazione dei repository

Il primo passo che devi fare per questa guida è aggiungere sia i repository di ProxySQL che quelli di Percona XtraDB al tuo server. Il repository di Percona XtraDB sarà utilizzato per installare il pacchetto ‘ percona-xtradb-cluster-client ‘, di cui ProxySQL ha bisogno.

Per iniziare, installa alcune dipendenze di base inserendo il seguente comando apt. Inserisci y quando richiesto e premi INVIO per procedere.

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

Successivamente, scarica il file del repository per Percona XtraDB e installalo tramite il comando ‘ dpkg ‘ qui sotto.

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

Ora aggiorna e aggiorna l’indice dei pacchetti Debian utilizzando il seguente comando apt qui sotto. Dovresti vedere che il nuovo repository Percona XdtraDB è stato aggiunto.

sudo apt update

Quindi, inserisci il comando ‘ percona-release ‘ per abilitare il repository del cluster Percona XtraDB 8.0.

sudo percona-release setup pxc80

Con il repository Percona XtraDB aggiunto, ora esegui il seguente comando per aggiungere la chiave GPG e il repository per 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

Aggiorna e aggiorna di nuovo l’indice dei pacchetti Debian per applicare il nuovo repository.

sudo apt update

Con entrambi i repository Percona XtraDB e ProxySQL aggiunti, puoi installare i pacchetti ProxySQL e Percona XtraDB client.

Installazione di ProxySQL e Client del Cluster Percona XtraDB

In questa sezione, installerai il pacchetto ProxySQL v2 con il pacchetto client Percona XtraDB. Inserisci il seguente comando apt per installare entrambi i pacchetti ProxySQL e Percona XtraDB client.

sudo apt install proxysql2 percona-xtradb-cluster-client

Quando richiesto, inserisci y per confermare e premi INVIO per procedere.

Una volta installato ProxySQL, verrà eseguito automaticamente e avviato automaticamente all’avvio del sistema. Inserisci il seguente comando systemctl per verificare lo stato del servizio ProxySQL.

sudo systemctl is-enabled proxysql  
sudo systemctl start proxysql

Un output ‘ enabled ‘ conferma che ProxySQL si avvierà automaticamente all’avvio del sistema. E l’output ‘ active (running) ‘ conferma che ProxySQL è in esecuzione.

Successivamente, inserisci il seguente comando mysql per accedere al server ProxySQL con l’utente e la password predefiniti ‘ admin ‘.

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

Una volta riuscito, dovresti ottenere la shell di ProxySQL come segue. Ti sei connesso al modulo di amministrazione di ProxySQL tramite il client Percona XtraDB.

Ora inserisci la seguente query per ottenere l’elenco dei database sul server ProxySQL. Dovresti vedere più database disponibili sul server ProxySQL.

SHOW DATABASES;

Con il server ProxySQL e il client del cluster Percona XtraDB installati, sei pronto per aggiungere MySQL o il cluster Percona XtraDB al server ProxySQL e configurare il bilanciamento del carico.

Aggiunta del Cluster MySQL/Cluster Percona XtraDB a ProxySQL

Il primo passo che devi fare dopo aver installato il server ProxySQL è aggiungere il tuo MySQL o il cluster Percona XtraDB al server ProxySQL. E questo può essere realizzato tramite la shell di ProxySQL. Questo esempio utilizza il cluster Percona XtraDB con i seguenti dettagli:

Hostname    IP Address      Port  
---------------------------------  
pxc01       192.168.5.15    3306  
pxc02       192.168.5.16    3306  
pxc03       192.168.5.17    3306

Prima di iniziare, assicurati di essere connesso alla shell di ProxySQL. Quindi, inserisci le seguenti query per aggiungere i tuoi server MySQL a ProxySQL. Assicurati di modificare i dettagli degli indirizzi IP e delle porte con i dettagli del tuo 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);

Un output ‘ Query OK, 1 row affected ‘ conferma che il server MySQL è stato aggiunto al server ProxySQL.

Inserisci la seguente query per verificare l’elenco dei server MySQL disponibili su ProxySQL. Dovresti vedere tre server MySQL aggiunti al server ProxySQL.

SELECT * FROM mysql_servers;

Con il cluster MySQL o Percona XtraDB aggiunto a ProxySQL, dovrai ora configurare il monitoraggio del cluster Percona XtraDB su ProxySQL.

Configurazione dell’utente di monitoraggio di ProxySQL

Per garantire che MySQL o il cluster Percona XtraDB sia sano, ProxySQL monitorerà continuamente tutti i server MySQL disponibili. Per raggiungere questo obiettivo, devi creare un nuovo utente sul tuo cluster Percona XtraDB, quindi definire l’utente di monitoraggio sul server ProxySQL.

Accedi al tuo server del cluster Percona XtraDB, accedi alla shell di MySQL tramite il comando ‘mysql’, quindi esegui le seguenti query MySQL per creare un nuovo utente ‘monitor’ con la password ‘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'@'%';

Successivamente, spostati sul server ProxySQL ed esegui la seguente query per definire l’utente che verrà utilizzato per monitorare il cluster Percona XtraDB. Questo cambierà la variabile predefinita ‘ mysql-monitor_username ‘ con l’utente ‘monitor’ e la variabile ‘ mysql-monitor_password ‘ per la password ‘ 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';

Inserisci la seguente query per aggiornare alcuni parametri aggiuntivi per il monitoraggio del cluster Percona XtraDB sul server 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');

Salva le modifiche che hai effettuato su ‘ global_variables ‘ all’interno del server ProxySQL eseguendo le seguenti query.

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

Dovresti ricevere un output simile a questo.

Ora esegui la seguente query per verificare l’elenco delle ‘ global_variables ‘ per ‘ mysql-monitor_ ‘ sul server ProxySQL.

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

Dovresti ottenere un output simile allo screenshot seguente - La variabile ‘mysql-monitor_username’ deve avere il valore dell’utente ‘monitor’ e la variabile ‘mysql-monitor_password’ per la password è ‘monitor’. Inoltre, puoi vedere che il valore delle variabili ‘mysql-monitor_connect_interval’,’mysql-monitor_ping_interval’,’mysql-monitor_read_only_interval’ i è cambiato in ‘ 2000 ‘.

Infine, inserisci le seguenti query per verificare lo stato di monitoraggio del cluster Percona XtraDB sul server ProxySQL. Questo ti mostrerà i log dell’ultimo stato di log e dell’ultimo ping dal server ProxySQL a tutti i server del cluster Percona XtraDB disponibili.

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;

Quando avrai successo, dovresti ricevere un output simile a questo.

Gli ultimi log di connessione del server ProxySQL al cluster Percona XtraDB.

Gli ultimi log di ping del server ProxySQL al cluster Percona XtraDB.

Con il MySQL o il cluster Percona XtraDB monitorato dal server ProxySQL, sei ora pronto per configurare un nuovo utente che le tue applicazioni utilizzeranno.

Configurazione dell’utente del database per le applicazioni

Con il server ProxySQL installato e configurato, e il MySQL o il cluster Percona XtraDB aggiunto come backend del database, ora configurerai un nuovo utente che le tue applicazioni utilizzeranno. Quindi, invece di connettersi direttamente al server MySQL, le tue applicazioni dovrebbero connettersi al server ProxySQL che viene eseguito per impostazione predefinita sulla porta ‘ 6033 ‘.

Per configurare un nuovo utente per le tue applicazioni, devi seguire i seguenti passaggi:

• Crea un nuovo utente sul cluster MySQL o sul cluster Percona XtraDB e configura i privilegi.
• Aggiungi il tuo utente del cluster Percona XtraDB al server ProxySQL - inserisci nella tabella ‘ mysql_users ‘.

Per iniziare, torna al tuo server del cluster Percona XtraDB e accedi alla shell di MySQL tramite l’utente root. Quindi, crea un nuovo utente MySQL e concedi all’utente i privilegi per accedere a tutti i database inserendo le seguenti query. In questo esempio, creerai un nuovo utente ‘ sbuser ‘ con la password ‘ sbpass ‘. Inoltre, assicurati di cambiare l’indirizzo IP ‘ 192.168.5.85 ‘ con l’indirizzo IP del server 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;

Successivamente, torna al server ProxySQL ed esegui la seguente query per aggiungere il tuo nuovo utente MySQL al server ProxySQL. I tuoi utenti MySQL dovrebbero essere aggiunti alla tabella ‘ mysql_users ‘ sul server ProxySQL. Inoltre, al momento della scrittura, il server ProxySQL non supporta ancora password criptate.

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

Ora inserisci le seguenti query per confermare e salvare le modifiche sul tuo server ProxySQL. Con questo, il tuo nuovo utente è pronto e puoi utilizzare questo utente MySQL per le tue applicazioni.

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

Con questo, la tua applicazione dovrebbe ora connettersi al server ProxySQL con l’utente ‘ sbuser ‘ e la password ‘ sbpass ‘ con la porta 6033.

Verifica del database e dell’utente

In questa sezione, verificherai l’installazione di ProxySQL connettendoti al server ProxySQL utilizzando l’utente e la password che hai creato.

Inserisci il seguente comando ‘mysql’ per accedere al server ProxySQL con il nuovo utente ‘sbuser’ e la password ‘sbpass’. Quando avrai successo, dovresti vedere la shell del server ProxySQL.

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

Inserisci la seguente query per controllare l’elenco dei database sul cluster Percona XtraDB. In questo esempio, c’è un database ‘ percona ‘ sul cluster Percona XtraDB.

show databases;

Passa al database ‘ percona ‘ tramite la query ‘ USE ‘ qui sotto.

use percona

Successivamente, inserisci le seguenti query per controllare l’elenco delle tabelle nel database ‘ percona ‘. Quindi, recupera i dati disponibili nel database attuale su cui stai lavorando.

show tables;  
select * from example;

Dovresti ricevere un output simile a questo - I database memorizzati nel cluster Percona XtraDB sono accessibili dal server ProxySQL. Questo significa che la tua installazione di ProxySQL come bilanciatore di carico per il cluster Percona XtraDB è stata un successo.

Successivamente, inserisci le seguenti query per inserire nuovi dati nel database ‘ percona ‘ e nella tabella ‘ example ‘.

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

Quindi, verifica di nuovo i dati appena aggiornati utilizzando la seguente query. Se avrai successo, dovresti ottenere i nuovi dati disponibili nella tabella ‘ example ‘. Questo conferma che il tuo utente può leggere e scrivere nei database sul cluster Percona XtraDB.

select * from example;

Infine, per garantire che i tuoi nuovi dati siano aggiornati sul cluster Percona XtraDB, devi verificare direttamente dal server del cluster Percona XtraDB.

Accedi al tuo server del cluster Percona XtraDB, accedi alla shell di MySQL tramite l’utente root, quindi esegui la seguente query per recuperare i dati dalla tabella ‘ example ‘ del database ‘ percona ‘.

select * from percona.example;

Quando avrai successo, dovresti ottenere i nuovi dati che hai aggiunto tramite il server ProxySQL.

Tenendo presente questo, la tua installazione del server ProxySQL come bilanciatore di carico per il cluster MySQL o il cluster Percona XtraDB è stata un successo.

Conclusione

In questa guida, hai configurato un bilanciamento del carico del cluster MySQL o del cluster Percona XtraDB tramite ProxySQL su un server Debian 11. Hai installato ProxySQLv2, aggiunto il cluster Percona XtraDB a ProxySQL e configurato il monitoraggio del cluster Percona XtraDB su ProxySQL.

In aggiunta a ciò, hai anche creato e configurato utenti MySQL o Percona XtraDB Cluster che possono essere utilizzati per le tue applicazioni. Hai anche verificato il bilanciamento del carico del cluster MySQL o del cluster Percona XtraDB tramite ProxySQL che consente alta disponibilità e tolleranza ai guasti del tuo server di database e delle tue applicazioni.

Con questo, puoi ora aggiungere più nodi del cluster Percona XtraDB al tuo server di database bilanciato. Inoltre, puoi configurare la divisione delle letture/scritture tramite ProxySQL, configurare lo sharding di ProxySQL e configurare la connessione SSL da ProxySQL al cluster Percona XtraDB. Per saperne di più su ProxySQL, visita la documentazione ufficiale di ProxySQL.