back to top

Backup MySQL (e ripristino) con mysqldump

Garantire la sicurezza e l’integrità dei dati è una esigenza di primaria importanza, per questo motivo è molto importante effettuare backup periodici del proprio database MySQL.

Perché è importante effettuare il backup di MySQL

Il backup non è un’operazione facoltativa. Effettuare backup della propria base dati è una procedura obbligata per chiunque gestisca database.

Approntare strategie di disaster recovery è indispensabile per chiunque lavori con i dati e voglia prevenire il rischio che questi, a causa di un malfunzionamento o di un virus, vengano compromessi e persi per sempre.

la perdita o la manomissione di dati, infatti, potrebbe avere conseguenze disastrose sia in ambito privato che, a maggior ragione, in ambito aziendale (dove vi sono anche precise responsabilità qualora i dati trattati siano di terzi).

Il backup di MySQL consente, appunto, di garantire la disponibilità e l’integrità dei dati anche a fronte di malfunzionamenti hardware o software, causati da guasti dovuti al logorio delle apparecchiature, virus o manomissioni.

Un’altra esigenza nella quale il backup si rivela indispensabile è la migrazione dei dati, cioè il loro spostamento da un computer ad un altro. Ad esempio uno dei problemi classici quando si decide di spostare un sito web che si appoggia su un DB MySQL è quello di garantire non solo la corretta migrazione dei file ma anche dei dati attraverso, appunto, un’operazione di backup di MySQL e, successivamente, di ripristino all’interno del nuovo server.

In questa lezione della nostra guida a MySQL vedremo come effettuare un backup completo di tutti i database o di una singola base dati. Vedremo, inoltre, come effettuare il ripristino (o restore) dei dati.

Come effettuare il backup di MySQL

Esistono diversi modi per fare il backup di un database MySQL ma il più diffuso di questi è basato sul’utilizzo dell’utility mysqldump. Si tratta di un programma, nato dal lavoro dello sviluppatore russo Igor Romanenko, grazie al quale è possibile effettuare il dump di un database o di una collezione di database.

Con mysqldump, infatti, è possibile fare una esatta fotografia della struttura e del contenuto del nostro database che ci sarà molto utile in caso di crash, di perdite di dati o qualora vi sia la necessità di trasferire i nostri preziosi dati su un nuovo server. Il dump generato da mysqldump è un insieme di tutte le istruzioni SQL necessarie per ricreare le tabelle e/o popolarle di dati. E’ bene ricordare, tuttavia, che mysqldump può anche essere usato per esportare i dati di un database all’interno di file CSV o XML.

Backup di un database con mysqldump

Vediamo di seguito come utilizzare questo programma per effettuare una copia SQL del nostro DB. Per utilizzare mysqldump entriamo nella shell del nostro server e digitiamo:

mysqldump -u NOME_UTENTE -p NOME_DATABASE > backup.sql

Ovviamente dobbiamo inserire al posto di user il nostro utente di MySQL e al posto di NOME_DATABASE il nome esatto del database di cui vogliamo fare il backup.

Il database verrà copiato all’interno del file .sql che abbiamo specificato (nell’esempio backup.sql); questo file non è nulla di più di un comune file di testo al cui interno troveremo, come anticipato nell’introduzione di questo articolo, le istruzioni SQL che serviranno per ricostruire le tabelle (CREATE TABLE…) e popolarle con i vecchi dati (INSERT INTO…).

Copiare più database in un colpo solo

Se vogliamo copiare più di un database in un colpo solo possiamo utilizzare l’opzione –databases facendola seguire dal nome dei diversi DB che vogliamo copiare separati da uno spazio:

mysqldump -u NOME_UTENTE -p --databases NOME_DB1 NOME_DB2 NOME_DB3 > backup_di_tre_db.sql

Per copiare tutti i database presenti sul nostro server DB, invece, non sarà necessario elencarli ma sarà sufficiente utilizzare l’opzione –all-databases in questo modo:

mysqldump -u NOME_UTENTE -p --all-databases > backup_tutti_i_db.sql

Backup di una singola tabella del database

Negli esempi visti sopra abbiamo effettuato il backup di interi database, ma avremmo potuto limitarci ad una sola tabella; in questo caso la sintassi da usare sarebbe stata:

mysqldump -u NOME_UTENTE -p NOME_DATABASE NOME_TABELLA > backup.sql

Da segnalare inoltre che l’utility mysqldump per la copia di singole tabelle offre anche la possibilità di utilizzare la clausola WHERE, in questo modo:

mysqldump -u NOME_UTENTE -p --where="id > 10" NOME_DATABASE nome_tabella > backup.sql

Nell’esempio qui sopra, infatti, abbiamo effettuato il dump della sola tabella indicata e limitatamente ai record con ID maggiore di 10.

Se si desidera copiare più di una tabella è possibile specificarne un elenco avendo cura di separarle con uno spazio:

mysqldump -u NOME_UTENTE -p NOME_DATABASE NOME_TABELLA1 NOME_TABELLA2 NOME_TABELLA3 > backup.sql

Comprimere il backup

Se la base dati da backuppare è molto grande può essere utile eseguire la compressione dell’output prodotto da mysqldump. Per farlo possiamo reindirizzare il risultato prodotto dal programma ad un utility di compressione come GZIP. Ad esempio:

mysqldump -u NOME_UTENTE -p NOME_DATABASE | gzip -9 > backup.sql.gz

Altre opzioni interessanti di mysqldump

Oltre a quelle già viste, mysqldump supporta diverse altre opzioni interessanti tra cui:

  • –no-data – Effettua il backup solo della struttura (e non dei contenuti del DB);
  • –no-create-db – Elimina la query di creazione del nuovo database;
  • –add-drop-database – Aggiunge la sintassi DROP DATABASE prima della sintassi CREATE DATABASE;
  • –add-drop-table – Aggiunge la sintassi DROP TABLE prima della sintassi CREATE TABLE;
  • –insert-ignore – Utilizza la sintassi INSERT IGNORE al posto di INSERT;
  • –skip-comments – Non scrive commenti all’interno del dump.

Ad esempio, si supponga di voler effettuare il backup solo della struttura (e non anche dei dati). La query da utilizzare:

mysqldump --no-data -u NOME_UTENTE -p NOME_DATABASE

Ripristino di un backup

Una volta creata la copia di backup del nostro DB non ci resta che vedere come effettuare il ripristino (o restore). Se nel backup è presente la query per la ricreazione del database sarà sufficiente digitare:

mysql -u NOME_UTENTE -p < backup.sql

Con la medesima sintassi, ovviamente, è possibile ripristinare il backup di tutti i DB presenti sul server originario:

mysql -u NOME_UTENTE -p < backup_tutti_i_db.sql

Ovviamente se in fase di backup è stata utilizzata l’opzione –no-create-db bisognerà prima ricreare manualmente i database di destinazione.

Se da un backup contenente una pluralità di DB ne vogliamo ripristinare uno solo, possiamo farlo utilizzando l’opzione –one-database in questo modo:

mysql -u NOME_UTENTE -p --one-database NOME_DATABASE < backup_tutti_i_db.sql

Per ripristinare i dati di un backup compresso con GZIP, infine, utilizzeremo una sintassi del genere:

gunzip < backup.sql.gz | mysql -u NOME_UTENTE -p

Effettuare la migrazione dei dati senza mysqldump

Può capitare, in alcune limitate circostanze, di essere impossibilitati ad utilizzare mysqldump. In questi casi come è possibile effettuare il backup di MySQL?

In situazioni del genere è possibile provare ad effettuare una procedura non proprio ortodossa che, tuttavia, può portare al risultato voluto se eseguita correttamente.

La prima cosa da fare è stoppare MySQL sul sistema sorgente:

stop mysql

dopodiché effettuare la copia di tutti i dati presenti in /var/lib/mysql (questa è la posizione di default dei dati di MySQL, ma è bene verificare la configurazione in /etc/mysql/my.cnf). Così facendo si copieranno tutti i database ed utenti del sistema originario.

Una volta effettuata la copia questa dovrà essere riversata nella nuova destinazione. MySQL deve essere già installato nel nuovo sistema ed il servizio non deve essere in esecuzione.

Anche in questo caso è necessario controllare la configurazione di MySQL per accertarsi di quale sia la posizione corretta dei dati.

Una volta completata la procedura di riversamento dei dati sarà necessario riavviare MySQL e verificare che tutto sia ok.

start mysql

Affinchè tutto funzioni correttamente è necessario verificare che i permessi sui file siano corretti.

Come già precisato, la procedura appena descritta deve essere considerata l’ultima spiaggia, quando cioè tutte le altre strade non sono percorribili! Il modo corretto per migrare i dati è quello descritto all’inizio della lezione oppure utilizzando altre utility appositamente create per gestire la migrazione dei dati di MySQL.

Pubblicità
Massimiliano Bossi
Massimiliano Bossi
Stregato dalla rete sin dai tempi delle BBS e dei modem a 2.400 baud, ho avuto la fortuna di poter trasformare la mia passione in un lavoro (nonostante una Laurea in Giurisprudenza). Adoro scrivere codice e mi occupo quotidianamente di comunicazione, design e nuovi media digitali. Orgogliosamente "nerd" sono il fondatore di MRW.it (per il quale ho scritto centinaia di articoli) e di una nota Web-Agency (dove seguo in prima persona progetti digitali per numerosi clienti sia in Italia che all'estero).

In questa guida...