Gli strumenti di backup di MySQL

Non ricordo dove ho sentito questa frase, ma mi è sempre parsa molto azzeccata: "Il backup è quella cosa totalmente inutile finché non serve".

Una delle incombenze, ahimé, del database admin in caso di "disastro", è ripristinare la situazione precedente all'incidente... preferibilmente nel minor tempo possibile. Chiunque si sia trovato ad affrontare una situazione simile o causata da un problema hardware, o da errore umano, sa che la sensazione è alquanto spiacevole.

Adottiamo, quindi, alcune strategie che ci facciano dormire sonni più tranquilli. Ovviamente l'impegno sarà commisurato all'importanza dei dati di cui siamo responsabili, se si tratta del nostro sito personale al massimo potremo prendercela con noi stessi. Il discorso cambia se rischiamo il linciaggio da parte di una torma di clienti inferociti. Un piccolo consiglio: provate di tanto in tanto un restore dei dati prima di trovarvi di fronte all'emergenza. Sarebbe terribile scoprire solo in quel momento che qualcosa non ha funzionato!

MySQL mette a disposizione vari strumenti di backup e restore: starà a noi scegliere quale preferiamo o quale sia più adatto alla situazione contingente. Questo articolo vi fornirà una panoramica di tali strumenti con particolare riferimento alle tabelle di tipo MyISAM. Come al solito la documentazione ufficiale in proposito è molto esauriente, per consultarla potete partire dalla sezione Database Backups.

Le tabelle di MySQL

MySQL gestisce le tabelle come file: i dati sono memorizzati in un file del tipo nome_tabella.MYD, la struttura in nome_tabella.frm e gli indici in nome_tabella.MYI. Potete trovarli nella cosiddetta datadir, in Windows di default è la sottodirectory data della directory di installazione. Se usate Linux la directory è mysql, ad esempio il percorso su Fedora è /var/lib/mysql. La posizione può essere comunque configurata a piacere attraverso il file my.cnf o my.ini. A ciascun database corrisponde una directory con il medesimo nome, all'interno della quale troverete i file relativi alle singole tabelle.

Detto questo possiamo grossolanamente suddividere i sistemi di backup in due categorie:

• backup prevede la copia dei file;
• backup che prevede la copia dei dati.

Un primo metodo elementare

Il primo sistema di backup è, oserei dire, banale e consiste nel copiare manualmente i file corrispondenti alle tabelle o le intere directory corrispondenti ai database. Mi raccomando fermate il server prima di procedere e riavviatelo una volta terminata l'operazione. In realtà non sarebbe strettamente necessario: si potrebbe operare un LOCK e un FLUSH delle tabelle prima della copia e poi un UNLOCK una volta terminato. Vedremo più avanti che esiste un'apposita utility che svolge questo compito senza complicarci eccessivamente la vita.

I file così ottenuti possono essere in un secondo momento copiati nella directory destinata ai dati per effettuarne il restore. Personalmente non ho trovato nessun problema a trasferire dati anche tra linux e windows e neppure da una vecchia versione di MySQL ad una nuova.

Backup table e restore table

Questo metodo viene attualmente considerato "deprecato", in virtù di altri strumenti migliori, risulta utilizzabile a livello di comando SQL. Funziona solo con tabelle MyISAM ed effettua una copia dei file .frm e .MYD. Gli indici .MYI potranno essere ricostruiti a partire da questi ultimi. La sintassi è:

BACKUP TABLE nome_tabella TO '/directory/di/destinazione'

Vediamo subito un esempio, supponendo di avere una tabella di nome "utenti" all'interno del database "articolo":

mysql> USE articolo;
Database changed
mysql> BACKUP TABLE utenti TO '/tmp';
+-----------------+--------+----------+----------+
| Table           | Op     | Msg_type | Msg_text |
+-----------------+--------+----------+----------+
| articolo.utenti | backup | status   | OK       |
+-----------------+--------+----------+----------+
1 row in set (0,02 sec)

Nella directory tmp troveremo i file utenti.frm e utenti.MYD. Una piccola notazione per chi usa Windows, il percorso di destinazione va' scritto alla "unix", ovvero l'equivalente dell'esempio precedente sarà 'c:/tmp' altrimenti otterremo un errore del tipo: Failed copying .frm file.

Per ripristinare i file salvati possiamo usare il comando RESTORE TABLE che rappresenta il contraltare di BACKUP TABLE. Oltre ad creare una copia dei file .frm e .MYD ricostruirà gli indici. Badate che il comando non effettua sovrascritture quindi, se i file esistono già, riceverete un messaggio d'errore. Dovrete necessariamente eliminarli dalla directory di destinazione. Di seguito la sintassi:

RESTORE TABLE nome_tabella FROM '/directory/di/destinazione'

Cancelliamo la tabella prova dal nostro database articolo e proviamo il restore:

mysql> DROP TABLE utenti;
Query OK, 0 rows affected (0,00 sec)
mysql> RESTORE TABLE utenti FROM '/tmp';
+-----------------+---------+----------+----------+
| Table           | Op      | Msg_type | Msg_text |
+-----------------+---------+----------+----------+
| articolo.utenti | restore | status   | OK       |
+-----------------+---------+----------+----------+
1 row in set (0,01 sec)

La tabella risulta così ripristinata, controllate la datadir del vostro MySQL e all'interno della directory articolo compariranno i file utenti.frm, utenti.MYD e utenti.MYI.

SELECT INTO OUTFILE e LOAD DATA INFILE

Un altro metodo a livello di comando SQL è SELECT ....INTO OUTFILE 'nome_file'. Si noti che il file di output non deve esistere, può essere scritto solo sulla macchina locale e chi esegue il comando deve avere il privilegio FILE. Questa istruzione scriverà i record selezionati in un semplice file di testo.

mysql> SELECT * FROM utenti INTO OUTFILE '/tmp/utenti.dump';
Query OK, 3 rows affected (0,00 sec)

il risultato sarà un file di nome "utenti.dump" del tipo:

1       gino     rossi   grossi@prova.it
2       mauro    bianchi mauro@sito.com
3       giuseppe verdi   verdi@verdi.net

Una possibile variante, che non introduce caratteri di terminazione tra campi, ma genera un unica riga è SELECT ....INTO DUMPFILE 'nome_file'. Vi potrebbe risultare utile per copiare oggetti BLOB in un file.

L'istruzione LOAD DATA INFILE permette, al contrario, di leggere le righe di un file di testo ed inserirle in una tabella: possiamo utilizzarla come metodo di restore. La sintassi è:

LOAD DATA INFILE 'nome_file' INTO TABLE 'nome_tabella'

Vuotiamo la tabella utenti e procediamo così:

mysql> DELETE FROM utenti;
Query OK, 3 rows affected (0,00 sec)
mysql> LOAD DATA INFILE '/tmp/utenti.dump' INTO TABLE utenti;
Query OK, 3 rows affected (0,00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Se andiamo a ricontrollare la tabella utenti la ritroveremo com'era prima della cancellazione. Abbiamo anche la possibilità di utilizzare la keyword REPLACE. Così facendo le righe che hanno la medesima chiave primaria verranno aggiornate. Per fare una prova apriamo il file utenti.dump e modifichiamo la prima riga sostituendo "gino" con "sandro".

mysql> LOAD DATA INFILE '/tmp/utenti.dump' REPLACE INTO TABLE utenti;
Query OK, 6 rows affected (0,00 sec)
Records: 3  Deleted: 3  Skipped: 0  Warnings: 0
mysql> SELECT * FROM utenti;
+----+----------+---------+-----------------+
| id | nome     | cognome | email           |
+----+----------+---------+-----------------+
|  1 | sandro   | rossi   | grossi@prova.it |
|  2 | mauro    | bianchi | mauro@sito.com  |
|  3 | giuseppe | verdi   | verdi@verdi.net |
+----+----------+---------+-----------------+
3 rows in set (0,00 sec)

Per questioni di brevità vi segnalo solo alcune utili opzioni che permettono di specificare come sia strutturato il file di testo che vogliamo utilizzare e che potrebbe provenire da una qualsivoglia elaborazione o programma. Che carattere usiamo per la terminazione dei campi, FIELDS TERMINATED BY, quale per racchiudere i valori, ENCLOSED BY, il carattere di escape, ESCAPED BY. Come iniziamo e terminiamo le righe, LINES STARTING BY, TERMINATED BY.

Negli esempi non specificando nulla abbiamo implicitamente usato i valori di default: FIELDS TERMINATED BY 't' ENCLOSED BY '' ESCAPED BY '' LINES TERMINATED BY 'n' STARTING BY ''

Per chi fosse interessato a maggiori dettagli consiglio di consultare la documentazione ufficiale.

MySQL mette a disposizione l'utility da riga di comando mysqlimport che permette di inviare al server l'istruzione SQL, LOAD DATA INFILE. La maggior parte delle sue opzioni corrisponde esattamente a quelle di LOAD DATA INFILE, può risultare utile per automatizzare le operazioni tramite script.

MYSQLHOTCOPY

Si tratta di uno script in Perl, molto veloce, che permette di eseguire il backup di interi database o singole tabelle. Purtroppo risente di alcune limitazioni: funziona solo con tabelle MyISAM o ISAM, deve essere eseguito sulla stessa macchina su cui "gira" il database e, per quanto mi risulta, funziona solo su sistemi *nix (dalla versione 4 si è aggiunta NetWare) quindi su Windows scordatevi di usarlo.

Mysqlhotcopy esegue un LOCK TABLES, FLUSH TABLES, copia i file nella directory di destinazione e poi esegue un UNLOCK TABLES. Per questo nel descrivere il primo metodo di backup, la copia diretta, vi avevo anticipato l'esistenza di un comando che automatizzava egregiamente il processo. La sintassi basilare è:

mysqlhotcopy nome_database /directory/di/destinazione

Facciamo subito alcuni esempi:

# mysqlhotcopy articolo  /tmp -u root -p miapass
Locked 3 tables in 0 seconds.
Flushed tables (`articolo`.`prova`, `articolo`.`prova2`,
`articolo`.`utenti`) in 0 seconds.
Copying 10 files...
Copying indices for 0 files...
Unlocked tables.
mysqlhotcopy copied 3 tables (10 files) in 0 seconds (0 seconds overall).

Come si noterà sulla riga di comando ho specificato direttamente le credenziali di root. Al termine dell'esecuzione nella directory tmp troveremo una directory articolo contenete i file corrispondenti alle tre tabelle: prova, prova2, utenti. Se vogliamo effettuare il backup di più database contemporaneamente basterà specificarne i nomi in sequenza:

# mysqlhotcopy articolo agenda  /tmp -u root -p miapass

Possiamo limitarci solo ad una o alcune tabelle i cui nomi possono essere indicati mediante una espressione regolare:

# mysqlhotcopy articolo./utenti/  /tmp -u root -p miapass
# mysqlhotcopy articolo./prova./  /tmp -u root -p miapass

Nel primo caso copierà i file relativi alla tabella utenti, nel secondo quelli relativi alle tabelle prova e prova2. Anche in questo caso ci sono varie opzioni che si possono specificare nell'eseguire lo script, per un quadro completo digitate mysqlhotcopy --help o perldoc mysqlhotcopy.

MYSQLDUMP e MYSQL

Mysqldump è forse l'utility di backup da riga di comando più utilizzata. Genera un file con le istruzioni SQL necessarie a ricreare le tabelle ed a popolarle. In pratica all'interno del file troverete le istruzioni CREATE TABLE per la struttura e INSERT per ripristinare i dati. È sicuramente lo strumento ideale se volete trasportare dati su un altro motore di database, data la generalità dell'output prodotto. Se lavoriamo solo con MySQL, a mio parere, mysqlhotcopy risulta più pratico e veloce. Mysqldump può anche creare un file di soli dati. La sintassi base è la seguente:

mysqldump nome_database > /percorso/file_destinazione.dump

# mysqldump  -uroot -pmiapass articolo > /tmp/articolo.dump

Sulla riga di comando specifichiamo le credenziali, utente root - password miapass, e ridirigiamo l'output del comando sul file articolo.dump che verrà creato nella directory /tmp. Se non specifichiamo la password, una volta lanciato il comando, ci verrà richiesta. Se preferiamo inserirla subito non dobbiamo lasciare spazio tra -p e quest'ultima.

Vediamo ora come effettuare il backup di una singola tabella:

# mysqldump  -uroot -pmiapass articolo utenti > /tmp/articolo.dump

In questo caso ci siamo limitati alla tabella utenti del database articolo. Se vogliamo effettuare il backup di più database possiamo specificare l'opzione --databases ed elencare di seguito i nomi. Nel caso in cui volessimo un backup globale --all-databases:

# mysqldump  -uroot -pmiapass -databases articolo agenda  > /tmp/due_db.dump
# mysqldump  -uroot -pmiapass --all-databases > /tmp/tutti_db.dump

Per effettuare il ripristino possiamo ricorrere all'utility mysql, ad esempio per il nostro database articolo precedentemente salvato nel file articolo.dump, possiamo procedere così:

# mysql -uroot -pmiapass articolo < /tmp/articolo.dump

Se apriamo il file creato con mysqldump, oltre ad una serie di commenti, troveremo istruzioni SQL del tipo:

...
DROP TABLE IF EXISTS `utenti`;
CREATE TABLE `utenti` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `nome` varchar(30) default NULL,
  `cognome` varchar(30) default NULL,
  `email` varchar(30) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
...
LOCK TABLES `utenti` WRITE;
INSERT INTO `utenti` VALUES (1,'sandro','rossi','grossi@prova.it'),
(2,'mauro','bianchi','mauro@sito.com'),(3,'giuseppe','verdi','verdi@verdi.net');
UNLOCK TABLES;

Fate attenzione che il formato differisce leggermente da una versione all'altra, le righe estrapolate si riferiscono ad una versione 4.1.11. Nel file generato con la 3.23 non troverete ad esempio i DROP TABLE o LOCK e UNLOCK. Questo va' principalmente attribuito alle opzioni che sono considerate default in una versione e non nell'altra. Il medesimo effetto si ottiene sulla 3.23 con le opzioni --add-drop-table e --add-locks.

Altra opzione utile è --opt che in realtà corrisponde ad un gruppo di opzioni, tra cui le precedenti. Il suo scopo è rendere il dump più veloce possibile. Risulta fondamentale se si vuole effettuare il backup da un server all'altro:

# mysqldump -uroot -pmiapass --opt articolo |
mysql -uuser -psuapass --host=192.168.0.2 articolo

Il comando permette di trasferire il database articolo dal server locale al server con IP 192.168.0.2. Abbiamo fatto in pratica una pipe tra mysqldump e mysql, l'utente "user" con password "suapass", proveniente dall'host su cui stiamo eseguendo il comando deve ovviamente avere i privilegi necessari perché il tutto possa funzionare.

Ulteriori opzioni corrispondono a quelle di LOAD DATA INFILE, --fields-terminated-by, --fields-enclosed-by, --fields-escaped-by etc. per generare un file esattamente come vogliamo noi. Ovviamente ce ne sono tantissime altre e risulta impossibile descriverle tutte.

Conclusioni

Per questioni di spazio la trattazione ha potuto essere solo una panoramica sull'utilizzo basilare dei vari strumenti e comandi di backup. Come già accennato l'adozione di questo o quello strumento è legata alla propria inclinazione o alla particolare situazione. Personalmente lavorando con MySQL e non dovendo garantire la portabilità trovo comodo utilizzare mysqlhotcopy. Una volta padroneggiati gli strumenti, se non volete procedere a mano, dovrete automatizzare e schedulare le operazioni con qualche script da mettere a cron o in operazioni pianificate. Per ottenere un sistema di ripristino ancora più flessibile e preciso ci si può basare sull'utilizzo combinato di file di backup e file di log ....ma questo meriterebbe un articolo a parte.