Come accedere da remoto a MySQL

Introduzione

Molti siti Web e applicazioni iniziano con il server Web e il back-end del database ospitati sulla stessa macchina. Con il tempo, tuttavia, una configurazione come questa può diventare ingombrante e difficile da scalare. Una soluzione comune consiste nel separare queste funzioni impostando un database remoto, consentendo al server e al database di crescere al proprio ritmo sulle proprie macchine.

Uno dei problemi più comuni che incontrano gli utenti quando tentano di configurare un database MySQL remoto è che la loro istanza MySQL è configurata solo per ascoltare le connessioni locali. Questa è l'impostazione predefinita di MySQL, ma non funzionerà per una configurazione di database remota poiché MySQL deve essere in grado di ascoltare un indirizzo IP esterno in cui è possibile raggiungere il server.

Abilitare l'accesso remoto

Per abilitare l'accesso remoto a MySQL, apri il tuo file mysqld.cnf:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Passare alla riga che inizia con la direttiva bind-address. Sembrerà così:

. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
. . .

Per impostazione predefinita, questo valore è impostato su 127.0.0.1, il che significa che il server cercherà solo connessioni locali. Sarà necessario modificare questa direttiva per fare riferimento a un indirizzo IP esterno. Ai fini della risoluzione dei problemi, è possibile impostare questa direttiva su un indirizzo IP jolly, *, ::o 0.0.0.0:

. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0
. . .

Nota: in alcune versioni di MySQL la direttiva bind-address potrebbe non essere nel file mysqld.cnf per impostazione predefinita. In questo caso, aggiungi la seguente riga evidenziata in fondo al file:/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
bind-address            = 0.0.0.0

Dopo aver modificato questa riga, salva e chiudi il file (CTRL + X, Y, quindi ENTERse lo hai modificato con nano).

Quindi riavvia il servizio MySQL per rendere effettive le modifiche apportate :

sudo systemctl restart mysql

Se disponi di un account utente MySQL esistente che prevedi di utilizzare per connetterti al database dal tuo host remoto, dovrai riconfigurare tale account per connetterti dal server remoto invece che da localhost . Per fare ciò, apri il client MySQL come utente MySQL root o con un altro account utente privilegiato:

sudo mysql

Se hai abilitato l'autenticazione della password per root , dovrai invece utilizzare il seguente comando per accedere alla shell MySQL:

mysql -u root -p

Cambiare l'host di un utente

Per cambiare l'host di un utente, puoi usare il comando di MySQL RENAME USER. Esegui il seguente comando, assicurandoti di modificare il nome noviello del tuo account utente MySQL e l'indirizzo IP del tuo server remoto remote_server_ip:

RENAME USER 'noviello'@'localhost' TO 'noviello'@'remote_server_ip';

Creare un nuovo utente

In alternativa, puoi creare un nuovo account utente che si connetterà solo dall'host remoto con il seguente comando:

CREATE USER 'noviello'@'remote_server_ip' IDENTIFIED BY 'password';

Nota : questo comando creerà un utente che si autentica con il plug-in di autenticazione predefinito di MySQL, caching_sha2_password. Tuttavia, esiste un problema noto con alcune versioni di PHP che può causare problemi con questo plug-in.

Se prevedi di utilizzare questo database con un'applicazione PHP, ad esempio phpMyAdmin, potresti voler creare un utente remoto che si autenticherà con il plug-in precedente mysql_native_password, sebbene ancora sicuro:

CREATE USER 'noviello'@'remote_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';

Se non sei sicuro, puoi sempre creare un utente che si autentichi con caching_sha2_plugin e ALTER successivamente con questo comando:

ALTER USER 'noviello'@'remote_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';

Quindi concedi al nuovo utente i privilegi appropriati per le tue particolari esigenze. L'esempio seguente concede a un utente i privilegi globali a CREATE, ALTER e DROP database, tabelle e utenti, nonché l'alimentazione a INSERT, UPDATEe DELETE dati da qualsiasi tabella sul server. Concede inoltre all'utente la possibilità di interrogare i dati con SELECT, creare chiavi esterne con la parola REFERENCES chiave ed eseguire FLUSHoperazioni con il privilegio RELOAD. Tuttavia, dovresti concedere agli utenti solo le autorizzazioni di cui hanno bisogno, quindi sentiti libero di modificare i privilegi del tuo utente se necessario.

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'noviellO'@'remote_server_ip' WITH GRANT OPTION;

Successivamente, è buona norma eseguire il comando FLUSH PRIVILEGES. Ciò libererà tutta la memoria che il server ha memorizzato nella cache come risultato delle istruzioni CREATE USER e GRANT precedenti:

FLUSH PRIVILEGES;

Quindi puoi uscire dal client MySQL:

exit

Configurare il firewall

Infine, supponendo che tu abbia configurato un firewall sul tuo server di database, dovrai anche aprire la porta 3306, la porta predefinita di MySQL, per consentire il traffico verso MySQL.

Se si prevede di accedere al server del database solo da una macchina specifica, è possibile concedere a quella macchina l'autorizzazione esclusiva per connettersi al database in remoto con il seguente comando. Assicurati di sostituire remote_IP_address con l'indirizzo IP effettivo della macchina con cui prevedi di connetterti:

sudo ufw allow from remote_IP_address to any port 3306

Se hai bisogno di accedere al database da altre macchine in futuro, puoi concedere loro l'accesso su base ad hoc con questo comando. Ricorda solo di includere i rispettivi indirizzi IP.

In alternativa, puoi consentire le connessioni al tuo database MySQL da qualsiasi indirizzo IP con il seguente comando:

Attenzione : questo comando consentirà a chiunque di accedere al tuo database MySQL. Non eseguirlo se il tuo database contiene dati sensibili.

sudo ufw allow 3306

Successivamente, prova ad accedere al tuo database in remoto da un'altra macchina:

Nota : se hai aggiunto una regola firewall per consentire solo connessioni da un indirizzo IP specifico, devi provare ad accedere al database con la macchina associata a quell'indirizzo.

mysql -u user -h database_server_ip -p

Conclusione

Se sei in grado di accedere al tuo database, conferma che la direttiva bind-address nel tuo file di configurazione era il problema. Tieni presente, tuttavia, che l'impostazione bind-address su 0.0.0.0 non è sicura in quanto consente le connessioni al tuo server da qualsiasi indirizzo IP. D'altra parte, se non riesci ancora ad accedere al database da remoto, allora qualcos'altro potrebbe causare il problema.