Database MySQL backup: esempi e guida rapida completa a mysqldump su Windows o Linux Ubuntu/CentOS

Per generare il backup di un database MySQL non basta copiare i file presenti su disco fisso: dobbiamo invece impiegare un'utility dedicata. Vediamo allora come usare mysqldump per creare il backup di un database MySQL, comprimere il file e scaricarlo dal server remoto per poi ripristinare il database MySQL dal backup

Per chi ha fretta: i comandi da ricordare

Backup:

mysqldump -u "NomeUtenteMySQL" -p"MiaPasswordMySQL" --opt --add-drop-database --lock-all-tables --databases "NomeMioDatabase" > "mio_dump.sql"

Ripristino:

mysql -u "NomeUtenteMySQL" -p"MiaPasswordMySQL" < "mio_dump.sql"

Backup di MySQL con mysqldump

mysqldump è l'utility da linea di comando tramite la quale realizzare backup dei database MySQL. Per prima cosa, quindi, è necessario aprire una shell di comando testuale sul server o PC che ospita l'istanza di MySQL da backuppare:

• Per Windows: Windows 10, Prompt dei comandi: accesso negato - come aprire (sempre) il "Prompt dei comandi" di Amministratore
• Per Ubuntu: Come aprire il terminale di Ubuntu

In ambito Linux, mysqldump fa parte del pacchetto mysql-client. Se già non è installato, otteniamolo:

• Ubuntu: sudo apt install mysql-client -y
• CentOS: sudo yum install mysql-community-client -y

Sotto Windows, l'eseguibile mysqldump.exe si trova invece nella sottocartella bin della directory nella quale abbiamo installato MySQL. Per prima cosa, impartiamo dunque qualcosa di simile a cd "%ProgramFiles%\MySQL\MySQL Server 8.0\bin" per entrare nella cartella appropriata

Indipendentemente dalla piattaforma, lanciamo semplicemente mysqldump --version per accertarci che il programma sia disponibile: ci verrà così mostrato il numero di versione

Generare il backup di un database MySQL con mysqldump

Per creare il backup di un database MySQL (chiamato anche dump) lanceremo un comando simile a questo:

mysqldump -u "NomeUtenteMySQL" -p"MiaPasswordMySQL" --opt --add-drop-database --lock-all-tables --databases "NomeMioDatabase" > "mio_dump.sql"

Dove:

• -u "NomeUtenteMySQL": indica il nome utente per l'accesso a MySQL. Probabilmente, useremo spesso root
• -p"MiaPasswordMySQL": password associata al nome utente specificato per l'accesso a MySQL. Se indichiamo solo -p, senza la password a seguire, ci verrà chiesta in maniera interattiva. Altrimenti, possiamo scriverla subito, senza inserire lo spazio dopo -p
• --opt: attiva alcune opzioni che velocizzano notevolmente le operazioni. È abilitato di default, ma è indubbiamente meglio esplicitarlo
• --add-drop-database: fa sì che, al momento del ripristino, il database preesistente venga automaticamente eliminato prima di ricaricare quello presente nel backup
• --lock-all-tables: garantisce la massima consistenza del backup, posticipando le richieste di scrittura originatesi dagli altri client fino alla fine dell'operazione di backup. Nota: può generare un disservizio temporaneo se usato su un sistema di produzione, poiché i client rimangono "in attesa"
• --databases "NomeMioDatabase": il nome del database di cui creare il backup. Sostituendo questo parametro con --all-databases vengono esportati in un unico file tutti i database disponibili. È comunque altamente consigliabile creare il backup di un singolo database per volta (v. seguito)
• > "mio_dump.sql": il nome del file nel quale scrivere il backup. Possiamo anche fornire un percorso completo, come > "C:\mio_dump.sql" (Windows) oppure > "/home/zane/mio_dump.sql" (Linux)

Il file di backup generato è un lungo elenco di comandi SQL (creazione delle tabelle, inserimento dei dati veri e propri ecc.) che saranno eseguiti in maniera sequenziale al momento del ripristino. Tramite un editor di testi possiamo ispezionarne il contenuto:

• Per Linux: Guida: modificare i file di configurazione di Linux Ubuntu/CentOS da terminale è facile con "nano" (alternativa a "vi")
• Per Windows: Programma/editor free per aprire/modificare log, dump, SQL, XML grandi/enormi (100/250/500 MB, 1 GB) su Windows?

Per semplificare e automatizzare enormemente questa procedura, raccomandiamo di valutare il seguente script:

» Leggi: MySQL/mysqldump: creare un file distinto/singolo per ogni database con zzmysqldump (script)

Comprimere il file generato da mysqldump

Se state realizzando il backup di un database MySQL di "produzione", siate pronti all'eventualità che mysqldump generi un file da parecchie centinaia di megabyte o, addirittura, decine di gigabyte. Fortunatamente, trattandosi di file testuali, la comprimibilità è elevatissima: possiamo dunque abbattere le dimensioni comprimendo in formato 7z.

In ambiente Windows, usiamo 7-Zip oppure uno degli altri gestori di archivi compressi:

» Leggi: I programmi gratuiti per la compressione dei dati

In ambiente Linux, installiamo 7z e procediamo alla compressione con un comando simile a questo:

7z a -t7z -mx=9 -mfb=256 -md=256m -ms=on "/home/zane/mio_dump.7z" "/home/zane/mio_dump.sql"

Ovviamente dovete sostituire:

• "/home/zane/mio_dump.7z": il percorso del file compresso da creare
• "/home/zane/mio_dump.sql": il percorso completo del vostro file originale (quello generato da mysqldump)

In entrami i casi, prestate attenzione a conservare le virgolette.

Per la guida completa alla compressione con 7z:

» Leggi: File .7z, Linux e linea di comando: come creare/comprimere ed estrarre archivi 7z con 7-Zip per Ubuntu e CentOS

Copiare i file fra server remoto e PC locale

Se stiamo lavorando su di un database server remoto, probabilmente vorremo copiare in locale il backup appena creato.

Se il PC locale impiega Windows, dobbiamo installare PSCP e poi impartire un comando simile a questo:

"%ProgramFiles(x86)%\PuTTY\pscp.exe" -l "root" -pw "miapasswordssh" miodominio.com:"/home/zane/mio_dump.7z" "%USERPROFILE%\Desktop"

Adattando opportunamente i percorsi, il file verrà scaricato in locale (sul desktop dell'utente corrente), dove potremo aprirlo con il consueto gestore d'archivi. Per la guida completa ai trasferimenti via SCP in ambiente Windows, vedi:

» Leggi: Guida: download e upload con SCP fra PC Windows e server Linux

Se invece anche il PC locale monta Linux, scarichiamo con un comando simile al seguente:

scp root@miodominio.com:"/home/zane/mio_dump.7z" "$HOME"

Anche in questo caso dobbiamo adattare i percorsi, quindi fornire la password. Al termine del trasferimento, troveremo il file scaricato nella cartella "Home" locale. Per la guida all'uso del comando SCP in ambito Linux:

» Leggi: Guida rapida a SCP: come caricare e scaricare file da un server Linux via SSH

Per chi preferisse uno strumento ad interfaccia grafica, ricordiamo che un client come FileZilla supporta in modo "trasparente" lo scambio file anche tramite protocollo sicuro SCP ed è disponibile sia per Windows, sia per Linux:

» Leggi: Client FTP (FileZilla), guida rapida - caricare e scaricare file da un server FTP remoto

Decomprimere il dump del database

Prima di poter ripristinare il backup del database creato da mysqldump è necessario "scompattare" il file .7z che avevamo compresso ed ottenere così il .sql originale.

In ambito Windows possiamo usare un gestore d'archivi compressi, mentre su Linux impieghiamo il seguente comando:

7z x "$HOME/mio_dump.7z" -y

Indicando, ovviamente, il percorso completo per raggiungere il file compresso. Per altre info:

» Leggi: File .7z, Linux e linea di comando: come creare/comprimere ed estrarre archivi 7z con 7-Zip per Ubuntu e CentOS

Ripristinare il backup generato da mysqldump

È certamente una buona idea ripristinare periodicamente in un ambiente di test i backup creati con mysqldump. Questo ci consente di verificare che tutto stia funzionando correttamente, oltre a mantenerci pronti a farlo "in produzione" in caso di catastrofi.

Per ricaricare il database da un file di mysqldump si usa il programma mysql, ovvero lo stesso client che consente di interagire con la controparte server.

In ambito Linux, il programma mysql fa parte del pacchetto mysql-client. Se già non è installato, otteniamolo:

• Ubuntu: sudo apt install mysql-client -y
• CentOS: sudo yum install mysql-community-client -y

Sotto Windows, mysql.exe si trova invece nella sottocartella bin della directory nella quale abbiamo installato MySQL. Impartiamo dunque qualcosa di simile a cd "%ProgramFiles%\MySQL\MySQL Server 8.0\bin" per entrare nella cartella appropriata

Naturalmente, il "motore" MySQL Server deve essere in esecuzione anche sul sistema nel quale vogliamo ripristinare i dati.

» Leggi anche: Installare e configurare un server web: La Grande Guida ad Apache, MySQL, PHP su Windows e Linux

A questo punto, possiamo ripristinare il backup di MySQL con questo comando:

mysql -u "NomeUtenteMySQL" -p"MiaPasswordMySQL" < "mio_dump.sql"

• -u "NomeUtenteMySQL": indica il nome utente per l'accesso a MySQL. Probabilmente, useremo spesso root
• -p"MiaPasswordMySQL": password associata al nome utente specificato per l'accesso a MySQL. Se indichiamo solo -p, senza la password a seguire, ci verrà chiesta in maniera interattiva. Altrimenti, possiamo scriverla subito, senza inserire lo spazio dopo -p
• < "mio_dump.sql": il nome del file che contiene il backup da ripristinare. Possiamo anche fornire un percorso completo, come < "%USERPROFILE%\Desktop\mio_dump.sql" (Windows) oppure < "$HOME/mio_dump.sql" (Linux)

L'operazione di ripristino è sempre abbastanza lenta, quindi preparatevi a pazientare un po'. I tempi dipendono dalla complessità del database e dall'hardware sul quale si ripristina, ma, spannometricamente, parliamo di 2 minuti ogni 100 megabyte di file .sql lavorando su un comune PC, meno della metà con un server.

Velocizzare il ripristino dal dump di MySQL

L'argomento --opt di mysqldump si occupa automaticamente di creare un backup (dump) del database MySQL altamente ottimizzato: il file .sql generato presenta dunque quasi tutte le accortezze necessarie per velocizzare il ripristino. Ma c'è ancora una modifica manuale che può fare la differenza e ridurre i tempi di ripristino da dump MySQL: autocommit=0. Vediamo come impostarla

» Leggi: Ripristino da backup MySQL/mysqldump: un semplice trucco per velocizzare al massimo le operazioni (autocommit=0)

Backup e ripristino di MySQL fra Versioni/sistemi operativi diversi

Non è necessario che il PC/server sul quale era stato generato il dump e quello sul quale si va a ripristinare montino il medesimo sistema operativo. È certamente meglio che la versione di MySQL sia la stessa (o, comunque, siano temporalmente vicine fra loro), ma poi possiamo tranquillamente ricaricare su Windows un backup di MySQL ottenuto su Linux, e viceversa.

Quanto "vicine" devono essere le versioni di MySQL per evitare problemi? Il primo numero (versione principale) deve corrispondere, e, preferibilmente, anche il secondo. Il terzo, invece, può variare. In passato, ad esempio, ricordo di aver avuto problemi a ripristinare backup generati da MySQL 5.5 sulla vecchia versione 5.1, ma ero riuscito nell'intento con un po' di "smanettamento". Fra la versione 5.7.16 e la 5.7.11, invece, non ho riscontrato problemi. In linea generale, i rischi di incompatibilità sono più alti quando si realizza il backup su una versione "nuova" e poi si tenta di ripristinarlo su una "vecchia".

Il file .sql di backup può essere utilizzato anche per ripristinare su di un motore di database diverso da MySQL (Oracle, Microsoft SQL Server, ecc.) ma si tratta di una procedura altamente sconsigliabile, poiché la compatibilità non è garantita e alcune informazioni (ad esempio: tipi di dato) possono andare perse. Se questa è la necessità, il comando mysqldump usato per generare il backup dovrebbe includere sempre un parametro come --compatible=oracle oppure --compatible=mssql o --compatible=postgresql.

Ripristini parziali da un dump MySQL?

Non è possibile "selezionare" cosa ricaricare dal backup (per ripristinare solo determinate tabelle e non altre, ad esempio). Per questo motivo, è sempre meglio usare mysqldump ripetutamente e creare il backup di un singolo database per file. Questo script automatizza l'operazione:

» Leggi: MySQL/mysqldump: creare un file distinto/singolo per ogni database con zzmysqldump (script)

Come eccezione a quanto detto: in verità si può ottenere un ripristino parziale aprendo il dump con un editor di testi ed eliminando le istruzioni SQL che desideriamo ignorare, ma è un lavoraccio:

• Per Linux: Guida: modificare i file di configurazione di Linux Ubuntu/CentOS da terminale è facile con "nano" (alternativa a "vi")
• Per Windows: Programma/editor free per aprire/modificare log, dump, SQL, XML grandi/enormi (100/250/500 MB, 1 GB) su Windows?

Molto più facile, in caso di criticità, è ricaricare l'intero backup su di un sistema temporaneo, quindi agire di nuovo con mysqldump per esportare solo le parti d'interesse.

mysqldump: generare il backup del database, ma senza dati

Una procedura che risulta spesso necessaria è la generazione di un dump con il database e la struttura delle tabelle (colonne, tipi di dato, ecc.), ma senza i dati.

Il risultato si può ottenere facilmente in questo modo:

mysqldump -u "NomeUtenteMySQL" -p"MiaPasswordMySQL" --no-data --opt --add-drop-database --lock-all-tables --databases "NomeMioDatabase" > "mio_dump.sql"

L'unica differenza rispetto a quanto visto in precedenza è costituita dall'argomento --no-data

In questa circostanza, le operazioni di backup e ripristino sono quasi istantanee. Inoltre, a meno di infrastrutture straordinariamente complesse, il file .sql generato è sufficientemente "piccolo" da risultare scaricabile anche senza compressione.

mysqldump: come creare un backup di alcune, specifiche tabelle

mysqldump permette anche di generare backup di specifiche tabelle, invece che dell'intero database. Questo è utile, come discusso sopra, per eseguire ripristini parziali. Il comando da usare allo scopo diviene:

mysqldump -u "NomeUtenteMySQL" -p"MiaPasswordMySQL" --opt --add-drop-table --lock-all-tables "NomeMioDatabase" "NomeTabella1" "NomeTabella2" > "mio_dump.sql"

Dove:

• --add-drop-table: elimina e ricrea solo le specifiche tabelle presenti nel dump
• "NomeMioDatabase": il nome del database che contiene le tabelle da backuppare. Si noti che è stata rimossa la stringa --databases.
• "NomeTabella1" "NomeTabella2": lista delle tabelle che devono essere incluse nel backup

Anche in questo caso, aggiungere il già trattato parametro --no-data consente di esportare solamente la struttura delle singole tabelle richieste, senza i rispettivi dati.

In fase di ripristino, notiamo un'importante differenza rispetto a prima: dobbiamo esplicitare il nome del database nel quale importare le tabelle presenti nel dump. Useremo quindi:

mysql -u "NomeUtenteMySQL" -p"MiaPasswordMySQL" "NomeMioDatabase" < "mio_dump.sql"

Si noti dunque l'argomento "NomeMioDatabase". Per il resto, è tutto uguale al solito.

mysqldump: escludere determinate tabelle dal backup

Se vogliamo creare il backup di un database MySQL escludendo alcune tabelle, indichiamo gli elementi da tralasciare con --ignore-table=:

mysqldump -u "NomeUtenteMySQL" -p"MiaPasswordMySQL" --opt --add-drop-database --lock-all-tables --ignore-table=NomeMioDatabase.TabellaIndesiderata1 --ignore-table=NomeMioDatabase.TabellaIndesiderata2 --databases "NomeMioDatabase" > "mio_dump.sql"

È obbligatorio qualificare la tabella esplicitando il nome del database che la contiene seguito da un punto (NomeMioDatabase.TabellaIndesiderata1).

Il parametro deve essere ripetuto per ogni tabella da ignorare.

Se il nome del database include un punto, uno spazio o un altro carattere riservato, dobbiamo usare \ come escape. Ad esempio: per escludere la tabella phpbb_users dal backup del database turbolab.it_forum, devo sempre scrivere --ignore-table=turbolab\.it_forum.phpbb_posts.

MySQL: backup degli account-utente

Tramite mysqldump possiamo anche realizzare il backup degli account-utente (e relative password) di accesso a MySQL. Allo scopo, dobbiamo usare l'account MySQL root, ma poi il resto del'operazione è analoga a quanto già visto: detti account sono infatti salvati all'interno del database chiamato mysql:

mysqldump -u root -p"MiaPasswordMySQL" --opt --add-drop-database --lock-all-tables --flush-privileges --databases mysql > "mysql_dump.sql"

Notate l'aggiunta dell'argomento --flush-privileges: è necessario per far indicare che, alla fine del ripristino da dump, deve essere ricaricata la tabella dei privilegi: un passo necessario per rendere effettivamente utilizzabili le credenziali appena caricate.

In fase di ripristino resta tutto invariato:

mysql -u root -p"MiaPasswordMySQL" < "mysql_dump.sql"

Conclusioni, riferimenti e alternative

In questa guida abbiamo visto come realizzare il backup di un database MySQL tramite mysqldump, per poi ripristinare lo stato del database MySQL dal dump, potenzialmente su di un server diverso.

La trattazione si è incentrata sulle sole opzioni più importanti, quelle da conoscere e usare nel quotidiano: chi però volesse approfondire, può consultare la documentazione ufficiale:

» Vedi: mysqldump - A Database Backup Program

Per semplificare enormemente le operazioni di dump, raccomandiamo vivamente di valutare anche il seguente script:

» Leggi: MySQL/mysqldump: creare un file distinto/singolo per ogni database con zzmysqldump (script)

Se usato correttamente, mysqldump è affidabile e garantisce risultati soddisfacenti anche in ambito aziendale. Chi però avesse a che fare con database molto grandi (superiori ai 100 GB di file .sql generato), potrebbe scontrarsi con tempi di ripristino inaccettabilmente lunghi. In tal caso, fra la miriade di prodotti presenti sul mercato, Percona XtraBackup (anch'esso gratuito, ma disponibile solo per Linux) è una delle alternative più raccomandate.