Come connettersi a un database MySQL via SSH e lavorare con MySQL Workbench (guida rapida)

Hai appena acquistato il tuo VPS o server dedicato, ti sei connesso in SSH e ci hai installato sopra MySQL. Bene! Ma... ora come si fa a creare database e tabelle, per poi inserire, modificare ed eliminare dati? Ebbene: in questa guida vedremo proprio come connettersi a MySQL via SSH tramite MySQL Workbench, il programma gratuito con interfaccia grafica che permette di lavorare comodamente sui database visualizzando i dati sottoforma di tabelle composte da righe e colonne. La connessione avviene, appunto, tramite SSH: questo significa che funziona anche se sul server è presente un firewall e/o MySQL non è esposto pubblicamente su Internet (configurazione consigliata)

In questa guida ci concentreremo nello specifico sulla procedura per collegarci a MySQL con MySQL Workbench tramite SSH. Se invece stai cercando la guida per installare MySQL, fai riferimento a questi altri approfondimenti:

• MySQL su Linux (Ubuntu): Guida: come installare MySQL 8 su Ubuntu
• MySQL su Windows: Guida: come installare MySQL 8 su PC Windows

Per prima cosa: connessione MySQL da linea di comando

Prima ancora di iniziare a smanettare con MySQL Workbench, ti stra-raccomando di accedere al server MySQL in SSH e provare a connetterti tramite il client MySQL da linea di comando: questo ti confermerà che il processo è in esecuzione e accetta connessioni dai client. Allo scopo, impartisci il seguente comando sul sistema sul quale è in esecuzione MySQL:

mysql -u nomeUtente -p

Sostituendo, ovviamente, nomeUtente con il nome dell'utente che utilizzerai per accedere a MySQL. Se sei tu stesso l'amministratore del database in questione e non hai ancora creato utenti, dovrai usare l'account root. Il comando diventa quindi:

mysql -u root -p

Quando richiesto, digita la password prestando attenzione al fatto che questo è un account di MySQL, e non del sistema Linux!

» Leggi anche: [risolto] MySQL, password di root dimenticata: come reimpostare/resettare la password di root di MySQL?

Non appena vedrai il prompt mysql>, significa che la connessione è stata stabilita e sei "dentro"!

Per fare una prova veloce, visualizza la lista dei database impartendo:

show databases;

Se riesci ad arrivare fino a qui, il sistema funziona correttamente. In caso contrario, è inutile procedere con un tool ad interfaccia grafica: devi prima capire dove sta il problema e risolverlo.

Per uscire, basta impartire exit;.

Installare MySQL Workbench

Procedi ora a installare MySQL Workbench sul tuo PC "di lavoro", ovvero quello dotato di interfaccia grafica. Scaricalo per Windows, Linux o Mac dalla pagina ufficiale:

» Download: MySQL Workbench

Per Windows: Visual C++ è un pre-requisito. Scaricalo e installalo da qui prima di installare MySQL Workbench.

Per Linux: Il pacchetto mysql-workbench è disponibile nativamente anche nei repository di molte distribuzioni Linux, fra le quali Ubuntu. Ma si tratta spesso di una versione datata. Scaricando il file dal sito ufficiale, invece, si ottiene la versione più recente.

Una volta completata la semplice procedura di installazione, avviare il programma.

Primo avvio di MySQL Workbench

Al primo avvio, chiudi l'inutile schermata di benvenuto cliccando sulla X in alto a destra.

Una rapida nota circa il messaggio MySQL Workbench could not detect any MySQL server running mostrato in basso: è assolutamente normale, se hai installato MySQL su un server remoto e diverso dal sistema sul quale è in esecuzione MySQL Workbench.

Procedi dunque spedito cliccando sul + accanto a MySQL Connections per creare una nuova connessione.

Creare una connessione con MySQL Workbench

La prima cosa da fare è assegnare un nome alla nuova connessione. Io generalmente indico il nome del server, seguito dal suffisso dev, test, PROD a seconda che il database al quale mi sto per connettere sia un'istanza di sviluppo, di collaudo oppure di produzione

Ora: MySQL Workbench supporta molteplici modalità di connessione. Ma quella ideale per raggiungere un'istanza MySQL in esecuzione su un sistema remoto è tramite SSH. Questo significa che configureremo il programma per:

1. utilizzare la modalità di connessione SSH
2. autenticarsi sul server SSH
3. autenticarsi sull'istanza MySQL

Connessione via SSH con MySQL Workbench

Allo scopo, iniziamo aprendo il menu a tendina Connection method. Da qui, scegliere Standard TCP/IP over SSH.

Ora immaginiamo che la sezione sottostante sia divisa in due parti: una relativa alla connessione SSH, l'altra relativa a MySQL

Nella sezione dedicata a SSH, indichiamo i parametri relativi al collegamento SSH:

• SSH Hostname: è il dominio oppure l'indirizzo IP del sistema remoto al quale vogliamo connetterci in SSH
• SSH Username: è lo username del sistema Linux da utilizzare per il login via SSH
• SSH Password oppure SSH Key File: è la password oppure la "chiave SSH" (consigliato) da utilizzare per il login SSH

Per maggiori informazioni, fai riferimento a questa guida dedicata:

» Leggi: SSH con Windows, Linux, Mac: la Guida Definitiva

Ti ricordo che, se stai utilizzando una "chiave SSH" e non la password, devi indicare il file, generalmente chiamato id_rsa, che si trova nella sotto-cartella .ssh all'interno della "home" dell'utente corrente. Nello specifico, il percorso è:

• Windows: C:\Users\<nomeUtente>\.ssh\
• Linux: /home/<nomeUtente>/.ssh/
• Mac: /Users/<nomeUtente>/.ssh/

Attenzione! Il file da scegliere qui è quello "senza estensione", generalmente chiamato semplicemente id_rsa. NON DEVI invece scegliere quello con estensione .pub (generalmente: id_rsa.pub)

Connessione a MySQL con MySQL Workbench

Riprendiamo la grafica precedente e, nella "sezione" dedicata a MySQL, indichiamo i parametri relativi al collegamento al database:

• MySQL Hostname: se MySQL è in esecuzione sullo stesso sistema al quale ci siamo collegati in SSH (caso più comune), indicare 127.0.0.1​​​​​. Se invece ci siamo connessi in SSH ad un primo server solo per poi raggiungere il secondo sul quale c'è effettivamente MySQL, indicare qui l'indirizzo IP del secondo server
• MySQL Server Port: a meno di configurazioni inusuali, indicare la porta TCP utilizzata di default di MySQL: 3306
• Username: è lo username da usare per l'autenticazione su MySQL. Se siamo noi gli amministratori del database remoto, probabilmente useremo root
• Password: è la relativa password. Ancora una volta: attenzione! Questa è la password dell'account di MySQL, e non del sistema Linux!

Test e salvataggio della connessione

Dopo aver valorizzato opportunamente tutti i campi, clicca Test connection per tentare la connessione e assicurarti che sia tutto corretto.

In alcuni casi potresti visualizzare una schermata che ti avverte di una potenziale incompatibilità: questo si verifica, ad esempio, se stai utilizzando MySQL Workbench 8.0 (l'ultima versione disponibile al momento in cui scrivo) per collegarti a un database MySQL 8.4 (più nuovo) oppure MySQL 5.7 (più vecchio)

Ci potrebbero essere alcuni casi specifici e molto limitati in cui questa incompatibilità emerge sottoforma di errore mentre usi il programma, ma non te ne preoccupare ora: per la maggior parte delle operazioni quotidiane, tutto funziona correttamente. Clicca quindi Continue anyway per proseguire.

Se le informazioni di connessione sono corrette, il test dovrebbe avere esito positivo

Clicca dunque OK per chiudere la finestra e tornare alla schermata principale.

Connessione a MySQL

La schermata principale ti mostra ora un rettangolo grigio che identifica la connessione appena creata: ti basterà cliccarci sopra per connetterti

In caso volessi gestire molteplici connessioni, con parametri diversi, dovrai ripetere l'operazione appena indicata per creare nuovi "profili". Questo risulta molto utile anche se hai una sola istanza di MySQL in esecuzione su un singolo server: puoi infatti creare una prima connessione usando l'utente "root" di MySQL, che userai occasionalmente e solo per l'amministrazione del database, e una seconda, nella quale hai impostato un utente con privilegi ridotti, da usare per lavorare sui dati nel quotidiano.

Alla prima connessione, il programma potrebbe mostrare nuovamente la schermata di avviso già tratta in precedenza

Proseguendo, si arriva ad una schermata che offre numerosi pannelli. Segnalo in particolare che, cliccando sulla piccola chiave inglese in corrispondenza di Instance (sulla sinistra) è possibile accedere ad una finestra nella quale ri-immettere i dati relativi alla connessione SSH: questo permette di poi di gestire il servizio remoto (avvio, arresto), visualizzare i log e operare sul file di configurazione direttamente da interfaccia grafica. Io non lo uso, ma può tornare comodo.

Per accedere alla sezione più interessante del programma è necessario cliccare sulla tab Schemas

Qui vengono elencati tutti i database configurati sul sistema remoto.

Creare un nuovo database

Se l'istanza MySQL remota è stata appena attivata, probabilmente non troveremo nemmeno un database (eccezion fatta per quello denominato sys, che contiene alcune informazioni di sistema). Per crearne uno subito:

1. fai click con il pulsante destro del mouse in un punto vuoto della sezione Schemas
2. scegli Create schema

Ora puoi assegnare un nome a piacere al tuo database.

Subito sotto, devi scegliere il set di caratteri più indicato. Il tema è molto articolato ma, per semplificare al massimo e selezionare una soluzione che possa andar bene nella maggior parte di casi, compreso il salvataggio delle emoji e di molti caratteri speciali internazionali, raccomando:

• Charset: scegliere utf8mb4
• Collation: scegliere utf8mb4_0900_ai_ci (nota: il vecchio MySQL 5.7 non supporta questa scelta: in tal caso, ripiegare su utf8mb4_general_ci)

Cliccare infine Apply per confermare. Appare così una nuova finestra nella quale ci viene mostrata in anteprima la query SQL che verrà eseguita per creare il nostro nuovo database. Questo ci rivela un dettaglio molto interessante: MySQL Workbench altro non fa se non esporre un'interfaccia grafica che consente di costruire automaticamente le stesse query che potremmo scrivere in autonomina e impartire da linea di comando.

Confermare nuovamente con Apply per proseguire

Nascondere i pannelli

A questo punto è altamente consigliabile nascondere il pannello di destra e quello inferiore del programma, per recuperare area di lavoro e svolgere le prossime operazioni con più comodità. Allo scopo, cliccare sulle icone in alto a destra.

Vogliamo mantenere il pannello di sinistra e nascondere gli altri. In seguito, comunque, potremo facilmente riattivarli cliccando sui medesimi comandi

Creare una tabella

Ora che abbiamo creato un nuovo database, esso è visibile nella sezione di sinistra. Procedi così:

1. espandi l'alberatura del tuo nuovo database, di modo visualizzare gli elementi che contiene (Tables, Views, ...)
2. fai click con il pulsante destro del mouse su Tables
3. dal menu contestuale, clicca Create table

Ora assegna un nome alla tabella e quindi definisci le colonne che andranno a comporla.

La struttura esatta della tua tabella dipende dai dati che devi salvare, ma ti raccomando di iniziare sempre con una colonna di nome id, di tipo INT, che sarà la chiave primaria, ovvero il riferimento univoco di ogni riga. Per quanto riguarda questa riga, spunta le seguenti caselle: PK, NN, UN, AI. Questo farà sì che l'ID di ogni riga sia un numero intero positivo valorizzato automaticamente.

Procedi poi ad aggiungere tutte le altre colonne, secondo le tue necessità. I Datatype più comuni sono int (numeri interi) varchar (testo) decimal (numeri con decimali) o datetime (date e orari) ma questo dipende solamente dallo specifico tipo di dato che devi salvare.

Le caselle adiacenti hanno invece nomi abbreviati e poco "parlanti": per visualizzare il significato passaci sopra con il cursore del mouse. Te li esplicito comunque di seguito con una breve spiegazione:

• PK (Primary Key): indica che la colonna sarà la chiave primaria della relazione. Se hai definito la tabella come raccomandato poco fa, solo la colonna id avrà questo attributo
• NN (Not Null): indica che la colonna non potrà contenere il valore "nullo" (null). Nota che potrà comunque contenere il valore "stringa vuota" (ovvero '')
• UQ (Unique): se spuntata, ogni riga dovrà contenere un valore univoco in questo campo, che non potrà essere ripetuto da nessuna altra riga
• B (Binary): il dato viene salvato come valore binario. La conseguenza più evidente è la distinzione fra maiuscole minuscole nell'ordinamento delle stringhe, ma, francamente, non l'ho mai usata
• UN (Unsigned): indica che i valori numerici devono essere memorizzati senza il segno. Questo raddoppia il numero massimo di valori che è possibile archiviare, e dovrebbe essere sempre usato quando possibile
• ZF (Zero Fill): se si tratta di un campo numerico e non viene inserito esplicitamente un valore, il database riempirà il campo di zeri
• AI (Auto Increment): no, non c'entra nulla con l'Intelligenza Artificiale. Piuttosto, significa che il valore del campo è gestito dal database, e deve essere incrementato di uno per ogni riga. La prima riga avrà quindi valore "1", la seconda "2" eccetera. Solo una colonna può essere AI per ogni tabella, e dovrebbe essere quella denominata id (consigliata in precedenza)
• G (Generated): significa che la colonna è generata automaticamente a partire dai dati presenti in altre colonne. Un'ipotetica colonna nome_completo potrebbe essere definita come l'unione delle colonne adiacenti nome e cognome

Concludi cliccando Apply e, ancora una volta, ti verrà mostrata in anteprima la query SQL generata automaticamente. Clicca di nuovo Apply per procedere

Lavorare con i dati

Ora che la tua tabella è pronta, aprila cliccando sulla piccola icona con il fulmine visualizzata quando passi sopra di essa con il cursore del mouse

Qui puoi creare nuove righe, valorizzare le celle, e, in seguito, modificare o eliminare i dati salvati. Ricorda che tutte le operazioni vengono semplicemente "accodate": la scrittura vera e proprio avviene solo quando clicchi Apply

Conclusioni e alternative

In questa guida abbiamo visto come lavorare su un database MySQL remoto via SSH, con MySQL Workbench. Il programma espone ancora molte altre funzionalità interessanti, fra le quali cito in particolare quella per importare ed esportare i dati.

In caso MySQL Workbench non ti piacesse, ti segnalo queste alternative:

• DBeaver Community: alternativa gratuita e multi-piattaforma compatibile con MySQL, SQL Server, PostgreSQL e altri
• phpMyAdmin: software gratuito che consente di interagire con i database MySQL direttamente dal browser web
• DataGrip: un ambiente di sviluppo integrato (IDE) per SQL e NoSQL
• SQLyog Community Edition: una buona soluzione gratuita per Windows
• Sequel Pro (gratuito) e Navicat (a pagamento): fra i migliori per Mac

Altre guide a MySQL

Per la lista completa di guide a MySQL, vedi tutti gli articoli con tag "mysql".