mysqldump

Il client mysqldump è un programma di backup scritto originariamente da Igor Romanenko. Serve a creare il dump di un database o di una collezione di databases allo scopo di effettuare un backup o trasferire i dati ad un altro server SQL (non necessariamente un server MySQL). Il dump tipicamente contiene istruzioni SQL per creare le tabelle, popolarle, o entrambe le cose. Tuttavia, mysqldump può anche generare dei file CSV, o in altri formati di testo delimitato, o in XML.

Se si desidera effettuare il backup su un server in cui tutte le tabelle sono di tipo MyISAM, si può in alternativa utilizzare mysqlhotcopy, perché può eseguire i backup e recuperare i dati più rapidamente.

Utilizzo

Ci sono tre modi per invocare mysqldump:

shell> mysqldump [opzioni] nome_db [nome_tab ...]
shell> mysqldump [opzioni] --databases nome_db ...
shell> mysqldump [opzioni] --all-databases

Se non si specifica alcuna tabella dopo nome_db, o se si utilizzano le opzioni --databases o --all-databases, verrà effettuato il dump di tutto il database.

mysqldump per default non copia il database INFORMATION_SCHEMA. In tutte le versioni di MariaDB e in MySQL 5.1.38 e più recenti, mysqldump effettua il dump di INFORMATION_SCHEMA solo se questo viene nominato esplicitamente da riga di comando, sebbene attualmente occorra usare anche l'opzione --skip-lock-tables. Prima di MySQL 5.1.38, mysqldump ignorava silenziosamente INFORMATION_SCHEMA anche se veniva specificato nella riga di comando.

Per ottenere un elenco delle opzioni supportate dalla propria versione di mysqldump, si esegua mysqldump --help.

Gruppi di opzioni

Alcune opzioni di mysqldump sono scorciatoie per gruppi di altre opzioni:

• Usare --opt è come specificare --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick e --set-charset. Tutte queste opzioni sono inoltre attive per default, perché --opt è attiva per default.
• Usare --compact è come specificare --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys e --skip-set-charset.

Per invertire l'effetto di un gruppo di opzioni, si usa la sua forma --skip-xxx (--skip-opt o --skip-compact). E' anche possibile selezionare soltanto una parte dell'effetto di un gruppo di opzioni, facendo seguire ad esso le opzioni che abilitano o disabilitano specifiche funzionalità. Ecco alcuni esempi:

• Per selezionare l'effetto di --opt eccetto alcune funzionalità, si possono usare le opzioni --skip corrispondenti. Per disabilitare le insert estese e il buffering della memoria, si può usare --opt --skip-extended-insert --skip-quick. (In effetti --skip-extended-insert --skip-quick sarebbe sufficiente, dato che --opt è attivo per default.)
• Per invertire --opt per tutte le funzionalità eccetto la disabilitazione degli indici e i lock sulle tabelle, si può usare --skip-opt --disable-keys --lock-tables.

Quando si abilita o disabilita selettivamente l'effetto di un gruppo di opzioni, l'ordine è importante perché i parametri vengono letti ed eseguiti dal primo all'ultimo. Per esempio, --disable-keys --lock-tables --skip-opt non avrebbe l'effetto desiderato; ha lo stesso effetto di --skip-opt.

Riga per riga o buffering?

mysqldump può estrarre e copiare il contenuto delle tabelle riga per riga, oppure può estrarlo interamente dalla tabella e immetterlo in un buffer di memoria prima di copiarlo. Usare il buffering può essere un problema quando si copiano grandi tabelle. Per effettuare il dump riga per riga, si può usare l'opzione --quick (o --opt, che abilita --quick). L'opzione --opt (e quindi anche --quick) è abilitata per default, perciò per abilitare il buffering, si usi --skip-quick.

mysqldump in MariaDB 5.3 e superiore

A partire da MariaDB 5.3, mysqldump supporta i nuovi miglioramenti a START TRANSACTION WITH CONSISTENT SNAPSHOT.

mysqldump e le vecchie versioni di MySQL

Se si utilizza una versione recente di mysqldump per generare un dump da caricare in un server MySQL molto vecchio, si dovrebbe evitare di usare le opzioni --opt e --extended-insert. Si usi invece --skip-opt.

Opzioni

mysqldump supporta le seguenti opzioni, che possono essere specificate dalla riga di comando o nei file di configurazione nei gruppi [mysqldump] e [client]. Le opzioni predefinite vengono lette dai seguenti file, nell'ordine:

1. /etc/my.cnf
2. /etc/mysql/my.cnf
3. /usr/etc/my.cnf
4. ~/.my.cnf

mysqldump supporta anche le opzioni per elaborare i file di configurazione.

Le seguenti opzioni possono essere indicate come primo argomento:

mysql> STOP SLAVE SQL_THREAD;

mysql> SHOW SLAVE STATUS;

shell> mysqldump --master-data=2 --all-databases > dumpfile

mysql> START SLAVE;

shell> mysql < dumpfile

mysql> CHANGE MASTER TO MASTER_LOG_FILE = ´file_name´, MASTER_LOG_POS = file_pos;

-- Dump completed on DATE

--where="user=´jimf´"

-w"userid>1"

-w"userid<1"

NULL, ´NULL´ e i valori vuoti in XML

Per una colonna chiamata nome_colonna, il valore NULL, una stringa vuota e la stringa ´NULL´ si distinguono l'uno dall'altro nel modo seguente.

A cominciare da MySQL 5.1.12, anche l'output del client mysql avviato con l'opzione --xml segue le regole sopra indicate.

A partire da MySQL 5.1.18, l'output XML prodotto da mysqldump include i namespace XML, come mostrato di seguito:

shell> mysqldump --xml -u root world City
<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="world">
<table_structure name="City">
<field Field="ID" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" />
<field Field="Name" Type="char(35)" Null="NO" Key="" Default="" Extra="" />
<field Field="CountryCode" Type="char(3)" Null="NO" Key="" Default="" Extra="" />
<field Field="District" Type="char(20)" Null="NO" Key="" Default="" Extra="" />
<field Field="Population" Type="int(11)" Null="NO" Key="" Default="0" Extra="" />
<key Table="City" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="ID"
Collation="A" Cardinality="4079" Null="" Index_type="BTREE" Comment="" />
<options Name="City" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="4079"
Avg_row_length="67" Data_length="273293" Max_data_length="18858823439613951"
Index_length="43008" Data_free="0" Auto_increment="4080"
Create_time="2007-03-31 01:47:01" Update_time="2007-03-31 01:47:02"
Collation="latin1_swedish_ci" Create_options="" Comment="" />
</table_structure>
<table_data name="City">
<row>
<field name="ID">1</field>
<field name="Name">Kabul</field>
<field name="CountryCode">AFG</field>
<field name="District">Kabol</field>
<field name="Population">1780000</field>
</row>
...
<row>
<field name="ID">4079</field>
<field name="Name">Rafah</field>
<field name="CountryCode">PSE</field>
<field name="District">Rafah</field>
<field name="Population">92020</field>
</row>
</table_data>
</database>
</mysqldump>

Variabili

E' anche possibile impostare le seguenti variabili (--variable-name=valore) e le opzioni booleane {FALSE|TRUE} usando:

Esempi

Un uso comune di mysqldump è per ottenere un backup di un intero database:

shell> mysqldump nome_db > file-di-backup.sql

E' possibile ricaricare il file di dump nel server così:

shell> mysql nome_db < file-di-backup.sql

Oppure così:

shell> mysql -e "source /path-del-backup/file-di-backup.sql" nome_db

mysqldump è molto utile anche per popolare i database copiando i dati da un server MariaDB ad un altro:

shell> mysqldump --opt nome_db | mysql --host=host_remoto -C nome_db

E' possibile esportare diversi database con un solo comando:

shell> mysqldump --databases nome_db1 [nome_db2 ...] > miei_db.sql

Per esportare tutti i database si usa l'opzione --all-databases:

shell> mysqldump --all-databases > tutti_i_db.sql

Per le tabelle InnoDB, mysqldump fornisce un modo per creare un backup in linea:

shell> mysqldump --all-databases --single-transaction tutti_i_db.sql

Questo backup acquisisce un lock in lettura globale su tutte le tabelle (usando FLUSH TABLES WITH READ LOCK)all'inizio del dump. Subito dopo il lock è acquisito, le coordinate del log binario vengono lette e il lock rilasciato. Quando il comando FLUSH viene eseguito, se sono in esecuzione delle lunghe istruzioni che modificano i dati, il server MySQL potrebbe essere bloccato fino a quando quelle istruzioni non terminano. Dopodiché, il dump sarà libero dai lock e non disturberà le letture e le modifiche sulle tabelle. Se le istruzioni che modificano i dati sono brevi anche il lock iniziale dovrebbe essere breve, anche nel caso in cui le modifiche siano molte.

Per un recupero "point-in-time" (chiamato anche "roll-forward", quando si desidera recuperare un vecchio backup e ripetere i cambiamenti che sono avvenuti da quel momento in poi), è spesso utile ruotare i lock binari (si veda la Sezione 5.2.4, “The Binary Log”) o almeno conoscere le coordinate del log binario a cui corrisponde il dump:

shell> mysqldump --all-databases --master-data=2 > tutti_i_db.sql

Oppure:

shell> mysqldump --all-databases --flush-logs --master-data=2 > tutti_i_db.sql

Le opzioni --master-data e --single-transaction possono essere usate simultaneamente; è un modo comodo per effettuare un backup in linea, che è adatto al recupero point-in-time sopra spiegato se le tabelle sono di tipo InnoDB.