Come utilizzare MariaDB e MySQL come database remoto su altro server

MariaDB è un sistema di database relazionale gratuito, open source e uno dei più popolari in tutto il mondo. È un sostituto immediato del sistema di database MySQL. Tuttavia, la struttura e gli indici di entrambi i sistemi di database sono gli stessi. Ciò ti consente di cambiare il tuo database da MySQL a MariaDB senza dover modificare le tue applicazioni.

Per impostazione predefinita, MariaDB è configurata per l’ascolto solo su localhost. Quindi è possibile accedervi solo da utenti locali che operano sullo stesso server del database. Nella moderna architettura delle applicazioni Web, la maggior parte dei backend dei database sono ospitati sul proprio server dedicato. Uno dei modi più semplici per raggiungere questo obiettivo è configurare il database per consentire connessioni remote.

Un altro caso d’uso è se desideri utilizzare un software di gestione remota con i tuoi database come MySQL Workbench, HeidiSQL, DBeaver, Sequel Pro o simili.

In questo tutorial ti mostreremo come configurare MariaDB per le connessioni remote utilizzando la dashboard Webdock o manualmente con accesso alla shell su un Server Ubuntu che esegue MariaDB precedente alla v10.6.

Abilitazione dell’accesso remoto nella dashboard Webdock

Abbiamo cercato di rendere relativamente semplice la creazione di un database con accesso remoto in Webdock. Questo approccio può essere considerato non sicuro, vedere la nota seguente.

I passi sono come segue:

1. Esegui lo script “Abilita accesso MySQL remoto” sul tuo server (disponibile nella Libreria di script)
2. Crea un nuovo database e un utente database che contenga la parola “remoto” nel nome utente. Come “ilmioutentedeldatabaseremoto”

Nota: quando si utilizza questo metodo si deve aprire la porta 3306 al mondo e chiunque possa indovinare il nome utente e la password del database può ottenere l’accesso. Se desideri limitare gli utenti o la porta del firewall, ad esempio tramite IP, devi seguire il metodo di configurazione manuale riportato di seguito.

Se devi concedere l’accesso remoto a un database esistente dovrai concedere i privilegi appropriati a un utente nuovo o esistente in MySQL utilizzando la riga di comando o in alternativa utilizzando phpmyadmin – consulta le istruzioni su come ottenere ciò sulla riga di comando in le istruzioni riportate di seguito.

Configurazione manuale utilizzando la riga di comando

Prerequisiti

• Una nuova istanza Ubuntu cloud con LEMP / LAMP installato.

Verifica il server MariaDB

Prima di iniziare, assicurati che il server MariaDB sia in esecuzione utilizzando il seguente comando:

$ ps -ef | grep -i mysql

Dovresti ottenere il seguente output:

mysql        595       1  0 04:17 ?        00:00:00 /usr/sbin/mysqld
root        1350    1337  0 04:22 pts/0    00:00:00 grep --color=auto -i mysql

Per impostazione predefinita, il server MariaDB è in ascolto su localhost solo per motivi di sicurezza. Puoi verificarlo con il seguente comando:

$ netstat -ant | grep 3306

Nel seguente output, dovresti vedere che il server MariaDB è in ascolto su localhost (127.0.0.1):

tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN

Configura MariaDB

Per impostazione predefinita, MariaDB consente la connessione solo dall’host locale, tutte le connessioni da un server remoto sono negate per impostazione predefinita.

La prima cosa che devi fare è configurare il server MariaDB per ascoltare tutti gli indirizzi IP sul sistema.

Puoi farlo modificando il file di configurazione predefinito di MariaDB. Cerca la direttiva “bind-address” in queste due posizioni (apporta la modifica in qualunque file trovi quella direttiva). Puoi aprire il file utilizzando il tuo editor di testo preferito:

$ nano /etc/mysql/my.cnf

O

$ sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Modificare il valore dell’indirizzo di collegamento da 127.0.0.1 A 0.0.0.0 in modo che il server MariaDB accetti connessioni su tutte le interfacce IPv4 dell’host.

bind-address = 0.0.0.0

Salva e chiudi il file quando hai finito. Quindi, riavvia il servizio MariaDB per applicare le modifiche:

$ sudo systemctl restart mariadb

Ora puoi verificare lo stato di ascolto di MariaDB con il seguente comando:

$ netstat -ant | grep 3306

Se tutto va bene, dovresti ottenere il seguente output:

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN

Ora il server MariaDB è configurato per ascoltare tutti gli indirizzi IP.

Nota: Se stai utilizzando MySQL Server anziché MariaDB, il file di configurazione principale si trova in /etc/mysql/mysql.conf.d/mysqld.cnf. È possibile modificare l’indirizzo di collegamento modificando questo file.

I passaggi rimanenti sono tutti uguali a quelli di MariaDB.

Concedere l’accesso a un utente da un sistema remoto

In questa sezione creeremo un nuovo database denominato wpdb e un utente denominato wpuser e concederemo l’accesso al sistema remoto per connettersi al database wpdb come utente wpuser.

Innanzitutto, accedi alla shell MariaDB con il seguente comando:

$ mysql -u admin -p

Fornisci la password dell’amministratore (root) come mostrato nel backend Webdock e quando ricevi la richiesta crea un database e un utente con il seguente comando:

MariaDB [(none)]> CREATE DATABASE wpdb;
MariaDB [(none)]> CREATE USER  'wpuser'@'localhost' IDENTIFIED BY 'password';

Successivamente, dovrai concedere le autorizzazioni al sistema remoto con indirizzo IP 208.117.84.50 per connettersi al database denominato wpdb come utente wpuser. Puoi farlo con il seguente comando:

MariaDB [(none)]> GRANT ALL ON wpdb.* to 'wpuser'@'208.117.84.50' IDENTIFIED BY 'password' WITH GRANT OPTION;

Successivamente, svuota i privilegi ed esci dalla shell MariaDB con il seguente comando:

MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> EXIT;

Di seguito è riportata una breve spiegazione di ciascun parametro:

• wpdb: È il nome del database MariaDB a cui l’utente vuole connettersi.

• wpuser: È il nome dell’utente del database MariaDB.

• 208.117.84.50: È l’indirizzo IP del sistema remoto dal quale l’utente vuole connettersi.

• parola d’ordine: È la password dell’utente del database.

Se desideri concedere l’accesso remoto a tutti i database per wpuser, esegui il comando seguente:

MariaDB [(none)]> GRANT ALL ON *.* to 'wpuser'@'208.117.84.50' IDENTIFIED BY 'password' WITH GRANT OPTION;

Se desideri concedere l’accesso a tutti gli indirizzi IP remoti su wpdb come wpuser, utilizza % invece dell’indirizzo IP (208.117.84.50) come mostrato di seguito:

MariaDB [(none)]> GRANT ALL ON wpdb.* to 'wpuser'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

Se desideri concedere l’accesso a tutti gli indirizzi IP nella sottorete 208.117.84.0/24 su wpdb come utente wpuser, esegui il comando seguente:

MariaDB [(none)]> GRANT ALL ON wpdb.* to 'wpuser'@'208.117.84.%' IDENTIFIED BY 'password' WITH GRANT OPTION;

Configura firewall

Se il tuo server MariaDB è configurato con il firewall UFW (che è di default su tutti i server), dovrai consentire il traffico sulla porta 3306 dal sistema remoto.

È possibile concedere l’accesso al sistema remoto con IP 208.117.84.50 per connettere la porta 3306 con il seguente comando:

$ sudo ufw allow from 208.117.84.50 to any port 3306

Se desideri concedere l’accesso da qualsiasi indirizzo IP, utilizzerai il seguente comando:

$ sudo ufw allow 3306

Successivamente, ricarica il firewall con il seguente comando:

$ sudo ufw reload

Una volta terminato, puoi procedere al passaggio successivo.

Testare la connessione dal sistema remoto

A questo punto il server MariaDB è configurato per consentire la connessione dal sistema remoto con indirizzo IP 208.117.84.50. Ora è il momento di testare la connessione dal sistema client al server MariaDB. Qui mostriamo come eseguire questa operazione dalla riga di comando in Linux, ma puoi anche testarlo dal tuo desktop (se non su Linux) utilizzando qualsiasi gestore remoto MySQL come quelli elencati nell’introduzione a questo articolo.

Innanzitutto, dovrai installare il pacchetto MariaDB Client nel sistema remoto. Puoi installarlo con il seguente comando:

$ sudo apt-get install mariadb-client -y

Una volta completata l’installazione, connettiti al server MariaDB eseguendo il seguente comando sul sistema remoto:

mysql -u wpuser -h 45.148.28.101 -p

Ti verrà chiesto di fornire la password del wpuser come mostrato di seguito:

Enter password:

Una volta stabilita la connessione, dovresti ottenere il seguente output:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 50
Server version: 10.4.10-MariaDB-1:10.4.10+maria~bionic-log mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Ora puoi elencare i database utilizzando il seguente comando:

MariaDB [(none)]> show databases;

Dovresti ottenere il seguente output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| wpdb               |
+--------------------+
2 rows in set (0.10 sec)

Conclusione

Ci auguriamo che questa guida ti aiuti a capire come connettere la tua applicazione web al database ospitato sul server remoto e come proteggerlo adeguatamente con le restrizioni IP.