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.

Option Groups

Some mysqldump options are shorthand for groups of other options:

• Use of --opt is the same as specifying --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset. All of the options that --opt stands for also are on by default because --opt is on by default.
• Use of --compact is the same as specifying --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, and --skip-set-charset options.

To reverse the effect of a group option, uses its --skip-xxx form (--skip-opt or --skip-compact). It is also possible to select only part of the effect of a group option by following it with options that enable or disable specific features. Here are some examples:

• To select the effect of --opt except for some features, use the --skip option for each feature. To disable extended inserts and memory buffering, use --opt --skip-extended-insert --skip-quick. (Actually, --skip-extended-insert --skip-quick is sufficient because --opt is on by default.)
• To reverse --opt for all features except index disabling and table locking, use --skip-opt --disable-keys --lock-tables.

When you selectively enable or disable the effect of a group option, order is important because options are processed first to last. For example, --disable-keys --lock-tables --skip-opt would not have the intended effect; it is the same as --skip-opt by itself.

Row by Row vs. Buffering

mysqldump can retrieve and dump table contents row by row, or it can retrieve the entire content from a table and buffer it in memory before dumping it. Buffering in memory can be a problem if you are dumping large tables. To dump tables row by row, use the --quick option (or --opt, which enables --quick). The --opt option (and hence --quick) is enabled by default, so to enable memory buffering, use --skip-quick.

mysqldump in MariaDB 5.3 and higher

Starting in MariaDB 5.3, mysqldump has been extended to support new enhancements for START TRANSACTION WITH CONSISTENT SNAPSHOT.

mysqldump and old versions of MySQL

If you are using a recent version of mysqldump to generate a dump to be reloaded into a very old MySQL server, you should not use the --opt or --extended-insert option. Use --skip-opt instead.

Options

mysqldump supports the following options, which can be specified on the command line or in the [mysqldump] and [client] option file groups. Default options are read from the following files in the given order:

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

mysqldump also supports the options for processing option files.

The following options may be given as the first argument:

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´, and Empty Values in XML

For a column named column_name, the NULL value, an empty string, and the string value ´NULL´ are distinguished from one another in the output generated by this option as follows.

Beginning with MySQL 5.1.12, the output from the mysql client when run using the --xml option also follows the preceding rules.

Beginning with MySQL 5.1.18, XML output from mysqldump includes the XML namespace, as shown here:

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>

Variables

You can also set the following variables (--variable-name=value) and boolean options {FALSE|TRUE} by using:

Examples:

A common use of mysqldump is for making a backup of an entire database:

shell> mysqldump db_name > backup-file.sql

You can load the dump file back into the server like this:

shell> mysql db_name < backup-file.sql

Or like this:

shell> mysql -e "source /path-to-backup/backup-file.sql" db_name

mysqldump is also very useful for populating databases by copying data from one MySQL server to another:

shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name

It is possible to dump several databases with one command:

shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql

To dump all databases, use the --all-databases option:

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

For InnoDB tables, mysqldump provides a way of making an online backup:

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

This backup acquires a global read lock on all tables (using FLUSH TABLES WITH READ LOCK) at the beginning of the dump. As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH statement is issued, the MySQL server may get stalled until those statements finish. After that, the dump becomes lock free and does not disturb reads and writes on the tables. If the update statements that the MySQL server receives are short (in terms of execution time), the initial lock period should not be noticeable, even with many updates.

For point-in-time recovery (also known as “roll-forward,” when you need to restore an old backup and replay the changes that happened since that backup), it is often useful to rotate the binary log (see Section 5.2.4, “The Binary Log”) or at least know the binary log coordinates to which the dump corresponds:

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

Or:

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

The --master-data and --single-transaction options can be used simultaneously, which provides a convenient way to make an online backup suitable for use prior to point-in-time recovery if tables are stored using the InnoDB storage engine.