Il backup e il recovery dei dati sono, da sempre, attività fondamentali per garantire la sicurezza dei dati di un DBMS. In particolare, attraverso i backup effettuiamo salvataggi del contenuto del database ad un determinato momento, mentre il recovery è l’operazione con la quale ripristiniamo il contenuto del database a seguito di un danneggiamento.

Per poter effettuare dei recovery completi in caso di problemi, è necessario eseguire il server MySQL con i log attivati (opzione –log-bin del server); in caso contrario, tutto ciò che sarà possibile, se ci saranno problemi, sarà ripristinare i dati all’ultimo backup, perdendo le modifiche successive.

Sono comunque piuttosto rari i casi in cui si rende necessario ricorrere all’uso dei backup per recuperare i dati: in genere si tratta di guasti all’hardware o all’alimentazione del sistema, oppure crash del sistema operativo o del filesystem. Anche in questi casi tuttavia è possibile che MySQL sia in grado di riavviarsi correttamente.

Backup dei dati

La prima misura di sicurezza da adottare nei confronti dei nostri dati è quindi quella di effettuare backup periodici. Il modo migliore per fare ciò è usare il programma mysqldump, che ci consente di salvare i nostri dati in maniera molto semplice.

Attenzione! In tutti gli esempi di codice di questa lezione che lanciano programmi, alle opzioni indicate vanno aggiunte quelle relative ad utente e password per l’accesso al server – ved. lezione 3

mysqldump –single-transaction –all-databases > nome_file

Con questo comando otteniamo, nel file nome_file, una lista di istruzioni INSERT che ci permettono di ripristinare l’intero contenuto del database. Il file è in formato testo, quindi possiamo aprirlo e verificarne il contenuto. L’opzione –single-transaction è utile se il database contiene tabelle InnoDB, in quanto permette di ottenere una vista consistente di tali tabelle, cioè non influenzate da eventuali aggiornamenti effettuati durante l’operazione di backup. Per quanto riguarda invece le tabelle non transazionali (MyISAM) non è possibile avere questa garanzia; bisogna quindi essere certi che le tabelle non vengano modificate durante il backup.

Se vogliamo effettuare il backup di un singolo database invece che dell’intero server possiamo farlo omettendo l’opzione –all-databases e indicando al suo posto il nome del database che ci interessa.

Nel momento in cui vogliamo ricaricare sul server il contenuto del backup effettuato, è sufficiente far leggere il file di backup al client mysql:

mysql < nome_file
mysql nome_db < nome_file

La seconda sintassi va usata nel caso in cui il backup contenga i dati di un singolo database; in questo caso infatti nel backup non ci sono riferimenti al database utilizzato.
Immaginiamo ora di volerci garantire la possibilità di effettuare, in caso di crash, un recovery completo dei dati fino al momento del guasto. Innanzitutto dovremo quindi preoccuparci di abilitare i file di log. Per fare questo dobbiamo aggiungere al file di configurazione, nella sezione mysqld, l’opzione log-bin:

log-bin=percorso/basename

Il server creerà quindi i file di log nella directory indicata, e come nome di file utilizzerà il basename aggiungendovi, come estensione, un numero progressivo di 6 cifre che viene incrementato ad ogni nuovo file di log. È anche possibile omettere l’indicazione del percorso e del basename, utilizzando semplicemente l’opzione log-bin; in questo caso i log verranno creati nella directory dei dati di MySQL, e come nome dei file verrà usato ‘nome_computer-bin’. L’ideale tuttavia sarebbe che i log si trovassero su un’unità disco diversa da quella dei dati, in modo che un’eventuale guasto dell’hardware non comprometta la loro disponibilità.

Ad ogni riavvio del server MySQL crea un nuovo file di log, incrementando il progressivo; inoltre è possibile forzare questa operazione attraverso l’istruzione FLUSH LOGS.
Per una migliore gestione dei backup in questo caso potremo utilizzare un formato più esteso del comando di esecuzione di mysqldump quando effettuiamo il salvataggio completo dei dati:

mysqldump –single-transaction –flush-logs –master-data=2 –all-databases –delete-master-logs > nome_file

Come vedete abbiamo aggiunto due opzioni: con flush-logs MySQL crea un nuovo file di log, che sarà il primo da utilizzare in caso di ripristino dei dati a partire da questo backup; con master-data=2 sul file di backup viene scritto (in forma di commento) il nome del file di log appena creato; infine l’opzione delete-master-logs cancella i log precedenti, che non servono più.

In caso di disastro, quindi, dovremo innanzitutto ripristinare il backup principale come visto in precedenza; poi ci occuperemo dei file di log, attraverso il programma mysqlbinlog:

mysqlbinlog nome_file_log nome_file_log | mysql
mysqlbinlog nome_file_log nome_file_log > nome_file

Con questo comando diamo in input due file di log al programma: nel primo caso gli indichiamo di indirizzare l’output sul client mysql in modo da rieseguire le istruzioni memorizzate nei log (ricordiamoci di aggiungere i dati per la connessione!), e quindi ricostruire la situazione della base dati. È importante, qualora i file di log da elaborare siano più di uno, rieseguirli tutti con una unica istruzione. Nel secondo caso invece scriviamo l’output su un file di testo in modo da poterlo poi visualizzare ed esaminare.

Nel leggere i log binari abbiamo la possibilità di delimitare, in due modi, le istruzioni da prendere in considerazione: possiamo usare un timestamp di inizio e di fine, oppure le posizioni sul file.

Vediamo come:

mysqlbinlog –start-date=”2006-01-25 9:55:00″ –stop-date=”2006-01-25 10:00:00″ nome_file
mysqlbinlog –start-position=”2345″ –stop-position=”4567″ nome_file

Nel primo caso diciamo a mysqlbinlog di leggere solo le istruzioni comprese fra le 9.55 e le 10.00 della data indicata; con il secondo invece indichiamo le posizioni nel file di log a cui fare riferimento. In entrambi i casi possiamo usare anche una sola delle due opzioni (inizio o fine). Per sapere a quali posizioni fare riferimento, possiamo esaminare l’output del programma sul file di testo, che riporta per ogni istruzione memorizzata la posizione di inizio e di fine.

Manutenzione delle tabelle

Ci occupiamo ora della manutenzione delle tabelle MyISAM, per le quali abbiamo a disposizione il programma myisamchk. Tuttavia questo programma andrebbe utilizzato a server non attivo, in quanto può causare problemi qualora tenti di accedere alle tabelle in contemporanea al server MySQL: il nostro consiglio è quindi quello di utilizzare, al suo posto, le istruzioni SQL che svolgono le stesse funzioni:

CHECK TABLE tabella [opzione]
REPAIR TABLE tabella [opzione]
OPTIMIZE TABLE tabella

CHECK TABLE si usa per verificare lo stato di una tabella e vedere se ci sono dei problemi.
I possibili valori di opzione sono: QUICK, FAST, MEDIUM, EXTENDED, CHANGED; tali opzioni sono in ordine crescente di complessità, e garantiscono un controllo via via più accurato ma anche più lento. L’output di CHECK TABLE dovrebbe, normalmente, segnalare che la tabella è OK; in caso contrario è necessario ripararla con REPAIR TABLE.
Anche qui abbiamo la possibilità di indicare più opzioni (QUICK, EXTENDED, USE_FRM). Il consiglio è di iniziare con la prima e provare le successive solo in caso di insuccesso. Anche in questo caso avremo un output che ci comunica l’esito dell’operazione.

L’istruzione OPTIMIZE TABLE serve, infine, per ottimizzare l’occupazione di spazio di una tabella: è bene eseguirla in particolare quando sono state effettuate molte cancellazioni o molti aggiornamenti su una tabella.

Un’altra alternativa alle istruzioni SQL per la manutenzione delle tabelle è il programma client mysqlcheck, che a differenza di myisamchk può essere eseguito tranquillamente anche a server avviato.