Come eseguire il backup e il ripristino dei database mysql con mysqldump

Questo tutorial spiega come eseguire il backup e il ripristino dei database MySQL o MariaDB dalla riga di comando utilizzando l'utilità mysqldump.

I file di backup creati dall'utilità mysqldump sono fondamentalmente un insieme di istruzioni SQL che possono essere utilizzate per ricreare il database originale. Il comando mysqldump può anche generare file in formato CSV e XML.

Puoi anche usare l'utilità mysqldump per trasferire il tuo database MySQL su un altro server MySQL.

Sintassi del comando Mysqldump

Prima di andare su come utilizzare il comando mysqldump, iniziamo esaminando la sintassi di base.

Le espressioni dell'utilità mysqldump hanno la forma seguente:

mysqldump > file.sql

• options - Le opzioni mysqldump file.sql - Il file dump (backup)

Per utilizzare il comando mysqldump, il server MySQL deve essere accessibile e in esecuzione.

Eseguire il backup di un singolo database MySQL

Il caso d'uso più comune dello strumento mysqldump è il backup di un singolo database.

Ad esempio, per creare un backup del database denominato database_name utilizzando l'utente root e salvarlo in un file denominato database_name.sql , eseguire il comando seguente:

mysqldump -u root -p database_name > database_name.sql

Ti verrà richiesto di inserire la password di root. Dopo aver eseguito correttamente l'autenticazione, verrà avviato il processo di dump. A seconda delle dimensioni del database, il processo può richiedere del tempo.

mysqldump database_name > database_name.sql

Backup di più database MySQL

Per --database il backup di più database MySQL con un solo comando è necessario utilizzare l'opzione --database seguita dall'elenco dei database di cui si desidera eseguire il backup. Ogni nome di database deve essere separato da spazio.

mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql

Il comando sopra creerà un file di dump contenente entrambi i database.

Backup di tutti i database MySQL

Utilizzare l'opzione --all-databases per eseguire il backup di tutti i database MySQL:

mysqldump -u root -p --all-databases > all_databases.sql

Come nell'esempio precedente, il comando precedente creerà un singolo file di dump contenente tutti i database.

Eseguire il backup di tutti i database MySQL in file separati

L'utilità mysqldump non fornisce un'opzione per il backup di tutti i database in file separati, ma possiamo facilmente raggiungerlo con un semplice ciclo FOR bash:

for DB in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $DB > "$DB.sql"; done

Il comando sopra creerà un file di dump separato per ciascun database usando il nome del database come nome file.

Creare un backup del database MySQL compresso

Se le dimensioni del database sono molto grandi, è consigliabile comprimere l'output. Per fare ciò, convoglia semplicemente l'output all'utilità gzip e reindirizzalo a un file come mostrato di seguito:

mysqldump database_name | gzip > database_name.sql.gz

Crea un backup con Timestamp

mysqldump database_name > database_name-$(date +%Y%m%d).sql

Il comando sopra creerà un file con il seguente formato database_name-20180617.sql

Ripristino di un dump MySQL

Puoi ripristinare un dump MySQL usando lo strumento mysql . La sintassi generale del comando è la seguente:

mysqld database_name < file.sql

Nella maggior parte dei casi dovrai creare un database in cui importare. Se il database esiste già, devi prima eliminarlo.

Nell'esempio seguente il primo comando creerà un database denominato database_name e quindi importerà il dump database_name.sql in esso:

mysql -u root -p -e "create database database_name"; mysql -u root -p database_name < database_name.sql

Ripristina un singolo database MySQL da un dump MySQL completo

mysql --one-database database_name < all_databases.sql

Esporta e importa un database MySQL in un comando

Invece di creare un file di dump da un database e quindi importare il backup in un altro database MySQL è possibile utilizzare il seguente one-liner:

mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name

Il comando precedente reindirizzerà l'output a un client mysql sull'host remoto e lo importerà in un database chiamato remote_database_name . Prima di eseguire il comando, assicurarsi che il database esista già sul server remoto.

Automatizza i backup con Cron

Automatizzare il processo di backup dei database è semplice come creare un cron job che eseguirà il comando mysqldump al momento specificato.

Per configurare i backup automatici di un database MySQL utilizzando cronjob, attenersi alla seguente procedura:

1. Crea un file chiamato .my.cnf nella tua home directory utente:

   sudo nano ~/.my.cnf

   Copia e incolla il seguente testo nel file.my.cnf.

   user = dbuser password = dbpasswd

   Non dimenticare di sostituire dbuser e dbpasswd con l'utente del database e la password dell'utente.

   Limitare le autorizzazioni del file delle credenziali in modo che solo l'utente abbia accesso ad esso:

   chmod 600 ~/.my.cnf

   Creare una directory per archiviare i backup:

   mkdir ~/db_backups

   Apri il tuo file crontab utente:

   crontab -e

   Aggiungi il seguente cron job che creerà un backup di un nome di database mydb ogni giorno alle 3:

   0 3 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +%Y%m%d).sql

   Non dimenticare di sostituire il username con il tuo vero nome utente.

Puoi anche creare un altro cronjob per eliminare eventuali backup più vecchi di 30 giorni:

find /path/to/backups -type f -name "*.sql" -mtime +30 -delete

Naturalmente, è necessario regolare il comando in base alla posizione del backup e ai nomi dei file. Per ulteriori informazioni sul comando find, consulta la nostra guida Come trovare file in Linux usando la guida della riga di comando.

Conclusione

Questo tutorial copre solo le basi, ma dovrebbe essere un buon inizio per chiunque voglia imparare come creare e ripristinare database MySQL dalla riga di comando usando l'utilità mysqldump.

Puoi anche controllare il tutorial su come reimpostare una password di root di MySQL nel caso in cui l'hai dimenticata.

mysql mariadb backup mysqldump