Gli amministratori di database e gli amministratori di sistema devono eseguire i backup del proprio database MySQL e l'utilità mysqldump è lo strumento più comune utilizzato per convertire un intero database o un singolo oggetto in un file di testo. Dopo che l'utilità mysqldump ha creato un file di backup, può essere utilizzata per ripristinare i dati su un server o per migrare i dati in un altro database.
Che cos'è mysqldump?
mysqldump è un'utilità a riga di comando inclusa in MySQLda utilizzare per esportare istruzioni SQL che possono ricostruire un database o un sottoinsieme di oggetti in un database. Può essere utilizzato per creare un backup di oggetti e dati di database o per trasferire un database da un server all'altro. Gli amministratori possono utilizzare mysqldump per esportare i dati in un formato specifico come XML e CSV.
Come utilizzare mysqldump per eseguire il backup di una singola tabella
L'utilità mysqldump può essere utilizzata per esportare solo tabelle specifiche all'interno di un database. Ad esempio, supponiamo di voler esportare solo una singola tabella clienti da un database di e-commerce. I dati della tabella dei clienti possono quindi essere utilizzati per inviare e-mail di marketing. L'utilità mysqldump esporterà la tabella del cliente e i relativi dati in un file di testo.
Nota: Per eseguire correttamente qualsiasi comando in mysqldump, il nome utente utilizzato per accedere al database deve disporre delle autorizzazioni appropriate per accedere agli oggetti. Senza accesso, il comando non esporterà schemi, oggetti o dati.
È necessario specificare il nome del database e il nome della tabella per esportare correttamente gli oggetti in un file di testo. Se vuoi verificare di avere il nome della tabella corretto, prima assicurati di essere autenticato nel database con cui vuoi lavorare. Quindi, esegui il seguente comando SQL nella riga di comando MySQL o nell'applicazione Workbench:
SHOW TABLES;
Dopo aver trovato il nome della tabella corretto, è possibile esportarla in un file di testo. Il comando seguente consente di esportare la tabella Cliente dal database myDB al file customers.sql:
mysqldump -u username -p password myDB Customer > customers.sql
I termini "nome utente" e "password" devono essere sostituiti con il proprio nome utente e password. È possibile esportare più tabelle separando ciascuna tabella con un carattere di spazio. Di seguito vengono esportate le tabelle Cliente e Ordine in customers_orders.sql:
mysqldump -u username -p password myDB Customer Order > customers_orders.sql
Dopo aver esportato le istruzioni SQL in un file, è possibile aprire il file SQL con un editor di testo standard o con MySQL Workbench. MySQL Workbench è un'installazione separata dal database MySQL, ma è possibile scaricarla gratuitamente dal sito Web MySQL. È meglio aprirlo con Workbench perché in questo modo è possibile eseguire le istruzioni e importare i dati nel server di database MySQL di destinazione.
esempi di mysqldump
L'utilità mysqldump viene utilizzata principalmente per i backup o il trasferimento di dati a un altro server di database. Che tu effettui un backup del database o desideri trasferire i dati a un altro server, l'utilità mysqldump funziona allo stesso modo. Un uso comune di un backup mysqldump è la creazione di un ambiente di test dal database di produzione.
Per utilizzare un database di produzione per creare un ambiente di test, è possibile eseguire un backup completo del database utilizzando l'utility mysqldump. Il comando seguente consente di eseguire un backup dell'intero database denominato myDB ed esportarlo in un file denominato myDB.sql:
mysqldump -u username -p password -databases myDB > myDB.sql
Ora accedi al server di test. Inoltre, deve avere MySQL installato in modo da poter importare i dati esportati. Si noti che il comando di esportazione utilizza il carattere ">" per spostare i dati da un database a un file SQL. Il processo di importazione utilizza il carattere "<". Il comando seguente importa tutti i dati nel file myDB.sql in un ambiente di test con lo stesso database denominato myDB:
mysqldump -u username -p password -databases myDB < myDB.sql
Con i due comandi precedenti, puoi replicare i dati dal database myDB originale in produzione nell'ambiente di test. L'ambiente di test offre agli sviluppatori e agli addetti alla garanzia di qualità la possibilità di lavorare con i dati del mondo reale e una struttura di database che imita qualsiasi problema dei clienti o dei dipendenti per la risoluzione dei problemi.
Uso di base di mysqldump
Il file .sql creato durante un mysqldump è un semplice file di testo. Puoi aprirlo in qualsiasi editor di testo, ma è meglio aprirlo con MySQL Workbench. I comandi dei codici colore e i tipi di dati di Workbench, che facilitano la lettura dei comandi SQL.
È possibile aprire il file .sql in Workbench utilizzando il menu File. Fare clic su "Apri script SQL" nella finestra di dialogo e scegliere il file .sql che si desidera leggere. Con il file .sql aperto, puoi leggerlo ed eseguirlo con la voce di menu "Esegui script SQL" File, se scegli di eseguire i comandi. Workbench viene eseguito su Windows e Linux, quindi è possibile eseguire uno script in questo modo su entrambi i sistemi operativi.
Puoi anche eseguire un file mysqldump in Windows e Linux utilizzando l'utility mysqldump. Il comando seguente esegue le istruzioni SQL su un database MySQL in Windows e Linux dall'utility mysqldump:
mysqldump -u username -p password -databases myDB < myDB.sql
Il comando precedente importa i dati dal file myDB.sql mysqldump ed esegue le istruzioni SQL sul server di database locale. Il comando importa i dati nel database myDB.
Se non sono presenti dati nel database o in una delle tabelle del database, le strutture del database vengono comunque create e il database viene aggiunto allo schema. Le strutture delle tabelle sono disponibili per aggiungere dati in futuro.
Uso avanzato di mysqldump
Un database di grandi dimensioni potrebbe contenere tabelle con terabyte di dati. Troppe esportazioni potrebbero esaurire le risorse di storage, ma è possibile comprimere ogni file per ridurre lo spazio di storage necessario per le esportazioni di dati MySQL. La compressione di un file può far risparmiare diversi gigabyte di spazio di storage necessario per i file di grandi dimensioni. Il seguente comando mysqldump esporta il database myDB e lo comprime utilizzando l'utilità gzip in un file denominato myDB.sql.gz:
mysqldump -u username -p password -databases myDB | gzip myDB.sql.gz
I backup spesso contengono dati sensibili, quindi la crittografia è necessaria per la conformità e la data protection. Per crittografare i dati è necessario uno strumento di terze parti. In questo esempio utilizzeremo ccrypt per crittografare il file myDB.sql. È necessaria una chiave (simile a una passphrase) per crittografare un file, ma è necessario ricordare questa chiave per decrittografarlo. L'utilità di crittografia richiede di immettere una chiave quando si esegue la crittografia del file, oppure è possibile memorizzarla in un file sicuro dove può essere riutilizzata per crittografare e decrittografare un file .sql.
Nell'esempio seguente, il file .sql esportato viene crittografato utilizzando una chiave memorizzata nel file denominato mykey.key:
mysqldump -u username -p password -databases myDB | ccrypt -k mykey.key > myDB.sql.cpt
Si noti che i dati vengono esportati in un file .cpt. In questo file .cpt vengono memorizzati tutti i dati crittografati. Per decrittografare il file è necessaria la stessa chiave. È possibile decrittografarlo utilizzando il seguente comando:
cdecrypt -k mykey.key myDB.sql.cpt
La maggior parte dei database contiene più di tabelle e dati. Devono essere sottoposti a backup anche i trigger e le procedure. Per impostazione predefinita, l'utilità mysqldump esporta i trigger con un'esportazione standard, ma gli amministratori possono esplicitamente dire al comando di esportarli. Le procedure non vengono esportate per impostazione predefinita, pertanto gli amministratori devono esportarle esplicitamente. Il comando seguente consente di esportare trigger e procedure insieme ad altri oggetti e dati di database:
mysqldump -u username -p password -databases myDB –triggers=true –routines=true > myDB.sql
Best practice per mysqldump
Ogni volta che è necessario utilizzare l'utility mysqldump, è possibile utilizzarlo liberamente senza interrompere la produttività. È particolarmente utile quando è necessario eseguire backup o esportazioni frequenti del database e dei relativi dati. I backup devono essere eseguiti spesso in modo che i dati possano essere recuperati in caso di guasto del database, malware o danneggiamento dei dati.
La frequenza dei backup dipende dalla quantità di dati aggiunti a un database ogni giorno e dalla frequenza delle modifiche apportate al database. In genere, la frequenza di utilizzo del comando mysqldump dipende dalla quantità di dati che possono essere persi senza causare un impatto negativo sulle entrate e sulla business continuity. Non è sempre necessario un backup completo, ma alcune aziende utilizzano l'utility mysqldump una volta alla settimana e altre la utilizzano una volta al giorno. Determina la frequenza dei backup completi necessari per la tua azienda e utilizza gli strumenti di automazione per eseguire mysqldump a un intervallo prestabilito.
Conclusione
La gestione di un database è un lavoro a tempo pieno, ma l'utilità mysqldump rende la gestione molto più efficiente. È possibile utilizzarli per i backup completi dei dati o per trasferire i dati tra i server. È compatibile con Windows o Linux, quindi puoi utilizzarlo in qualsiasi ambiente che ospita il server MySQL.
