Backup e ripristino di MySQL o MariaDB

In questo sintetico tutorial, cercheremo di affrontare i comandi necessari per eseguire il Backup (salvataggio dei dati) ed il Restore (ripristino dei dati) di un generico database schema di MySQL o MariaDB. Sono stati presi come riferimento per questo tutorial MySQL e MariaDB in quanto, come ormai ben sappiamo, essi sono più o meno equivalenti.

Mi auguro che sia ormai ben chiaro a tutti quanto sia cruciale eseguire dei backup affidabili di database MySQL o MariaDB per poter ripristinare i dati e le complete funzionalità di un servizio. Così come pure sono sicuro che la maggior parte dei sysadmin sanno perfettamente che non ci si può mai fidare del tutto dell’hardware o del software. I disastri accadono (shit happens) dobbiamo solo fare in modo da farci trovare preparati.

Nota su questo tutorial.

Il presente tutorial è stato realizzato utilizzando il seguente ambiente operativo

• Sistema operativo: Debian GNU/Linux 12 (bookworm)
• Motore Database: Ver 15.1 Distrib 10.11.6-MariaDB (Client & Server)
• Utente Database: momo
• Database: mio_db

Concetti sul backup e restore di MySQL o MariaDB

Prima di addentrarci nell’utilizzo degli specifici comandi per eseguire il backup ed il restore dei database MySql o MariaDB, può tornare utile fissare alcuni concetti basilari.

Cominciamo subito col dire che per eseguire il backup dei database MySQL o MariaDB è stata creato il programma di utilità specifico mysqldump. Mentre per eseguire il ripristino non c’è uno strumento dedicato; vediamo subito il perché di questa bizzarra situazione.

Se per eseguire il backup c’era bisogno di uno strumento che estraesse dati e struttura del database e li organizzasse in modo appropriato da poter successivamente ripristinare dati e struttura. Per il ripristino i progettisti hanno invece pensato di sfruttare la potenza del linguaggio SQL. Infatti, il comando mysqldump è in grado di crea re un file che ha esattamente il formato dei comandi SQL necessari per ricreare la struttura del database e per reinserire i dati all’interno della struttura appena ricreata.

La sicurezza dei file di backup

Esaminando il file prodotto da mysqldump ti sarà facile individuare tutti i comandi SQL necessari per creare il database, creare tabelle, views, trigger e quant’altro. Non ti sarà inoltre difficile individuare i comandi necessari ad inserire i dati nelle tabelle.

In altre parole, un file prodotto da mysqldump contiene tutto il necessario per creare da zero un database ed inserirci tutti i dati. Proprio come faresti se dovessi farlo a mano.

Può sembrare superfluo, ma vale qui la pena di ricordare che il backup di un database MySQL o MariaDB contiene in chiaro in un file tutti i contenuti del database. Proprio tutti i dati; compresi i dati sensibili, riservati o che dovrebbero rimanere segreti.

Va da se che il file prodotto con mysqldump deve essere sempre mantenuto inaccessibile ai non addetti ai lavori. Ancora meglio se i file di backup

La sintassi del comando per il backup

Prima d tuffarci nei dettagli di come utilizzare al meglio i comandi per eseguire il backup di MySql o MariaDB, iniziamo col la sintassi del comando mysqldump.

Questa è la forma generale del comando

$ mysqldump [opzioni] > file.sql

Dove:

• mysqldump – Il nome dell’utility (il comando) per eseguire il backup
• opzioni – Opzioni che possono essere passate al comando
• file.sql – Il dump file (backup) creato dalla utility

Inutile dire che il servizio MySql p MariaDB deve essere attivo ed accessibile dall’amministratore.

Ora che abbiamo la sintassi generale del comando, vediamo come utilizzare al meglio mysqldump nelle varie circostanze.

Backup di un singolo database schema

Il caso che capita più frequentemente consiste nell’eseguire il backup di un singolo database.

Ad esempio, per creare un backup del database mio_db utilizzando l’account amministratore di MySQL o MariaDB e salvare il contenuto del bump (backup) nel file mio_db.sql, si può utilizzare il seguente comando:

$ mysqldump -u root -p mio_db > file_mio_db.sql

Notare che nel comando qui sopra abbiamo accuratamente evitato di specificare la password per accedere come utente root a MySQL o MariaDB, per evitare di mostrare in chiaro una password così critica. In virtù di questo, il sistema ci chiederà la password dello user root per eseguire il backup.

Backup di database schema multipli

L’utility mysqldump può eseguire il dump (backup) anche di database multipli in cui risultato sia salvato in un unico file. Tale funzionalità può essere utile nel caso di database che sono in qualche modo collegati tra loro e che quindi possono essere considerati logicamente come un’unico insieme di dati.

Per eseguire il backup di database multipli possiamo sfruttare l’opzione --database seguita dalla lista di database di cui fare il backup in un unico file di dump. In questo caso il nome di ciascun database dovrà essere separato da spazio (carattere ‘blank‘).

Ecco un esempio:

$ sudo mysqldump -u root -p --databases mio_db altro_db > db_mio_e_altro.sql

Il comando qui sopra crea il file dump di db_mio_e_altro.sql contenente il backup dei database mio_db e altro_db.

Backup di tutti i database schema

L’utility mysqldump è in grado di eseguire il backup di tutti i database presenti nel sistema di database (database cluster).

Il backup di tutti i database presenti nel sistema di database è utile in tutti quei casi inn cui si desidera eseguire il backup anche degli oggetti interni del database come ad esempio information_schema, performance_schema ed altre strutture interne.

Per eseguire il backup di tutti i database su utilizza l’opzione `–all-databases`, come mostrato qui di seguito:

$ sudo mysqldump -u root -p --all-databases > all_databases.sql

Notare che con uesta opzione non è possibile salvare ciascun database in un file differente.

Per tale scopo può essere utile lo script bash riportato qui di seguito:

for DB in $(sudo mysql -u root -p -e 'show databases' -s --skip-column-names); 
do
   mysqldump $DB > "$DB.sql";
done

Il comando qui sopra creerà file dump separati per ciascun database schema presente nel sistema di database. A ciascun file sarà assegnato il nome del relativo nome di database schema, seguito dall’estensione “.sql“.

Backup di un database in un file compresso

Quando si esegue il backup di uno o più database molto grandi, anche il file di dump sarà ovviamente molto grande; e si sa che i file molto grandi sono anche difficile da manipolare.

Un valido aiuto ci viene offerto da gzip (l’utility di compressione di Linux). Con l’aiuto di gzip è possibile comprimente «al volo» il file di dump proprio mentre questo viene generato ed immediatamente prima che i dati vengano effettivamente scritti nel file di destinazione.

Di seguito un esemppio di backup di un database il cui file di dump viene compresso run-rime:

$ mysqldump mio_db | gzip > mio_db.sql.gz

Utilizzare la data e l’ora per il file di dump

In tutte le varianti di mysqldump discusse più sopra il nome del file di dump che viene sempre assegnato in modo statico (Es. file_mio_db.sql, oppure db_mio_e_altro.sql, ecc.).

Sfruttando alcune delle enormi potenzialità della bash di Linux, possiamo fare in modo che il nome file possa tenere conto anche della data e dell’ora in cui il backup viene effettuato. Ciò può tornare utile nel caso in cui è necessario creare molti backup nel tempo ad esempio un backup ogni ora, oppure ogni giorno, o altro.

Per eseguire il backup del database mio_db in un file che tenga conto della data e dell’ora di esecuzione, il comando che segue può essere un valido esempio:

$ mysqldump mio_db > mio_db_del-$(date +%Y%m%d).sql

La sintassi del comando per il ripristino

Così come abbiamo fatto più sopra per la funzione di backup, fissiamo qui la sintassi generale del comando bash per il ripristino (restore) dei dati di MySQL o MariaDB.

La sintassi generale è la seguente:

$ mysql database_name < file.sql

Laddove:

• mysql – Il nome dell’utility (il comando) per eseguire il ripristino
• opzioni – Opzioni che possono essere passate al comando
• file.sql – Il dump file (backup) di input generato con mysqlbump.

Inutile anche qui sottolineare che il servizio MySQL p MariaDB deve essere attivo ed accessibile all’amministratore.

Ora che abbiamo la sintassi generale del comando, vediamo come utilizzare al meglio il ripristino dei database, con qualche esempio.

Ripristino di un database da file dump

Con riferimento alla sintassi generale del comando da utilizzare per il ripristino dei dati di un database MySql o MariaDB, è possibile ripristinare il nostro ormai famoso database `mio_db` dal file di dump `file_mio_db.sql`, con il seguente comando bash:

$ mysql mio_db < file_mio_db.sql

Molto probabilmente prima di eseguire il comando riportato qui sopra, sarà necessario in precedenza creare fisicamente il database, se all’interno del file di dump non è presente tale comando. Va ricordato che il file di dump generato con mysqldump può contenere alcuni comandi aggiuntivi, come appunto la creazione del database prima dell’inserimento dei dati veri e propri,

A tale proposito può essere utile verificare le opzioni dei comandi di backup e ripristino.

Con questi assunti, nel caso sia effettivamente necessario creare il database prima dell’inserimento dei dati, si può utilizzare, la sequenza di comandi riportati qui sotto in luogo del singolo comando precedente

$ sudo mysql -u root -p -e "CREATE DATABASE mio_db";
$ mysql mio_db < file_mio_db.sql

Verificare che non siano riportati errori nell’esecuzione del comando.

Ripristino da un dump multiplo

Supponiamo che hai a disposizione il file di dump db_mio_e_altro.sql realizzato con mysqldump, contenente più dump di database, poiché per il backup hai utilizzato l’opzione --all-databases (vedi l’esempio più sopra) e desideri ripristinare soltanto il database schema mio_db.

In questo caso, dovrai utilizzare l’opzione --one-database nel comando di ripristino.

Nell’esempio riportato qui di seguito, il comando per ripristinare soltanto il database schema mio_db:

$ mysql --one-database mio_db < db_mio_e_altro.sql

Verificare sempre che non siano riportati errori nell’esecuzione del comando.

Backup e Ripristino con un unico comando

In alcune specifiche circostanze potrebbe non essere necessario creare un file di dump per poi ripristinarlo in un altro sistema di database MySQL o MariaDB. Ad esempio, nei casi in cui è richiesto che uno o più database siano «spostati» (migrati) su un altro host, oppure su una diversa istanza di MySQL o Maria DB dello stesso host.

Per «spostare» (migrare) un database schema da un host ad diverso host, si può utilizzare il seguente comando bash:

$ sudo mysqldump -u root -p mio_db | mysql -h host_remoto -u root -p mio_db_2

Se invece si vuole «spostare» (migrare) un database schema da una istanza MySQL o MariaDB ad una diversa istanza dello stesso host, si può usare il comando bash riportato qui di seguito:

$ sudo mysqldump -h localhost -u root -p mio_db | mysql -h localhost -P 3307 -u root -p mio_db_2

Notare che nel comando qui sopra è stato specificato il nome dell’host di origine e di destinazione (“localhost”) ed è stato inoltre necessario specificare il numero di porta TCP dell’istanza del database system di destinazione. A significare che i dati vengono prelevati dal database di origine mio_db dalla porta standard (TCP/3306) di localhost ed inseriti del database mio_db_2 tramite la porta TCP/3307 di localhost.

Conclusioni

Questo sintetico tutorial mira a descrivere, in modo generico e sintetico, come eseguire Backup e Ripristino di dei dati di database schema di MySQL o MariaDB.

La descrizione di tutte le possibili opzioni di Backup e Ripristino è al di fuori dello scopo di questo articolo. Per approfondimenti sulle opzioni e sui concetti più squisitamente sistemistici, si rimanda alla documentazione ufficiale di MySQL e MariaDB.

In questo sito abbiamo già avuto modo di affrontare alcune tematiche relative a MySQL e/o MariaDB: da come installare MySQL in Windows ad una utile Cheatsheet che sintetizza i comandi di uso comune.

Questo articolo mira pertanto ad aggiungere ulteriori informazioni tecniche per rendere più agevole l’implementazione e l’utilizzo di questo prezioso strumento.

Condividi