Introduzione
Come suggerisce il suo nome, ProxySQL è un server proxy compatibile con il linguaggio SQL, collocabile tra la tua applicazione e il suo database.
Configurando la memorizzazione nella cache di MySQL, il risultato di una query viene archiviato in modo tale che, quando la stessa viene ripetuta, possa essere restituito il risultato senza dover riordinare il database. Questo sistema permette di aumentare notevolmente la velocità delle query più comuni.
Per abilitare la cache generalmente gli sviluppatori devono modificare il codice della loro applicazione, con il rischio che possano crearsi dei bug all'interno del codice sorgente. Per evitare ogni tipo di problema di questa natura, ProxySQL consente di impostare la cache trasparente.
In questo tutorial, vedremo quindi come utilizzare il tool ProxySQL per configurare la cache trasparente attraverso un server MySQL su Ubuntu 20.04.
Per prima cosa dovrai connetterti al tuo server mediante SSH. Se non l’hai ancora fatto, ti consigliamo di seguire la nostra guida per connetterti in sicurezza con il protocollo SSH. In caso di server locale puoi passare al punto successivo e aprire il terminale del tuo server.
Installazione di MySQL Server
Per prima cosa dovrai installare il server MySQL e configurarlo in modo da poterlo utilizzare tramite ProxySQL:
sudo apt-get update
sudo apt-get install mysql-server -y
Completata l'installazione, utilizza il seguente comando per creare una password come utente amministratore di sistema:
mysql_secure_installation
Ora che il tuo server MySQL è pronto per essere utilizzato, è necessario aggiungere un utente di monitoraggio per ProxySQL. Questo utente verrà utilizzato da ProxySQL per ottenere informazioni sul server MySQL e determinare il suo stato di operatività.
Innanzitutto, accedi a MySQL utilizzando l'utente root (amministratore di sistema) e la password precedentemente creata:
mysql -u root -p
Ora dovrai creare due utenti, uno denominato "psql_mon" per ProxySQL e un altro che utilizzerai per eseguire le query dei client e concedere loro i privilegi appropriati:
mysql > CREATE USER 'psql_mon'@'%' IDENTIFIED BY 'psql_mon_password';
Attraverso questo comando, stai creando un nuovo utente chiamato "psql_mon", associato alla password “psql_mon_password” (ricordati di cambiarla per garantire un adeguato livello di sicurezza).
Prosegui quindi utilizzando il comando GRANT per fornire i privilegi necessari a ProxySQL:
mysql > GRANT SELECT ON sys.* TO 'psql_mon'@'%';
Dopo aver completato, puoi procedere creando l’utente necessario a ProxySQL per accedere al database di riferimento:
mysql > CREATE USER 'test_user'@'%' IDENTIFIED BY 'test_user_password';
mysql > GRANT ALL PRIVILEGES on *.* TO 'test_user'@'%';
Completa la configurazione delle utenze attraverso il comando:
mysql > FLUSH PRIVILEGES;
Infine, esci dalla shell MySQL:
mysql > exit;
Ora hai installato mysql-server e creato un utente da utilizzare per ProxySQL per monitorare il tuo server MySQL e un altro per eseguire le query del client. Successivamente installerai e configurerai ProxySQL.
ProxySQL: Installazione e configurazione
A questo punto puoi procedere con l’installazione del server ProxySQL, che sarà utilizzato come cache per le tue query: il servizio si inserirà fra la query effettuate dai client, e il server reale MySQL, permettendoti di ottenere dei risultati più velocemente.
La pagina ufficiale di Github ti permetterà di scaricare i file di installazione necessari utilizzando il comando wget.
wget https://github.com/sysown/proxysql/releases/download/v2.0.4/proxysql_2.0.4-ubuntu16_amd64.deb
A seguire, installa il pacchetto usando dpkg:
sudo dpkg -i proxysql_2.0.4-ubuntu16_amd64.deb
Una volta installato, avvia ProxySQL con questo comando:
sudo systemctl start proxysql
Per configurare il servizio ProxySQL, puoi utilizzare la sua interfaccia SQL di amministrazione, che per impostazioni predefinite ha come nome utente e password "admin" ed è in ascolto sulla porta 6032.
mysql -uadmin -p -h 127.0.0.1 -P6032
Quando richiesto utilizza la password “admin” per accedere e procedi configurando l'utente di monitoraggio precedentemente creato:
mysql > UPDATE global_variables SET variable_value='psql_mon' WHERE variable_name='mysql-monitor_username';
Utilizzando il precedente comando, stai modificando la variabile “mysql-monitor_username” utilizzata da ProxySQL per controllare lo stato del server MySQL.
Invece, con il seguente comando imposti la password da utilizzare (ricordati di cambiarla con quella inserita nello step precedente):
mysql > UPDATE global_variables SET variable_value='psql_mon_password' WHERE variable_name='mysql-monitor_password';
Quando crei una modifica nell'interfaccia di amministrazione di ProxySQL, utilizza il comando LOAD corretto affinché si possano applicare le modifiche all'istanza di ProxySQL in esecuzione. Hai modificato le variabili globali di MySQL, quindi caricale su RUNTIME per applicare le modifiche:
mysql > LOAD MYSQL VARIABLES TO RUNTIME;
A seguire, SALVA le modifiche al database su disco fisico per rendere persistenti le modifiche tra i riavvii. ProxySQL utilizza un database locale di pertinenza propria, SQLite, affinché possa memorizzare le proprie tabelle e variabili:
mysql > SAVE MYSQL VARIABLES TO DISK;
A questo punto devi modificare le impostazioni per i server MySQL gestiti da ProxySQL. La tabella "mysql_servers" contiene informazioni su ciascun server gestito e puoi andare a modificarla utilizzando i comandi:
mysql >INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 3306);
Ora, potrai comunicare a ProxySQL quale utente si connetterà al server MySQL. Utilizzando la tabella "mysql_users", che contiene le informazioni sugli utenti utilizzati, imposta il nome utente e password creati in precedenza come segue (ricordati sempre di cambiare la password se necessario):
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('test_user', 'test_user_password', 1);
Per applicare tutte le modifiche fatte, esegui i comandi LOAD e SAVE:
mysql > LOAD MYSQL SERVERS TO RUNTIME;
mysql > SAVE MYSQL SERVERS TO DISK;
Puoi quindi uscire dalla console di gestione:
mysql > exit;
Verifica del funzionamento dell’installazione di ProxySQL
A questo punto puoi procedere verificando che il collegamento con ProxySQL funzioni correttamente.
In questo caso andrai a creare un database di esempio, inserendo dei dati al suo interno. Procedi quindi collegandoti al database attraverso il comando:
mysql -utest_user -hlocalhost -p -P6033
Inserisci la password di "test_user" quando richiesta e prosegui creando il database:
mysql> CREATE DATABASE test;
mysql> USE test;
Utilizza la seguente sintassi per creare una tabella ed inserire una riga al suo interno:
mysql> CREATE TABLE test_table ( field1 VARCHAR( 30 ) );
mysql> INSERT INTO test_table VALUES ( 'test_value' );
A questo punto, utilizzando il comando SELECT, controlla che MySQL ti restituisca la riga appena inserita:
mysql> SELECT * FROM test_table;
+------------+
| field1 |
+------------+
| test_value |
+------------+
1 row in set (0.00 sec)
Se l'installazione di ProxySQL funziona correttamente, visualizzerai il risultato mostrato sopra.
Per eliminare le tracce del test appena effettuato, utilizza i comandi DROP:
mysql> DROP TABLE test_table;
mysql> DROP DATABASE test;