Nel lavoro di manutenzione quotidiana, spesso sono richieste operazioni di esportazione dati e mysqldump è uno strumento frequentemente utilizzato per l'esportazione dei dati. Dispone di molti parametri funzionali integrati e in questo articolo verranno elencate alcune operazioni comunemente usate.
Che cos'è mysqldump?
mysqldump è un programma client MySQL utilizzato per eseguire il dump dei database. Genera principalmente una serie di istruzioni SQL che possono essere racchiuse in un file. Questo file contiene tutti i comandi SQL necessari per ricostruire il database, come CREATE DATABASE, CREATE TABLE, INSERT, ecc. Può essere utilizzato per migrazioni o recuperi rapidi e leggeri del database ed è un metodo per eseguire backup logici nei database MySQL.
Come esportare un database MySQL utilizzando mysqldump?
1. Esporta tutti i database
Questo comando esporta tutti i database, inclusi i database di sistema.
mysqldump -uroot -proot --all-databases > /tmp/all.sql
2. Esporta tutti i dati dai database db1 e db2
mysqldump -uroot -proot --databases db1 db2 > /tmp/user.sql
Esporta più database. Tutti i parametri successivi all'opzione sono considerati nomi di database.
Aggiungi un'istruzione DROP DATABASE prima di creare ogni database.
mysqldump -uroot -p --databases test --add-drop-database
3. Esporta le tabelle a1 e a2 da db1
Nota che l'esportazione di tabelle specificate può essere effettuata solo per un singolo database e il contenuto esportato è diverso dall'esportazione di un intero database. Il testo esportato per le tabelle specificate non include istruzioni per la creazione del database; include soltanto istruzioni DROP TABLE, CREATE TABLE e INSERT.
mysqldump -uroot -proot --databases db1 --tables a1 a2 > /tmp/db1.sql
Sostituisci il parametro --databases e specifica le tabelle da esportare.
mysqldump -uroot -p --host=localhost --databases test --tables test
Esporta tutti gli spazi delle tabelle.
mysqldump -uroot -p --databases test --all-tablespaces
Escludi tutte le informazioni sul tablespace dall'esportazione.
mysqldump -uroot -p --databases test --no-tablespaces
Aggiungi un'istruzione DROP TABLE prima di creare ogni tabella (abilitata per impostazione predefinita, utilizza --skip-add-drop-table per disabilitarla).
mysqldump -uroot -p --databases test
mysqldump -uroot -p --databases test --skip-add-drop-table
4. Esportazione condizionale: esporta i record con id=1 dalla tabella a1 nel database db1
L'esportazione condizionale può essere eseguita solo su una singola tabella.
mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1' > /tmp/a1.sql
Scarica solo i record selezionati dalla condizione WHERE indicata. Se la condizione contiene interpreti di comandi, spazi o caratteri speciali, deve essere racchiusa tra virgolette.
mysqldump -uroot -p --host=localhost --databases db1 --where="user='root'"
5. Esporta solo le strutture delle tabelle, senza dati
mysqldump -uroot -proot --no-data --databases db1 > /tmp/db1.sql
Esporta solo i dati, senza aggiungere istruzioni CREATE DATABASE.
mysqldump -uroot -p --host=localhost --databases db1 --no-create-db
Esporta solo i dati, senza aggiungere istruzioni CREATE TABLE.
mysqldump -uroot -p --host=localhost --databases db1 --no-create-info
Esclude tabelle specifiche. Per ignorare più tabelle, ripetere l'opzione per ciascuna tabella. Ogni tabella deve specificare sia il nome del database che della tabella, ad esempio --ignore-table=database.tabella1 --ignore-table=database.tabella2 ...
mysqldump -uroot -p --host=localhost --databases db1 --ignore-table=mysql.user
Utilizza la modalità di inserimento differito (INSERT DELAYED) durante l'esportazione dei dati.
mysqldump -uroot -p --databases db1 --delayed-insert
Utilizzare istruzioni INSERT con più clausole VALUES. Questo riduce le dimensioni del file di esportazione e velocizza l'importazione. Abilitato per impostazione predefinita; utilizzare --skip-extended-insert per disabilitare.
mysqldump -uroot -p --databases db1
mysqldump -uroot -p --databases db1 --skip-extended-insert
6. Esportare e importare dati tra server
mysqldump --host=h1 -uroot -proot --databases db1 | mysql --host=h2 -uroot -proot db2
Questo significa esportare tutti i dati da db1 sul server h1 e importarli in db2 su h2. Il database db2 deve esistere; in caso contrario, si verificherà un errore.
Utilizza una pipe denominata per connettersi a MySQL.
mysqldump -uroot -p --host=localhost --all-databases --pipe
mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp
Abilita la compressione per la comunicazione tra client e server.
mysqldump -uroot -p --all-databases --compress
7. Aggiungi la posizione del binlog e il nome del file del database master al file di esportazione
Questa opzione, quando eseguita su un server slave, equivale all'esecuzione di SHOW SLAVE STATUS.
Impostandolo su 1, viene prodotto un comando CHANGE MASTER nel file di dati.
Impostandolo a 2 vengono aggiunti commenti prima di CHANGE MASTER.
Questa opzione abilita --lock-all-tables a meno che non sia specificato --single-transaction.
Dopo l'esecuzione, --lock-tables viene disabilitato automaticamente. Il valore predefinito per --dump-slave è 1.
mysqldump -uroot -proot --dump-slave=1 --database db1 > /tmp/db1.sql
mysqldump -uroot -proot --dump-slave=2 --database db1 > /tmp/db1.sql
Esporta eventi.
mysqldump -uroot -p --all-databases --events
8. Svuota i registri prima dell'esportazione
Nota: Se si esportano più database (--databases o --all-databases), i log vengono scaricati per ogni singolo database. Se si utilizza --lock-all-tables o --master-data, i log vengono scaricati una sola volta, bloccando tutte le tabelle. Per esportare e scaricare i log contemporaneamente, utilizzare --lock-all-tables o --master-data insieme a --flush-logs.
mysqldump -uroot -p --all-databases --flush-logs
9. Garantire uno stato coerente durante l'esportazione
Prima di esportare i dati, questa opzione emette un'istruzione SQL BEGIN. BEGIN non blocca alcuna applicazione e garantisce la coerenza del database durante l'esportazione. È applicabile solo a motori di archiviazione multiversione (ad esempio, InnoDB). Questa opzione è mutuamente esclusiva rispetto a --lock-tables, poiché LOCK TABLES committa implicitamente qualsiasi transazione in sospeso. Per esportare tabelle di grandi dimensioni, utilizzarla insieme all'opzione --quick.
mysqldump -uroot -p --host=localhost --all-databases --single-transaction
10. Esporta stored procedure e funzioni
mysqldump -uroot -p --host=localhost --all-databases --routines
Un altro metodo: utilizzo di SELECT...INTO OUTFILE per esportare i dati MySQL
SELECT...INTO OUTFILE è una sintassi MySQL utilizzata per esportare i risultati di una query in un file. In MySQL, puoi utilizzare questa istruzione per esportare facilmente i dati in un file di testo.
Utilizzo di base:
SELECT column1, column2, ...
INTO OUTFILE 'file_path'
FROM your_table
WHERE your_conditions;
Esempio:
SELECT id, name, email
INTO OUTFILE '/tmp/user_data.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM users;
Nell'istruzione SQL precedente, abbiamo selezionato le colonne id, nome ed email dalla tabella users e scritto i risultati nel file /tmp/user_data.csv. FIELDS TERMINATED BY ',' specifica il separatore di colonna (virgola), mentre LINES TERMINATED BY '\n' specifica il separatore di riga (a capo).
Note importanti:
L'esecuzione di SELECT...INTO OUTFILE richiede i permessi appropriati e la directory del file di output deve essere un percorso in cui il server MySQL può scrivere.
Nell'esempio seguente, esportiamo i dati dalla tabella runoob_tbl al file /tmp/runoob.txt:
mysql> SELECT * FROM runoob_tbl
-> INTO OUTFILE '/tmp/runoob.txt';
È possibile specificare il formato di output utilizzando le opzioni del comando. L'esempio seguente esporta i dati in formato CSV:
mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n';
Proprietà di SELECT ... INTO OUTFILE:
LOAD DATA INFILE è l'operazione inversa di SELECT ... INTO OUTFILE. Per scrivere i dati del database in un file, utilizzare SELECT ... INTO OUTFILE, e per leggere nuovamente il file nel database, utilizzare LOAD DATA INFILE.
Un'istruzione SELECT nella forma SELECT...INTO OUTFILE 'file_name' scrive le righe selezionate in un file. Poiché il file viene creato sull'host del server, è necessario disporre del privilegio FILE per utilizzare questa sintassi.
Il file di output non deve esistere già, per evitare manomissioni dei dati.
È necessario un account con accesso al login del server per recuperare il file. In caso contrario, SELECT ... INTO OUTFILE non funzionerà.
Sui sistemi UNIX, dopo la creazione del file, esso è leggibile e i suoi permessi sono di proprietà del server MySQL. Ciò significa che, sebbene tu possa leggere il file, potresti non essere in grado di eliminarlo.
Protezione Completa per il Tuo Ecosistema di Database
In un'era in cui la resilienza dei dati è fondamentale, scegliere una soluzione di backup con scalabilità globale e agilità multi-cloud è essenziale. Vinchin Backup & Recovery si distingue per la sua solida protezione di MySQL, progettata appositamente per le aziende internazionali:
✅ Strategie complete di backup
Supporta strategie di backup in tempo reale, backup completo, backup incrementale e backup del log delle transazioni;
✅ Supporto universale per database
Unifica la protezione per MySQL, Oracle, SQL Server e altri ancora in ambienti ibridi (fisici/cloud/VM).
Semplifica la gestione con una singola console per le politiche di backup multi-database.
✅ Rafforzamento anti-ransomware
Archiviazione crittografata dei dati di backup + funzione anti-ransomware per resistere agli attacchi dannosi.
✅ Supporto globale, efficienza locale
Assistenza tecnica 24/7 e licenze ottimizzate per i costi, ideali per PMI e team distribuiti.
Per creare attività di backup del database MySQL con Vinchin, accedere alla pagina Physical Backup > Database Backup > Backup:
1. Selezionare i database da cui eseguire il backup.
2. Seleziona un nodo di backup su cui vuoi che i dati di backup vengano elaborati e archiviati.
3. Configura le strategie di backup in base alle tue esigenze.
4. Esamina e conferma le impostazioni.
Vinchin è affidato da istituzioni finanziarie e infrastrutture IT multinazionali in oltre 60 paesi. Clicca sul pulsante qui sotto per provare la versione di prova gratuita di 60 giorni di Vinchin e scoprire una soluzione di backup dati efficiente e affidabile!
Conclusione
In questo articolo abbiamo esplorato diverse opzioni di mysqldump per esportare in modo efficiente i dati del database MySQL, dalle esportazioni complete del database fino a esportazioni più granulari, come tabelle singole o record specifici. Comprendere questi comandi e le loro funzionalità aiuta a semplificare i processi di backup, migrazione e ripristino del database.
