Effettuare il backup, e il ripristino, di un database MySQL con mysqldump, in locale o in remoto: guida ed esempi.

di Giulio di Bari :: 06 marzo 2018

Mysqldump è una utility da linea di comando tramite la quale è possibile fare il backup di uno o più database MySQL, o di singole tabelle di un database.

Con mysqldump è possibile salvare una fotografia esatta della struttura e del contenuto di un database, in modo da conservare una copia esatta da utilizzare in caso di crash del server, o di perdite di dati, o semplicemente qualora si voglia trasferire il database in un server differente. Il file generato da mysqldump (il "dump") contiene l'insieme di tutte le istruzioni SQL necessarie per ricreare database, tabelle e i dati contenuti in esse.

In questo articolo esaminiamo alcuni esempi pratici su come utilizzare mysqldump per il backup e il ripristino, anche con uno script bash che in automatico salvi il backup dei database presenti sul nostro server MySQL.

Backup di un singolo database MySql

Vediamo subito come esemportare, cioè fare un "dump" di un database presente sul nostro server MySQL.

Per procedere nella nostra spiegazione:

Dovete aver accesso come utenti root al server linux dove MySQL è installato (noi utilizziamo Centos 7)
MySQL deve essere già installato e deve avere almeno un database creato e popolato

Apriamo il terminale del server dove MySQL è installato e digitiamo il comando "mysqldump"

# mysqldump -u [user_name] -p[user_password] [database_name] > [file_da_salvare]

In questa istruzione abbiamo utilizzato le opzioni minime per salvare un database presente sul nostro MySQL, in un file chiamato "backup.sql", vediamole assieme.

-u [user_name]: al posto di [user_name] dobbiamo indicare un utente con gli adeguati privilegi per poter operare nel database, normalmente si utilizza l'utente root quindi "-u root"
-p[user_password]: al posto di [user_password] indichiamo la password dell'utente indicato. Notare la mancanza di spazi tra "-p" e la password. Non è un errore! Ad esempio "-plamiapwd"
Potremmo non indicare la password, ma solamente "-p": in questo caso ci verrà chiesto di inserire successivamente la password
[database_name]: è il nome del database da salvare. Ad esempio "miodb"
[file_da_salvare]: il nome che ciamo al file che vogliamo salvare, ad esempio "backup.sql". Oppure, se vogliamo salvare il file in una differente posizione nel server, indichiamo il percorso completo a questo file, ad esempio "/home/backup.sql"

# mysqldump -u root -plamiapwd miodb > backup.sql

All'interno del file "backup.sql" generato, troveremo tutte le istruzioni SQL per ricreare il database, le sue tabelle, e per popopolare i suoi dati

Ecco un esempio di estratto del file, dove viene ricreata una tabella chiamata "admin" e ricaricati i suoi records

--
-- Table structure for table `admins`
--

DROP TABLE IF EXISTS `admins`;
CREATE TABLE IF NOT EXISTS `admins` (
  `admins_id` smallint(3) unsigned NOT NULL AUTO_INCREMENT,
  `admins_datasave` datetime DEFAULT NULL,
  `admins_cognome` varchar(100) DEFAULT NULL,
  `admins_email` varchar(200) DEFAULT NULL,
  `admins_pwd` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`admins_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `admins`
--

LOCK TABLES `admins` WRITE;
/*!40000 ALTER TABLE `admin` DISABLE KEYS */;
INSERT INTO `admins` (`admins_id`, `admins_datasave`, `admins_nome`, `admins_cognome`, `admins_email`, `admins_pwd`) VALUES
(1, '2016-06-10 00:00:00', 'Web', 'Master', 'mailtest@gmail.com', '5ecdep1352146i505032dfy5e63bc2c9');
UNLOCK TABLES;

Durante l'esportazione, MySQL impedisce la scrittura delle tabelle, quindi è in modalità di sola lettura.

Questo è evidenziato dall'istruzione "LOCK TABLES `admins` WRITE;".

Al termine della esportazione, il processo di scrittura viene riabilitato con "UNLOCK TABLES;"

Backup di alcuni database

Se abbiamo più database, e vogliamo fare il backup di alcuni tra questi database, utilizziamo una istruzione simile alla precedente.

Facciamo una veloce digressione e vediamo come visualizzare i database presenti sul nostro server MySQL.

Accediamo a mysql digitando quando segue

# mysql -u root -plamiapwd

Siamo all'interno di MySQL: utilizziamo "show databases" per vedere l'elenco dei database presenti, tra cui ritroviamo il nostro database "miodb"

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| miodb              |
| mysql              |
| fatture            |
+--------------------+
4 rows in set (0.00 sec)

Bene, adesso che sappiamo quali database abbiamo su MySQL, decidiamo di esportare i database "miodb" e "fatture".

Utilizziamo una istruzione simile a quella vista per esportare un singolo db

# mysqldump -u root -plamiapwd --databases miodb fatture > backup.sql

Abbiamo aggiunto l'opzione "--databases" seguita dai database da esportare separati da uno spazio

Backup di tutti i database

Se vogliamo esportare tutti i database del nostro MySQL, utilizziamo l'opzione "--all-databases" senza indicare i nomi dei database da esportare.

# mysqldump -u root -plamiapwd --all-databases > backup.sql

Backup di una tabella di un database

Se invece vogliamo esportare una specifica tabella di un database, ad esempio la tabella "admin" del database "miodb"

# mysqldump -u root -plamiapwd miodb admins > backup.sql

Possiamo anche esportare più tabelle: è sufficiente separarle con uno spazio, ad esempio le tabelle "admin" "visite" "news"

# mysqldump -u root -plamiapwd miodb admins visite news > backup.sql

Backup condizionali

Potremmo voler esportare solo alcuni records di una determinata tabella, che rispondano a determinate condizioni, ad esempio con id > di un certo valore. Utilizziamo il classico "WHERE" che utilizziamo nelle normali query MySQL, in questo modo

#mysqldump -u root -plamiapwd --where="admins_id > 5" admins > backup.sql

In questo esempio stiamo esportando i records della tabella admin che vanno un ID maggiore di 5
Abbiamo utilizzato l'opzione "--where" il cui valore, tra virgolette, è la condizione della query di ricerca

Ripristinare un database MySQL

Adesso come come ripristinare un database esportato precedentemente.

E' importante, quando importi un database, che lo stesso (anche se vuoto) sia presente sul server. Quindi se sono in un server differente da quello dove hai esportato il database, verifica se presente. Se non lo è vediamo velocemente come creare il database.

Accedi a MySQL, come hai visto in precedenza, e poi usa il comando "create database" seguito dal nome del database che vuoi importare.

# mysql -u root -plamiapwd
mysql> create database miodb;

Se vuoi essere sicuro che adesso esista, verifica l'elenco dei database presenti usando "show databases" come abbiamo visto precedentemente.

Bene, adesso importiamo il database "miodb"

Utilizziamo il comando "mysql" in modo simile a come abbiamo usato "mysqldump": cambia la "freccia" che punta alla sinistra, per indicare appunto che sto importanto un database

# mysql -u [user_name] -p[user_password] [database_name] < [file_da_importare]

Nel nostro esempio:

# mysql -u root -plamiapwd miodb < backup.sql

Ripristinare un database su un server remoto

Se dobbiamo esportare un database su un server "A", e vogliamo reimportarlo su un server differente "B", localizzato sulla stessa rete, o raggiungibile in rete tramite un IP Pubblico, invece di salvare il database, uplodarlo sul nuovo server, e importarlo, possiamo procedere con un solo passaggio.

Anche in questo caso, il database sul server remoto "B" deve essere già presente, per cui, nel caso non lo fosse, procedi alla sua creazione.

Andiamo a concatenare le istruzione di esportazione (mysqldump) con l'importazione (mysql)

# mysqldump -u [user_name] -p[user_password] [database_name] | mysql -u [user_name_remote] -p[user_password_remote] --host=[remote-server] -C [database_name]

Prima del pipe "|" abbiamo l'azione di esportazione del database scelto, dopo abbiamo l'importazione sul server remoto, dove

[user_name_remote] e [user_password_remote] sono user e pwd dell'utente mysql del server remoto (che quindi potrebbe avere credenziali diverse da quelle del server locale)
[remote-server] è l'ip del server remoto, ad esempio 192.168.1.11
[database_name] è il nome del db che stiamo importando "miodb"

# mysqldump -u root -plamiapwd miodb | mysql -u root -plasuapwd --host=192.168.1.11 -C miodb

Opzioni di mysqldump

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

--no-data: effettua il backup solo della struttura, senza i dati contenuti nelle tabelle.
--no-create-db: elimina la query di creazione del nuovo database.
--add-drop-database: aggiunge la sintassi DROP DATABASE prima della sintassi CREATE DATABASE. In pratica fa sì che, al momento del ripristino, il database preesistente venga automaticamente eliminato prima di ricaricare quello presente nel backup.
--add-drop-table: aggiunge la sintassi DROP TABLE prima della sintassi CREATE TABLE.
--skip-comments: non scrive commenti di Mysql all'interno del dump.
--lock-all-tables: blocca da scrittura tutte le tabelle del database durante l'operazione di backup, posticipando le richieste di scrittura originate da altri client, al termine del backup. In poche parole, i client restano in attesa di scrivere sul database fino a quando il backup non è ultimato. In siti ad alto traffico, ed in presenza di un backup notevoli, e che impiegano molto tempo, questa attesa potrebbe generare disservizi sul sito che utilizza il database.
–add-locks: ha effetto quando si importa il file generato da mysqldump. nel codice generato vengono aggiunti (come avevamo visto nel nostro esempio iniziare) le istruzioni LOCK TABLES e UNLOCK TABLES ad ogni dump di tabella. Ciò si traduce in inserimenti più veloci quando il file di dump viene ricaricato perchè mentre importi i dati, ogni tabella sarà bloccata da letture e scritture mentre sta importanto la tabella stessa.
--ignore-table: da utilizzare se vogliamo escludere dal backup una o più tabelle. Queste tabelle vanno indicate, ad una ad una, esplicitando anche il database al quale appartengono
Il formato da utilizzare, per ogni tabella da escludere, è: --ignore-table=[nomedatabase].[nometabella]
Ad esempio: --ignore-table=miodb.anagrafiche --ignore-table=modb.categorie

Per l'elenco completo delle opzioni si rimanda alla documentazione ufficiale di MySQL (link a fondo pagina)

Opzioni di gruppo

Esistono opzioni che, se al loro interno, raggruppano varie opzioni. In pratica sono delle scorciatioie: invece di indicare una sequenza di opzioni, ne indico una sola che ne abilita molte.

Queste opzioni di gruppo sono

–opt : è equivalente ad indicare le opzioni –add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, –disable-keys.
Questa opzione è presente di default, quindi anche se non indicata avrà effetto. Per disabilitarla usiamo "–skip-opt"
–compact : è utilizzata principalmente in attività di debugging. Questa opzioni ad esempio disabilita i commenti e abilita le opzioni –skip-add-drop-table –no-set-names –skip-disable-keys –skip-add-locks

Performance di mysqldump

Per backup di dati di notevoli dimensioni, il ripristino dei dati ottenuto con mysqldump può essere molto lento poiché vanno riprodotte le singole istruzioni SQL, vanno ricreati gli indici e così via.

Se le tabelle sono principalmente di tipo InnoDB, o se hai un mix di tabelle InnoDB e MyISAM, è preferibile utilizzare lo strumento mysqlbackup presente nel pacchetto MySQL Enterprise Backup, ma non è gratuito.
Se le tue tabelle sono principalmente MyISAM, potresti utilizzare mysqlhotcopy, per ottenere prestazioni migliori rispetto a mysqldump nelle operazioni di backup e ripristino.

Un ulteriore problema che si presenta con una notevole quantità di dati è relativo al buffing in memoria dei dati: mysqldump può estrarre e copiare il contenuto della tabella riga per riga, oppure può estrarre l'intero contenuto della tabella e bufferizzarlo in memoria prima di riversarlo. Il buffering in memoria può essere un problema se si stanno copiando tabelle di grandi dimensioni. Per effettuare il dump riga per riga, è quindi preferibile usare l'opzione "--quick", che tuttavia è già abilitata se usiamo "--opt". L'opzione --opt, come detto, è abilitata di default (e quindi lo è anche --quick), per cui se vogliamo abilitare il buffering della memoria, usiamo "--skip-quick" (cioè disabilitiamo "--quick").

Compressione di un file backup

Se vogliamo salvare il nostro bpk su uno storage, o sul nostro server, conviene comprire il file così da occupare minore spazio disco.

Essendo il dump ottenuto sostanzialmente un file di testo, l'utilizzo di programmi di compressione, come ad esempio gzip, possono ridurre notevolmente il peso del file.

Utilizzano gzip otterremo un file con estensione ".gz". Il comando è il seguente

# gzip [compressione] [nomefile_da_comprimere]

Il parametro [compressione] è facoltativo, e serve a indicare il livello di compressione che vogliamo ottenere, da 1 (minima) a 9 (massima)

Il parametro [nomefile_da_comprimere] è ovviamente il file che vogliamo comprimere

Ad esempio

# gzip -9 backup.sql

Così facendo il file backup.sql verrà sostituito dal file compresso "backup.sql.gz", quindi con estenzione ".gz"

Potremmo anche ottenere da "mysqldump" un file compresso... basta concatenare!

# mysqldump -u root -plamiapwd miodb | gzip -9 > backup.sql.gz

Per decomprimere un file gz utilizziamo l'opzione "-d" e tornare al file originario

# gzip -d backup.sql.gz