Introduzione
Questo tutorial spiega come eseguire il backup e il ripristino dei database MySQL o MariaDB dalla riga di comando utilizzando l'utility mysqldump.
I file di backup creati dall'utility 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'utility mysqldump per trasferire il tuo database MySQL su un altro server MySQL.
Se non si esegue il backup dei database, un bug del software o un guasto del disco rigido potrebbero essere disastrosi. Per aiutarti a risparmiare un sacco di tempo e frustrazione, si consiglia vivamente di prendere la precauzione di eseguire regolarmente il backup dei database 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 [options] > file.sql
options- Le opzioni di mysqldumpfile.sql- Il file di 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.
Se hai effettuato l'accesso come lo stesso utente che stai utilizzando per eseguire l'esportazione e quell'utente non richiede una password, puoi omettere le opzioni -u e -p:
mysqldump database_name > database_name.sql
Backup di più database MySQL
Per eseguire il backup di più database MySQL con un 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.sqlCome 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 eseguire il backup di tutti i database in file separati, ma possiamo ottenerlo facilmente con un semplice ciclo FOR in 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ò è sufficiente reindirizzare l'output all'utilità gzip e reindirizzarlo su un file come mostrato di seguito:
mysqldump database_name | gzip > database_name.sql.gz
Crea un backup con Timestamp
Se si desidera conservare più di un backup nella stessa posizione, è possibile aggiungere la data corrente al nome del file di backup:
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
È possibile ripristinare un dump MySQL utilizzando 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.sqlRipristina un singolo database MySQL da un dump MySQL completo
Se è stato eseguito il backup di tutti i database utilizzando l'opzione -all-databases e si desidera ripristinare un singolo database da un file di backup che contiene più database, utilizzare l'opzione --one-database come mostrato di seguito:
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 sopra invierà l'output a un client mysql sull'host remoto e lo importerà in un database denominato 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:
Creare un file denominato .my.cnf nella directory home dell'utente:
sudo nano ~/.my.cnf
Copia e incolla il seguente testo nel file .my.cnf.
[client]
user = dbuser
password = dbpasswdNon 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 del nome di un database mydb ogni giorno alle 3:00:
0 3 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +%Y%m%d).sql
Non dimenticare di sostituire 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.
Conclusione
Questo tutorial copre solo le basi, ma dovrebbe essere un buon inizio per chiunque voglia imparare a creare e ripristinare database MySQL dalla riga di comando usando l'utilità mysqldump.
