Tutorial > Come effettuare un backup periodico di un database MySQL su Ubuntu 18.04

Come effettuare un backup periodico di un database MySQL su Ubuntu 18.04

Pubblicato il: 10 ottobre 2019

Backup Database MySQL Ubuntu

Effettuare backup regolari è una procedura essenziale per qualsiasi tipo di dato. Questo tutorial ti aiuterà a conoscere alcuni degli strumenti più utilizzati per effettuare backup e ripristino del tuo database MySQL.

Per prima cosa dovrai connetterti al tuo server tramite una connessione SSH. Se non l’hai ancora fatto, ti consigliamo di seguire la nostra guida per connetterti in sicurezza con il protocollo SSH. In caso di server locale puoi passare al punto successivo e aprire il terminale del tuo server.

Utilizzare mysqldump 

Mysqldump è un applicativo sempre disponibile in ogni installazione MySQL e ti permette di effettuare un dump completo dei tuoi dati in formato testo.

La sintassi del comando è la seguente:

$ mysqldump -u [nomeutente] -p [nome_database] > backup.sql

Lanciando questo comando, troverai un backup completo del database indicato nel file backup.sql.

Per ripristinare il backup utilizza il client MySQL come segue:

$ mysql -u [nomeutente] -p [nome_database] < backup.sql

A seconda delle dimensioni del database, questi file di backup possono raggiungere dimensioni importanti, rendendo difficoltoso il loro trasferimento o la loro copia. Per ottimizzare la dimensione della copia di backup del database, puoi utilizzare gzip per comprimere il file:

$ mysqldump -u [nomeutente] -p [nome_database] | gzip > backup.sql.gz

Volendo, puoi anche aggiungere automaticamente la data attuale nel filename utilizzando:

$ mysqldump -u [nomeutente] -p [nome_database] | gzip > backup_$(date +%F.%H%M%S).sql.gz

Per effettuare il ripristino utilizza invece la seguente sintassi:

$ mysql -u [nomeutente] -p [nome_database] < backup.sql

Note su mysqldump:

É bene tenere in considerazione che durante il dump e backup di una database, mysqldump non garantisce la consistenza dei dati tra tabelle. Se durante il backup avvengono delle operazioni di scrittura, il backup ottenuto, potrebbe risultare parziale e/o corrotto.

Per prevenire questo problema ci sono delle opzioni specifiche fornite dall’applicativo:

  • --lock-all-tables: blocca tutte le operazioni di scrittura su tutte le tabelle di tutti i database

  • --lock-tables: questa opzione invece imposta le tabella dei database in fase di backup in modalità di sola lettura. 

Quale opzioni scegliere dipende se hai dati relazionati fra più database o meno. Nella maggior parte dei casi --lock-tables rappresenta la soluzioni ideale per garantire l’integrità dei dati per ogni singolo database.

Automatizzare mysqldump attraverso crontab

Per rendere il backup periodico non ti resta che aggiungerlo nel crontab del sistema programmando, ad esempio, una volta al giorno. Per prima cosa, apri il crontab avviando:

$ sudo crontab -e

A questo punto aggiungere una riga alla fine del file come segue:

00 23 * * * mysqldump -u [nomeutente] -p [nome_database] | gzip > /home/utente/backup.sql.gz

Salvando il file, il sistema effettuerà un backup al giorno (alle ore 23:00) all’interno della tua directory utente, sovrascrivendo ogni giorno il backup precedente.

Utilizzare automysqlbackup

Oltre a utilizzare mysqldump esportando i backup manualmente, potresti affidarti a una delle utility più note nel settore: automysqlbackup. 

Per prima cosa devi installare il pacchetto attraverso apt-get:

$ sudo apt-get install automysqlbackup

Dopo aver completato l’installazione, automysqlbackup effettuerà backup giornalieri all’interno della directory /var/lib/automysqlbackup. Puoi anche avviare manualmente un backup, lanciando:

$ sudo automysqlbackup

Una volta completato, puoi verificare i backup presenti semplicemente listando il contenuto della directory:

$ ls -R /var/lib/automysqlbackup/

/var/lib/automysqlbackup/:

daily  monthly  weekly
                /var/lib/automysqlbackup/daily:

exampledb  information_schema   performance_schema test  wordpress
                /var/lib/automysqlbackup/daily/exampledb:

exampledb_2019-08-03_06h48m.Sunday.sql.gz

...

Se tutto ha funzionato correttamente, noterai una serie di file con estensioni .sql.gz come mostrato sopra.

Ti consigliamo di controllare periodicamente gli ultimi backup effettuati e verificare che tutti funzionino correttamente.

Personalizzazione di automysqlbackup

Per modificare la frequenza di backup di automysqlbackup o i dati di accesso necessari ad accedere al tuo database puoi modificare il file di configurazione presente in /etc/default/automysqlbackup.

Alcune tra le impostazioni sono:

  • USERNAME: nome utente utilizzato per effettuare l’accesso al db

  • PASSWORD: password del database

  • DBHOST: Indirizzo ip o nome host 

  • DBNAMES: nomi dei database di cui fare il backup

  • BACKUPDIR: Directory dove salvare i backup

Dopo aver modificato le impostazioni desiderate, salva il file per applicare le modifiche.