[risolto] MySQL 8 con Ubuntu: come spostare i file del database e salvare i dati in un altro percorso (disco di rete, SSD, NAS, USB)

Per impostazione predefinita, MySQL salva i file che compongono il database in un percorso predefinito, che si trova sullo stesso disco e partizione del sistema operativo. Per Ubuntu, questo path predefinito è /var/lib/mysql. Se però le dimensioni del database stanno crescendo rapidamente e lo spazio libero sta per esaurirsi, oppure osserviamo problemi di performance dovuti ad un collo di bottiglia sullo storage, è opportuno spostare i file di MySQL su un disco secondario: può trattarsi di un secondo SSD collegato al server, un percorso di rete esposto da un NAS o persino un disco su USB. Ebbene: MySQL offre un parametro dedicato allo scopo... che, però, da solo non è sufficiente a consentirci di raggiungere l'obbiettivo. Vediamo allora la procedura passo passo per spostare i dati di MySQL su un altro disco

nota: questa guida è incentrata sulla procedura da svolgere per spostare i file di MySQL in un altro percorso, su un altro disco. Per la guida all'installazione, vedi quest'altro approfondimento:

» Leggi: Guida: come installare MySQL per Linux (Ubuntu/CentOS)

Cambiare percorso dati MySQL, Passo 1: Aprire il terminale

Se stai lavorando via SSH su un server MySQL remoto oppure con un'edizione spiccatamente "server" (come Ubuntu Server), sei già davanti alla linea di comando, quindi procedi al Passo successivo.

Se invece hai installato MySQL sul tuo PC locale e stai usando una distribuzione "desktop" con interfaccia grafica, devi avviare il Terminale. Con Ubuntu, ad esempio, procedi così:

1. clicca sul pulsante con i 3x3 punti in basso a sinistra
2. cerca terminale per lanciare rapidamente l'omonima applicazione

» Leggi anche: Come aprire il terminale di Ubuntu

Cambiare percorso dati MySQL, Passo 2: Diventare root

Iniziamo ottenendo privilegi massimi, diventando root. Il comando è:

sudo -s

In caso ti fosse richiesta la password, digita quella dell'account corrente.

» Leggi anche: Linux (Ubuntu) e sudo: come disabilitare la richiesta di password quando si eseguono comandi come root

In alternativa, puoi utilizzare il tuo utente "normale" ed anteporre sudo ad ogni comando mostrato di seguito.... ma divenire root immediatamente è molto più comodo.

Cambiare percorso dati MySQL, Passo 3: Mount del disco secondario

Prima ancora di impostare il nuovo percorso per i dati di MySQL è necessario che tale percorso... sia disponibile. Questo significa che il disco sul quale vogliamo spostare i dati deve essere "montato". In pratica: devi modificare il file /etc/fstab per fare in modo che il disco o l'unità di rete sulla quale stiamo per spostare i dati venga "montata" automaticamente ad ogni avvio. Abbiamo visto la procedura passo per passo qui:

» Leggi: Guida Ubuntu: come montare automaticamente un disco USB, SSD esterno o chiavetta NTFS, ext4, exFAT ad ogni avvio del PC/server (linea di comando, Ubuntu Server, mount al boot)

Nel mio caso, il disco secondario nel quale volevo spostare i dati era identificato come UUID="8a...40", e ho scelto di montarlo in /media/sd. Ho quindi inserito questa riga in /etc/fstab:

UUID="8a...40" /media/sd ext4 rw,suid,nodev,noexec,auto,nouser,async 0 2

Di nuovo: fai riferimento alla guida dedicata al mount dei dischi per la spiegazione completa.

Cambiare percorso dati MySQL, Passo 4: Scoprire il percorso attuale

Come detto in apertura, il percorso predefinito in cui MySQL salva i file dovrebbe essere /var/lib/mysql. E' però importante verificarlo puntualmente prima di procedere. Allo scopo, dobbiamo eseguire una query SQL.

Impartiamo innanzitutto questo comando da terminale per eseguire login a MySQL con il client testuale:

mysql -uroot -p -h localhost

Quando richiesto, digitiamo la password dell'utente root di MySQL (attenzione a non confondere tale password con quella dell'account Linux. Potrebbero essere diverse!).

Non appena ci troviamo davanti al prompt mysql>, lanciamo questa query:

SELECT @@datadir;

In risposta, MySQL ci mostrerà il percorso nel quale sta attualmente leggendo e scrivendo i dati. Nell'immagine seguente si legge quello predefinito, cioè il già citato /var/lib/mysql

Prendiamo nota del percorso mostrato, poi lanciamo exit; per uscire.

Cambiare percorso dati MySQL, Passo 5: Arrestare il servizio

Dobbiamo ora arrestare il servizio di MySQL. Il comando è

service mysql stop

Anche se non riceviamo nessun errore, è di importanza critica essere sicuri al 100% che MySQL si sia arrestato correttamente. Impartiamo dunque

service mysql status

Verifichiamo che l'output riportati un'indicazione simile a Active: inactive (dead) e che l'ultima riga legga Stopped MySQL Community Server

Battiamo q sulla tastiera per uscire.

Raccomando ora di impartire mysql -uroot -p -h localhost, seguito dalla password, per tentare comunque di connettersi. Dobbiamo ricevere un errore simile a

ERROR 2002 (HY000): Can't connect to local MySQL server

E' la ri-conferma che il servizio non è in esecuzione.

Cambiare percorso dati MySQL, Passo 6: Copiare i file

Procediamo ora a copiare i file che contengono il database nel nuovo percorso. Il comando è:

cp -a /var/lib/mysql /media/sd/mysql-datadir

Dove:

• cp -a: è il comando di copia. Il parametro -a è critico per la corretta riuscita dell'operazione, poiché preserva i permessi di file e cartelle, il proprietario e tutto il resto
• /var/lib/mysql: è il percorso nel quale si trovano attualmente i file. E' quello che abbiamo letto poco fa tramite SELECT @@datadir;
• /media/sd/mysql-datadir: è il nuovo percorso nel quale vogliamo spostare i file. Da notare che stiamo usando il percorso nel quale abbiamo montato il disco in precedenza (/media/sd) seguito da un nome di cartella a piacere (io qui ho scelto mysql-datadir), che verrà creata dal comando di copia

L'operazione di copia potrebbe richiedere qualche minuto, in particolar modo se il database conserva un volume di dati segnificativo. Al termine, verifichiamo che i file siano stati effettivamente copiati impartendo

ls -la /media/sd/mysql-datadir

Da notare che, in risposta al comando, dobbiamo vedere immediatamente una lunga lista di file, quasi tutti di proprietà dell'utente mysql, fra i quali alcuni il cui nome inizia con binlog e altri ib_logfile. Se non è così e troviamo solo un'ulteriore sotto-cartella, hai sbagliato la copia: eliminala (rm -rf /media/sd/mysql-datadir) e svolgi nuovamente l'operazione.

Cambiare percorso dati MySQL, Passo 7: Rinominare la cartella originale

Vogliamo ora rinominare la cartella originale. Questo ha il duplice scopo di:

1. creare un backup dei dati attuali, pronto da ripristinare in caso qualcosa vada storto
2. evitare che il servizio continui a scrivere nel vecchio percorso a causa di un qualche errore di configurazione.

Impartiamo dunque:

mv /var/lib/mysql /var/lib/mysql_originale

Di nuovo: sostituiamo a /var/lib/mysql il percorso che abbiamo letto in precedenza tramite SELECT @@datadir;.

Verifichiamo per sicurezza la riuscita dell'operazione lanciando

ls -la /var/lib | grep mysql

Da qui, vogliamo assicurarci che:

1. NON esista alcuna cartella chiamata solamente mysql
2. esista la cartella mysql_originale

Come si vede nell'immagine, in /var/lib potrebbero rimanere altre cartelle di nome mysql-files e mysql-keyring. E' normale che sia così e devi lasciarle al loro posto (non conterranno nulla che riguardi i dati del database).

Cambiare percorso dati MySQL, Passo 8: Creare la cartella di default, vuota

Prima di andare oltre, dobbiamo assolutamente svolgere un altro passaggio: ricreare la cartella di default, lasciandola vuota. Allo scopo, impartiamo:

mkdir -p /var/lib/mysql/mysql && chmod u=rx,go= /var/lib/mysql -R

Presta attenzione:

1. devi sostituire /var/lib/mysql con il percorso di default, ovvero sempre quello che hai scoperto in precedenza tramite SELECT @@datadir;
2. il percorso della cartella da creare legge proprio mysql/mysql (due volte) mentre il cambio dei permessi ha un solo /mysql

Se non ti interessano i dettagli, puoi già saltare al Passo successivo.

Spiegazione estesa: il file che contiene la procedura di avvio di MySQL (si tratta di /usr/share/mysql/mysql-systemd-start oppure /usr/share/mysql-8.0/mysql-systemd-start, a seconda di come è stato installato il pacchetto) effettua una chiamata a funzione che verifica esplicitamente la presenza di una sotto-cartella specifica nel percorso di default. Se il percorso non esiste, la procedura di avvio si blocca e MySQL non si avvia. Con il comando precedente l'abbiamo creata, così il servizio verrà avviato correttamente.

Non è necessario che la cartella abbia permessi specifici. Anzi: abbiamo impostato esplicitamente un set di permessi super-ridotti per avere la tranquillità che il servizio di MySQL non riuscirebbe comunque a scriverci, nemmeno in caso sbagliassimo la configurazione.

Cambiare percorso dati MySQL, Passo 9: Configurare AppArmor

Un'altra noia alla quale dobbiamo far fronte è il software di sicurezza AppArmor. Va infatti esplicitamente configurato per consentire al processo di MySQL di utilizzare i dati dal nuovo percorso. In caso contrario, al primo tentativo di riavviare MySQL otterremo un errore simile al seguente:

Job for mysql.service failed because the control processexited with error code. See "systemctl status mysql.service"and "journalctl -xe" for details.

I log riporterebbero poi un inutile dicitura simile a mysql.service: Main process exited, code=exited, status=1/FAILURE.

Per evitare di perdere 3 ore e imprecare in varie lingue (come ho fatto io), modifichiamo immediatamente la configurazione di AppArmor impartendo:

nano /etc/apparmor.d/tunables/alias

» Leggi: Guida: modificare i file di configurazione di Linux Ubuntu/CentOS da terminale è facile con "nano" (alternativa a "vi")

Vogliamo ora accodare alla fine del file una riga simile a questa:

alias /var/lib/mysql/ -> /media/sd/mysql-datadir/,

Dove:

• alias: è una parola chiave della configurazione di AppArmor. Riportala proprio così come è scritta
• /var/lib/mysql: è il percorso di default, ovvero quello che hai letto in precedenza tramite SELECT @@datadir;
• ->: è un simbolo proprio della configurazione di AppArmor. E' davvero il "trattino" (-) seguito dal "maggiore" (>), nulla di strano
• /media/sd/mysql-datadir: è il nuovo percorso che hai scelto, quello nel quale MySQL deve leggere e scrivere i file
• entrambi i percorsi hanno la barra (/) alla fine
• ,: è una semplice "virgola". Devi aggiungerla obbligatoriamente alla fine della riga

Una volta modificato il file, premi Ctrl+O, poi Invio e Ctrl+X per salvare le modifiche e chiudere l'editor.

Riavvia il servizio per rendere effettiva la nuova configurazione:

service apparmor restart

Cambiare percorso dati MySQL, Passo 10: Modificare il file di configurazione di MySQL

Sembra incredibile, ma tutto quello che abbiamo fatto fino ad ora è stata solamente propedeutico a modificare una singola riga di configurazione in MySQL! Impartiamo dunque:

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

Da qui, scendiamo fino in fondo e individuiamo la riga che legge

datadir =

Modifichiamo il percorso in questione digitando quello relativo alla nostra nuova cartella. Nel mio caso, ho usato

datadir = /media/sd/mysql-datadir

Una volta modificato il file, premi Ctrl+O, poi Invio e Ctrl+X per salvare le modifiche e chiudere l'editor.

Cambiare percorso dati MySQL, Passo 11: Riavviare il servizio

Siamo quasi giunti a destinazione. Riavviamo il servizio di MySQL:

service mysql start

Se non ricevi alcun errore.... complimenti! MySQL ha iniziato a leggere e scrivere i file dal nuovo percorso.

In caso di problemi, ispeziona i log per capire cosa c'è che non va:

tail -n 100 /var/log/mysql/error.log

Verifica finale e pulizia

Da qui in poi possiamo ricominciare a lavorare come al solito.

La prima cosa da fare è impartire mysql -uroot -p -h localhost, seguito dalla password, per connettersi al database. Non appena ci troviamo davanti al prompt mysql>, lanciamo di nuovo la nostra query:

SELECT @@datadir;

Questa volta, la risposta mostra il nuovo percorso scelto:

Ancora: verifica che tutti i dati siano stati correttamente migrati:

SHOW DATABASES;

Selezionane uno con qualcosa di simile a

USE nomeMioDatabase;

Leggi i dati da una tabella con una semplice query come

SELECT id, title FROM article ORDER BY id DESC LIMIT 10;

Se visualizzi i dati che ti aspetti, è tutto in ordine. Esci dal client impartendo exit;.

Prima di concludere, valuta di fare pulizia eliminando la vecchia copia di backup, che potrebbe occupare inutilmente parecchi gigabyte sul disco primario (oltre a contenere dati sensibili che è meglio non lasciare in circolazione inutilmente):

rm -rf /var/lib/mysql_originale

Conclusioni

In questa guida abbiamo visto come spostare i dati di MySQL su un altro disco. Come evidente, la vera e propria configurazione di MySQL è semplicissima (basta modificare il percorso datadir). Ma, da sola, non è sufficiente: lo spostamento dei dati preesistenti richiede un minimo di attenzione per non ritrovarsi con permessi sballati, è importante ri-creare la cartella di default vuota e configurare opportunamente il maledetto AppArmor.

Una volta terminata la procedura, però, tutto funziona esattamente come al solito. Di più: se abbiamo spostato i dati su un SSD o altro disco veloce, abbiamo ottenuto anche un importante miglioramento prestazionale, poiché il disco primario è stato sgravato dal pesante carico del database.